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.

ADVERTISEMENTS

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.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallCentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
5 comments… add one
  • Dylan Apr 3, 2006 @ 18:40

    Thanks this is helpful.
    Also, if you want to disable the caching how would you do this?
    Thanks

  • 🐧 nixcraft Apr 4, 2006 @ 0:16

    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.

  • vikram Sep 26, 2008 @ 22:19

    my queries like SELECT round(sum(i.withdrawrealwin)) FROM invoice are getting cached and it is taking 3 secs time to execute a query and i want to reduce to 1 secs , so how can i do this…
    waiting for ur replay

    thanks and regards

  • vikram Sep 26, 2008 @ 22:21

    sorry my queries are not getting cached
    so how to cache this kind of queries ….?

    SELECT round(sum(i.withdrawrealwin)) FROM invoice

    • Michael Chang Sep 17, 2012 @ 21:33

      Vikram, queries like those typically can’t be cached. The use of functions (especially aggregation functions like SUM() and AVG(), as well as functions that are non-deterministic like NOW()) often preclude query caching because the output of such a query is different each time it is run. If you have no choice but to use those functions, then start looking at your index usage.

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.