Loading ...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.
Want to stay up to date with the latest Linux tips, news and announcements? Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
You may also be interested in other helpful articles:
- Enable the query cache in MySQL to improve performance
- Tutorial: MySQL Connection Management and caching in PHP
- How to improve Apache httpd performance with mod_cache - caching dynamic content
- How to optimize a web page for faster and better experience
- MySQL Proxy Load balancing and Failover Tutorial
Discussion on This Article:
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
Thanks this is helpful.
Also, if you want to disable the caching how would you do this?
Thanks
A value of 0 or OFF (in config file) prevents caching or retrieval of cached results i.e. disable the caching type following command as mysql admim:
mysql> SET GLOBAL query_cache_size = 0;
Or open my.cnf remove query_cache_size variable or set it to zero.