刷新InnoDB缓存 [英] Flush InnoDB cache

问题描述

我有一些很少运行的报告查询，我需要表现出色而又不依赖于将它们缓存在系统中的任何位置.在测试各种模式和sproc更改时，我通常会看到第一次运行非常慢，而随后的运行很快，因此我知道正在进行一些缓存，这使得测试更改变得很麻烦.重新启动mysqld或运行其他几个大型查询是重现它的唯一可靠方法.我想知道是否有更好的方法.

I have some reporting queries that are rarely run, which I need to be performant without relying on them being cached anywhere in the system. In testing various schema and sproc changes I'll typically see the first run be very slow and subsequent runs fast, so I know there's some caching going on that's making it cumbersome to test changes. Restarting mysqld or running several other large queries are the only reliable ways to reproduce it. I'm wondering if there's a better way.

MySQL查询缓存已关闭.

The MySQL Query Cache is turned OFF.

监视磁盘，除了第一次运行，我看不到任何读取.我对磁盘缓存不那么熟悉，但是我希望如果那是正在发生缓存的地方，我仍然会看到磁盘读取，它们会非常快.

Monitoring the disk, I don't see any reads happening except on the first run. I'm not that familiar with disk cache but I would expect if that's where the caching is happening I'd still see disk reads, they'd just be very fast.

MONyog给了我我确定的证明，那就是InnoDB缓存命中率.监视它，我发现当查询速度快时，它将达到InnoDB缓冲区；当查询速度慢时，它将达到磁盘.

MONyog gives me what I think is the definitive proof, which is the InnoDB cache hit ratio. Monitoring it I see that when the query's fast it's hitting the InnoDB buffer, when it's slow it's hitting disk.

在实时系统上，我很乐意让InnoDB这样做，但是出于开发和测试目的，我对最坏的情况感兴趣.

On a live system I'll gladly let InnoDB do this, but for development and test purposes I'm interested in worst case scenarios.

我在Windows Server 2008R2上使用MySQL 5.5

I'm using MySQL 5.5 on Windows Server 2008R2

推荐答案

我发现

I found a post on the Percona blog that says:

对于MySQL缓存，您可以重新启动MySQL，这是清除所有缓存的唯一方法.您可以执行FLUSH TABLES来清除MySQL表缓存(但不能清除Innodb表元数据)，也可以执行设置全局key_buffer_size = 0；将global key_buffer_size = DEFAULT设置为零密钥缓冲区，但是无法在不重新启动的情况下清理Innodb缓冲池.

For MySQL Caches you can restart MySQL and this is the only way to clean all of the caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can do "set global key_buffer_size=0; set global key_buffer_size=DEFAULT" to zero out key buffer but there is no way to clean Innodb Buffer Pool without restart.

在评论中他继续说:

实际上，所有内容都有缓存.要进行真实的性能分析，您需要分析真实的查询组合，这将使每个具有适当缓存/命中率的查询不在循环中运行一个查询，并假设结果会很好.

Practically everything has caches. To do real profiling you need to profile real query mix which will have each query having appropriate cache/hit ratio not running one query in the loop and assuming results will be fine.

我想总结一下.确实很难测试单个查询.我的情况是我想尝试强制使用不同的索引，以确保查询计划程序选择正确的索引，而且显然，我必须在两次测试之间重新启动MySQL，才能消除缓存！

I guess that sums it up. It does make it hard to test individual queries. My case is that I want to try forcing different indices to make sure the query planner is picking the right one, and apparently I'll have to restart MySQL between tests to take the cache out of the equation!

这篇关于刷新InnoDB缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！