Optimization is a complicated task because it ultimately requires understanding of the whole
system. While it may be possible to do some local optimizations with small knowledge of
your system/application, the more optimal you want your system to become the more you
will have to know about it.
So this chapter will try to explain and give some examples of di erent ways to optimize
MySQL. But remember that there are always some (increasingly harder) additional ways
to make the system even faster.
Optimization Overview
The most important part for getting a system fast is of course the basic design. You also
need to know what kinds of things your system will be doing, and what your bottlenecks
are.
The most common bottlenecks are:
Disk seeks. It takes time for the disk to nd a piece of data. With modern disks in
1999, the mean time for this is usually lower than 10ms, so we can in theory do about
1000 seeks a second. This time improves slowly with new disks and is very hard to
optimize for a single table. The way to optimize this is to spread the data on more
than one disk.
Disk reading/writing. When the disk is at the correct position we need to read the
data. With modern disks in 1999, one disk delivers something like 10-20Mb/s. This is
easier to optimize than seeks because you can read in parallel from multiple disks.
CPU cycles. When we have the data in main memory (or if it already were there) we
need to process it to get to our result. Having small tables compared to the memory
is the most common limiting factor. But then, with small tables speed is usually not
the problem.
Memory bandwidth. When the CPU needs more data than can t in the CPU cache
the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck
for most systems, but one should be aware of it.
system. While it may be possible to do some local optimizations with small knowledge of
your system/application, the more optimal you want your system to become the more you
will have to know about it.
So this chapter will try to explain and give some examples of di erent ways to optimize
MySQL. But remember that there are always some (increasingly harder) additional ways
to make the system even faster.
Optimization Overview
The most important part for getting a system fast is of course the basic design. You also
need to know what kinds of things your system will be doing, and what your bottlenecks
are.
The most common bottlenecks are:
Disk seeks. It takes time for the disk to nd a piece of data. With modern disks in
1999, the mean time for this is usually lower than 10ms, so we can in theory do about
1000 seeks a second. This time improves slowly with new disks and is very hard to
optimize for a single table. The way to optimize this is to spread the data on more
than one disk.
Disk reading/writing. When the disk is at the correct position we need to read the
data. With modern disks in 1999, one disk delivers something like 10-20Mb/s. This is
easier to optimize than seeks because you can read in parallel from multiple disks.
CPU cycles. When we have the data in main memory (or if it already were there) we
need to process it to get to our result. Having small tables compared to the memory
is the most common limiting factor. But then, with small tables speed is usually not
the problem.
Memory bandwidth. When the CPU needs more data than can t in the CPU cache
the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck
for most systems, but one should be aware of it.