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.
[root@test-machine02 ~]#
[root@test-machine02 ~]# ps -ef|grep postmaster
root 6795 94656 0 14:59 pts/1 00:00:00 grep --color=auto postmaster
postgres 100818 1 0 Nov14 ? 00:00:30 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
[root@test-machine02 ~]#
[root@test-machine02 ~]#
[root@test-machine02 ~]# grep ^VmPeak /proc/100818/status
VmPeak: 406236 kB
[root@test-machine02 ~]#
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
[root@test-machine02 ~]# grep ^Huge /proc/meminfo
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@test-machine02 ~]#
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.
[root@test-machine02 ~]#
[root@test-machine02 ~]# vi /etc/sysctl.conf
#Hugepage setting
vm.nr_hugepages = 200
:wq!
[root@test-machine02 ~]#
[root@test-machine02 ~]#
[root@test-machine02 ~]# systemctl stop postgresql-13
[root@test-machine02 ~]#
[root@test-machine02 ~]#
[root@test-machine02 ~]# sysctl -p
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
vm.nr_hugepages = 200
[root@test-machine02 ~]#
[root@test-machine02 ~]#
[root@test-machine02 ~]#
[root@test-machine02 ~]# grep ^Huge /proc/meminfo
HugePages_Total: 200
HugePages_Free: 200
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@test-machine02 ~]#
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.
[root@test-machine02 ~]# cd /var/lib/pgsql/13/data/
[root@test-machine02 data]# vi postgresql.conf
huge_pages = on
:wq!
[root@test-machine02 data]#
[root@test-machine02 data]#
[root@test-machine02 data]# systemctl start postgresql-13
[root@test-machine02 data]#
[root@test-machine02 data]#
[root@test-machine02 data]# grep ^Huge /proc/meminfo
HugePages_Total: 200
HugePages_Free: 191
HugePages_Rsvd: 64
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@test-machine02 data]#
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
Hello and welcome to DBsGuru,
I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.
Thanks for the visits!
Share Learn Grow!
