Let’s take a closer look at defining MySQL query_cache_size. At Bobcares, with our MySQL Support Services, we can handle your MySQL issues.

What is query_cache_size in MySQL?

In order to retrieve query results fast without having to start from scratch, MySQL query cache saves query results of frequently and recently run queries. They are highly helpful in enhancing database performance and query speed.

Let’s see how to enable MySQL query cache.

• Firstly, log into MySQL and check the availability of query cache for the DB using the below command:

  mysql> SHOW VARIABLES LIKE 'have_query_cache';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | have_query_cache | YES   |
  +------------------+-------+

   
• After confirming the availability, check the default values before modifying it.

  mysql> show variables like 'query_cache_%' ;

   
• We will see different values for the variables. Now we have to configure it after. So log off MySQL. Open the MySQL configuration by typing the following command on the terminal.

  $ sudo vi /etc/mysql/my.cnf

   
• Now add the following lines under /etc/my/my.cnf.

  query_cache_type = 1
  query_cache_size = 256M
  query_cache_limit = 2M

   
• Finally, restart MySQL to apply changes.

  $ sudo service mysql restart

   

Now we’ve successfully enabled the MySQL query cache. While we are configuring the query cache, we came across certain variables. Let’s explore the details of each of them.

query_cache_type

From MySQL 5.6.8, it is OFF by default. Setting this to 0 or OFF disables the MySQL query cache. We have to set it to 1 to enable query cache.

MySQL query_cache_size

It displays the overall memory allotted for the MySQL cache. 1MB is the default setting. We can set it up to 4GB taking into account our needs. However, we don’t advise high values.

query_cache_limit

It displays the maximum size of query results that can be cached. The default value is 1MB. We can set it up to 4GB. However, we don’t advise high values.

[Need assistance with another issue? We’re here to help.]

Conclusion

In this article, we have provided the details from our Support team of the MySQL query cache, including the details of query_cache_size.