Huge pages with Postgres & InnoDB: better perf, but a bit more work

I started to use huge pages for the benchmarks I do with Postgres and MySQL/InnoDB. It helps performance but requires a few more steps to get working and this will be another source of failures for production deployments. See here for an example of the perf improvements.

Disclaimer - I am new to this and far from an expert.

Postgres

As always, the Postgres docs are useful:

1. Figure out how many huge pages will be needed, call this X
2. Edit /etc/sysctl.conf with vm.nr_huge_pages=$X
3. sudo sysctl -p
4. Compact the Linux VM (optional, not in the PG docs)
5. Add huge_pages=try or huge_pages=on to the Postgres configuration
6. Start Postgres

To estimate the number of huge pages (the value for X). First figure out the value of Hugepagesize (grep Huge /proc/meminfo) and in the example below that is 2MB. Then X = sizeof(buffer pool) / Hugepagesize.

$ grep Huge /proc/meminfo

AnonHugePages:         0 kB

ShmemHugePages:        0 kB

FileHugePages:         0 kB

HugePages_Total:       0

HugePages_Free:        0

HugePages_Rsvd:        0

HugePages_Surp:        0

Hugepagesize:       2048 kB

Hugetlb:               0 kB

I suggest compacting the Linux VM because huge pages might not be available if you don't do this (I learned this from experience) and you can judge that based on the value of HugePages_Total from /proc/meminfo. After starting Postgres check /proc/meminfo again to confirm that huge pages were used. This post has more details

 My script for compacting the VM is:

sync; sync; sync

echo 3 > /proc/sys/vm/drop_caches

echo 1 > /proc/sys/vm/compact_memory

MySQL/InnoDB

The docs are here for InnoDB with huge pages although I think they need to be updated. AFAIK InnoDB always uses mmap now, while it used to use shm (shmget, shmat) prior to modern 8.0. When shm is used you need to worry about /proc/sys/vm/hugetlb_shm_group, /proc/sys/kernel/shmmax and /proc/sys/kernel/shmmall as mentioned in the InnoDB docs. I don't think those need to be updated now that InnoDB appears to always use mmap. For the 5.7 code that uses shm see here and for modern 8.0 that uses mmap see here.

To use huge pages with InnoDB:

1. Figure out how many huge pages will be needed, call this X. See the Postgres section above.
2. Edit /etc/sysctl.conf with vm.nr_huge_pages=$X
3. sudo sysctl -p
4. Compact the Linux VM (optional, not in the InnoDB docs). See the Postgres section above.
5. Confirm that innodb_buffer_pool_chunk_size is larger than the huge page size. The default is 128M which is great when the huge page size is 2M.
6. Add large_pages=ON to my.cnf
7. Start mysqld

When I first tried this with MySQL 5.6 I forgot to edit /proc/sys/vm/hugetlb_shm_group as described in the InnoDB docs. I was able to start mysqld, but there were errors in the database error log and odd query and connection errors afterwards. Fortunately, I don't think you need to edit hugetlb_shm_group with modern 8.0 because it uses mmap instead of shm.