how to clear/flush mysql innodb buffer pool?

Question

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.

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?

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

Solution 1

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

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

// This is 0.25 seconds
SET GLOBAL innodb_old_blocks_time=250; 
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
• innodb_old_blocks_time
• innodb_old_blocks_pct
• innodb_max_dirty_pages_pct
• MySQL 5.1.41+
• innodb_old_blocks_time
• innodb_old_blocks_pct
• innodb_max_dirty_pages_pct

Solution 2

Much simpler... Run this twice

SELECT SQL_NO_CACHE ...;

And look at the second timing.

The first one warms up the buffer_pool; the second one avoids the QC by having SQL_NO_CACHE. (In MySQL 8.0, leave off SQL_NO_CACHE; it is gone.)

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

This Handler technique is impervious to what else is going on; it gives consistent results.

"Handler_write" indicates that a tmp table was needed.

Numbers that approximate the number of rows in the table (or a multiple of such), probably indicate a table scan(s). A number that is the same as LIMIT might mean that you build such a good index that it consumed the LIMIT into itself.

If you do flush the buffer_pool, you could watch for changes in Innodb_buffer_pool_reads to give a precise(?) count of the number of pages read in a cold system. This would include non-leaf index pages, which are almost always cached. If anything else is going on in the system, this STATUS value should not be trusted because it is 'global', not 'session'.