5.6 Disk Issues

• As mentioned before, disks seeks are a big performance bottleneck. This problems gets more and more apparent when the data starts to grow so large that effective caching becomes impossible. For large databases, where you access data more or less randomly, you can be sure that you will need at least one disk seek to read and a couple of disk seeks to write things. To minimise this problem, use disks with low seek times.
• Increase the number of available disk spindles (and thereby reduce the seek overhead) by either symlink files to different disks or striping the disks.

  Using symbolic links

  This means that you symlink the index and/or datafile(s) from the normal data directory to another disk (that may also be striped). This makes both the seek and read times better (if the disks are not used for other things). See section Using Symbolic Links.

  Striping

  Striping means that you have many disks and put the first block on the first disk, the second block on the second disk, and the Nth on the (N mod number_of_disks) disk, and so on. This means if your normal data size is less than the stripe size (or perfectly aligned) you will get much better performance. Note that striping is very dependent on the OS and stripe-size. So benchmark your application with different stripe-sizes. See section Using Your Own Benchmarks.

  Note that the speed difference for striping is very dependent on the parameters. Depending on how you set the striping parameters and number of disks you may get a difference in orders of magnitude. Note that you have to choose to optimise for random or sequential access.

• For reliability you may want to use RAID 0+1 (striping + mirroring), but in this case you will need 2*N drives to hold N drives of data. This is probably the best option if you have the money for it! You may, however, also have to invest in some volume-management software to handle it efficiently.
• A good option is to have semi-important data (that can be regenerated) on RAID 0 disk while storing really important data (like host information and logs) on a RAID 0+1 or RAID N disk. RAID N can be a problem if you have many writes because of the time to update the parity bits.
• You may also set the parameters for the filesystem that the database uses. One easy change is to mount the filesystem with the noatime option. That makes it skip the updating of the last access time in the inode and by this will avoid some disk seeks.
• On Linux, you can get much more performance (up to 100% under load is not uncommon) by using hdparm to configure your disk's interface! The following should be quite good hdparm options for MySQL (and probably many other applications):

  hdparm -m 16 -d 1

  Note that the performance/reliability when using the above depends on your hardware, so we strongly suggest that you test your system thoroughly after using hdparm! Please consult the hdparm man page for more information! If hdparm is not used wisely, filesystem corruption may result. Backup everything before experimenting!

• On many operating systems you can mount the disks with the -o async option to set the filesystem to be updated asynchronously. If your computer is reasonably stable, this should give you more performance without sacrificing too much reliability. (This flag is on by default on Linux.)
• If you don't need to know when a file was last accessed (which is not really useful on a database server), you can mount your filesystems with the -o noatime option.

5.6.1 Using Symbolic Links

You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space or increase the speed of your system by spreading your tables to different disk.

The recommended way to do this, is to just symlink databases to a different disk and only symlink tables as a last resort.

5.6.1.1 Using Symbolic Links for Databases

The way to symlink a database is to first create a directory on some disk where you have free space and then create a symlink to it from the MySQL database directory.

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test mysqld-datadir

MySQL doesn't support that you link one directory to multiple databases. Replacing a database directory with a symbolic link will work fine as long as you don't make a symbolic link between databases. Suppose you have a database db1 under the MySQL data directory, and then make a symlink db2 that points to db1:

shell> cd /path/to/datadir
shell> ln -s db1 db2

Now, for any table tbl_a in db1, there also appears to be a table tbl_a in db2. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be problems.

If you really need this, you must change the following code in `mysys/mf_format.c':

if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

to

if (1)

On Windows you can use internal symbolic links to directories by compiling MySQL with -DUSE_SYMDIR. This allows you to put different databases on different disks. See section Distributing Data Across Different Disks on Windows.

5.6.1.2 Using Symbolic Links for Tables

Before MySQL 4.0 you should not symlink tables, if you are not very careful with them. The problem is that if you run ALTER TABLE, REPAIR TABLE or OPTIMIZE TABLE on a symlinked table, the symlinks will be removed and replaced by the original files. This happens because the above command works by creating a temporary file in the database directory and when the command is complete, replace the original file with the temporary file.

You should not symlink tables on systems that don't have a fully working realpath() call. (At least Linux and Solaris support realpath())

In MySQL 4.0 symlinks are fully supported only for MyISAM tables. For other table types you will probably get strange problems when doing any of the above mentioned commands.

The handling of symbolic links in MySQL 4.0 works the following way (this is mostly relevant only for MyISAM tables).

• In the data directory you will always have the table definition file and the data and index files.
• You can symlink the index file and the datafile to different directories independently of the other.
• The symlinking can be done from the operating system (if mysqld is not running) or with the INDEX/DATA DIRECTORY="path-to-dir" command in CREATE TABLE. See section CREATE TABLE Syntax.
• myisamchk will not replace a symlink with the data or index file but work directly on the file the symlink points to. Any temporary files will be created in the same directory where the data or index file is located.
• When you drop a table that is using symlinks, both the symlink and the file the symlink points to are dropped. This is a good reason to why you should not run mysqld as root or allow persons to have write access to the MySQL database directories.
• If you rename a table with ALTER TABLE RENAME and you don't move the table to another database, the symlinks in the database directory will be renamed to the new names and the data and index files will be renamed accordingly.
• If you use ALTER TABLE RENAME to move a table to another database, the table will be moved to the other database directory and the old symlinks and the files they pointed to will be deleted. (In other words, the new table will not be symlinked.)
• If you are not using symlinks, you should use the --skip-symlink option to mysqld to ensure that no one can drop or rename a file outside of the mysqld data directory.

Things that are not yet supported:

• ALTER TABLE ignores all INDEX/DATA DIRECTORY="path" options.
• CREATE TABLE doesn't report if the table has symbolic links.
• mysqldump doesn't include the symbolic link information in the output.
• BACKUP TABLE and RESTORE TABLE don't respect symbolic links.

Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45