11.7 Configuring Disks for MySQL Use

The MySQL server makes heavy use of disk resources. All storage engines except the HEAP engine store table contents on disk, and log files are recorded on disk. Consequently, the physical characteristics of your disks and disk-related subsystems strongly influence server performance:

• Physical disk characteristics are important because slow disks hinder the server. However, disk speed can be measured in various ways, and the most important parameter is seek time, not transfer rate. (It's more important for the heads to move quickly from track to track than for the platters to spin more quickly.) A RAM disk reduces seek time to near-zero because there is no physical movement at all.

• With a heavy I/O burden, a faster disk controller helps improve disk subsystem throughput. So does installing an additional controller and dividing disk assignments between controllers.

• RAID drives can improve retrieval performance, and some forms of RAID also boost write performance. Other benefits of RAID drives include data redundancy through mirroring and parity checking. Some RAID systems enable you to replace a disk without powering down the server host.

Using disks with better physical characteristics is one way to improve server performance. In addition, the way you employ your disks has a bearing on performance. The following list describes some key strategies for better using your disks:

• Distributing parts of your MySQL installation onto different disks can improve performance by splitting up database-related disk activity to distribute it more evenly. You can do this in several ways:

  • Put log files on one disk, and databases on another disk. This can be done using server options; each option that enables a log allows you to specify the log file location. To move the entire data directory, copy it to a different location and specify the new location with the --datadir option.

  • Use a separate disk for temporary file storage. This can be done using the --tmpdir server option.

  • Distribute databases among several disks. To do this for a given database, move it to a different location, and then create a symbolic link in the data directory that points to the new location of the database. Section 11.7.1, "Moving Databases Using Symbolic Links," discusses this technique.

  • A strategy for distributing disk activity that's possible but not necessarily recommended is to put individual MyISAM tables on different disks. This technique is available for MyISAM tables using CREATE TABLE options. The technique is described in section 14.1.3, "MyISAM Table Symlinking." It has some drawbacks. Table symlinking isn't universally supported on all systems, and spreading your tables around can make it difficult to keep track of how much table storage you're using on which file systems. In addition, some filesystem commands don't understand symbolic links.

• Use a type of filesystem that is suited for the tables you have. MySQL can run on pretty much any kind of filesystem supported by your operating system, but some types of filesystems might be better for your installation than others. Two factors to consider are the maximum table size you need and the number of tables in your database.

  In general, you can use larger MyISAM tables with filesystems or operating systems that allow larger files. The MyISAM storage engine has an internal file size limit of about 8TB, but MyISAM tables cannot actually use files that large unless the filesystem allows it. For example, with older Linux kernels, a common size limit is 2GB. If you use a newer Linux kernel instead, the file size limit goes up considerably and the MySQL server can create much larger MyISAM tables.

  The number of tables in a database can have an effect on table-opening time and on the time to check files after a machine crash. For example, because MySQL represents a MyISAM table on disk by three files (the .frm format file, the .MYD data file, and the .MYI index file), that translates into many small files in the database directory if you have many small MyISAM tables in a database. For some filesystem types, this results in significantly increased directory lookup times when opening the files associated with tables. In situations like this, filesystems such as ReiserFS or ext3 can help performance. They're designed to deal well with large numbers of small files and to provide good directory lookup time. Also, the recovery time to check the filesystem after a machine crash is very good, so the MySQL server becomes available again faster.

Table use is subject to the read/write characteristics of the filesystem on which tables are located. It's most common for MySQL installations to store databases and tables on media that are readable and writable, so that both retrieval and update operations can be performed. However, it's possible to initialize a database and then modify the properties of the filesystem on which it's located to disable write access or to copy a database to read-only media such as CD-ROM. In both cases, the server must only perform retrievals from a disk-based table. Any attempt to issue a query that updates a table fails with an error. HEAP tables are an exception to this because table contents reside in memory.

Under some circumstances, MySQL can run on a completely read-only system (one that has both data and programs stored on read-only media). To do this, you must observe certain precautions:

• No logging can be enabled.

• InnoDB cannot be used because the InnoDB storage engine expects to open its log files for read/write access and writes an information stamp to the tablespace files at shutdown.

• On Unix, the server expects to create a Unix socket file and a process ID file. This does not work on read-only media. The workaround is to run the server on read-write media and keep only databases on read-only media.

11.7.1 Moving Databases Using Symbolic Links

It's possible to move individual databases under either Windows or Unix. Use the instructions in the following sections. While moving a database, be sure that the MySQL server isn't running.

11.7.1.1 Using Database Symbolic Links on Windows

To relocate a database directory under Windows, you must be using a mysqld-max or mysqld-max-nt server because only those servers are compiled with symbolic link support.

11.7.1.2 Using Database Symbolic Links on Unix

To relocate a database directory under Unix, no special server support is needed.