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:
- Figure out how many huge pages will be needed, call this X
- Edit /etc/sysctl.conf with vm.nr_huge_pages=$X
- sudo sysctl -p
- Compact the Linux VM (optional, not in the PG docs)
- Add huge_pages=try or huge_pages=on to the Postgres configuration
- 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:
- Figure out how many huge pages will be needed, call this X. See the Postgres section above.
- Edit /etc/sysctl.conf with vm.nr_huge_pages=$X
- sudo sysctl -p
- Compact the Linux VM (optional, not in the InnoDB docs). See the Postgres section above.
- 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.
- Add large_pages=ON to my.cnf
- 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.
Update 1: watch out for OOM
Have been using this for sysbench and the insert benchmark on large GCP servers (c2-standard-60 with 240G RAM and 30 cores). Works great for Postgres but I have intermittent problems with MySQL/InnoDB --
see here.
A description of the /proc/meminfo contents
is here.
The server has 240G of RAM, I set vm.nr_hugepages=108000, Hugepagesize from /proc/meminfo is 2MB. Also, a script is run to compact the Linux VM prior to starting mysqld.
From the following I don't understand why HugePages_Rsvd shrinks from 92153 to 62071, but something similar happens with Postgres and I have yet to experience intermittent problems there.
Example contents of /proc/meminfo immediately after starting mysqld is:
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
FileHugePages: 0 kB
HugePages_Total: 108000
HugePages_Free: 105113
HugePages_Rsvd: 92153
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 221184000 kB
And then after running a full benchmark (a few hours of work):
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
FileHugePages: 0 kB
HugePages_Total: 108000
HugePages_Free: 75031
HugePages_Rsvd: 62071
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 221184000 kBLooking at dmesg output I see the OOM killer has been active and killed mysqld --
see here.
From this I assume the server has 2 sockets and the HugePages_Total (108000) is split evenly between them. I am curious how that interacts with the large allocations done by InnoDB. Does it need to know to split them evenly between NUMA nodes?
See here.
Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=1048576kB
Node 0 hugepages_total=54000 hugepages_free=53741 hugepages_surp=0 hugepages_size=2048kB
Node 1 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=1048576kB
Node 1 hugepages_total=54000 hugepages_free=53173 hugepages_surp=0 hugepages_size=2048kB
I assume the problem is the
large value for anon-rss here and the workaround would be to reduce the size of the InnoDB buffer pool from 180G, maybe to 150G.
Out of memory: Killed process 81153 (mysqld) total-vm:206822548kB, anon-rss:24688304kB, file-rss:0kB, shmem-rss:0kB, UID:1001 pgtables:54752kB oom_score_adj:0Using huge pages feels brittle, options are:
- Make buffer pool as large as normal, risk OOM
- Make buffer pool smaller than normal, avoid OOM
I prefer to use InnoDB with O_DIRECT, but if #2 is the workaround then perf suffers unless I switch to buffered IO.
Update 2: odd failures with Postgres
Huge pages continue to be a huge-ly brittle. Now I get intermittent failures in the benchmark with Postgres. The failures are from a failure to connect to Postgres, for example this happens a few hours into a benchmark after the initial load (insert without secondary indexes), index create, and then insert with secondary index steps all completed with success.
The server has 240G of RAM, from ps I see that Postgres VSZ is ~160G while RSS is small. I don't see problems in dmesg output but I lost the Postgres logfile and will try to reproduce the problem.
Looks like /dev/shm is configured to get up to 119G on this server, which oversubscribes memory given that I have vm.nr_hugepages=85000 which takes 166G of the 240G on the server. But after starting PG I see that only 1.1M is used from /dev/shm. Does something try to put 80G of data there (what, why)? Or is there some source of pressure that just reclaims memory, and removes the files created in /dev/shm by Postgres long before it uses 80G of RAM?
$ bin/psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: could not open shared memory segment "/PostgreSQL.1400317704": No such file or directory
And from Python
psycopg2.OperationalError: connection to server at "127.0.0.1", port 5432 failed: FATAL: could not open shared memory segment "/PostgreSQL.1400317704": No such file or directory
From /proc/meminfo long after the problem started ...
$ grep -i huge /proc/meminfo
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
FileHugePages: 0 kB
HugePages_Total: 85000
HugePages_Free: 6610
HugePages_Rsvd: 90
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 174080000 kB
And /proc/meminfo immediately after starting Postgres
grep -i huge /proc/meminfo
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
FileHugePages: 0 kB
HugePages_Total: 85000
HugePages_Free: 83492
HugePages_Rsvd: 76972
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 174080000 kB
Started to read these posts:
- https://postgrespro.com/list/thread-id/2494899
- https://postgreshelp.com/postgresql-dynamic-shared-memory-posix-vs-mmap/
- https://postgreshelp.com/postgresql-kernel-parameters
And I am using POSIX for dynamic_shared_memory_type.
ib=# select name, setting from pg_settings where name like 'dynamic_shared_memory_type';
name | setting
----------------------------+---------
dynamic_shared_memory_type | posix
From ipcs -a
------ Message Queues --------
key msqid owner perms used-bytes messages
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000404 557079 mcallaghan 600 56 6
------ Semaphore Arrays --------
key semid owner perms nsems
And from /dev/shm
$ ls -lrt /dev/shm
total 1052
-rw------- 1 mcallaghan mcallaghan 26976 May 1 15:48 PostgreSQL.4270028686
-rw------- 1 mcallaghan mcallaghan 1048576 May 1 15:50 PostgreSQL.837122846
No comments:
Post a Comment