如何清除/刷新 mysql innodb 缓冲池？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10542853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to clear/flush mysql innodb buffer pool?
提问by carpii
I'm perf tuning a large query, and want to run it from the same baseline before and after, for comparison.
我正在对一个大型查询进行性能调整，并希望在之前和之后从相同的基线运行它，以进行比较。
I know about the mysql query cache, but its not relevant to me, since the 2 queries would not be cached anyway.
我知道 mysql 查询缓存，但它与我无关，因为无论如何都不会缓存 2 个查询。
What is being cached, is the innodb pages, in the buffer pool. Is there a way to clear the entire buffer pool so I can compare the two queries from the same starting point?
缓存的是缓冲池中的 innodb 页面。有没有办法清除整个缓冲池，以便我可以从相同的起点比较两个查询？
Whilst restarting the mysql server after running each query would no doubt work, Id like to avoid this if possible
虽然在运行每个查询后重新启动 mysql 服务器无疑会起作用，但我想尽可能避免这种情况
回答by RolandoMySQLDBA
WARNING : The following only works for MySQL 5.5 and MySQL 5.1.41+ (InnoDB Plugin)
警告：以下仅适用于 MySQL 5.5 和 MySQL 5.1.41+（InnoDB 插件）
Tweek the duration of entries in the InnoDB Buffer Pool with these settings:
使用以下设置调整 InnoDB 缓冲池中条目的持续时间：
SET GLOBAL innodb_old_blocks_time=250; // This is 0.25 seconds
SET GLOBAL innodb_old_blocks_pct=5;
SET GLOBAL innodb_max_dirty_pages_pct=0;
When you are done testing, setting them back to the defaults:
完成测试后，将它们设置回默认值：
SET GLOBAL innodb_old_blocks_time=0;
SET GLOBAL innodb_old_blocks_pct=37;
SET GLOBAL innodb_max_dirty_pages_pct=90; // 75 for MySQL 5.5/MySQL 5.1 InnoDB Plugin
Check out the definition of these settings
查看这些设置的定义
- MySQL 5.5
- MySQL 5.1.41+
回答by Rick James
Much simpler... Run this twice
更简单... 运行两次
SELECT SQL_NO_CACHE ...;
And look at the secondtiming.
再看第二个时机。
The first one warms up the buffer_pool; the second one avoids the QC by having SQL_NO_CACHE.
第一个预热buffer_pool；第二个通过具有 来避免 QC SQL_NO_CACHE。
So the second timing is a good indication of how long it takes in a production system with a warm cache.
因此，第二个时间很好地表明了在具有热缓存的生产系统中需要多长时间。
Further, Look at Handler counts
此外，查看处理程序计数
FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handlers%';
gives a reasonably clear picture of how many rows are touched. That, in turn, gives you a good feel for how much effort the query takes. Note that this can be run quite successfully (and quickly) on small datasets. Then you can (often) extrapolate to larger datasets.
给出了有多少行被触摸的相当清晰的图片。反过来，这会让您很好地了解查询需要多少努力。请注意，这可以在小型数据集上非常成功地（并且很快）运行。然后您可以（通常）外推到更大的数据集。
A "Handler_read" might be reading an index row or a data row. It might be the 'next' row (hence probably cached in the block that was read for the previous row), or it might be random (hence possibly subject to another disk hit). That is, the technique fails to help much with "how many blocks are needed".
“Handler_read”可能正在读取索引行或数据行。它可能是“下”行（因此可能缓存在为前一行读取的块中），也可能是随机的（因此可能会受到另一个磁盘命中的影响）。也就是说，该技术在“需要多少块”方面没有多大帮助。
This Handler technique is impervious to what else is going on; it gives consistent results.
这种 Handler 技术不受其他正在发生的事情的影响；它给出了一致的结果。
"Handler_write" indicates that a tmp table was needed.
“Handler_write”表示需要一个 tmp 表。
Numbers that approximate the number of rows in the table (or a multiple of such), probablyindicate a table scan(s). A number that is the same as LIMITmightmean that you build such a good index that it consumed the LIMITinto itself.
近似于表中行数的数字（或此类的倍数）可能表示表扫描。与 相同的数字LIMIT可能意味着您构建了一个很好的索引，它会将 消耗LIMIT到自身中。
If you do flush the buffer_pool, you could watch for changes in Innodb_buffer_pool_readsto give a precise(?) count of the number of pages read in a coldsystem. This would include non-leaf index pages, which are almost always cached. If anything else is going on in the system, this STATUSvalue should not be trusted because it is 'global', not 'session'.
如果您确实刷新了 buffer_pool，您可以观察变化Innodb_buffer_pool_reads以提供在冷系统中读取的页面数的精确（？）计数。这将包括几乎总是被缓存的非叶索引页。如果系统中发生任何其他事情，则STATUS不应信任此值，因为它是“全局”而不是“会话”。
