top of page

Support Group

Public·13 members
Nick Neithan
Nick Neithan

Benchmark PostgreSQL With Linux HugePages




Benchmark PostgreSQL with Linux HugePages


Benchmark PostgreSQL with Linux HugePages




PostgreSQL is a powerful, open source object-relational database system that has earned a strong reputation for reliability, feature robustness, and performance. However, like any other database system, PostgreSQL can benefit from some performance tuning and optimization techniques. One of them is to use Linux HugePages, which are larger memory pages that can reduce the overhead of memory management and improve the cache hit ratio. In this article, we will explain what Linux HugePages are, how to enable them on a Linux system, and how to benchmark PostgreSQL with and without them.


Download File: https://imfixcompko.blogspot.com/?mc=2w41Pa


What are Linux HugePages?




Linux HugePages are a feature of the Linux kernel that allows the allocation of larger chunks of contiguous physical memory to processes. By default, Linux uses small memory pages of 4 KB each, which means that a process that consumes a lot of memory will also use a lot of pages. This can result in more page faults, more TLB (Translation Lookaside Buffer) misses, and more CPU cycles spent on page table walks. Moreover, each page has an associated page table entry (PTE) that stores information about the page, such as its physical address, access permissions, and status flags. The PTEs are stored in page tables, which are organized in a hierarchical structure. The more levels of page tables there are, the more memory is consumed by them and the more time is needed to traverse them.


Linux HugePages can help to mitigate these problems by using larger memory pages, such as 2 MB or 1 GB, depending on the CPU architecture. This reduces the number of pages and PTEs needed for a given amount of memory, which in turn reduces the TLB pressure and the page table size. As a result, memory access becomes faster and more efficient.


How to enable Linux HugePages?




Linux supports two types of huge pages: standard huge pages (HugeTLB) and transparent huge pages (THP). Standard huge pages are explicitly allocated by applications that request them via system calls or memory mapping flags. Transparent huge pages are automatically managed by the kernel, which tries to merge small pages into huge pages whenever possible. Both types of huge pages have their advantages and disadvantages.


To enable standard huge pages on a Linux system, we need to do the following steps:



  • Create a group for users of huge pages, and retrieve its GID (in this example, 2021) then add yourself to the group.



  • Edit the /etc/security/limits.conf file and add the following lines:



@hugepages soft memlock 209715200 @hugepages hard memlock 209715200


  • This sets the maximum amount of memory (in bytes) that processes in the hugepages group can lock into RAM. In this example, we use 200 MB as the limit.



  • Edit the /etc/sysctl.conf file and add the following line:



vm.nr_hugepages = 100


  • This sets the number of huge pages to be reserved by the system. In this example, we use 100 huge pages of 2 MB each, which equals 200 MB.



  • Reboot the system for the changes to take effect.



  • Verify that huge pages are enabled by running the following command:



cat /proc/meminfo grep --ignore-case -e 'huge' -e 'filepmd' grep --invert-match 'Shmem'


  • You should see something like this:



AnonHugePages: 0 kB FileHugePages: 0 kB FilePmdMapped: 0 kB HugePages_Total: 100 HugePages_Free: 100 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 204800 kB



To enable transparent huge pages on a Linux system, we need to do the following steps:



  • Check the current status of THP by running the following command:



cat /sys/kernel/mm/transparent_hugepage/enabled


  • You should see something like this:



[always] madvise never


  • This means that THP is enabled by default for all processes, unless they explicitly opt out via the madvise system call. Alternatively, you can set THP to be enabled only for processes that explicitly opt in via the madvise system call, or disable it completely.



  • To change the THP mode, edit the /etc/rc.local file and add the following line:



echo madvise > /sys/kernel/mm/transparent_hugepage/enabled


  • This sets the THP mode to madvise, which means that only processes that request huge pages via the madvise system call will get them. You can also use never to disable THP completely, or always to enable it for all processes.



  • Reboot the system for the changes to take effect.



  • Verify that THP is enabled by running the following command:



cat /sys/kernel/mm/transparent_hugepage/enabled


  • You should see something like this:



always [madvise] never



How to benchmark PostgreSQL with Linux HugePages?




To benchmark PostgreSQL with Linux HugePages, we need to do the following steps:



  • Install PostgreSQL on a Linux system that has huge pages enabled, either standard or transparent.



  • Edit the /etc/postgresql/15/main/postgresql.conf file and set the following parameters:



shared_buffers = 1GB # this is the amount of memory PostgreSQL uses for caching data huge_pages = try # this tells PostgreSQL to try to use huge pages if available


  • Restart PostgreSQL for the changes to take effect.



  • Verify that PostgreSQL is using huge pages by running the following command:



ps aux grep postgres grep huge


  • You should see something like this:



postgres 2023 0.0 0.0 666666 6666 ? S 20:27 0:00 postgres: checkpointer huge postgres 2024 0.0 0.0 666666 6666 ? S 20:27 0:00 postgres: background writer huge postgres 2025 0.0 0.0 666666 6666 ? S 20:27 0:00 postgres: walwriter huge postgres 2026 0.0 0.0 666666 6666 ? S 20:27 0:00 postgres: autovacuum launcher huge postgres 2027 0.0 0.0 666666 6666 ? Ss 20:27 0:00 postgres: stats collector huge postgres 2028 0.0 0.0 666666 6666 ? Ss 20:27 0:00 postgres: logical replication launcher huge


  • This shows that PostgreSQL processes are using huge pages (the huge flag).



  • Create a test database and populate it with some data, using tools such as pgbench or pgloader.



  • Run some queries on the test database and measure their execution time, using tools such as psql or pgAdmin.



  • Compare the results with and without huge pages, and observe the difference in performance.




The exact performance improvement of using huge pages depends on many factors, such as the workload, the data size, the memory size, and the hardware configuration. However, some benchmarks have shown significant speedups of up to two times faster for some queries. Therefore, it is worth experimenting with huge pages and finding the optimal settings for your PostgreSQL system.


: [PostgreSQL: The world's most advanced open source database] : [How to enable hugepages on Linux - Linux Tutorials - Learn Linux Configuration]( Here is the continuation of the HTML article on the topic of "Benchmark PostgreSQL with Linux HugePages": Conclusion




In this article, we have learned what Linux HugePages are, how to enable them on a Linux system, and how to benchmark PostgreSQL with and without them. We have seen that using huge pages can improve the performance of PostgreSQL by reducing the memory management overhead and increasing the cache hit ratio. However, we have also noted that huge pages are not a silver bullet, and that they may have some drawbacks, such as increased memory fragmentation, reduced memory reclaimability, and potential compatibility issues with some applications or libraries. Therefore, we recommend that you test your PostgreSQL system with different huge page settings and find the optimal configuration for your workload and hardware.