PostgreSQL DBA(97) - System Conf(huge_pages)
PostgreSQL提供了参数huge_pages用以使用OS的huge pages，默认为try，即可用就用，用不了就不用。使用HUGE PAGE其中一个原因是因为HUGE PAGE是常驻内存的，不会被交换出去。
In a virtual memory system, the tables store the mappings between virtual addresses and physical addresses. When the system needs to access a virtual memory location, it uses the page tables to translate the virtual address to a physical address. Using huge pages means that the system needs to load fewer such mappings into the Translation Lookaside Buffer (TLB), which is the cache of page tables on a CPU that speeds up the translation of virtual addresses to physical addresses. Enabling the HugePages feature allows the kernel to use hugetlb entries in the TLB that point to huge pages. The hugetbl entries mean that the TLB entries can cover a larger address space, requiring many fewer entries to map the SGA, and releasing entries that can map other portions of the address space.
With HugePages enabled, the system uses fewer page tables, reducing the overhead for maintaining and accessing them. Huges pages remain pinned in memory and are not replaced, so the kernel swap daemon has no work to do in managing them, and the kernel does not need to perform page table lookups for them. The smaller number of pages reduces the overhead involved in performing memory operations, and also reduces the likelihood of a bottleneck when accessing page tables.
测试
机器配置为4C，4G内存，PG共享缓存设置为1G。
1.不使用huge_pages，client_min_messages等为默认参数
alter system set huge_pages=try;
alter system set client_min_messages=notice;
alter system set update_process_title=on;
alter system set track_activities=on;
-----------------------------------------------------------------------------------------./runSQL.sh props.pg sqlTableDrops./runSQL.sh props.pg sqlTableCreates./runLoader.sh props.pg numwarehouses 32./runSQL.sh props.pg sqlIndexCreatesecho 3 > /proc/sys/vm/drop_cachespg_ctl stopsysctl -w vm.nr_hugepages=0grep -i huge /proc/meminfo[pg12@localhost ~]$ grep -i huge /proc/meminfoAnonHugePages: 2048 kBHugePages_Total: 0HugePages_Free: 0HugePages_Rsvd: 0HugePages_Surp: 0Hugepagesize: 2048 kBpg_ctl start测试结果
2019-09-25 17:27:04,687 INFO - Term-00, Measured tpmC (NewOrders) = 7991.69 2019-09-25 17:27:04,687 INFO - Term-00, Measured tpmTOTAL = 17646.94 2019-09-25 17:27:04,687 INFO - Term-00, Session Start = 2019-09-25 17:22:042019-09-25 17:27:04,687 INFO - Term-00, Session End = 2019-09-25 17:27:042019-09-25 17:27:04,687 INFO - Term-00, Transaction Count = 882792.不使用huge_pages，client_min_messages为error，其他调整为off
alter system set huge_pages=try;
alter system set client_min_messages=error;
alter system set update_process_title=off;
alter system set track_activities=off;
-----------------------------------------------------------------------------------------./runSQL.sh props.pg sqlTableDrops./runSQL.sh props.pg sqlTableCreates./runLoader.sh props.pg numwarehouses 32./runSQL.sh props.pg sqlIndexCreatesalter system set huge_pages=try;alter system set client_min_messages=error;alter system set update_process_title=off;alter system set track_activities=off;echo 3 > /proc/sys/vm/drop_cachespg_ctl restart测试结果
2019-09-25 16:09:10,721 INFO - Term-00, Measured tpmC (NewOrders) = 7963.92 2019-09-25 16:09:10,721 INFO - Term-00, Measured tpmTOTAL = 17692.15 2019-09-25 16:09:10,721 INFO - Term-00, Session Start = 2019-09-25 16:04:102019-09-25 16:09:10,721 INFO - Term-00, Session End = 2019-09-25 16:09:102019-09-25 16:09:10,721 INFO - Term-00, Transaction Count = 884943.使用huge_pages，client_min_messages为error，其他调整为off
alter system set huge_pages=on;
alter system set client_min_messages=error;
alter system set update_process_title=off;
alter system set track_activities=off;
-----------------------------------------------------------------------------------------./runSQL.sh props.pg sqlTableDrops./runSQL.sh props.pg sqlTableCreates./runLoader.sh props.pg numwarehouses 32./runSQL.sh props.pg sqlIndexCreatesalter system set huge_pages=on;alter system set client_min_messages=error;alter system set update_process_title=off;alter system set track_activities=off;pg_ctl stopecho 3 > /proc/sys/vm/drop_cachessysctl -w vm.nr_hugepages=640sysctl -a|grep nr_hugepagesgrep -i huge /proc/meminfopg_ctl start-------- 过程[root@localhost ~]# echo 3 > /proc/sys/vm/drop_caches[root@localhost ~]# free -m total used free shared buff/cache availableMem: 3782 98 3531 43 152 3448Swap: 1023 77 946[root@localhost ~]# echo 3 > /proc/sys/vm/drop_caches[root@localhost ~]# sysctl -w vm.nr_hugepages=640vm.nr_hugepages = 640[root@localhost ~]# sysctl -a|grep nr_hugepagessysctl: reading key "net.ipv6.conf.all.stable_secret"sysctl: reading key "net.ipv6.conf.default.stable_secret"sysctl: reading key "net.ipv6.conf.ens33.stable_secret"sysctl: reading key "net.ipv6.conf.ens37.stable_secret"sysctl: reading key "net.ipv6.conf.lo.stable_secret"vm.nr_hugepages = 640vm.nr_hugepages_mempolicy = 640[root@localhost ~]# cat /proc/meminfo |grep -i hugeAnonHugePages: 2048 kBHugePages_Total: 640HugePages_Free: 640HugePages_Rsvd: 0HugePages_Surp: 0Hugepagesize: 2048 kB[root@localhost ~]# free -m total used free shared buff/cache availableMem: 3782 1365 2335 1 82 2237Swap: 1023 77 946[root@localhost ~]# ---- pg_ctl restart[root@localhost ~]# free -m total used free shared buff/cache availableMem: 3782 1375 2311 1 96 2220Swap: 1023 77 946[root@localhost ~]# cat /proc/meminfo |grep -i hugeAnonHugePages: 2048 kBHugePages_Total: 640HugePages_Free: 615HugePages_Rsvd: 512HugePages_Surp: 0Hugepagesize: 2048 kB[root@localhost ~]# ./runBenchmark.sh props.pg[root@localhost ~]# grep -i huge /proc/meminfoAnonHugePages: 2048 kBHugePages_Total: 640HugePages_Free: 104HugePages_Rsvd: 1HugePages_Surp: 0Hugepagesize: 2048 kB测试结果
2019-09-25 17:05:17,586 INFO - Term-00, Measured tpmC (NewOrders) = 7905.19 2019-09-25 17:05:17,586 INFO - Term-00, Measured tpmTOTAL = 17565.52 2019-09-25 17:05:17,586 INFO - Term-00, Session Start = 2019-09-25 17:00:172019-09-25 17:05:17,586 INFO - Term-00, Session End = 2019-09-25 17:05:172019-09-25 17:05:17,586 INFO - Term-00, Transaction Count = 87860结论
从上面的测试结果来说，三者并没有太大的差异，可以认为是扰动引起的变化而已。
参考资料
Page table
PostgreSQL Huge Page 使用建议 - 大内存主机、实例注意
Tune Linux Kernel Parameters For PostgreSQL Optimization
