Enabling MySQL and MariaDB Query Cache: A Reliable Acceleration Method

Database Technologies
1

Enabling MySQL and MariaDB Query Cache: A Reliable Acceleration Method

When it comes to optimizing database performance, many beginners often turn to external caching extensions like Memcached or Redis. However, these extensions can sometimes be a burden and introduce security risks, especially for small blog servers. In this article, we’ll explore a native and reliable acceleration method: the MySQL and MariaDB query cache.

Understanding Query Cache Usage

MySQL and MariaDB support the “Query Cache” feature, which can significantly reduce the CPU usage of your database server. By enabling the query cache, you can expect a reduction of up to 10% in CPU usage, as observed by the author, who has accelerated their MariaDB database performance by nearly 20% using this method.

Enabling Query Cache

To enable the query cache, you need to modify the MySQL configuration file. Run the following command to view the current query cache settings:

mysql> show variables like '%query_cache%';

This will display the current settings for the query cache. If the query_cache_type is not set to ON, you need to modify the configuration file by adding the following lines:

[Mysqld]
query_cache_size = 20M
query_cache_type = ON

Restart the MySQL service to apply the changes:

service mysql restart

Viewing Cache Usage

To view the current cache usage, run the following command:

mysql> show status like 'qcache%';

This will display the current cache statistics, including:

  • Qcache_free_blocks: The number of free cache memory blocks.
  • Qcache_free_memory: The amount of free cache memory.
  • Qcache_hits: The number of queries that have hit the cache.
  • Qcache_inserts: The number of queries that have been inserted into the cache.
  • Qcache_lowmem_prunes: The number of cache entries that have been pruned due to low memory.
  • Qcache_not_cached: The number of queries that have not been cached.
  • Qcache_queries_in_cache: The number of queries currently cached.
  • Qcache_total_blocks: The total number of cache blocks.

Understanding Each Parameter

  • Qcache_free_blocks: This parameter indicates the number of free cache memory blocks. A large number of free blocks may indicate debris in the cache.
  • Qcache_free_memory: This parameter indicates the amount of free cache memory.
  • Qcache_hits: This parameter indicates the number of queries that have hit the cache. A high number of hits compared to inserts may indicate effective caching.
  • Qcache_inserts: This parameter indicates the number of queries that have been inserted into the cache.
  • Qcache_lowmem_prunes: This parameter indicates the number of cache entries that have been pruned due to low memory. A growing number of prunes may indicate severe debris or low memory issues.
  • Qcache_not_cached: This parameter indicates the number of queries that have not been cached. A high number of non-cached queries may indicate that the cache is not effective for certain types of queries.
  • Qcache_queries_in_cache: This parameter indicates the number of queries currently cached.
  • Qcache_total_blocks: This parameter indicates the total number of cache blocks.

Avoiding Cached Statements

If you don’t want to use cached statements, you can use the SQL_NO_CACHE keyword:

select SQL_NO_CACHE count(*) from users where email = 'hello';

Conclusion

The MySQL and MariaDB query cache is a native and reliable acceleration method that can significantly reduce CPU usage and improve database performance. By enabling the query cache and monitoring its usage, you can optimize your database performance without introducing security risks or relying on external caching extensions.