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](https://en.wikipedia.org/wiki/Page_table)
[PostgreSQL Huge Page 使用建議 - 大記憶體主機、例項注意](https://github.com/digoal/blog/blob/master/201803/20180325_02.md)
[Tune Linux Kernel Parameters For PostgreSQL Optimization
](https://www.percona.com/blog/2018/08/29/tune-linux-kernel-parameters-for-postgresql-optimization/)

PostgreSQL DBA(97) - System Conf(huge_pages)

相關文章