How to setup huge pages on a postgreSQL server

The Postgres Documentation states:

The use of huge pages results in smaller page tables and less CPU time spent on memory management, increasing performance.

This is how I set up huge pages on one of my postgreSQL servers.

First, check the server's huge page size. My huge pages are 2M each:

 grep Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB

On my 16G server, I would like to start with 25% of the total memory as available for shared_buffers:

su - postgres
sudo systemctl stop postgresql-15.service
postgres --shared-buffers=4096MB -D $PGDATA -C shared_memory_size_in_huge_pages
2102

Update /etc/sysctl.conf. Add

vm.nr_hugepages=2102

Restart sysctl

sysctl -p

Now, tell the postgres server to use huge pages, if they are available.

Add the following directive to the config file postgresql.conf

huge_pages = try

Add the following lines to /etc/security/limits.conf so that postgres can lock down the memory set aside for huge tables:

postgres soft memlock unlimited
postgres hard memlock unlimited

Reboot the server and verify that huge pages are being used:

cat /proc/meminfo | grep Huge

Interestingly, the parameter huge_page_size should only be used if you wish to override the default huge page size on your system. The default is zero (0). When set to 0, the default huge page size on the system will be used. In my case this is what I want to so the parameter can be ignored.

How to see the number of huge pages configured on a Linux server

On a RHEL server, to see if hugespages have been configured, you can use

cat /proc/meminfo|grep -iE 'tables|huge'

PageTables:        33368 kB
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    5121
HugePages_Free:        9
HugePages_Rsvd:        9
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        10487808 kB

From the above, we can see that the total amount of huge pages is 5121.
Each page is 2M in size, as can be seen from Hugepagesize.
The total size of hugepages amounts to 10242M, or 10G.

Frank Pachot has written a really neat pice of code to format the output from sysctl to display huge pages usage on a Linux server.

awk '/Hugepagesize:/{p=$2} / 0 /{next} / kB$/{v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$2;next} {h[$0]=$2} /HugePages_Total/{hpt=$2} /HugePages_Free/{hpf=$2} {h["HugePages Used (Total-Free)"]=hpt-hpf} END{for(k in v) print sprintf("%-60s %10d",k,v[k]/p); for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)}' /proc/meminfo|sort -nr|grep --color=auto -iE "^|( HugePage)[^:]*"

The output is much easier to draw conclusions from, and it gives quite a lof of other useful information about your system, too:

32767 GB VmallocTotal:   34359738367 kB                    16777215
24 GB CommitLimit:    26025928 kB                          12707
19 GB SwapTotal:      20971516 kB                          10239
19 GB SwapFree:       20969176 kB                          10238
19 GB MemTotal:       20596632 kB                          10056
12 GB DirectMap1G:    12582912 kB                           6144
10 GB Hugetlb:        10487808 kB                           5121
10 GB HugePages_Total:    5121
9 GB HugePages Used (Total-Free)
9 GB DirectMap2M:    10082304 kB                           4923
7 GB MemAvailable:    8173656 kB                           3991
6 GB Cached:          6329396 kB                           3090
5 GB Inactive:        5862704 kB                           2862
4 GB Inactive(file):  4679556 kB                           2284
2 GB Committed_AS:    2912096 kB                           1421
1 GB MemFree:         1835428 kB                            896
1 GB Inactive(anon):  1183148 kB                            577
1 GB AnonPages:       1056944 kB                            516
1 GB Active(file):    1502496 kB                            733
1 GB Active:          1514284 kB                            739
0 GB VmallocUsed:       30708 kB                             14
0 GB Unevictable:       12376 kB                              6
0 GB SwapCached:          432 kB                              0
0 GB SUnreclaim:       101008 kB                             49
0 GB SReclaimable:     375752 kB                            183
0 GB Slab:             476760 kB                            232
0 GB Shmem:            139612 kB                             68
0 GB Percpu:             4960 kB                              2
0 GB PageTables:        33448 kB                             16
0 GB Mlocked:           12376 kB                              6
0 GB Mapped:           322284 kB                            157
0 GB KReclaimable:     375752 kB                            183
0 GB KernelStack:        6080 kB                              2
0 GB HugePages_Surp:        0
0 GB HugePages_Rsvd:        9
0 GB Hugepagesize:       2048 kB                              1
0 GB HugePages_Free:        9
0 GB Dirty:              1272 kB                              0
0 GB DirectMap4k:      403328 kB                            196
0 GB Buffers:            2852 kB                              1
0 GB Active(anon):      11788 kB                              5

How to find the number of huge pages to set on a PostgreSQL server

Thanks to Ibrar Ahmed for posting the article "Tune Linux Kernel Parameters For PostgreSQL Optimization"

Log on to the server as the os user that owns the PostgreSQL server. On my server, this user is called "postgres":

su - postgres

Create a file called find_hp.sh. Insert the following:

#!/bin/bash
pid=`head -1 $PGDATA/postmaster.pid`
echo "Pid:            $pid"
peak=`grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'`
echo "VmPeak:            $peak kB"
hps=`grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'`
echo "Hugepagesize:   $hps kB"
hp=$((peak/hps))
echo Set Huge Pages:     $hp

Make sure the environment variable $PGDATA is set. Give the script execution rights:

chmod 755 find_hp.sh

Execute it, and it will tell you how many huge pages you need:

 ./find_hp.sh
Pid:             128678
VmPeak:          68986484 kB
Hugepagesize:    2048 kB
Set Huge Pages:  33684

I can now proceed to allow for 33684 huge pages on my system with

sysctl -w vm.nr_hugepages=33684