Run mysqlcheck -o database. This will optimize your tables, reclaiming lost space by "defragging" your database. This is especially useful if you have recently changed the structure of your database, or have deleted a large amount of data from it.

Renice mysqld. If you are running a dedicated MySQL server, you can tell the scheduler to run mysql at a much higher priority than other tasks. The mysql manual recommends adding a line like this to your safe_mysqld script:

renice -20 $$

However, I have also found it necessary to find the following hunk of code:

NOHUP_NICENESS="nohup"
if test -w /
then
NOHUP_NICENESS=`nohup nice 2>&1`
if test $? -eq 0 && test x"$NOHUP_NICENESS" != x0 && nice --1 echo foo > 
/dev/null 2>&1
then
NOHUP_NICENESS="nice --$NOHUP_NICENESS nohup"
else
NOHUP_NICENESS="nohup"
fi
fi

and replace it with simply:

NOHUP_NICENESS="nohup nice --20"

Now safe_mysqld and all of the mysqld processes will run at the greatest possible priority. It does this at the expense of all other processes, so if you are trying to run other services along with mysql on the same machine, you may want to pick a higher number (somewhere in the -10 to -5 range is probably a bit more conservative).

Create indices. If you have long running queries, one very good optimization you can make is to add appropriate indices. If you see a long running query when running mtop (as discussed in [Hack #81]), then consider creating a relevant index:

mysql> create index name on Member (Name(10));

Indexing is a trade-off of disk space for performance, and in this age of inexpensive storage, there's no excuse for neglecting your indices. Having too many indices usually doesn't hurt, but doing a linear search (or a unique insert) on a large table that doesn't have an associated index can make your server crawl.

Check your server variables. The default server variables are designed to provide safe, sensible settings for modestly equipped machines. If you have a large amount of RAM (512MB or more) then you can see tremendous benefits by increasing the size of the default buffers and caches.

Here are some variables that we run on a production database server (a dual Pentium 4/1.0GHz with 2GB RAM and lots of fast disk space). Put them in the [mysqld] section of your /etc/my.cnf:

set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = myisam_sort_buffer_size=64M
set-variable = tmp_table_size=8M

set-variable = max_connections=768

Note that many of these are straight out of the my-huge.cnf sample included with the mysql distribution (we haven't had to change them, since they work fine for our installation.) Since we run Apache::DBI on our web servers, we also run with a wait_timeout of a few minutes:

set-variable = wait_timeout=120

This helps prevent idle DBI threads from hanging out and taking up all available max_connections.

Patch glibc and threads. When you have exhausted the abilities of the default installation of glibc, consider patching it (see [Hack #86]) to allow for smaller threads and more open files. This is normally not an issue except for very large, very busy MySQL installations.