If you have noticed a higher CPU usage by MySQL after running the top command, then you need to investigate the situation. In the case of dedicated servers, these days the CPUs we use are multi-core. For the top command, we have to press SHIFT + i on the keyboard to get an idea of the average load on each core. Alternatively, you can run top -H or top -H -p ID-number to check the actual consumption. Probably htop is a better tool than top in these situations.
Secondly, notice the loadaverage. If the load average is high, such as 4.64, 4.92, 3.95, again you have to take consideration of multi-cores. However, it is not great to allow a load average of more than 2.00 for a longer period.
Find What is Forcing MySQL to Consume High CPU
For WordPress usually some plugins are the root cause behind the sudden spike. When the number of visitors is lower, these faulty plugins do not show their true colour.
---
To identify what is creating the issue, install a WordPress plugin named Query Monitor. Go to Database Query > to Queries by Component. You’ll get the names of the WordPress plugins which are possibly increasing CPU usage. For example, Yet Another Related Post plugin can make WordPress extremely CPU hungry. The plugin is enough good, we informed about the risk.
Next, check the analytics data. If there is a sudden spike in traffic, your present strategy of caching may need revision.
Most importantly, check the log files for any external attacks. Checking the auth log, Fail2Ban log etc are important part of this scrutiny.
Check the situation of the WordPress cache plugin and avoid any kind of cache conflict. Usually using Varnish and/or Memcached is a good idea for a high-traffic site than using the MySQL cache.
Optimize MySQL
Maybe, you need to increase query cache size (on mariadb.cnf in the case of MariaDB, mysql.cnf for Percona and MySQL), it can be ridiculously low.
1 2 | query_cache_limit = 128K query_cache_size = 128M |
Or even try to make it:
1 2 | query_cache_limit = 384K query_cache_size = 512M |
Use the SlowLog that you have been capturing. Try to optimize the whole configuration file (or examine it again) and test it using the MySQLTuner script.
Thankfully, most of the users can solve this kind of issue. These problems are part of WordPress user’s life.
