High load averages and MySQL's query cache

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