Symptoms
MySQL is heavily using CPU time, and load averages are increasing due to MySQL's usage.
Description
High load averages and high MySQL's CPU time usage can occur for many different reasons. In this article, we will cover the "query_cache" option, which is often not correctly enabled and leads to re-executing the same "select" SQL statements each time it is requested.
This article tries to cover the most fundamental aspects of MySQL's query cache system.
The primary two directives to cover are:
query_cache_type - This option has been removed in MySQL 8.0, deprecated in 5.7.
query_cache_size - Determines the size of the cache space.
The above directives would be set in the "/etc/my.cnf" configuration file, under the [mysqld] section.
For MySQL servers versions previous to 8.0, the query_cache_type should be set to 2
The "query_cache_size" should be set to a value no higher than 200MB for overall optimizations.
The final configuration would look like this:
[mysqld]
query_cache_type = 2
query_cache_size = 100M (or 200MB)
Make sure to restart the MySQL service by running the following command:
/scripts/restartsrv_mysql
For detailed information on how to configure the MySQL query cache, please visit https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_query_cache_size