什么是 mysql 中每天的查询缓存修剪

what is Query cache prunes per day in mysql

提问by ursitesion

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:

我在 Windows 2008 服务器中使用 MySQl 5.1 社区版 MySQL。我的应用程序面临性能问题。所以，我开始关注 MySQL 配置变量的调整。当我在 MySQL 数据库中运行 MySQL tuner 时，我收到以下红色标记的消息：

 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...

它对我的数据库有什么影响以及它如何影响性能？它的最佳值应该是什么值？哪些 MySQL 配置参数与查询缓存修剪有关？
如果您需要更多信息，请告诉我。
非常感谢...

回答by Jason Heo

About The MySQL Query Cache

关于 MySQL 查询缓存

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

Percona 提供了很好的演示。你可以在这里阅读 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....

MySQL 查询缓存有大小限制。当新的 QC 条目尝试在大小达到最大值时保存 QC 时，会发生什么？思考1分钟....

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.

是的。我们必须删除 QC 中的一些数据以保存新条目。这意味着“修剪”。要删除的 QC 页由 LRU 算法决定。

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

所以我建议你增加QC尺寸。参考这里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.

有限的可用内存量– 查询缓存中的查询不断因表更新而失效，这意味着即使您正在运行大量不同的查询，缓存中的查询数量和使用的内存也不会永远增长。当然，在某些情况下，您有从未修改过的表，这会导致查询缓存泛滥，但这是不寻常的。因此，您可能希望将查询缓存设置为某个值并观察 Qcache_free_memory 和 Qcache_lowmem_prunes – 如果您没有获得太多的 lowmem_prunes 并且 free_memory 保持高位，您可以适当地减少 query_cache。否则，您可能希望增加它并查看效率是否提高。

Query Cache lock

查询缓存锁

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

增加 QC 是一个很好的起点。但Query Cache不是魔法。查询缓存有缺点。同时，只有一个连接可以使用Query Cache。您可以使用 MySQLPROFILE功能吗？这是示例。当每个查询执行时，连接锁查询缓存锁两次。（一份用于检查，一份用于保存）

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

修改表时 QC 刷新

(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.

（我不确定您是否已经知道这一点）。当 QC 有 100 个与 T1 相关的查询时，一个连接插入到 T1（或删除或更新），100 个查询被刷新。所以如果有很多变化。关闭 QC 更好。这取决于你的环境。

Identify what is bottleneck

确定什么是瓶颈

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

当有 200 个连接并且服务器很慢时，请查看此查询

• SHOW ENGINE INNODB STATUS;
• SHOW PROCESSLIST;

• 显示引擎 INNODB 状态；
• 显示处理程序；

if You are using InnoDB, then check innodb_thread_concurrency, innodb_read_io_threadsand innodb_write_io_threadsthese variables are related to thread concurrency.

如果你正使用InnoDB，然后检查innodb_thread_concurrency，innodb_read_io_threads和innodb_write_io_threads这些变量与线程并发。

回答by Scalable

I think hit ratio is calculated as :

我认为命中率计算如下：

QC hit ratio = (Qcache_hits) / (Qcache_hits + Qcache_inserts) 

I believe

我相信

Com_Select = Qcache_hits + Qcache_inserts + Qcache_not_cached ;