Popular Posts

Thursday, December 08, 2011

MariaDB or MySQL ext3 ext4 journaling options for faster databases

MariaDB or MySQL ext3 ext4 journaling options for faster databases.
In my previous posting I was suggesting to use "noatime" to improve your databases performance. Now we can go a step further to make things even faster - ext 3 or ext4 filesystem journaling options:

Most of us are aware that modern filesystems (ext3, ext4, xfs, reiser etc...) use journaling. Journaling in concept is very similar to databases write ahead logging WAL it helps to avoid data corruption on the filesystem when the system is abruptly shutdown. On system's comeback the filesystem will replay the journal and correct the data on the filesystem. Awesome concept, but were you aware that there are several options to journaling and depending on the options selected it could improve your database performance?

Linux ext3, ext4 journaling options from fastest to slowest:

When your ext3 filesystem is mounted with data=writeback option ext3(ext4) records onlythe metadata. BTW this is default journaling mode for xfs and Reiserfs filesystems.

data=ordered is recording metadata and grouping metadata related to the data changes. By doing so  it provides more of journaling protection vs. data=writeback at the expense of reduced performance when compared to data=writeback.

data=journal - the is the ext3 default mode
data=journal is recording metadata and all data and is the slowest method at the benefit of full protection against data corruption.

data=writeback will deliver the fastest results for the database mount points. The databases have build in  data protection suchas  - WAL.

How to enjoy the benefits of data=writeback? Very simple. Dismount your filesystem using umount - modify the mounting option by adding data=writeback and mount the filesystem back.  Of course be sure to shut down your MariaDB prior to the unmout/mount. Once completed your /etc/fstab will look like this:

/dev/VolGroup01/LogVol01 /data ext3 noatime, data=writeback, defaults 0 0

No comments: