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

Nemathelminth

Answer 1

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

Tweak 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

Cohdwell

great answer, thanks. I just have a question though... When a buffer page is expired, is it just flagged as deleted? I just want to make sure there is not some innodb thread running which periodically prunes expired buffer pages (and potentially creates a bigger load on the database because the timeout is so low). --- I know its only temporary for testing, but I need to get a good handle on these particular benchmarks – Nemathelminth

@Rolando, If I have many other programs running and need MySQL to use as little RAM as possible, does it mean that I should set innodb buffer pool to zero? Or is there a minimum required amount? – Yellowstone

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

Lucania

Just a side note, SQL_NO_CACHE has no effect on MySQL 8, the query cache was removed and the SQL_CACHE modifier was removed as well. – Chrischrism

@Chrischrism - Thanks. I like to keep Answers up-to-date, so I added edited in a note to that effect. – Lucania