If you ever have managed some website/portal that has any meaningful traffic, you already know what can it do to your MySQL server performance if you don’t optimise your queries and enable php server side caching. One of the things that you can do at DB level is to enable mysql_query_cache to speed up DB performance.
A Good or not so good thing about mysql_query_cache is that the cache expires automatically once the table is modified (inserts, updates, delete, etc). If there are these operations are veing performed in every table in your website, it might not help much but it can’t harm your website either so go ahead and just enable the cache and see what difference it makes.
How to Enable mysql_query_cache
To enable mysql_query_cache, you need to edit your my.cnf and set query_cache_type to 1, and set the query_cache_size to some value. This is set it to 64Mb in the following example. Default MySQL location for this file is /etc/my.cnf on your Linux server.
How to Check if mysql_query_cache is Enabled Already
mysql> show variables like 'query%';
You will see something like this:
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864|
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
Please give special attention to query_cache_type variable which should be set to ON after making my.cnf changes, the query_cache_size variable should not be set to zero. Zero value means your query cache is practically disabled since it has no memory at its disposal to use.
When Enabling mysql_query_cache Can Help Most
Following are conditions which can best exploit the benefits of the MySQL query cache, although the query cache can be effective in most situations, but it will help greatly in following cases:
- Identical queries are issued by the same or multiple clients on a repetitive basis.
- The underlying data being accessed is static or semi-static in nature.
- Queries have the potential to be resource-intensive and/or build brief, but complexly computed result sets.
Hope that helps.
Cheers!
