Why MySQL tends to use more and more swap while there is enough RAM?

Question

I have a server with 64GB RAM and 64 GB swap.
It's running MySQL 8.0.19 and few php scripts.
Here is my config:
innodb_buffer_pool_size=40G
innodb_buffer_pool_instances=4
innodb_buffer_pool_chunk_size=10G
sync_binlog=0
innodb_log_file_size=1G
innodb_ft_result_cache_limit=4000000000
max_heap_table_size=4G
tmp_table_size=4G
innodb_online_alter_log_max_size=512M
range_optimizer_max_mem_size=0
performance_schema="OFF"

This server is used for heavy calculations, maximum 5 scripts running at same time, no users.
The huge swap is necessary for rare cases when some script needs a lot of RAM for itself.
The more server works the more memory MySQL tries to consume. If I don't restart it during few weeks it ends up with 90GB total memory consumed (~60RAM + ~30 in swap). And all of this degrades the performance drastically.
For now I don't know any solution besides simple restart. It clears entire swap and makes MySQL restart with ~40GB RAM consumption.
Do you have any ideas how to make MySQL not use swap that much? Perhaps this happens when some PHP script allocates too much RAM and pushes MySQL's memory into swap, but once that program finishes, MySQL doesn't free swap as I supposed it should do.

Rick James · Answer

These are excessive; don't set them to more than about 1% of RAM:
max_heap_table_size=4G
tmp_table_size=4G

It might help if you showed us the queries and SHOW CREATE TABLE.
See if your PHP code is using a high value in ini_set("memory_limit",...).  The OS may be picking MySQL to swap even though PHP is hogging memory.

Gordan Bobic · Answer

Yes, you can make MySQL not use as much swap by making it use huge memory pages. Huge pages are unswappable.
In my.cnf, [mysqld] section:
large-pages = 1

Additional OS level configuration is required. For example:
groupadd -g 630 hugetlb
usermod -G hugetlb mysql
sysctl -w vm.nr_hugepages = 20480
sysctl -w vm.hugetlb_shm_group = 630

You may need to increase nr_hugepages a little, look at the logs while it is starting for clues on when it no longer needs to be increased.
Additionally, 10GB buffer pool chunk size is absurdly large. Comment it out. Also, optimal for buffer pool instances is between 1GB and 2GB per instance, so a setting of 40 would be more appropriate than 4 for a 40GB buffer pool.
And finally - if your PHP script can use upward of 40GB of RAM, I dare say you need to fix that. Unless you actually need to manipulate 40GB of data in memory at once, there is almost certainly a better way.

Why MySQL tends to use more and more swap while there is enough RAM?

2 Answers

Add your own answers!

Ask a Question