How to optimize MySQL Query Cache
One simple task you can do to speed up your website is to activate the MySQL Query Cache mode.
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 query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content. The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
How to optimize MySQL Query Cache
- Log in to mysql server
mysql -u root -p - Check the MySQL Query Cache status
show variables like ‘query_cache_%’;
Parameter:- The query_cache_limit value determines the maximum size of individual query results that can be cached
- The minimum amount of memory allocated to each block is determined by the query_cache_min_res_unit variable.
- The query_cache_size controls the total amount of memory allocated to the query cache. If the value is set to zero, it means query cache is disabled.
- MySQL determines the queries to cache by examining the query_cache_type variable.
- The variable query_cache_wlock_invalidate controls whether MySQL should retrieve results from the cache if the table used on the query is locked.
- Edit file /etc/mysql/my.cnf with the command
nano /etc/mysql/my.cnf
add the following line
[mysqld]
query_cache_type=1
query_cache_size = 12M
query_cache_limit=256K - Restart mysql server
service mysql restart - Re-check the MySQL Query Cache status
show variables like ‘query_cache_%’;
