DBA conclusion: MySQL has the greatest advantage in combination with XFS file systems

Q & A from a foreign SNS website Quora: Which of the three file systems XFS, ZFS, and ext3 can produce the best performance with MySQL? Domas Mituzas, a senior Database Expert from Facebook, gave his own answer. He believes that MySQL is the most suitable for XFS file systems.

Before the release of the Linux version of The XFS file system, it is very easy to select the appropriate Linux File System. The pursuit of original performance usually tends to use ReiserFS, and the emphasis on data integrity is the first choice for ext3. This situation has changed when the XFS Linux version appears. A test comparing the advantages and disadvantages of XFS, ReiserFS, and ext3 in terms of raw performance shows that XFS has always been a leader in all tests in terms of large file operations.

Domas Mituzas analyzes several major access modes in the MySQL database system, as follows:

A) synchronously read the InnoDB Storage engine page input;

B) asynchronously write data to the InnoDB Storage engine page output;

C) synchronously rewrite the transaction log of the InnoDB Storage engine;

D) add MySQL database binary logs synchronously.

In a high-performance environment, it is important to select a file system that can improve efficiency in the above four access modes. If this is a read-write-intensive system, XFS is required to be able to effectively refresh the page, just as it supports the write operation to suspend each index node (or file. The metadata efficiency of XFS allows synchronous write operations to be faster than other file systems.

Until recently, XFS became the only large Linux file system that supports file alignment. The size of the InnoDB data file block is fixed, and alignment on the stripe boundary may increase the I/O performance by more than 5%. In general, XFS enables most projects to support parallel workloads. Multiple allocation groups allow concurrent metadata operations, not just data. In addition, there are other operation problems in the production system. For example, if the TABLE file is used and you want to disconnect the link as soon as possible, drop table and other files remoavls (such as TRUNCATE) will not delay the entire operation too long. The XFS file system is a better choice.

We can see that the XFS file system is used to reduce the I/O latency, which is more important than Throughput-oriented evaluation. Therefore, in general, the actual cost, the efficiency of the parallel and OLTP access modes is more important. Not all tests are evaluated because the test results ignore the I/O latency, which is extremely important in the production environment. If you want to correctly evaluate the OLTP system, XFS will be the fastest.

Finally, it provides support for developers. XFS has an active development community that can provide help and provide them with adequate problem descriptions.

XFS was initially developed by Silicon Graphics, Inc. In early 1990s. At that time, SGI found that their existing File System (EFS) was quickly becoming unable to adapt to the fierce Computing Competition at that time. To solve this problem, SGI decided to design a brand new high-performance 64-bit file system, instead of trying to adjust some defects of EFS in the innate design. This is the XFS file system, which is still used as the underlying File System of sgi irix-based products (workstation to supercomputer. Now, XFS can also be used in Linux. It provides a robust, excellent, and functional File System for the Linux community, in addition, the scalability of this file system can meet the most demanding storage requirements.

The main features of XFS include:

Data Integrity

When XFS file system is used, when unexpected downtime occurs, first of all, because the file system has enabled the log function, the files on your disk will not be damaged by unexpected downtime. Regardless of the number of files and data stored in the file system, the file system can quickly restore the disk file content according to the recorded logs.

Transmission features

The XFS file system adopts an optimization algorithm. log records have little impact on file operations. XFS queries and allocates storage space very quickly. The xfs file system continuously provides fast response time. I have tested the XFS, JFS, Ext3, and ReiserFS file systems, and the XFS file system has outstanding performance.

Scalability

XFS is a fully 64-bit file system that supports storage of millions of T bytes. The support for ultra-large files and small-sized files is outstanding, and supports ultra-large directories. The maximum supported file size is 263 = 9x1018 = 9 exabytes, and the maximum file system size is 18 exabytes.

XFS uses a high table structure (B + tree) to ensure that the file system can quickly search for and quickly allocate space. XFS provides continuous high-speed operations, and the file system performance is not limited by the number of directories and files in the directory.

Transmission bandwidth

XFS can store data close to bare device I/O performance. In the test of a single file system, the throughput can reach up to 7 GB per second. For read/write operations on a single file, the throughput can reach 4 GB per second.