Optimization for Administrators

The previous sections have described optimizations that can be performed by ordinary MySQL users in terms of table creation and indexing operations, and in terms of how you write queries. But there are also optimizations that can be performed only by MySQL and system administrators who have control of the MySQL server or the machine on which it runs. Some server parameters pertain directly to query processing and may be tuned, and certain hardware configuration issues have a direct effect on query processing speed.

Server Parameters

The server has several parameters (variables) that you can change to affect its operation. A general discussion of server parameter tuning is given in Chapter 11, "General MySQL Administration," but a few of these parameters are related primarily to query processing and merit brief mention here:

• delayed_queue_size

  This determines the number of rows from INSERT DELAYED statements that will be queued before clients performing additional INSERT DELAYED statements get blocked. Increasing this value allows the server to accept more rows from this type of request so that clients can proceed without blocking.

• key_buffer_size

  This is the size of the buffer used to hold index blocks. If you have the memory, increasing this value should improve index creation and modification time. Larger values allow MySQL to hold more index blocks in memory at once, which increases the likelihood of finding key values in memory without having to read a new block from disk.

In MySQL 3.23 and up, if you increase the key buffer size, you might also want to start the server using the --init-file option. This allows you to specify a file of SQL statements to be executed when the server starts up. If you have read-only tables that you'd like to have held in memory, you can copy them to HEAP tables for very fast index lookups.

Hardware Issues

You can use your hardware more effectively to improve server performance:

• Install more memory into your machine. This enables you to increase the server's cache and buffer sizes. This allows the server to use information held in memory more often and with less need to fetch information from disk.

• Reconfigure your system to remove all disk swap devices if you have enough RAM to do all swapping into a memory file system. Otherwise, some systems will continue to swap to disk even if you have sufficient RAM for swapping.

• Add faster disks to improve I/O latency. Seek time is typically the primary determinant of performance here. It's slow to move the heads laterally; once the heads are positioned, reading blocks off the track is fast by comparison.

• Try to redistribute disk activity across physical devices. For example, if you can store your two busiest databases on separate physical drives, do so. Note that using different partitions on the same physical device isn't sufficient. That won't help you because they'll still contend for the same physical resource (disk heads). The procedure for moving databases is described in Chapter 10, "The MySQL Data Directory."

  Before you relocate data to a different device, make sure you understand your system's load characteristics. If there's some other major activity already taking place on a particular physical device, putting a database there may actually make performance worse. For example, you may not realize any overall benefit if you process a lot of Web traffic and move a database onto the device where your Web server document tree is located. (If you have only a single drive, you can't perform much disk activity redistribution, of course.)

• When you build MySQL, configure it to use static libraries rather than shared libraries. Dynamic binaries that use shared libraries save on disk space, but static binaries are faster. (However, you cannot use static binaries if you want to load user-defined functions because the UDF mechanism relies on dynamic linking.)