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 <= 1G and Innodb_buffer_pool_instances(!=1).
[!!] InnoDB Write Log efficiency: 8.97% (62690490 hits/ 699060007 total)
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (5000) variable
should be greater than table_open_cache (2419)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 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)
