GNU Info

(mysql.info)Disk issues

---

Prev: Optimizing the Server Up: MySQL Optimization

---

Enter node , (file) or (file)node

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 minimize 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 data file(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 optimize 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 file system that the
     database uses. One easy change is to mount the file system 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 hdpram 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 'async'
     flag to set the file system to be updated asynchronously.  If your
     computer is reasonable 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 file
     systems with the noatime flag.

Symbolic links

Using Symbolic Links