Tuning the Server

The MySQL server has several parameters (variables) that affect how it operates. If the default parameter values are not appropriate, you can change them to values that are better for the environment in which your server runs. For example, if you have plenty of memory, you can tell the server to use larger buffers for disk and index operations. This will hold more information in memory and decrease the number of disk accesses that need to be made. If your system is more modest, you can tell the server to use smaller buffers to prevent it from hogging system resources to the detriment of other processes.

The current values of the server's variables can be examined by executing the mysqladmin variables command. Variables can be set with the --set-variable var_name=value option on the command line (-O var_name=value is equivalent.) If you want to set several variables, use multiple --set-variable options. You can also set variables in the [mysqld] group in an option file using this syntax:

set-variable=var_name=value
					

A complete list of server variables is given in Appendix E under the entry for the mysqld program. Those that are most likely to be useful for performance tuning are shown in the following list. You can also find additional discussion of this topic in the chapter "Getting Maximum Performance from MySQL" in the MySQL Reference Manual.

• back_log

  The number ofincoming client connection requests that can be queued while processing requests from the current clients. If you have a very busy site, you may want to increase the value of this variable.

• delayed_queue_size

  This variable controls the number of rows from INSERT DELAYED statements that can be queued. If the queue is full, further INSERT DELAYED statements block until there is room in the queue, which prevents the clients that issue those statements from continuing. If you have many clients that perform this kind of INSERT and you find that they are blocking, increasing this variable will allow more of them to continue more quickly. (INSERT DELAYED is discussed in the section "Scheduling and Locking Issues" of Chapter 4, "Query Optimization.")

• flush_time

  If your system has problems and tends to lock up or reboot often, setting this variable to a non-zero value causes the server to flush the table cache every flush_time seconds. Writing out table changes in this way degrades performance but can reduce the chance of table corruption or data loss.

  Under Windows, you can start the server with the --flush option on the command line to force table changes to be flushed after every update.

• key_buffer_size

  The sizeof the buffer used to hold index blocks. Creating and modifying indexes is faster if you increase the value of this variable. Larger values make it more likely that MySQL will find key values in memory, which reduces the number of disk accesses needed for index processing.

  This variable is called key_buffer in versions of MySQL prior to 3.23. MySQL 3.23 and up recognizes both names.

• max_allowed_packet

  The maximumsize to which the buffer used for client communications can grow. If you have clients that send large BLOB or TEXT values, this server variable may need to be increased.

  Clients currently use a default buffer size of 24MB. If you have older clients that use a smaller buffer, you may need to make the client buffer larger. For example, mysql can be invoked like this to specify a 24MB packet limit:

  mysql --set-variable max_allowed_packet=24M
  

• max_connections

  The maximum number of simultaneous client connections the server will allow. If your server is busy, you may need to increase this value. For example, if your MySQL server is used by your Web server to process queries generated by DBI or PHP scripts, and you have a lot of Web traffic, visitors to your site may find requests being refused if this variable is set too low.

• table_cache

  The size of the table cache. Increasing this value allows mysqld to keep more tables open simultaneously and reduces the number of file opens and closes that must be done.

If you increase the values of max_connections or table_cache, the server will require a larger number of file descriptors. That may cause problems with operating system limits on the per-process number of file descriptors, in which case you'll need to increase the limit or work around it. Procedures vary for increasing the limit on the number of file descriptors. You may be able to do this at runtime using the ulimit command in the script that you use to start up the server, or you may need to reconfigure your system. Some systems can be configured simply by editing a system description file and rebooting. For others, you must edit a kernel description file and rebuild the kernel. Consult the documentation for your system to see how to proceed.

One way to work around per-process file descriptor limits is to split your data directory into multiple data directories and run multiple servers. This effectively multiplies the number of file descriptors available by the number of servers you run. On the other hand, other complications can cause you problems. To name two, you cannot access databases in different data directories from a single server, and you might need to replicate privileges in the grant tables across different servers for users that need access to more than one server.

Two variables that administrators sometimes increase in hopes of improving performance are record_buffer and sort_buffer. These buffers are used during join and sort operations, but the values are per connection. That is, each client gets its own buffers. If you make the values of these variables quite large, performance may actually suffer due to exorbitant system resource consumption. If you want to modify these variables, execute mysqladmin variables to see what values they currently have, then adjust up the values incrementally. This will allow you to assess the effect of the change with less likelihood of serious performance degradation.