In Chapter 1, “Installing MySQL,” we covered only installation from binaries (specifically RPM under Unix). When you're learning how to use MySQL, this is by far the easiest way, especially if you are not used to compiling your own software, as is the case for many Windows users.

You may be able to get a performance improvement from your server by downloading the source version and compiling it yourself. Specifically, if you have a Pentium-based machine and run Linux, you will be able to get a significant performance increase by compiling MySQL with the pgcc compiler, which optimizes for Pentium only. (These executables are not compatible with AMD chips, hence the binary available from MySQL is compiled with plain gcc.) MySQL AB says that you can get an extra 10% to 30% faster server if you compile it yourself with the right compiler and options on—see the manual for further details.

Another improvement you can make over the MySQL-supplied binary is available by compiling MySQL with support for only the character set(s) you need. The downloadable binaries contain support for all the character sets. (You can find more information on character sets in Chapter 12, “Configuring MySQL.”)

The MySQL manual contains suggestions for optimizing user-compiled binaries for various operating systems. You can also find many operating-system and compiler-specific issues discussed on the MySQL mailing lists. The archives are at

http://lists.mysql.com

You can tune your MySQL server parameters to try to optimize your configuration.

As a reminder, you can check the current values of server parameters with this:

You can see the effects of your server configuration by looking at the output of this:

Another useful tool for monitoring what is happening with your server at any given time is Jeremy Zawodny's mytop Perl script.

This tool acts as the equivalent to the Unix top command and shows what processes are active, the process state, the time spent, and so on. Additionally, it allows you to drill into a MySQL process and see the actual query being executed.

You can download this from

http://jeremy.zawodny.com/mysql/mytop

All of these tools will allow you to keep track of the changes you have made and the effects they have.

There are so many server parameters in my.cnf options files that you may well wonder where to begin. Most versions of MySQL come with sample my.cnf files, typically in the support-files directory of your installation. There are four suggested my.cnf files in this directory: my-huge.cnf, my-large.cnf, my-medium.cnf, and my-small.cnf. You can choose one as a starting point for your system.

The key parameters you will tune have to do with how MySQL uses memory. For any database server, more memory is a good thing, but it's important that this memory is available to the database server and that it is allocated appropriately between tasks.

MySQL has a set of internal buffers and caches. You can control how much memory is allocated to each of these. The two most important parameters to control are the key_buffer_size and the table_cache. These two are shared across all the threads running on the server, and they have a huge influence on performance.

The key buffer is where MyISAM indexes are stored in memory. As index blocks are used, they will be loaded into the buffer. Each time a query is issued, if the relevant index block is in the buffer, it will be read from there. Otherwise, the index block will need to be loaded from the disk into the key buffer, which is obviously slower. Generally speaking, with the key buffer, bigger is better.

When considering what value to set for the key_buffer_size, you should look at how much memory you have overall, whether or not the server is a dedicated MySQL server, and how big your index data is (that is, how big your .MYI files are in total). Jeremy Zawodny, renowned MySQL tuning expert at Yahoo!, recommends setting this value to somewhere between 20% and 50% of the total memory on a dedicated server. If you are using a shared machine, it should obviously be a smaller amount. It should also be a smaller amount if your index data is small. If you have only 20MB of index data, there is little point in allocating 128MB to the key buffer.

Note also that the key buffer is only for MyISAM tables. Other table types have their own separate parameters for tuning. There is no point in greatly increasing the size of the key buffer if you are using only InnoDB tables, for example. In this case, the parameter you need is called innodb_buffer_pool_size. The InnoDB buffer pool stores both index and table data. (You can find more information on InnoDB configuration in Chapter 12 and in the MySQL manual.)

The second really important parameter is the table cache, controlled via the table_cache option. This limits the maximum number of tables that can be open at once. With MyISAM tables, each table and each index is a separate file on your operating system. Opening and closing files is slow, so these files are left open until they are explicitly closed, the server shuts down, or the total number of open tables exceeds the table_cache parameter. Increasing the table_cache value will be helpful if you have a large number of tables on your server. Your operating system will impose an upper limit on the number of open files or the number of files opened by a single process or user, so check this for your system before resetting the table_cache value.

Besides these two global memory pools, there are various chunks of memory allocated on a per-thread basis—for example, the sort buffer and the read buffer. The value is the same for each thread, but each thread can have this amount of memory allocated to the specified purpose.

The read buffer size, controlled by the read_buffer_size parameter, is used when a full table scan is performed to store the table data. The more table data that can be stored, the fewer disk reads that will need to be performed; however, if this value is set too high, the set of read buffers for each thread can become a memory hog. (You may want to note that this parameter was previously called the record buffer and was controlled by the record_buffer parameter.)

The sort buffer, controlled by the sort_buffer parameter, is used when you run queries containing ORDER BY clauses. It is used to sort the data. If you are sorting large datasets, make it bigger, but the same riders apply as to the record buffer.

Finally, there are a few tips that may help with your physical setup.

With regard to your operating system, MySQL recommends Solaris for getting the most out of multiple CPU machines. Although MySQL is used across various operating systems, development and initial testing are done on Solaris, so this would logically be the most optimized platform.

If you have access to multiple disks, you can improve performance by putting different databases on different disks. You can also use RAID—RAID 0 will improve reading and writing performance, and RAID 1 or 5 will improve reading performance. You will also get better performance from SCSI drives than IDE drives.

You may also consider the use of a journaling file system, such as Reiserfs or XFS. These may give a performance gain.

Obviously, a large and basic gain can be made by having fast networks between clients and servers and between masters and slaves when using replication.

There is an entire book's worth of material on MySQL optimization, so be sure to continue reading and researching for yourself.

In the next chapter, “Optimizing Your Database,” we will look at how to optimize your database schemas, how to index for optimization, and how to optimize individual database tables.