41.2. Configuring Disks for MySQL Use


The MySQL server makes heavy use of disk resources. All storage engines except the MEMORY 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 41.2.1, "Moving Databases Using Symbolic Links," discusses how to implement this technique.

    • A strategy for distributing disk activity that's possible but not necessarily recommended is to put individual MyISAM tables on different disks by using CREATE TABLE options. This technique is described in Section 41.2.2, "MyISAM Table Symlinking Table Symlinking," but it does have some drawbacks. Table symlinking is not 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 do not 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 65,536TB, but MyISAM tables cannot actually use files that large unless the filesystem allows it. For example, older Linux kernels may impose a size limit of 2GB. If you use a recent 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 is 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. MEMORY tables are an exception to this, because table contents reside in memory.

41.2.1. Moving Databases Using Symbolic Links

MySQL represents each database by means of a directory located in the data directory. It is possible to reconfigure the data directory by moving individual databases elsewhere and replacing them with symbolic links in the data directory. Reasons to do this are to achieve redistribution of storage and disk I/O:

  • Moving databases to different filesystems can free up disk space on the filesystem that contains the data directory.

  • If databases are moved to filesystems that are on different physical devices, database-related disk I/O is distributed among those devices.

The procedure for moving individual databases differs for Windows and Unix. Use the instructions in the following sections. While moving a database, be sure that the MySQL server isn't running.

41.2.1.1 Using Database Symbolic Links on Windows

To relocate a database directory under Windows, use the following procedure:

1.

Stop the server if it is running.

2.

Move the database directory from the data directory to its new location.

3.

In the data directory, create a file that has a basename that's the same as the database name and an extension of .sym. The file should contain the full pathname to the new database location. This .sym file is the symbolic link that enables MySQL Server to find the database in its new location. For example, if you move the world database directory to a new location of D:\world, you must create a world.sym file in the data directory that contains the pathname D:\world.

4.

Restart the server.

Use of database symlinking on Windows is subject to the condition that you have not started the server with the --skip-symbolic-links option.

41.2.1.2 Using Database Symbolic Links on Unix

To relocate a database directory under Unix, use the following procedure:

1.

Stop the server if it is running.

2.

Move the database directory from the data directory to its new location.

3.

In the data directory, create a symbolic link that has the same name as the database and that points to the new database location. For example, if you move the world database directory to a new location of /opt/data/world, you must create a symbolic link named world in the data directory that points to /opt/data/world. If the data directory is /usr/local/mysql/data, create the symbolic link like this:

 shell> cd /usr/local/mysql/data shell> ln -s /opt/data/world world 

4.

Restart the server.

41.2.2. MyISAM Table Symlinking

By default, a MyISAM table for a given database is created in the database directory under the data directory. This means that the .frm, .MYD, and .MYI files are created in the database directory. It's possible to create the table such that the data file or index file (or both) are located elsewhere. You might do this to distribute storage for the table to a filesystem with more free space, for example. If the filesystem is on a different physical disk, moving the files has the additional effect of distributing database-related disk activity, which might improve performance.

To relocate a table's data file or index file, use the DATA DIRECTORY or INDEX DIRECTORY options in the CREATE TABLE statement. For example, to put both files for a table t in the directory /var/mysql-alt/data/test, use a statement like this:

 CREATE TABLE t (i INT) DATA DIRECTORY = '/var/mysql-alt/data/test' INDEX DIRECTORY = '/var/mysql-alt/data/test'; 

This statement puts the data and index files in the same directory. To put the files in different directories, specify different pathnames for each option. You can also relocate one file only and leave the other in its default location (the database directory) by omitting either the DATA DIRECTORY or the INDEX DIRECTORY option from the CREATE TABLE statement. Directory names for DATA DIRECTORY and INDEX DIRECTORY must be specified as full (absolute) pathnames, not as relative pathnames.

The server implements data file or index file relocation by creating the file in the directory that you specify and placing in the database directory a symbolic link to the file. You can do the same thing manually to relocate an existing MyISAM table's data file or index file, as long as the server does not have the table open and is not using it. For each file to be relocated, use this procedure:

  • Move the file to a different directory.

  • Create a symlink in the database directory that points to the new location of the moved file.

Table symlinking is subject to the following restrictions:

  • It does not work on Windows.

  • On Unix, the operating system must have a working realpath() system call, and must have thread-safe symlinks.

  • You must not have started the server with the --skip-symbolic-links option.

Relocating MyISAM data files and index files as just described makes it somewhat more difficult to keep track of just where your table files are located or how table storage space is distributed among your filesystems. Thus, although it's possible to relocate MyISAM tables using symlinking, it isn't necessarily recommended as an everyday technique. If you're thinking about relocating several MyISAM tables in a database, consider the simpler alternative of relocating the entire database directory and replacing the original database directory under the data directory with a symbolic link that points to the new location. This is just as effective as moving many tables individually, but requires only a single symlink. For instructions, see Section 41.2.1, "Moving Databases Using Symbolic Links."



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net