MySQL and mSQL

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.^[5] 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 variables.

back_log

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.

key_buffer

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.

max_connections

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

table_cache

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.

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 this:

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

The ISM file is most important to the performance of MySQL. It is so important, in fact, that an entire utility, isamchk , is devoted to it. Running isamchk -d will display information about a table:

# isamchk -d mytable

ISAM file:     mytable
Data records:       1973  Deleted blocks:              0
Recordlength:        343
Record format: Packed

table description:
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 space:

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.

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 repair:

isamchk mytable

Note

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

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.

Note

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

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 a table:

isamchk -rq -k0

When you are ready to put them back in, issue this command to replace the keys:

isamchk -rq

Warning

Shut down the server before issuing isamchk with the -r option. If the server is running, isamchk-r could corrupt the table.

The isamchk 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 appear.

Troubleshooting

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 MySQL administration:

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.

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

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

3. 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 table_cache variable.

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

---

^[5] Remember that the options to safe_mysqld are passed on to mysqld.