Enquiry on MySQL, InnoDB, ext4 and innodb_flush_log_at_trx_commit

[ccto]

hello All,

From internet some community post, ext4 + InnoDB may cause jbd2 (journal block device) high I/O usage.

From MySQL documentation, InnoDB, by default, commit every transaction into file system (i.e. innodb_flush_log_at_trx_commit = 1 , default)

If set innodb_flush_log_at_trx_commit to 0 or 2 , shall it have better performance, is it?
Does it affect existing data?

Can anyone share your experience?

(one of our server running MySQL 5.5 on ext4, and some InnoDB database, it has some load average. So looking for ways to reduce it)

ref.:
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
http://themech.net/2011/10/struggling-with-mysql-performance-problems-under-linux/
http://forums.devshed.com/mysql-help-4/effect-filesystems-mysql-performance-948013.html

Thank you very much for your kind attention.

Regards

 

[Themis]

Yes, ext4 + InnoDB can and eventually will cause high I/O which may lead if you are unlucky to system crash, as the data volume is growing.

Currently I am experiencing system freeze for 1-2 minutes on a VM with RedHat 6.4 with the database on 2 other separate VMs (one for read and the second/replication for read and write), while the system tries to write a 65GB InnoDB log file to disk (all 3 VMs have the same specs: 12 vCPUs, 32Gb RAM and 0.20-1.00 average load).

Personally I don't want to mount the ext4 drive with barrier=0 as this may lead to lost transactions and/or filesystem corruption. On the other hand using ext3 will improve performance a lot, only because barrier=0 is set by default. And this also may lead to filesystem corruption.

Changing the innodb_flush_log_at_trx_commit value would be the only solution, but still, you may end up loosing up to 1 second of transactions.

If you are looking for filesystem integrity, go with ext4 + barrier=1 (the default mount option) and live with the reduced performance.
If you are absolutely sure that no accidental power failure will ever happen, turn off write barriers on ext4. But if something happens, you may not be able to recover your system.

Note that mounting all drives with "noatime", which also implies "nodiratime" (no need to place both flags) should give you a noticable improvement. Try this first if you haven't yet...

It all depends on how critical your system is.