How does query caching in MySQL works and how to find find out my MySQL query cache is working or not?

Few days back I wrote about how to improve performance with query caching in MySQL. Someone email me about more questions.

How does query caching in MySQL helps improve performance of dynamic web site?
First query cache is new and added in MySQL v4.x.x version only so if you are using old version of MySQL server it will not work.

When MySQL server recives a request it will parse it and retrives data from database/table and sent back to client browser. If same query request (in case of dynamic content) comes repeatedly and server will just sent them result from cache (thus saving disk I/O and other associated cost with each query).

Please note that when data stored in table is modified, any related cached entries in the query cache are flushed.

How do I find out my MySQL query cache is working or not...
Very simple, MySQL provides the stats of same just type following command at mysql> prompt:

mysql> show status like 'Qcache%';

Output:

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16766912 |
| Qcache_hits             | 3        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 6        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+

For more information see the MySQL Query Cache documentation.

Featured Articles:

• 20 Linux System Monitoring Tools Every SysAdmin Should Know
• 20 Linux Server Hardening Security Tips
• 10 Greatest Open Source Software Of 2009
• My 10 UNIX Command Line Mistakes
• Top 5 Email Client For Linux, Mac OS X, and Windows Users
• Top 20 OpenSSH Server Best Security Practices
• Top 10 Open Source Web-Based Project Management Software
• Top 5 Linux Video Editor Software