MySQL Reference Manual

Disk Issues

• As mentioned before, disks seeks are a big performance bottleneck. This problem 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 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). See Section 5.6.1.

  Striping

  Striping means that you have many disks and put the first block on the first disk, the second block on the second disk, 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 5.1.5.

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