Mastering MySQL&#s Query Cache: Key Variables and Best Practices for Optimization

What is the Query Cache?

The Query Cache is a feature in MySQL designed to boost database performance by caching the results of SELECT queries. When a query that's been executed before is run again, MySQL can quickly pull the result from this cache instead of re-executing it against the database. This not only speeds up data retrieval but also reduces the load on the database, making it highly efficient for frequently run queries with consistent parameters.

How the Query Cache Works

Whenever a query is issued, MySQL first looks in the Query Cache to check if the result of an identical query has been stored previously. If there’s a match, MySQL bypasses the usual query execution process and directly serves the cached result. This is much faster as it avoids the time-consuming steps of query processing and disk access, leveraging the speed of memory access instead.

We now turn to the critical query cache variables – query_cache_type, query_cache_size, query_cache_limit, and query_cache_min_res_unit – and their impact on MySQL performance.

1. query_cache_type

The query_cache_type variable in MySQL controls whether the query cache is enabled, disabled, or set to operate only on demand.

query_cache_type Usage

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the cache rather than parsing and executing the statement again. Enabling the query cache can significantly reduce the time it takes to get results for frequently run queries by serving them from memory rather than re-executing them.

Setting the query_cache_type variable in MySQL to different values determines how the query cache behaves:

• 0 (OFF) – Disables the query cache, although a buffer of query_cache_size bytes is still allocated.

• 1 (ON) – Enables the query cache for all SELECT queries unless SQL_NO_CACHE is specified in the query.

• 2 (DEMAND) – Enables the query cache only for queries that explicitly use the SQL CACHE clause.

query_cache_type Configuration

Query_cache_type can be configured offline or online, when the server is running. Configuration may be preferred online, to allow for testing. When the server is restarted the query_cache_type will revert.

Command Line Configuration:

mysqld> set global query_cache_type = XX

Replace XX with a value to suit your database needs. To verify that the variable has been changed:

mysqld> show global variables like 'query_cache_type'

Configuration File:

[mysqld]
query_cache_type = XX

Replace XX with a value to suit your database needs. Restart MySQL server.

query_cache_type Considerations

According to general recommendations, you should set query_cache_type to 1 (ON) for environments where the data changes infrequently but reads are frequent. Set to 0 (OFF) in highly dynamic environments where the overhead of maintaining the cache may outweigh the benefits.

Enabling the query cache indiscriminately may not always yield performance benefits and can even degrade performance in some scenarios. Consider the following factors when configuring query_cache_type:

• The size of the query cache – A larger cache can hold more query results but requires more memory.

• Query patterns – Queries with frequently changing results or large result sets may not benefit from caching.

• Cache invalidation – Updates, inserts, or deletes on cached tables invalidate corresponding cache entries, leading to cache churn.

• Concurrency – The query cache is not suitable for highly concurrent workloads due to contention issues.

• MySQL version – The query cache feature has been deprecated in MySQL 5.7 and removed in MySQL 8.0, as it has limitations and can cause contention in multi-threaded environments.

2. query_cache_size

Specifies the amount of memory allocated to store the results of cached queries. It's a primary factor in determining how many results can be cached at once.

query_cache_size Usage

The query_cache_size variable determines the amount of memory allocated for the query cache. This value should be adjusted based on the nature of your workload and available memory resources:

• Small Result Sets – If your application frequently executes queries that return small result sets, a larger query cache size can be beneficial. This allows more queries to be stored in the cache, reducing the need for query execution.

• Frequent Identical Queries – In scenarios where the same queries are executed repeatedly, increasing the query_cache_size can improve performance by caching these queries and their results.

• Query Cache Hit Ratio – Monitoring the query cache hit ratio can provide insights into the effectiveness of the cache. If the hit ratio is low, increasing the query_cache_size may help improve cache efficiency.

query_cache_size Configuration

Query_cache_size can be configured offline or online, when the server is running. Configuration may be preferred online, to allow for testing. When the server is restarted the query_cache_size will revert.

Command Line Configuration:

mysqld> set global query_cache_type = XX

Replace XX with a value to suit your database needs. To verify that the variable has been changed:

mysqld> show global variables like 'query_cache_type'

Configuration File:

[mysqld]
query_cache_type = XX

Replace XX with a value to suit your database needs. Restart MySQL server.

query_cache_size Considerations

The query_cache_size should be set based on the available memory and the nature of your workload. Setting it too large can lead to memory exhaustion, while setting it too small may limit its effectiveness.

Monitoring the utilization of the cache (hits versus inserts) will guide appropriate sizing. Start with a moderate size, like 64MB to 128MB, and adjust based on the performance and available system memory.

Consider the following factors when configuring query_cache_size:

• Query patterns – Queries with frequently changing results or large result sets may not benefit from caching.

• Cache invalidation – Updates, inserts, or deletes on cached tables invalidate corresponding cache entries, leading to cache churn.

• Concurrency – The query cache is not suitable for highly concurrent workloads due to contention issues.

• MySQL version – The query cache feature has been deprecated in MySQL 5.7 and removed in MySQL 8.0 due to limitations and contention in multi-threaded environments.

3. query_cache_limit

This variable sets the maximum size for individual query results that can be cached. It prevents large queries from consuming a disproportionate amount of the cache space.

query_cache_limit Usage

When a query result exceeds the query_cache_limit, the result is not cached. This prevents excessively large or resource-intensive queries from filling up the cache with results that might not be reused frequently. By setting an appropriate value for query_cache_limit, you can ensure that only smaller, more commonly used query results are cached, optimizing the use of memory.

query_cache_limit Configuration

Query_cache_limit can be configured offline or online, when the server is running. Configuration may be preferred online, to allow for testing. When the server is restarted the query_cache_limit will revert.
Command Line Configuration:

mysqld> set global query_cache_type = XX

Replace XX with a value to suit your database needs. To verify that the variable has been changed:

mysqld> show global variables like 'query_cache_type'

Configuration File:

[mysqld]
query_cache_type = XX

Replace XX with a value to suit your database needs. Restart MySQL server.

query_cache_limit Considerations

It is typically recommended to set query_cache_limit between 1MB and 4MB, depending on the nature of the queries and the available cache size. It's important to note that setting query_cache_limit too low may result in useful query results being excluded from the cache, reducing the effectiveness of the query cache.

4. query_cache_min_res_unit

The query_cache_min_res_unit variable in MySQL determines the minimum size in bytes for blocks allocated by the query cache. This setting impacts the efficiency of the query cache by controlling the granularity of cached results.

query_cache_min_res_unit Usage

When a query result is stored in the query cache, it occupies a certain amount of memory. The query_cache_min_res_unit variable defines the minimum size of memory blocks allocated for these cached results. If a query result is smaller than this value, it will still occupy the minimum size defined by query_cache_min_res_unit.

query_cache_min_res_unit Configuration

Query_cache_min_res_unit can be configured offline or online, when the server is running. Configuration may be preferred online, to allow for testing. When the server is restarted the query_cache_min_res_unit will revert.

Command Line Configuration:

mysqld> set global query_cache_size = XX

Replace XX with a value to suit your database needs. To verify that the variable has been changed:

mysqld> show global variables like 'query_cache_size'

Configuration File:

[mysqld]
query_cache_size = XX

Replace XX with a value to suit your database needs. Restart MySQL server.

query_cache_min_res_unit Considerations

Configuring query_cache_min_res_unit involves setting the variable to a suitable value that balances memory consumption with caching efficiency. The value should be chosen based on the average size of query results in your workload.

• A smaller value may lead to more efficient memory usage but could increase overhead due to more cache entries.

• Conversely, a larger value may reduce the number of cache entries but could lead to wasted memory for smaller query results.

Analyze your workload to determine the average size of query results. Adjust the value of query_cache_min_res_unit based on this analysis to achieve a balance between memory consumption and caching efficiency. For most setups, this will fall between 16MB and 64MB.

Enable and Tune the Query Cache in 4 Steps

The query cache was deprecated as of MySQL 5.7.20 and fully removed in MySQL 8.0. If your version of MySQL is still able to use query_cache, it has to be enabled because it is disabled by default. To enable and configure the query cache in MySQL, MariaDB, or Percona, you will typically need to access your server’s my.cnf or my.ini file. Here’s a step-by-step approach:

1. Enable Query Cache – Set query_cache_type to 1 or 2. Setting query_cache_type or query_cache_size to zero will always disable the cache. For selective caching (recommended for most use cases), you would use:

mysqld> set global query_cache_type = XX

2. Set Cache Size – Define query_cache_size. A starting point might be 10-20% of your total available memory, but this requires tuning based on your workload:

mysqld> show global variables like 'query_cache_type'

3. Define Result Size Limit – Configure query_cache_limit to control the size of results stored. This might start at a few megabytes, depending on your typical query size:

[mysqld]
query_cache_type = XX

4. Adjust Minimum Result Unit – Modify query_cache_min_res_unit based on your needs. Reducing this below the default can help utilize cache space more efficiently, especially if you expect a lot of small queries:

mysqld> set global query_cache_size = XX

Monitoring Query Cache with Health Checks

QCache Fragmentation is a key indicator of the Query Cache's performance in MySQL. This feature is built to store the results of SELECT queries so that repeated requests can be fulfilled quickly without needing to rerun the query, thereby boosting performance. However, as time passes, the Query Cache can become fragmented, leading to reduced effectiveness.

Calculate QCache Fragmentation

Check out our comprehensive Health Checks documentation, which contains information and step-by-step instructions on how to calculate QCache Fragmentation.

Once you have calculated the QCache Fragmentation and QcacheDeleteRate, you need to interpret the results. Ideally, the QCache Fragmentation should be less than 10, and the QcacheDeleteRate should be less than 20.

Recommended Actions

If the QCache Fragmentation is high, you may need to adjust the size of the Query Cache to reduce fragmentation. If the QcacheDeleteRate is high, you may need to increase the size of the Query Cache or optimize your queries to reduce the number of INSERTs.

Optimize Your Query Cache with Ease

Tuning the MySQL Query Cache involves adjusting several settings to optimize database performance, from managing memory usage to reducing query times. While the variables discussed here form a good foundation, effective management requires continuous monitoring and updates based on actual system load and performance.

To simplify this process, consider using a powerful management tool like Releem that automates these adjustments. Such a tool can continuously monitor your system's performance and dynamically update the query_cache settings in real-time.

This allows you time to focus on broader goals while Releem handles the intricacies of Query Cache optimization.

The above is the detailed content of Mastering MySQL&#s Query Cache: Key Variables and Best Practices for Optimization. For more information, please follow other related articles on the PHP Chinese website!