Improving Hardware to Speed Up the Server

Hardware is one of the easiest improvements (if you have the cash) you can make to a poorly performing database server. As a general rule of thumb, pack in as much memory as you can first, then use the fastest disks you can get, and finally get the fastest CPU (Central Processing Unit). The best is to benchmark your system, using the various tools available to your operating system to see whether it's the CPU, memory, disk speed, or a combination that is the bottleneck. This will give you the best idea of the vagaries of your usage and assist you in upgrading. Running the benchmark suite (see "Using Benchmarks" later in this chapter) will also help show the performance of different kinds of tasks.

Memory

Memory is the most important element because it allows you to tweak the mysqld variables. Large amounts of memory mean you can create large key and table caches. Memory that is as large as possible allows MySQL to use quicker memory rather than disk as much as possible, and the quicker the memory is, the faster MySQL can access the data stored there. Large amounts of memory on its own is not as useful as if you actively tweaked the mysqld variables to make use of the extra memory, so you can't be too lazy and just stick in the memory and wait for fireworks.

Disks

Ultimately, MySQL has to fetch the data from disk, and this is where fast disk access plays a role. The disk seek time is important because it determines how fast the physical disk can move to get to the data it needs, so you should choose the disk with the best disk seek time. Also, SCSI (small computer system interface) disks are usually faster than IDE (Intelligent [or Integrated] Drive Electronics) disks, so you'll probably want these.

An important improvement you can make is to stripe data across multiple disks (where the OS breaks data in parts, spreading it evenly over multiple disks), as well as symlinking (where you create a link from the data directory to another disk). InnoDB tables have a mechanism to split data across multiple disks quite easily, but MyISAM tables do not (being made up of single files), so striping or other forms of RAID can be quite useful. Chapter 16, "Multiple Drives," looks at these topics in more detail.

CPU

The faster the processor, the quicker any calculations can be done and the quicker the results sent back to the client. Besides processor speed, the bus speed and the size of the cache are important. An analysis of available processors is beyond the scope of this book and will probably be outdated before this book is even published, but be sure to investigate your processor possibilities carefully to see how it performs in various benchmarks.