MySQL high CPU usage under medium load

Question

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)

• Rick James: For more analysis: mysql.rjweb.org/doc.php/mysql_analysis
• Wilson Hauck: Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.

Answer 1

Significant problem 1 - table_open_cache

Table cache hit rate: 0% is significantly bad. Identify how many tables are in Magento and increase the table_open_cache to higher than that.

open_files_limit needs to be higher than this number however (as it includes connections also). High number like 64k are ok.

After that enable your slow_query_log, long_query_time and start identifying slow queries.

As you do have 16G of RAM setting innodb buffer pool size to 4G would be prudent as notably your data size is only 1.2G.

Examine performance again after these changes.

• Odin: How do I increase table_open_cache? My MySQL version is 5.7.28. I'm gonna implement your other suggestions right now.
• danblack: see option files. Also see open files limit. table open cache being a dynamic variable can be set at runtime with the SQL statement set global table_open_cache=16384, however the option file will also need to be changed to reflect the change on restart.

Answer 2

First enable slow_query_log and check if all queries are well optimized with all required indexes. Monitor disk IO/s. check total memory could be used with all settings and fully utilized.

select (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH

• (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb\G