Databases Reference
In-Depth Information
When possible, it's best to use a journaling filesystem, such as ext3, ext4, XFS, ZFS, or
JFS. If you don't, a filesystem check after a crash can take a long time. If the system is
not very important, nonjournaling filesystems might perform better than transactional
ones. For example, ext2 might perform better than ext3, or you can use
tunefs
to
disable the journaling feature on ext3. Mount time is also a factor for some filesystems.
ReiserFS, for instance, can take a long time to mount and perform journal recovery on
large partitions.
If you use ext3 or its successor ext4, you have three options for how the data is jour-
naled, which you can place in the
/etc/fstab
mount options:
data=writeback
This option means only metadata writes are journaled. Writes to the metadata are
not synchronized with the data writes. This is the fastest configuration, and it's
usually
safe to use with InnoDB because it has its own transaction log. The excep-
tion is that a crash at just the right time could cause corruption in a
.frm
file.
Here's an example of how this configuration could cause problems. Say a program
decides to extend a file to make it larger. The metadata (the file's size) will be logged
and written before the data is actually written to the (now larger) file. The result is
that the file's tail—the newly extended area—contains garbage.
data=ordered
This option also journals only the metadata, but it provides some consistency by
writing the data before the metadata so that they stay consistent. It's only slightly
slower than the
writeback
option, and it's much safer when there's a crash.
In this configuration, if we suppose again that a program wants to extend a file,
the file's metadata won't reflect the file's new size until the data that resides in the
newly extended area has been written.
data=journal
This option provides atomic journaled behavior, writing the data to the journal
before it's written to the final location. It is usually unnecessary and has much
higher overhead than the other two options. However, in some cases it can improve
performance because the journaling lets the filesystem delay the writes to the data's
final location.
Regardless of the filesystem, there are some specific options that it's best to disable,
because they don't provide any benefit and can add quite a bit of overhead. The most
famous is recording access time, which requires a write even when you're reading a file
or directory. To disable this option, add the
noatime,nodiratime
mount options to
your
/etc/fstab;
this can sometimes boost performance by as much as 5-10%, depending
on the workload and the filesystem (although it might not make much difference in
other cases). Here's a sample
/etc/fstab
line for the ext3 options we mentioned:
/dev/sda2 /usr/lib/mysql ext3 noatime,nodiratime,data=writeback 0 1
