Geek asks and answers

How to flush / flush mysql innodb buffer pool?

I am setting up a large query and want to run it from the same baseline before and after, for comparison.

I know about mysql query cache, but it has nothing to do with me, since 2 queries will not be cached anyway.

What is cached are innodb pages in the buffer pool. Is there a way to clear the entire buffer pool so that I can compare two requests from the same starting point?

When restarting the mysql server after running each query, it will undoubtedly work, Id would like to avoid this if possible

+5
source share
2 answers

WARNING: The following steps are performed only for MySQL 5.5 and MySQL 5.1.41+ (InnoDB plugin)

Tweek 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;

, :

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

+8

... 

SELECT SQL_NO_CACHE ...;

.

buffer_pool; QC, SQL_NO_CACHE.

, , .

, 

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handlers%';

, . , , , . , ( ) . () .

"Handler_read" . "" (, , ), (, , ). , " ".

Handler , ; .

"Handler_write" , tmp.

, ( ), , . , LIMIT, , , LIMIT .

buffer_pool, Innodb_buffer_pool_reads, (?) , . , . - , STATUS , "", "".

+2

More articles:


All Articles