Useful Snippets

Welcome!


This blog is used to collect useful snippets related to Linux, PHP, MySQL and more. Feel free to post comments with improvements or questions!

RSS Latest posts from my personal blog


Most viewed posts


Subscribe to RSS feed


MySQL query cache overview – Enabling and checking status

Stanislav KhromovStanislav Khromov

Enabling query cache

Edit your my.cnf

Below you will find some example values to get you started. (Paste them after the [mysqld] declaration)

query_cache_size    = 96M
tmp_table_size      = 16M
max_heap_table_size = 16M
table_open_cache    = 256
thread_cache_size   = 32
query_cache_limit   = 8M

Now log onto MySQL from CLI:

mysql -u root -p

Check cache settings:

SHOW VARIABLES LIKE 'query%';

Result example:

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_alloc_block_size       | 8192      |
| query_cache_limit            | 8388608   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 100663296 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
| query_prealloc_size          | 8192      |
+------------------------------+-----------+

The important part here is that query_cache_type is ON and that query_cache_size is not 0.

Check cache performance

To see how the cache is performing (hits/misses and free space), run:

SHOW STATUS LIKE 'Qc%';

Result example:

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 105      |
| Qcache_free_memory      | 98688872 |
| Qcache_hits             | 2946     |
| Qcache_inserts          | 1581     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 13       |
| Qcache_queries_in_cache | 1357     |
| Qcache_total_blocks     | 2850     |
+-------------------------+----------+

More reading

Speed Up Your Web Site With MySQL Query Caching
MySQL.com Query Cache Configuration

Web Developer at Aftonbladet (Schibsted Media Group)
Any opinions on this blog are my own and do not reflect the views of my employer.
LinkedIn
Twitter
WordPress.org Profile
Visit my other blog

Comments 0
There are currently no comments.