I'm managing a Magento server with MySQL installed in it. 16GB RAM 8 core vCPU (Digitalocean).
We're having a problem with the site becoming a bit sluggish when under medium to heavy load. Under low to none, MySQL uses around 25% to 60% of CPU (according to HTOP). Under medium to heavy load MySQL takes up 150% of CPU usage (again, according to HTOP). On the graph CPU usage on all cores is stuck at 100% during medium usage of the website.
My MySQL configuration is the default, a performance tuning was never done. I'm working on configuring PHP and Elasticsearch memory/CPU usage, but I need help with MySQL configuration since I never dealt with public facing servers (web store).
This is what mysqltuner says:
[!!] Maximum reached memory usage: 154.1G (983.67% of installed RAM)
[!!] Maximum possible memory usage: 153.1G (977.27% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Highest connection usage: 100% (152/151)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2B selects)
[!!] Joins performed without indexes: 600679
[!!] Table cache hit rate: 0% (2K open / 3M opened)
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Read Key buffer hit rate: 90.7% (378 cached / 35 reads)
[!!] InnoDB buffer pool / data size: 1.0G/1.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal to 25%
[!!] InnoDB buffer pool 151)
wait_timeout ( 1M, or use smaller result sets)
join_buffer_size (> 2.0M, or always use indexes with JOINs)
table_open_cache (> 2419)
innodb_buffer_pool_size (>= 1.2G) if possible.
innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances (=1)
Asked by Odin
(11 rep)
Jan 14, 2021, 05:07 PM
Last activity: Apr 14, 2025, 09:01 PM
Last activity: Apr 14, 2025, 09:01 PM