1

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.

  • A possible reason for that could be my swapiness=60. Changed it to 1 to avoid swapping as much as possible. – Stalinko Oct 15 at 5:28
  • 1
    memlock can also ensure that it isn't swapped. – danblack Oct 19 at 9:00
1

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.

| improve this answer | |
  • Thanks for the note about 10GB buffer pool :) I'll disable it. The script which requires 40GB RAM does really need that unfortunately, it was a weighted decision. Regards large-pages interesting idea... Need to read about it more, because it seems hard to configure properly but the profit isn't obvious. – Stalinko Oct 16 at 9:03
  • I provided a pastable that will get you up and running with large pages. The profit is pretty obvious, your buffer pool will not be swapped out as much which will cure most of your "mysqld doesn't unswap" issue. – Gordan Bobic Oct 16 at 9:05
  • I'm hesitating because 24h have passed since I changed swapiness and my swap is still almost empty :) Even after those heavy php scripts it gets flushed pretty quickly. So if my problem with swap will get back again then I'll try to play with huge pages. – Stalinko Oct 16 at 10:04
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.