Steps to enable HugePages in PostgreSQL

Steps to enable HugePages in PostgreSQL

By default, the page size on Linux is 4kb. A typical PostgreSQL instance may allocate many GBs of memory, which will end up with potential performance problems with such a small page size. Also, given that these pages will be defragged, using them for large data sets will end up with extra time for mapping them.


Enabling huge pages on Linux will give a performance boost to PostgreSQL. Also can potentially save the system from the brink of Out of Memory Error.

By default, huge pages are not enabled on Linux, And also PostgreSQL’s default huge_pages setting is “try” which means “use huge pages if available on the OS, otherwise no.”


Follow the  below steps to enable HugePages in PostgreSQL server.

Step 1. Find VmPeak value: We can figure out how much memory should be allocated to HugePages by checking the VmPeak of the postmaster process. In our case 100818 is the PID of the postmaster. Use the below command to get VmPeak value.

VmPeak: VmPeak is the maximum amount of memory the process has used since it was started.


Step 2. Calculate no. of HugePages required: Let’s confirm the huge page size using the below command by default Hugepage size will be 2048 kB, and calculate the number of huge pages that the instance will be using formula = VmPeak / Hugepagesize i.e. 406236 / 2048 = 198.35


Step 3. Edit sysctl.conf file: Now edit the /etc/sysctl.conf file and add the vm.nr_hugepages = 200 line. The ideal number of huge pages is just a bit higher than this 198.35 will use 200. Shutdown PostgreSQL instance and execute sysctl -p this will reload kernel parameters. After sysctl -p command is executed you can see HugePages is configured.


Step 4. Edit postgresql.conf file: Edit postgresql.conf and set parameter huge_pages = on. Upon startt up of PostgreSQL cluster at this stage, we can see that the HugePages_Rsvd is allocated.


Reference:
Tuning Red Hat Enterprise Linux Family for PostgreSQL:
https://www.enterprisedb.com/blog/tuning-red-hat-enterprise-linux-family-postgresql


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. 
Click here to understand more about our pursuit.


Related Articles

 


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Leave a Reply

Your email address will not be published. Required fields are marked *