Shulou(Shulou.com)06/01 Report--

PostgreSQL provides the parameter huge_pages to use the huge pages of OS. The default is try, which can be used as soon as it can be used. If it is not used, it will not be used. One of the reasons for using HUGE PAGE is that HUGE PAGE is resident in memory and will not be swapped out.

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.

test

The machine is configured with 4C memory 4G memory, and the PG shared cache is set to 1G.

1. Do not use huge_pages,client_min_messages and so on as the default parameter

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

Test result

2019-09-25 17 Measured tpmC 27Measured tpmC 04687 INFO-Term-00, Measured tpmC (NewOrders) = 7991.69 2019-09-25 17 17 Measured tpmC 04687 INFO-Term-00, Measured tpmTOTAL = 17646.94 2019-09-25 17 17 27 V 04687 INFO-Term-00, Session Start = 2019-09-25 1722 1422 INFO-Term-00 Session End = 2019-09-25 17 Term-00 27purl 042019-09-25 17 purl 27purl 04687 INFO-Term-00, Transaction Count = 88279

two。 Do not use huge_pages,client_min_messages to error, other adjustments to 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

Test result

2019-09-25 16V 09RV 10721 INFO-Term-00, Measured tpmC (NewOrders) = 7963.92 2019-09-25 16VO 09V 10721 INFO-Term-00, Measured tpmTOTAL = 17692.15 2019-09-25 16V 09RV 10721 INFO-Term-00, Session Start = 2019-09-25 16V 04RV 102019-09-25 169RV 10721 INFO-Term-00 Session End = 2019-09-25 16 Term-00 09 INFO-Term-00, Transaction Count = 88494

3. Use huge_pages,client_min_messages to error, other adjustments to 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- process [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 182 2237Swap: 1023 77 946 [root@localhost] #-pg_ctl restart [root@localhost] # free-m total used free Shared buff/cache availableMem: 3782 1375 2311 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

Test result

2019-09-25 1705 Measured tpmC INFO-Term-00, Measured tpmC (NewOrders) = 7905.19 2019-09-25 175V 05V 17586 INFO-Term-00, Measured tpmTOTAL = 17565.52 2019-09-25 175V 05RV 17586 INFO-Term-00, Session Start = 2019-09-25 1700V 172019-09-25 1705R 17586 INFO-Term-00 Session End = 2019-09-25 1715 INFO-Term-00, Transaction Count = 87860

Conclusion

From the above test results, there is not much difference between the three, which can be regarded as the change caused by disturbance.

references

Page table

Recommendations for the use of PostgreSQL Huge Page-large memory hosts, instance attention

Tune Linux Kernel Parameters For PostgreSQL Optimization

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.