Infrastructure at your Service

Daniel Westermann

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:

[email protected]:/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:

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

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

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

Notify the system about that change:

[email protected]:/home/postgres$ sysctl -p
vm.nr_hugepages = 200

… and we have 200 huge pages available:

[email protected]:/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:

[email protected]:/home/postgres/ [PG1] psql -c "alter system set huge_pages=on" postgres
ALTER SYSTEM
[email protected]:/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:

[email protected]:/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):

[email protected]:/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/"

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure