Configuring huge pages for your PostgreSQL instance, Debian version

In the last post we had a look at how you can configure huge pages on RedHat and CentOS systems. For Debian and Debian based systems the procedure is different as Debian does not come with tuned. Lets see how it works there.

Checking the basic system configuration works the same in Debian as in RedHat based distributions by checking the /proc/meminfo file:

postgres@debianpg:/home/postgres/ [PG1] cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

So nothing configured for huge pages in the default configuration. Using the same procedure from the last post this is how you calculate the required huge pages for the PostgreSQL instance:

postgres@debianpg:/home/postgres/ [PG1] head -1 $PGDATA/postmaster.pid
6661
postgres@debianpg:/home/postgres/ [PG1] grep ^VmPeak /proc/6661/status
VmPeak:	  393836 kB
postgres@debianpg:/home/postgres/ [PG1] grep ^Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB
postgres@debianpg:/home/postgres/ [PG1] echo "393836/2048" | bc
192

We’ll need at least 192 pages. Lets add that to /etc/sysctl.conf:

postgres@debianpg:/home/postgres/ [PG1] sudo bash
root@debianpg:/home/postgres$ echo "vm.nr_hugepages=200" >> /etc/sysctl.conf

Notify the system about that change:

root@debianpg:/home/postgres$ sysctl -p
vm.nr_hugepages = 200

… and we have 200 huge pages available:

postgres@debianpg:/home/postgres/ [PG1] cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:     200
HugePages_Free:      200
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Again, lets force PostgreSQL to use huge pages and restart the instance:

postgres@debianpg:/home/postgres/ [PG1] psql -c "alter system set huge_pages=on" postgres
ALTER SYSTEM
postgres@debianpg:/home/postgres/ [PG1] pg_ctl -D $PGDATA restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2018-02-25 17:13:59.398 CET [6918] LOG:  listening on IPv6 address "::1", port 5432
2018-02-25 17:13:59.398 CET [6918] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-02-25 17:13:59.403 CET [6918] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-02-25 17:13:59.421 CET [6919] LOG:  database system was shut down at 2018-02-25 17:13:59 CET
2018-02-25 17:13:59.427 CET [6918] LOG:  database system is ready to accept connections
 done
server started

… and that’s it:

postgres@debianpg:/home/postgres/ [PG1] cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:     200
HugePages_Free:      193
HugePages_Rsvd:       64
HugePages_Surp:        0
Hugepagesize:       2048 kB

We can do the same test as in the last post to check that the number of huge pages will increase when you have load on the system:

postgres=# create table t1 as select * from generate_series(1,1000000);
SELECT 1000000
postgres=# select count(*) from t1;
  count  
---------
 1000000
(1 row)

postgres=# ! cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:     200
HugePages_Free:      184
HugePages_Rsvd:       55
HugePages_Surp:        0
Hugepagesize:       2048 kB

Btw: This is on Debian 9 (not sure if it is the same for lower versions):

postgres@debianpg:/home/postgres/ [PG1] cat /etc/os-release 
PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
NAME="Debian GNU/Linux"
VERSION_ID="9"
VERSION="9 (stretch)"
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"