MySQL Performance: MySQL => Experience with innodb_flush

Members

Help

Home

Home » Performance » MySQL » Experience with innodb_flush_method setting

Show: Today's Messages :: Show Polls :: Message Navigator

Experience with innodb_flush_method setting [message #1803]

Wed, 12 September 2007 12:08

chriswest
Messages: 14
Registered: August 2007

Junior Member

I just wanted to ask about your experience with the innodb_flush_method setting since I usually went with

innodb_flush_method             = O_DIRECT

Recently, while working with new machines, same kernel but a different Raid Controller / HD Setup, I've noticed a heavy performance drop while using O_DIRECT. Trying O_DSYNC was the same. Using the default flush method (not using the innodb_flush_method setting) worked well instead...

Any suggestions or experiences about this setting on different setups (linux kernel versions, raid setups, etc.)?

Report message to a moderator

Re: Experience with innodb_flush_method setting [message #1820 is a reply to message #1803 ]

Thu, 13 September 2007 13:08

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

O_DIRECT bypasses OS cache for reads and for writes.

Typically it can slow down things in case

1) Your buffer pool is too small (so caching in OS gives benefit)

2) You do not have battery backed up cache on RAID - flushing data may become much slower with O_DIRECT in this case as OS will be unable to reorder page write requests.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Experience with innodb_flush_method setting [message #1822 is a reply to message #1820 ]

Thu, 13 September 2007 16:29

chriswest
Messages: 14
Registered: August 2007

Junior Member

Thanks a lot for your answer Peter!

I just don't get the point about the BBU - I thought a BBU just helps in case of a power failure and keeps data in the cache until the machine is up again - why would O_DIRECT benefit from it during "normal" operation? Or was the point all about enabling write cache on the raid controller which might result in data loss without a BBU and which, if disabled, would dramatically slow down O_DIRECT?

Report message to a moderator

Re: Experience with innodb_flush_method setting [message #1829 is a reply to message #1822 ]

Fri, 14 September 2007 06:50

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

If you have correctly configured box your drive/RAID cache will be write-through if you do not have BBU. Otherwise it is very easy to get database and journaling file system toasted on power failure.

so BBU usually makes a difference between cache operating in write back and write through mode which makes a lot of difference.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	4GB limit on 32 bit os?
Next Topic:	Can't change key_buffer_size

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 8 18:57:44 EST 2009

Total time taken to generate the page: 0.02323 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.