PostgreSQL DBA(97) - System Conf(huge_pages)

PostgreSQL提供了參數(shù)huge_pages用以使用OS的huge pages，默認(rèn)為try，即可用就用，用不了就不用。使用HUGE PAGE其中一個原因是因為HUGE PAGE是常駐內(nèi)存的，不會被交換出去。

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內(nèi)存，PG共享緩存設(shè)置為1G。

1.不使用huge_pages，client_min_messages等為默認(rèn)參數(shù)
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

測試結(jié)果

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，其他調(diào)整為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

測試結(jié)果

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，其他調(diào)整為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

測試結(jié)果

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

結(jié)論
從上面的測試結(jié)果來說，三者并沒有太大的差異，可以認(rèn)為是擾動引起的變化而已。

參考資料
Page table
PostgreSQL Huge Page 使用建議 - 大內(nèi)存主機、實例注意
Tune Linux Kernel Parameters For PostgreSQL Optimization