About MySQL Query Cache
Percona provides a good presentation. You can read it here http://www.percona.com/files/presentations/MySQL_Query_Cache.pdf
Cache cache request ratio
Qcache_hits / (Qcache_hits + Com_select)
You can get the values of each variable 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 does pruning mean?
The MySQL Query Cache has a size limit. when a new QC record tries to save QC and the size reaches its maximum, what can happen? I think about it for 1 minute ....
Yes. we need to delete some data in QC in order to save the new record. it means cropping. The QC page to be deleted is determined by the LRU algorithm.
Therefore, I suggest you increase the size of the QC. see here http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
Limited amount of usable memory . Requests are constantly invalid from the query cache using table updates, which means that the number of requests in the cache and used memory cannot grow forever, even if you have a very large number of different queries being executed. Of course, in some cases you have tables that never change, which might trigger a cahe query, but this is unusual. Thus, you can set the query cache to a specific value and see Qcache_free_memory and Qcache_lowmem_prunes. If you do not get most of lowmem_prunes and free_memory remains high, you can reduce query_cache accordingly. Otherwise, you can increase it and see if the efficiency improves.
Query cache lock
An increase in QC is a good starting point. but Query Cache not magic. The query cache has a drawback. At the same time, only one connection can use Query Cache. Could you use the MySQL PROFILE function? here is an example. when each request is executed, the lock request lock lock is blocked twice. (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 Resets when a table changes
(I'm not sure you already know about this). when the QC has 100 requests associated with T1, one connection is inserted into T1 (or removes or updates), 100 requests are cleared. So if there are a lot of changes. disabling QC is better. It depends on your environment.
Identify what is the bottleneck
when 200 connections and the server is slow check this request
- SHOW ENGINE INNODB STANDARD,
- 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 the concurrency stream.