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 sqlIndexCreates
echo 3 > /proc/sys/vm/drop_caches
pg_ctl stop
sysctl -w vm.nr_hugepages=0
grep -i huge /proc/meminfo
[pg12@localhost ~]$ grep -i huge /proc/meminfo
AnonHugePages:      2048 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
pg_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:04
2019-09-25 17:27:04,687  INFO - Term-00, Session End       = 2019-09-25 17:27:04
2019-09-25 17:27:04,687  INFO - Term-00, Transaction Count = 88279

2.不使用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 sqlIndexCreates
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;
echo 3 > /proc/sys/vm/drop_caches
pg_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:10
2019-09-25 16:09:10,721  INFO - Term-00, Session End       = 2019-09-25 16:09:10
2019-09-25 16:09:10,721  INFO - Term-00, Transaction Count = 88494

3.使用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 sqlIndexCreates
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;
pg_ctl stop
echo 3 > /proc/sys/vm/drop_caches
sysctl -w vm.nr_hugepages=640
sysctl -a|grep nr_hugepages
grep -i huge /proc/meminfo
pg_ctl start
-------- 过程
[root@localhost ~]# echo 3 > /proc/sys/vm/drop_caches
[root@localhost ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           3782          98        3531          43         152        3448
Swap:          1023          77         946
[root@localhost ~]# echo 3 > /proc/sys/vm/drop_caches
[root@localhost ~]# sysctl -w vm.nr_hugepages=640
vm.nr_hugepages = 640
[root@localhost ~]# sysctl -a|grep nr_hugepages
sysctl: 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 = 640
vm.nr_hugepages_mempolicy = 640
[root@localhost ~]# cat /proc/meminfo |grep -i huge
AnonHugePages:      2048 kB
HugePages_Total:     640
HugePages_Free:      640
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
[root@localhost ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           3782        1365        2335           1          82        2237
Swap:          1023          77         946
[root@localhost ~]# 
---- pg_ctl restart
[root@localhost ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           3782        1375        2311           1          96        2220
Swap:          1023          77         946
[root@localhost ~]# cat /proc/meminfo |grep -i huge
AnonHugePages:      2048 kB
HugePages_Total:     640
HugePages_Free:      615
HugePages_Rsvd:      512
HugePages_Surp:        0
Hugepagesize:       2048 kB
[root@localhost ~]# 
./runBenchmark.sh props.pg
[root@localhost ~]# grep -i huge /proc/meminfo
AnonHugePages:      2048 kB
HugePages_Total:     640
HugePages_Free:      104
HugePages_Rsvd:        1
HugePages_Surp:        0
Hugepagesize:       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:17
2019-09-25 17:05:17,586  INFO - Term-00, Session End       = 2019-09-25 17:05:17
2019-09-25 17:05:17,586  INFO - Term-00, Transaction Count = 87860

结论
从上面的测试结果来说，三者并没有太大的差异，可以认为是扰动引起的变化而已。

参考资料
Page table
PostgreSQL Huge Page 使用建议 - 大内存主机、实例注意
Tune Linux Kernel Parameters For PostgreSQL Optimization