MySQL Tuning Basics (iii) Linux file system

The Linux file system is a bit like the MySQL storage engine, which supports a wide variety of file systems. The top layer of the virtual files system is provided as an abstraction interface layer to provide calls externally. Then the various file systems on the lower level implement these calling interfaces.

1. log file system and non-log file system in Linux

The modification of the contents of the file involves two parts: the modification of the actual file contents and the modification of the file Meta (metadata) information. So after modifying a success, before modifying the other, the system crashes, which results in inconsistencies. Therefore, the concept of log file system is proposed.

The so-called log filesystem (Journaling file system) is to write their modifications to a log (journal log) before actually modifying the file contents and file meta information. In this case, if a system crash occurs, you can use the log for recovery . Of course, writing logs can have a certain effect on the performance of the file system. In addition to ext2, other file systems are almost always log file systems.

The process of log file system is:1) write the log first, 2) then write the actual file system; 3) Delete the log;

The log file system can be divided into three different types:

1) Log mode (journal): all the metadata and data changes are written to the log, the performance has the greatest impact;

2) Scheduled mode (ordered): Only the change of metadata is recorded, and then the metadata is modified after the data is written to disk, which has a moderate performance impact;

3) Writeback mode (writeback): Only record the changes of metadata changes, the data modification order is not required, the performance impact is minimal;

We can modify the file system's log mode in the /etc/fstab file.

/dev/sdb1/testfs ext3 defaults,data=writeback 0 0

Linux Common File System:

Ext4, Ext4, XFS, ReiserFS, JFS

One of the most commonly used is EXT4, XFS. where Redhat7/centos7 the XFS as the default file system . XFS performance has also significantly exceeded EXT4 in the latest kernel tests. So for MySQL server, it is best to use XFS file system .

There is a comparison of Ext4 and XFS on SSDs:

https://www.percona.com/blog/2012/03/15/ext4-vs-xfs-on-ssd/

Prior to rhel6.4, EXT4 performance was better than XFS because XFS had a lock contention bug. But at 6.4, the XFS bug was fixed. So XFS performance is better than EXT4.

When the lock contention bug in XFS is not resolved:

Sync asyncthreads Throughput Throughput XFS EXT4 XFS E XT41    1.90/124k1.41/92k1.72/112k1.41/92k2    1.01/64k1.65/108k0.97/62k1.65/108k4    0.27/17k1.55/102k0.21/13k1.55/102k8    0.13/8k1.45/95k0.15/9k1.45/95k -   0.12/7k1.45/95k0.12/7k1.45/95k

It's pretty clear from these results, that's lock contention is killing XFS as the thread count grows. EXT4 performance shows that it uses exclusive locking as well, but it's not degrading like XFS was due to different lock t Ypes being used.

But when XFS's lock contention is resolved with a bug:

Sync asyncthreads Throughput Throughput Vanilla patched Vanilla Patched1    1.90/124k1.83/120k1.72/112k1.69/111k2    1.01/64k2.85/185k0.97/62k2.57/168k4    0.27/17k3.68/241k0.21/13k3.41/223k8    0.13/8k4.42/290k0.15/9k4.16/273k -   0.12/7k4.95/325k0.12/7k4.86/319k

throughput Scales with thread count –each thread runs on 100% CPU utilsation, and XFS gets up to 3 X as much throughput as EXT4 does. So, basically, XFS are still the file system you want for direct IO.

Optimized for hanging files (noatime,nodiratime):

Mount–t Ext4–o Rw,noatime,nodiratime/dev/sda6/data

Noatime will have a 0-10% performance boost, typically with an average of 3% performance gains.

noatime:

Do not update the inode access times in this filesystem (e.g, for faster access in the news spool to speed up news servers).

nodiratime:

Do not update the directory inode access times on the this filesystem.

The configuration of the Noatime,nodiratime can also be performed in/etc/fstab.

MySQL Tuning Basics (iii) Linux file system