20240619_pg17新特性_SLRU缓存参数配置

原创惠星星 2024-06-19

630

内容概述

本文对pg17新特性SLRU缓存参数配置的特性进行分析，对比pg16的变化，调试参数与缓存分配算法。

特性介绍

从pg17开始SLRU缓存大小可以通过服务器参数进行配置，pg17之前的版本SLRU缓存参数是数据库自动计算获取，用户无法进行手工变更。
Allow the SLRU cache sizes to be configured (Andrey Borodin, Dilip Kumar)

The new server variables are commit_timestamp_buffers, multixact_member_buffers, multixact_offset_buffers, notify_buffers, serializable_buffers, subtransaction_buffers, and transaction_buffers.

新增参数

通过与pg16对比，在postgresql.conf中可以发现pg17新增加以下参数及默认值，通过以下参数可以根据用户需求变更SLRU缓存的大小。
# - SLRU Buffers (change requires restart) -

#commit_timestamp_buffers = 0           # memory for pg_commit_ts (0 = auto)
#multixact_offset_buffers = 16          # memory for pg_multixact/offsets
#multixact_member_buffers = 32          # memory for pg_multixact/members
#notify_buffers = 16                    # memory for pg_notify
#serializable_buffers = 32              # memory for pg_serial
#subtransaction_buffers = 0             # memory for pg_subtrans (0 = auto)
#transaction_buffers = 0                # memory for pg_xact (0 = auto)

缓存名称的变更

使用以下SQL脚本，对pg16和pg17进行分析，本文仅对SLRU缓存的7个参数进行分析对比。
postgres=# select name,off,size,allocated_size,off+allocated_size r_off FROM pg_shmem_allocations order by off;

和pg16.3对比，pg17beta1 中SLRU缓存值在pg_shmem_allocations中发生变化
 Xact                	->	 transaction         
 CommitTs            	->	 commit_timestamp    
 CommitTs shared     	->	 CommitTs shared     
 Subtrans            	->	 subtransaction      
 MultiXactOffset     	->	 multixact_offset  
 MultiXactMember        ->   	 multixact_member
 Notify 	        ->	 notify

transaction_buffers参数自动调节算法

transaction_buffers默认值

transaction_buffers默认值为0(auto)，自动优化运行值。
[postgres@pgdb1 ~]$ cd $PGDATA
[postgres@pgdb1 data]$ cat postgresql.conf |grep transaction_buffers
#subtransaction_buffers = 0             # memory for pg_subtrans (0 = auto)
#transaction_buffers = 0                # memory for pg_xact (0 = auto)
[postgres@pgdb1 data]$

transaction_buffers默认值算法

如果transaction_buffers参数设置为自动优化，则运行值为每1G的shared buffers分配2MB，否则配置值为16的整数倍到允许的最大值。
### backend\access\transam/clog.c:760
/*
 * Number of shared CLOG buffers.
 *
 * If asked to autotune, use 2MB for every 1GB of shared buffers, up to 8MB.
 * Otherwise just cap the configured amount to be between 16 and the maximum
 * allowed.
 */
static int
CLOGShmemBuffers(void)
{
	/* auto-tune based on shared buffers */
	if (transaction_buffers == 0)
		return SimpleLruAutotuneBuffers(512, 1024);

	return Min(Max(16, transaction_buffers), CLOG_MAX_ALLOWED_BUFFERS);
}

transaction_buffers 默认自动优化: shared_buffers=128M

[postgres@pgdb1 data]$ cat $PGDATA/postgresql.conf |egrep '^shared_buffers|^transaction_buffers'
[postgres@pgdb1 data]$

[postgres@pgdb1 data]$ psql
psql (17beta1)
Type "help" for help.

postgres=# show transaction_buffers ;
 transaction_buffers
---------------------
 256kB
(1 row)

postgres=# show shared_buffers ;
 shared_buffers
----------------
 128MB
(1 row)

postgres=#

transaction_buffers 默认自动优化: shared_buffers=1024MB

[postgres@pgdb1 data]$ cat $PGDATA/postgresql.conf |egrep '^shared_buffers|^transaction_buffers'
shared_buffers = 1024MB                 # min 128kB
[postgres@pgdb1 data]$ pg_ctl start

postgres=# show shared_buffers ;
 shared_buffers
----------------
 1GB
(1 row)

postgres=# ^C
postgres=# show transaction_buffers ;
 transaction_buffers
---------------------
 2MB
(1 row)

postgres=#

transaction_buffers 默认自动优化: shared_buffers=4GB

[postgres@pgdb1 data]$ cat $PGDATA/postgresql.conf |egrep '^shared_buffers|^transaction_buffers'
shared_buffers = 4GB            # min 128kB
[postgres@pgdb1 data]$ pg_ctl start

[postgres@pgdb1 data]$ psql
psql (17beta1)
Type "help" for help.

postgres=# show shared_buffers ;
 shared_buffers
----------------
 4GB
(1 row)

postgres=# show transaction_buffers ;
 transaction_buffers
---------------------
 8MB
(1 row)

postgres=#

transaction_buffers 默认自动优化: shared_buffers=5GB

[postgres@pgdb1 data]$ cat $PGDATA/postgresql.conf |egrep '^shared_buffers|^transaction_buffers'
shared_buffers = 5GB            # min 128kB
[postgres@pgdb1 data]$ pg_ctl start

[postgres@pgdb1 data]$ psql
psql (17beta1)
Type "help" for help.

postgres=# show shared_buffers ;
 shared_buffers
----------------
 5GB
(1 row)

postgres=# show transaction_buffers ;
 transaction_buffers
---------------------
 8MB
(1 row)

postgres=#

transaction_buffers最小值测试: 10

transaction_buffers = 10 

[postgres@pgdb1 data]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@pgdb1 data]$ pg_ctl start
waiting for server to start....2024-06-19 09:33:28.758 CST [21923] LOG:  invalid value for parameter "transaction_buffers": 10
2024-06-19 09:33:28.758 CST [21923] DETAIL:  "transaction_buffers" must be a multiple of 16
2024-06-19 09:33:28.758 CST [21923] FATAL:  configuration file "/u01/app/pg/17beta1/data/postgresql.conf" contains errors
 stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@pgdb1 data]$

transaction_buffers最小值测试: 16

transaction_buffers = 16

[postgres@pgdb1 data]$ pg_ctl start
waiting for server to start....2024-06-19 09:36:15.553 CST [21935] LOG:  starting PostgreSQL 17beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2024-06-19 09:36:15.553 CST [21935] LOG:  listening on IPv6 address "::1", port 5417
2024-06-19 09:36:15.553 CST [21935] LOG:  listening on IPv4 address "127.0.0.1", port 5417
2024-06-19 09:36:15.554 CST [21935] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5417"
2024-06-19 09:36:15.555 CST [21938] LOG:  database system was shut down at 2024-06-19 09:33:25 CST
2024-06-19 09:36:15.557 CST [21935] LOG:  database system is ready to accept connections
 done
server started
[postgres@pgdb1 data]$

transaction_buffers 必须为16的整数倍: 17

transaction_buffers = 17
[postgres@pgdb1 data]$ pg_ctl start
waiting for server to start....2024-06-19 09:35:01.204 CST [21930] LOG:  invalid value for parameter "transaction_buffers": 17
2024-06-19 09:35:01.204 CST [21930] DETAIL:  "transaction_buffers" must be a multiple of 16
2024-06-19 09:35:01.204 CST [21930] FATAL:  configuration file "/u01/app/pg/17beta1/data/postgresql.conf" contains errors
 stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@pgdb1 data]$

transaction_buffers 手工设置最大值算法

### backend\access\transam/clog.c:72

/* We need two bits per xact, so four xacts fit in a byte */
#define CLOG_BITS_PER_XACT	2
#define CLOG_XACTS_PER_BYTE 4
#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
#define CLOG_XACT_BITMASK	((1 << CLOG_BITS_PER_XACT) - 1)

/*
 * Because space used in CLOG by each transaction is so small, we place a
 * smaller limit on the number of CLOG buffers than SLRU allows.  No other
 * SLRU needs this.
 */
#define CLOG_MAX_ALLOWED_BUFFERS \
	Min(SLRU_MAX_ALLOWED_BUFFERS, \
		(((MaxTransactionId / 2) + (CLOG_XACTS_PER_PAGE - 1)) / CLOG_XACTS_PER_PAGE))
		
#define MaxTransactionId			((TransactionId) 0xFFFFFFFF)

/*
 * To avoid overflowing internal arithmetic and the size_t data type, the
 * number of buffers must not exceed this number.
 */
#define SLRU_MAX_ALLOWED_BUFFERS ((1024 * 1024 * 1024) / BLCKSZ)


### transaction_buffers默认最大值为：65536
postgres=# select ((4294967295 / 2) + (4*8192 - 1)) / (4*8192);
 ?column?
----------
    65536
(1 row)

### 设置参数大于最大值
[postgres@pgdb1 ~]$ cat $PGDATA/postgresql.conf |egrep '^shared_buffers|^transaction_buffers'
transaction_buffers = 65552             # memory for pg_xact (0 = auto)
shared_buffers = 5GB            # min 128kB
[postgres@pgdb1 ~]$

[postgres@pgdb1 ~]$ psql
psql (17beta1)
Type "help" for help.

postgres=# show shared_buffers ;
 shared_buffers
----------------
 5GB
(1 row)

postgres=# show transaction_buffers ;
 transaction_buffers
---------------------
 524416kB
(1 row)

postgres=# select 524416/8;
 ?column?
----------
    65552
(1 row)

postgres=#



###疑问
通过计算transaction_buffers最大值为：65536，但通过测试可以设置为更大的值如：65552。


[postgres@pgdb1 data]$ gdb $(which postgres)
(gdb) b CLOGShmemSize
Breakpoint 1 at 0x547848: file clog.c, line 783.
(gdb) r
Breakpoint 1, CLOGShmemSize () at clog.c:783
783             return SimpleLruShmemSize(CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE);
(gdb) p CLOGShmemBuffers()
$1 = 65536
(gdb)

###小结
实际内存计算还是按65536计算的

为什么transaction_buffers实际分配值比参数设置大？

[postgres@pgdb1 data]$ psql
psql (17beta1)
Type "help" for help.

postgres=# show transaction_buffers ;
 transaction_buffers
---------------------
 256kB
(1 row)

postgres=# select allocated_size  FROM pg_shmem_allocations where name='transaction';
 allocated_size
----------------
         529664
(1 row)

postgres=#

### 函数调用栈
SimpleLruShmemSize <- CLOGShmemBuffers <- CLOGShmemSize <- ipci.c:127 

1. backend\storage\ipc\ipci.c:127
size = add_size(size, CLOGShmemSize());

2. backend\access\transam/clog.c:CLOGShmemSize
/*
 * Initialization of shared memory for CLOG
 */
Size
CLOGShmemSize(void)
{
	return SimpleLruShmemSize(CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE);
}

3. CLOGShmemBuffers
/*
 * Number of shared CLOG buffers.
 *
 * If asked to autotune, use 2MB for every 1GB of shared buffers, up to 8MB.
 * Otherwise just cap the configured amount to be between 16 and the maximum
 * allowed.
 */
static int
CLOGShmemBuffers(void)
{
	/* auto-tune based on shared buffers */
	if (transaction_buffers == 0)
		return SimpleLruAutotuneBuffers(512, 1024);

	return Min(Max(16, transaction_buffers), CLOG_MAX_ALLOWED_BUFFERS);
}

4. SimpleLruShmemSize
/*
 * Initialization of shared memory
 */

Size
SimpleLruShmemSize(int nslots, int nlsns)
{
	int			nbanks = nslots / SLRU_BANK_SIZE;
	Size		sz;

	Assert(nslots <= SLRU_MAX_ALLOWED_BUFFERS);
	Assert(nslots % SLRU_BANK_SIZE == 0);

	/* we assume nslots isn't so large as to risk overflow */
	sz = MAXALIGN(sizeof(SlruSharedData));
	sz += MAXALIGN(nslots * sizeof(char *));	/* page_buffer[] */
	sz += MAXALIGN(nslots * sizeof(SlruPageStatus));	/* page_status[] */
	sz += MAXALIGN(nslots * sizeof(bool));	/* page_dirty[] */
	sz += MAXALIGN(nslots * sizeof(int64)); /* page_number[] */
	sz += MAXALIGN(nslots * sizeof(int));	/* page_lru_count[] */
	sz += MAXALIGN(nslots * sizeof(LWLockPadded));	/* buffer_locks[] */
	sz += MAXALIGN(nbanks * sizeof(LWLockPadded));	/* bank_locks[] */
	sz += MAXALIGN(nbanks * sizeof(int));	/* bank_cur_lru_count[] */

	if (nlsns > 0)
		sz += MAXALIGN(nslots * nlsns * sizeof(XLogRecPtr));	/* group_lsn[] */

	return BUFFERALIGN(sz) + BLCKSZ * nslots;
}

### 小结
transaction_buffers实际分配值比参数设置大，由于SimpleLruShmemSize函数中添加了 page_buffer/page_status/page_dirty/page_number/page_lru_count/buffer_locks/bank_locks/bank_cur_lru_count 结构的空间。

总结

1. 从pg17开始SLRU缓存大小可以通过服务器参数进行配置，pg17之前的版本SLRU缓存参数是数据库自动计算获取，用户无法进行手工变更。
2. pg17中新增以下服务SLRU缓存参数: commit_timestamp_buffers, multixact_member_buffers, multixact_offset_buffers, notify_buffers, serializable_buffers, subtransaction_buffers, and transaction_buffers.
3. 和pg16.3对比，pg17beta1 中SLRU缓存值在pg_shmem_allocations中发生变化
 Xact                	->	 transaction         
 CommitTs            	->	 commit_timestamp    
 CommitTs shared     	->	 CommitTs shared     
 Subtrans            	->	 subtransaction      
 MultiXactOffset     	->	 multixact_offset    
 MultiXactMember        ->	 multixact_member
 Notify 		->	 notify
4. transaction_buffers默认值为0(auto)，自动优化运行值,运行值为每1G的shared buffers分配2MB.
5. transaction_buffers手工设置的范围为 16的整数倍到CLOG_MAX_ALLOWED_BUFFERS，经计算最大默认值为：65536，如果设置值大于最大值时，参数查询显示为设置的值，但内存分配时以最大值进行计算分配。
6. transaction_buffers值的单位为 blocks数量;
7. transaction_buffers实际分配值比参数设置大，由于SimpleLruShmemSize函数中添加了 page_buffer/page_status/page_dirty/page_number/page_lru_count/buffer_locks/bank_locks/bank_cur_lru_count 结构的空间。

墨力计划

最后修改时间：2024-06-20 10:11:26

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

20240619_pg17新特性_SLRU缓存参数配置

内容概述

特性介绍

新增参数

缓存名称的变更

transaction_buffers参数自动调节算法

transaction_buffers默认值

transaction_buffers默认值算法

transaction_buffers 默认自动优化: shared_buffers=128M

transaction_buffers 默认自动优化: shared_buffers=4GB

transaction_buffers 默认自动优化: shared_buffers=5GB

transaction_buffers最小值测试: 10

transaction_buffers最小值测试: 16

transaction_buffers 必须为16的整数倍: 17

transaction_buffers 手工设置最大值算法

为什么transaction_buffers实际分配值比参数设置大？

总结

评论