what is Query cache prunes per day in mysql

Question

I am using MySQl 5.1 community version of MySQL in Windows 2008 server. I am facing performance issue in my application. So, I started focusing on MySQL configuration variables tweaking. When I run MySQL tuner in my MySQL database, I got below message in red mark:

 Query cache prunes per day: 57482 

What is its effect in my database and how it affects the performance? What value should be its optimal value? Which MySQL configuration parameters are related with Query cache Prunes?
Please let me know if you need more information.
Many thanks...

Answer 1

About The MySQL Query Cache

Percona provides a good presentation. You could read it here http://www.percona.com/files/presentations/MySQL_Query_Cache.pdf

Query Cache Hit ratio

Qcache_hits / (Qcache_hits + Com_select)

You can get values of each variables as follows.

mysql> SHOW GLOBAL STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16733288 |
| Qcache_hits             | 1291736  |
| Qcache_inserts          | 1301133  |
| Qcache_lowmem_prunes    | 15214    |
| Qcache_not_cached       | 10415252 |
| Qcache_queries_in_cache | 195      |
| Qcache_total_blocks     | 113      |
| Queries                 | 15142148 |
| Questions               | 15139850 |
+-------------------------+----------+

mysql> SHOW GLOBAL STATUS LIKE 'Com_select%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_select    | 12345488 |
+---------------+----------+

What pruning means

MySQL Query cache has size limitation. when new QC entry try to save QC while size reaches maximum value, what could happen? think about it 1 minute....

yes. we must remove some data in QC to save new entry. this means 'pruning'. QC page to be removed is decided by LRU algorithm.

So I suggest you to increase QC size. refer to here http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

Limited amount of usable memory – Queries are constantly being invalidated from query cache by table updates, this means number of queries in cache and memory used can’t grow forever even if your have very large amount of different queries being run. Of course in some cases you have tables which are never modified which would flood query cahe but it unusual. So you might want to set query cache to certain value and watch Qcache_free_memory and Qcache_lowmem_prunes – If you’re not getting much of lowmem_prunes and free_memory stays high you can reduce query_cache appropriately. Otherwise you might wish to increase it and see if efficiency increases.

Query Cache lock

Increasing QC is good start point. but Query Cache is not magic. Query Cache has disadvantage. At the same time, only one connection can use Query Cache. Could you use MySQL PROFILE feature? here is example. when every query is executed, connection locks query cache lock two times. (one for checking, one for saving)

mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a;
Empty set (0.00 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| Waiting for query cache lock   | 0.000007 |  <= waiting for query cache lock. to check weather query is in QC
| checking query cache for query | 0.000027 |      
| checking permissions           | 0.000010 |
                ....
| Waiting for query cache lock   | 0.000022 |
                ....
| Sending data                   | 0.000029 |
| end                            | 0.000009 |
                ....
| Waiting for query cache lock   | 0.000007 |  <= also here, to save query in QC
| freeing items                  | 0.000010 |
| Waiting for query cache lock   | 0.000007 |
| freeing items                  | 0.000005 |
| storing result in query cache  | 0.000009 |
| logging slow query             | 0.000008 |
| cleaning up                    | 0.000007 |
+--------------------------------+----------+
24 rows in set (0.00 sec)

QC Flushes when table modified

(I'm not sure you already know about this). when QC has 100 queries related T1, one connection inserts to T1 (or delete or update), 100 queries is flushed. So iif there are many changes. turning off QC is better. It depends on you environment.

Identify what is bottleneck

when there are 200 connections and server is slow, check out this query

• SHOW ENGINE INNODB STATUS;
• SHOW PROCESSLIST;

if You are using InnoDB, then check innodb_thread_concurrency, innodb_read_io_threads and innodb_write_io_threads these variables are related to thread concurrency.