Database Administrators Asked by Stalinko on January 28, 2021
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.
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.
Answered by Rick James on January 28, 2021
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.
Answered by Gordan Bobic on January 28, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP