如何清除/刷新mysql innodb缓冲池? [英] how to clear/flush mysql innodb buffer pool?

问题描述

我正在优化一个大型查询，并希望在前后相同的基线上运行它，以进行比较.

I'm perf tuning a large query, and want to run it from the same baseline before and after, for comparison.

我了解mysql查询缓存，但与我无关，因为无论如何都不会缓存2个查询.

I know about the mysql query cache, but its not relevant to me, since the 2 queries would not be cached anyway.

正在缓存的是缓冲池中的innodb页面. 有没有一种方法可以清除整个缓冲池，以便我可以从同一起点比较两个查询?

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?

虽然在运行每个查询后重新启动mysql服务器无疑是可行的，但我想尽可能避免这种情况

Whilst restarting the mysql server after running each query would no doubt work, Id like to avoid this if possible

推荐答案

警告:以下内容仅适用于MySQL 5.5和MySQL 5.1.41+(InnoDB插件)

WARNING : The following only works for MySQL 5.5 and MySQL 5.1.41+ (InnoDB Plugin)

使用以下设置，在InnoDB缓冲池中输入条目的持续时间:

Tweek the duration of entries in the InnoDB Buffer Pool with these settings:

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

查看这些设置的定义

• MySQL 5.5
  • innodb_old_blocks_time
  >
  • innodb_old_blocks_pct
  >
  • innodb_max_dirty_pages_pct
  >
  • MySQL 5.5
    • innodb_old_blocks_time
    • innodb_old_blocks_pct
    • innodb_max_dirty_pages_pct
    • innodb_old_blocks_time
    >
    • innodb_old_blocks_pct
    >
    • innodb_max_dirty_pages_pct
    >
    • innodb_old_blocks_time
    • innodb_old_blocks_pct
    • innodb_max_dirty_pages_pct

    这篇关于如何清除/刷新mysql innodb缓冲池?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！