频繁查询缓存失效的开销值得吗？

Question

我目前正在开发一个MySQL数据库，在这个数据库中，我们从查询缓存中看到了大量的失效，主要是因为在许多表上执行的INSERT、DELETE和UPDATE语句数量很大。

我试图确定的是，允许查询缓存用于针对这些表运行的SELECT语句是否有任何好处。由于它们很快就失效了，所以在我看来最好的方法是对SELECT语句和这些表一起使用SQL_NO_CACHE。

频繁失效的开销值得吗？

编辑:应用户@RolandoMySQLDBA的请求，以下是关于MyISAM和INNODB的信息。

InnoDB

• 数据大小: 177.414 GB
• 索引大小: 114.792 GB
• 表号: 292.205 GB

MyISAM

• 数据大小: 379.762 GB
• 索引大小: 80.681 GB
• 表号: 460.443 GB

更多信息：

• 版本: 5.0.85
• query_cache_limit: 1048576
• query_cache_min_res_unit: 4096
• query_cache_size: 104857600
• query_cache_type: ON
• query_cache_wlock_invalidate: OFF
• innodb_buffer_pool_size: 8841592832
• 24 of

Answer 1

应该直接禁用查询缓存。

[mysqld]
query_cache_size = 0

然后重新启动mysql。我为什么要提出这个建议？

查询缓存将始终与InnoDB对接。如果InnoDB的MVCC允许查询缓存中的查询服务，如果修改不影响其他事务的可重复读取，那就太好了。不幸的是，InnoDB并没有做到这一点。显然，您有许多查询会很快失效，并且可能不会被重用。

对于InnoDB下的MySQL 4.0，查询缓存对于事务是禁用的。对于MySQL 4.1+，InnoDB在允许按表访问查询缓存时扮演流量cop角色。

从您的问题的角度来看，我要说，删除查询缓存的理由不在于开销，而在于InnoDB如何管理它。

有关InnoDB如何与查询缓存交互的更多信息，请阅读图书高性能MySQL (第二版)的213-215页。

如果您的全部或大部分数据是MyISAM，那么您可以使用您最初的使用SQL_NO_CACHE的想法。

如果您将InnoDB和MyISAM混合在一起，则必须根据缓存丢失的高度为应用程序找到合适的平衡。事实上，同一本书第209至210页指出了缓存丢失的原因：

• 查询是不可缓存的，这是因为它包含一个不确定的结构(例如CURRENT_DATE)，或者因为它的结果集太大，以致于store.Both类型的不可缓存查询增加了Qcache_not_cached状态变量。
• 服务器以前从未见过该查询，因此它从未有机会缓存其结果。
• 查询的结果以前是缓存的，但是服务器删除了它。这可能是因为没有足够的内存来保存它，因为有人指示服务器删除它，或者因为它失效了。

高缓存丢失和很少不可缓存查询的根本原因可能是：

• 查询缓存还不温。也就是说，服务器还没有机会用结果集填充缓存。
• 服务器正在看到以前从未见过的查询。如果您没有很多重复的查询，甚至在缓存预热之后也会发生这种情况。
• 有许多缓存无效。

更新2012-09-06 10:10

查看最新更新的信息，您的query_cache_limit设置为1048576 (1M)。这将任何结果集限制为1M。如果检索到更大的内容，它就不会被缓存。虽然您已经将query_cache_size设置为104857600 ( 100米)，但这只允许在一个完美的世界中设置100个缓存结果。如果您执行数百个查询，碎片将很快出现。您也有4096 (4K)作为最小大小结果集。不幸的是，mysql没有对查询缓存进行碎片整理的内部机制。

如果您必须拥有查询缓存，并且拥有如此多的RAM，则可以执行以下操作：

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(60);
SET GLOBAL query_cache_size = 1024 * 1024 * 1024;

以清除查询缓存。您将丢失所有缓存的结果，因此在非高峰时间运行这些行。

我还将指派以下人员：

• query_cache_size = 1G
• query_cache_limit = 8M

剩下23G内存。我想提出以下几点：

• innodb_buffer_pool_size = 12G
• key_buffer_size = 4G

剩下7G了。这对于OS和DB连接来说应该足够了。

请记住，键缓冲区只缓存MyISAM索引页，而InnoDB缓冲池缓存数据和索引。

还有一个建议:升级到MySQL 5.5，以便为多个CPU配置InnoDB，为读/写I/O配置多个线程。

请参阅我以前关于使用MySQL 5.5同时访问InnoDB的多个CPU的文章

• Nov 24, 2011：为什么MySQL5.5比5.1慢(linux，使用mysqlslap)
• Oct 05, 2011：查询在一些较新的MySQL版本中运行很长时间
• Sep 20, 2011：多核与MySQL性能
• Jun 19, 2011：如何正确地执行MySQL烘焙？

更新2012-09-06 14:56美国东部时间

我清除查询缓存的方法相当极端，因为它会缓存数据并形成一个完全不同的RAM段。正如您在评论中指出的那样，FLUSH QUERY CACHE (如您所建议的)甚至RESET QUERY CACHE都会更好。为了澄清这一点，当我说“没有内部机制”时，我的意思就是这样。碎片整理是必需的，必须手动完成。它需要被卡。

如果您在InnoDB上执行DML (插入、更新、删除)比在MyISAM上更频繁，我会说完全删除查询缓存，我在开始时已经说过了。

Answer 2

坏: query_cache_size = 1G

为什么？因为同花顺要花多长时间。也就是说，当某些写入发生时，将对整个1GB进行扫描，以查找对被修改的表的任何引用。QC越大，速度越慢。我建议尺寸不超过5000万，除非你的数据很少改变。

QC是MyISAM和InnoDB的开销。它消灭了一个全球互斥体，而且太快就把它取出来了。这个互斥是MySQL不能有效地使用超过8个核心的原因之一。

SQL_NO_CACHE直到互斥锁之后才被注意到！那个标志的唯一用途就是基准测试。

通常情况下，最好将RAM分配给其他缓存。

Answer 3

我可以想出一个完美的案例，我们已经对它进行了彻底的测试并在生产中运行.我称之为“快车道”集群策略：

如果您确实使用像MaxScale这样的代理进行读写拆分，或者您的应用程序能够使用，那么您可以将那些很少失效的表的一些读取发送给打开查询缓存的辅助服务器，其余的则发送给关闭它的其他从站。

因此，我们这样做，并在负载测试期间(不是benchmark...the真正的交易)，每分钟处理对集群的400万次调用。应用程序确实在master_pos_wait()上等待一些东西，所以它被复制线程控制，尽管我们看到它在非常高的吞吐量下处于等待Qcache失效的状态，但是这些吞吐量级别甚至比集群没有Qcache时的能力还要高。

这是因为在这些机器上的小型查询缓存中很少有任何相关的内容可以使其失效(这些查询只与很少更新的表相关)。这些箱子是我们的“快车道”。对于应用程序所做的其他查询，它们不需要处理Qcache，因为它们在没有打开的情况下进入框。