4.6. Performance Tuning
The difference between being a good DBA (Database Administrator) and
being a top-notch DBA is the difference between knowing how to manage
your database server and knowing how your database server lives and
breathes. Solving performance problems is often a matter of
understanding just how MySQL works under the covers so that you can
optimize application performance to take advantage of those features.
MySQL presents three main potential bottlenecks for any connection.
The first possibility is the network connection between the client
and the server. Second is the processing time needed for activities
like building keys. Finally, disk I/O can be a problem. MySQL
provides variables that enable you to match MySQL's operations
to your application environment. You can set each of these variables
using the -O option to mysqld. For example, you set
back_log to 15 by adding the option -O
back_log=15 to the options for
mysqld. The following is a list of useful
Remember that the
options to safe_mysqld are passed on to
The number of TCP/IP connections that are queued at once. If you have
many remote users connecting to your database simultaneously, you may
need to increase this value. The trade-off for a high value is
slightly increased memory and CPU usage.
A buffer allocated to store recently used keys. If you have slow
queries, increasing this value could help. The trade-off is an
increase in memory usage.
The number of simultaneous connections allowed by the database
server. If some users are being denied access during busy times, you
may need to increase this value. The trade-off is a more heavily
loaded server. In other words, CPU usage, memory usage, and disk I/O
A buffer used to hold frequently accessed table data. If you gave the
memory to hold them, keeping your tables in memory greatly reduces
disk I/O. The trade-off is a significant increase in memory usage.
4.6.1. The MySQL Data Structure
MySQL stores each table as a set of three files. For example, a
medium-sized table called mytable may look like
-rw-rw---- 1 root root 1034155 Jun 3 17:08 mytable.ISD
-rw-rw---- 1 root root 50176 Jun 3 17:08 mytable.ISM
-rw-rw---- 1 root root 9114 Jun 3 14:24 mytable.frm
The ISD file
contains the actual data. The ISM file contains information about the
keys and other internal data that enables MySQL to find data in the
ISD file quickly. The frm file contains the structure of the table
The ISM file is most important to the performance of MySQL. It is so
important, in fact, that an entire utility,
, is devoted to it. Running
isamchk -d will display information about a
# isamchk -d mytable
ISAM file: mytable
Data records: 1973 Deleted blocks: 0
Record format: Packed
Key Start Len Index Type
1 2 50 unique text packed stripped
The important field to notice here is the "Deleted
blocks" field. If this value is too high, then the file is
wasting a lot of space. Fortunately, you can recover this space. The
following command will examine the table and recreate it, removing
most errors and eliminating unnecessary
isamchk -r mytable
You can obtain additional speed enhancements by running
isamchk -a on the table. This command analyzes
the distribution of data in a table. You should run it after you
insert or delete numerous records from the table.
220.127.116.11. Repairing damaged tables
Due to server crashes or other acts of nature, a table in your
database may become corrupted. When this happens,
isamchk provides several different levels of
If you alter a table using isamchk while the database server is
running, you may have to run mysqladmin reload
to make the server see the updated table.
This command will repair most common problems with tables. Adding the
-i and -v options will
provide extra output about what is wrong. You can use more than one
-v for extra information.
isamchk -rq mytable
This command will perform a quick check -- and repair, if
necessary -- of only the ISM file. It will not check for
corruption of the
isamchk -e mytable
Using this option, you can perform a full check and repair of
everything, eliminating any possible corruption. This sort of check
will naturally take much longer than a regular check. The command
will exit upon encountering the first severe error. If you want to
continue reparations even after severe corruption is encountered, you
can pass it the -v option. This option will
guarantee the resulting table to be clean of corruption, but you may
lose some data in the process.
Always back up your data before running any command that may alter
the contents of a table. The isamchk utility is
very good about repairing errors, but sometimes that means erasing
corrupt data that is interfering with the rest of the table. If you
have a backup, you can use it to recover any data that
18.104.22.168. Removing and replacing keys
Keys can sometimes get in the way of database performance. If, for
instance, you want to insert a large data set into a table, having
MySQL index the keys after every insert can be very inefficient. In
addition, if you have a table with corrupt keys, blindly repairing
that table with isamchk could delete some of the
data associated with the key.
In these situations, it can be helpful to temporarily remove the keys
from a table and then replace them when the troublesome work is
finished. The following command will remove the key information from
isamchk -rq -k0
When you are ready to put them back in, issue this command to replace
Shut down the server before issuing isamchk with
the -r option. If the server is
running, isamchk-r could corrupt the table.
command provides so many capabilities
it can be hard to sift through them all. However, there are some
basic guidelines to follow:
While the database is young, run isamchk -a
often. For most database applications, the bulk of data is inserted
near the beginning of the life of the database. If you run
isamchk with the analyze option every time the
size of your database doubles you can make sure the data is always
kept in the most efficient form.
Run isamchk -d once or twice a year. If the
number of deleted blocks used by your tables is a significant portion
of your disk space run isamchk -r to rebuild
your tables without the unneeded space. If you have an application
that involves a great deal of deleting old data and inserting new
data, run isamchk -d every couple of weeks and
if the number of deleted blocks grows quickly, you may want to run
isamchk -r routinely every month.
Except for removing and replacing keys, which should always be done
anytime more than a few dozen rows is being inserted at once, all
other forms of isamchk should be run only
reactively, whenever inconsistencies in the database
Even in the best of products, problems occur. Fortunately, many
problems you might run into have happened to others. The following is
a collection of frequently encountered trouble spots dealing with
- Changes to the access tables are not working.
Do not forget to issue the command mysqladmin
reload after making changes to access tables.
- MySQL is refusing connections at peak times.
You should first check how many connections the server allows. The
command mysqladmin variables will show this
value under max_connections. You can set this
value higher by starting mysqld with the
-O max_connections=### where
### is the limit you wish to set.
You can also check with the back_log value which
determines the size of the queue that MySQL creates for incoming
connections. The default value is 5. Versions of MySQL prior to
3.22.x could set this limit only as high as 64, but later versions
can set it as high as 1024. Your operating system, however, may limit
connections to 64.
Finally, this problem can also be caused by file descriptor limits.
In this case, the symptoms are that no connections at all are being
allowed when MySQL has a large number of threads running. Unix
systems handle setting the number of file descriptors in many
different ways, so refer to your system documentation on how to
increase the limit.
- MySQL claims to be unable to find a file that definitely exists, or it reports errors while reading it.
Most of the time, this problem is a result of the file descriptor
problem mentioned above. If, however, you increase MySQL's
table cache, it will not have to open the table files so many times
and you may avoid this problem. By default, the table cache value is
64. You can increase this value through the
- Threads start to pile up and they will not go away.
Certain systems, including Linux and some setups using NFS, have a
problem with their file locking mechanism. This problem can result in
a thread freezing. The mysqladmin processlist
can help identify this problem. If the frozen threads report
"System lock" under the "Command" field, use
the --skip-locking option when starting
Copyright © 2001 O'Reilly & Associates. All rights reserved.