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). *Note 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. *Note Custom 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.

   
     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.

Using Symbolic Links
--------------------

 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.

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. *Note Windows symbolic links::.

Using Symbolic Links for Tables
...............................


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.

   * `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.

       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 the `DATA DIRECTORY' and `INDEX DIRECTORY'
     table 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.

[Назад] [Содержание] [Вперед]