作者
digoal
日期
2021-09-22
标签
PostgreSQL , guc , runtime , hugepage , shared memory
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=43c1c4f65eab77bcfc4f535a7e9ac0421e0cf2a5
使用PostgreSQL hugepage功能时, 有个比较讨厌的问题是计算hugepage的需求量非常麻烦, 需要先启动实例, 然后从操作系统层面进行计算, 然后配置hugepage 然后再启动. 方法如下:
为什么需要这么繁琐呢, 为什么不能直接使用PG的shared_buffers折算huge page? 因为PG除了这部分动态内存, 还有一些动态库需要分配内存, 这些内存并没有被算在shared_buffers, 例如preload shared libraries, 需要数据库启动后才会分配这部分共享内存.
PG 15终于解决了这个问题, 可以在启动实例之前计算共享内存和huge page的使用量. 增加了runtime guc参数来支持这个需求.
man postgres
-C name
Prints the value of the named run-time parameter, and exits. (See the -c option above for details.) This returns values from postgresql.conf, modified by any parameters supplied in this
invocation. It does not reflect parameters supplied when the cluster was started.
This can be used on a running server for most parameters. However, the server must be shut down for some runtime-computed parameters (e.g., shared_memory_size,
shared_memory_size_in_huge_pages, and wal_segment_size).
This option is meant for other programs that interact with a server instance, such as pg_ctl(1), to query configuration parameter values. User-facing applications should instead use
SHOW or the pg_settings view.
https://www.postgresql.org/docs/devel/runtime-config-preset.html
shared_memory_size (integer)
- Reports the size of the main shared memory area, rounded up to the nearest megabyte.
shared_memory_size_in_huge_pages (integer)
- Reports the number of huge pages that are needed for the main shared memory area based on the specified huge_page_size. If huge pages are not supported, this will be -1.
- This setting is supported only on Linux. It is always set to -1 on other platforms. For more details about using huge pages on Linux, see Section 19.4.5.
wal_segment_size (integer)
- Reports the size of write ahead log segments. The default value is 16MB. See Section 30.5 for more information.
src/include/utils/guc.h
/*
* GUC_RUNTIME_COMPUTED is intended for runtime-computed GUCs that are only
* available via 'postgres -C' if the server is not running.
*/
#define GUC_RUNTIME_COMPUTED 0x200000
机器的hugepage页大小如下
cat /proc/meminfo |grep -i huge
AnonHugePages: 16384 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
实例关闭状态, 使用-C计算runtime参数值
postgres -D $PGDATA -C shared_memory_size_in_huge_pages
71
2021-09-22 09:56:05.437 CST [22608] LOG: database system is shut down
把shared_buffers改成4GB, 重新计算shared_memory_size_in_huge_pages
postgres@iZbp15sgewxe2ioglp30z4Z-> vi postgresql.conf
postgres@iZbp15sgewxe2ioglp30z4Z-> postgres -D $PGDATA -C shared_memory_size_in_huge_pages
2106
2021-09-22 09:56:41.675 CST [22698] LOG: database system is shut down
计算shared_memory_size
postgres@iZbp15sgewxe2ioglp30z4Z-> postgres -D $PGDATA -C shared_memory_size
4211
2021-09-22 09:57:59.979 CST [22881] LOG: database system is shut down
修改shared_preload_libraries
postgres@iZbp15sgewxe2ioglp30z4Z-> vi postgresql.conf
shared_preload_libraries='auto_explain,pg_stat_statements'
计算shared_memory_size_in_huge_pages, 需求多了1页
postgres@iZbp15sgewxe2ioglp30z4Z-> postgres -D $PGDATA -C shared_memory_size_in_huge_pages
2107
2021-09-22 09:58:41.081 CST [22995] LOG: database system is shut down
启动状态下, 无法使用postgres -C计算runtime guc参数, 但是可以连到数据库直接show.
pg_ctl start
waiting for server to start....2021-09-22 09:59:29.727 CST [23140] LOG: starting PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-09-22 09:59:29.728 CST [23140] LOG: listening on IPv6 address "::1", port 1921
2021-09-22 09:59:29.728 CST [23140] LOG: listening on IPv4 address "127.0.0.1", port 1921
2021-09-22 09:59:29.731 CST [23140] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921"
2021-09-22 09:59:29.733 CST [23143] LOG: database system was shut down at 2021-09-22 09:55:44 CST
2021-09-22 09:59:29.734 CST [23140] LOG: database system is ready to accept connections
done
server started
postgres@iZbp15sgewxe2ioglp30z4Z-> postgres -D $PGDATA -C shared_memory_size_in_huge_pages
2021-09-22 09:59:32.401 CST [23150] FATAL: lock file "postmaster.pid" already exists
2021-09-22 09:59:32.401 CST [23150] HINT: Is another postmaster (PID 23140) running in data directory "/data01/pg15/1921/pg_root"?
postgres=# show shared_memory_size_in_huge_pages ;
shared_memory_size_in_huge_pages
----------------------------------
2107
(1 row)
- data_checksums
- shared_memory_size
- shared_memory_size_in_huge_pages
- data_directory_mode
- wal_segment_size
src/backend/utils/misc/guc.c
{
{"data_checksums", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows whether data checksums are turned on for this cluster."),
NULL,
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_RUNTIME_COMPUTED
},
&data_checksums,
false,
NULL, NULL, NULL
},
{
{"shared_memory_size", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the size of the server's main shared memory area (rounded up to the nearest MB)."),
NULL,
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_UNIT_MB | GUC_RUNTIME_COMPUTED
},
&shared_memory_size_mb,
0, 0, INT_MAX,
NULL, NULL, NULL
},
{
{"shared_memory_size_in_huge_pages", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the number of huge pages needed for the main shared memory area."),
gettext_noop("-1 indicates that the value could not be determined."),
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_RUNTIME_COMPUTED
},
&shared_memory_size_in_huge_pages,
-1, -1, INT_MAX,
NULL, NULL, NULL
},
{
{"data_directory_mode", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the mode of the data directory."),
gettext_noop("The parameter value is a numeric mode specification "
"in the form accepted by the chmod and umask system "
"calls. (To use the customary octal format the number "
"must start with a 0 (zero).)"),
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_RUNTIME_COMPUTED
},
&data_directory_mode,
0700, 0000, 0777,
NULL, NULL, show_data_directory_mode
},
{
{"wal_segment_size", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the size of write ahead log segments."),
NULL,
GUC_UNIT_BYTE | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_RUNTIME_COMPUTED
},
&wal_segment_size,
DEFAULT_XLOG_SEG_SIZE,
WalSegMinSize,
WalSegMaxSize,
NULL, NULL, NULL
},
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
