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/)