DimitriK's (dim) Weblog

MySQL Performance: InnoDB Dirty Pages & Log Size Impact

The beginning of this story is coming from the first weeks of the last year - seeking for the most optimal MySQL config parameters I've discovered a strange thing:

• my dirty pages percentage setting 15 was completely ignored by InnoDB during my tests!...
• once the test workload was finished it still took 30 minutes yet to flush dirty pages!...

I've told about a lot of people on that time, but everybody were just surprised and did not really believe such case is possible... Agree, it's hard to believe ;-)

So, to refresh the story, let me first to show you the following picture:

As you may see here the dirty pages percentage is higher than 15% for sure :-) As well you may see the 30 minutes of dirty pages flushing once the workload is finished (too much according to me on the idle server! - and probably recovery process will take even more time if my server will crash?...)

It looked abnormal for me, and what I found on that time - reducing log size to 128MB  gave less or more better stability and dirty pages flushing always finished in few minutes at the end of each workload!

My understanding was :

• the dirty pages percentage is completely ignored by InnoDB
• the only thing which limit a number of dirty pages is the log size!
• once redo log has no more free blocks it'll force dirty pages flushing to to continue its work!
• the most optimal redo log size I've found on that time was 128MB

So, all my MySQL tests I've done until now were executed with innodb_log_file_size=128M. And I've started the current study to bring some lights on previously found issue and whenever possible to understand what's going wrong...

The full report about this study you may find here: http://dimitrik.free.fr/db_STRESS_MySQL_InnoDB_dirty_pages_and_log_size_impact_May2009.html

Hope it explains well what's going wrong with InnoDB on my tests. I'm also curious if somebody else met similar problems, or probably discovering them now :-) I think the found bug is important because it's touching InnoDB internals and when things are not working at all as expected - it's important to know. As well seeing buffer pool cache wasted was not fun either...

Don't know how easy it'll be to fix, but maybe you already have your idea? ;-)

Any comments are welcome! :-)