Symptoms
MySQL is heavily using CPU time, and load averages are increasing due to MySQL's usage.
Description
Please note, the query_cache option was deprecated in version 5.7 and removed in 8.0, and this option had proved to be inefficient. It's only recommended to use the query_cache if you know your application will benefit. MySQL has a blog post explaining the decision you can refer to for more information:
MySQL 8.0: Retiring Support for the Query Cache
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.
This article tries to cover the most fundamental aspects of MySQL's query cache system.
The primary two directives to cover are:
query_cache_size - Determines the size of the cache space.
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 see the MySQL Documentation:
System Variable query_cache_size | MySQL Documentation