how to clear/flush mysql innodb buffer pool?

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:

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
  • 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

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