MySQL Performance Tuning

This document explains how to enable and tune query caching to achieve good performance.

Checking
Check to see if you have query caching enabled.

mysql> SHOW GLOBAL VARIABLES LIKE "query_cache%";

If you do, query_cache_size will not be 0.


Enabling
Set the query_cache_size variable to a decent value, below we set it to approximately 8Mb.

mysql> SET GLOBAL query_cache_size = 8000000;

Set the query_cache_limit, this is the maximum size of individual query results. Below we set it to approximately 128Kb as we won't be returning anything bigger from queries to our table.

mysql> SET GLOBAL query_cache_limit = 128000;

Depending on your query result size, you may want to set the query_cache_min_res_unit which has a default of a 4Kb (4096) block size. Setting this to a lower value for smaller query results and larger for larger query results will give you better performance and reduce fragmentation. We'll leave this variable at its default.


Check the results
Lets see what is happening...

mysql> SHOW STATUS LIKE "QC%";


Make the changes permanent
Add the following to your /etc/my.cnf file under mysqld ...

query_cache_size=8000000
query_cache_limit=128000

---