Tuning the Server

The MySQL server has several system variables (parameters) that affect how it operates. If the default variable values are not appropriate, you can change them to values that are better for the environment in which your server runs. Some of these variables are used for performance tuning, such as those that control the size of memory buffers. 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. This will likely make the server run more slowly but may improve overall system performance by preventing the MySQL server from hogging system resources to the detriment of other processes.

Other variables affect how the server interacts with clients, such as the variables that control the SQL mode, the default storage engine, and the current time zone.

The following sections discuss the general syntax for setting or examining system variables and describe some of the variables that have application to the operation of the server as a whole. For tuning parameters specific to individual storage engines, see "Storage Engine Configuration." You can also find additional discussion of server tuning in the optimization chapter of the MySQL Reference Manual.

Setting and Checking System Variable Values

Most system variables can be set at server startup time using options on the command line or in option files. The general syntax is described in "Specifying Program Options," in Appendix F, "MySQL Program Reference." Many system variables can be modified dynamically while the server is running. The ability to set variables at runtime gives you better control over server operation, and can help you avoid stopping the server to reconfigure it under circumstances when that might otherwise be necessary. (For example, you can experiment with buffer sizes to see how that affects server performance, without having to stop and restart the server for each change.) Changes made at runtime do not last beyond termination of the server process, but if you determine a value for a variable that is better than its current default, you can set the variable in an option file to cause the value to be used whenever the server starts in the future.

Several system variables pertain to the manner in which the server interacts with clients. The ability to change these variables affords clients a measure of control over how the server operates and allows applications to customize the behavior they require.

System variables can exist at two levels: global and session-specific. Global variables affect the operation of the server as a whole. Session-specific variables affect only how the server treats a given client connection. For variables that exist at both levels, the global values are used to initialize the corresponding session variables. This happens only when a new connection begins; changing a global variable during a connection does not affect the current value of the connection's corresponding session variable.

It is possible for a system variable to have both global and session forms, only a global form, or only a session form:

The sql_mode system variable that indicates the default SQL mode is an example of a variable that exists at both the global and session levels. When each client connects, it gets its own session-specific sql_mode variable, which initially has the same value as the global variable. Any client can modify the value of its session variable to change the server's behavior for its own connection without affecting how the server treats other clients. A client that has the SUPER privilege also can change the global sql_mode variable. The new global value is used to initialize the session variable for clients that connect after the change.
The table_cache system variable is an example of a global-only variable. It controls the size of the table cache that holds file descriptors for open tables. There is a single table cache and it is shared among all clients, so there is no reason to have a session value for each client.
Some variables exist only at the session level. The autocommit variable is one of these. Each client begins with its autocommit mode enabled by default, but can disable it as necessary.

Appendix D, "System, Status, and User Variable Reference," lists all system variables and indicates which of them can be set at startup time and runtime. The following discussion indicates the syntax for setting variables and for checking their values.

Setting System Variables at Server Startup Time

Many global system variables can be set at startup time. There are two syntaxes for doing this:

First, you can treat a variable name as an option name and set it directly. For example, the size of the table cache is controlled by the table_cache variable. To set the table cache size to 128, you can do so using this option on the mysqld command line:
```
%  mysqld --table_cache=128
```
You can also set the variable in an option file using this syntax:
```
[mysqld]
table_cache=128
```
Another feature of the variable-as-option syntax is that underscores can be given as dashes so that the option looks more like other options. On the command line, set the variable like this:
```
%  mysqld --table-cache=128
```
In an option file, set it like this:
```
[mysqld]
table-cache=128
```
The second syntax for setting a variable at server startup is to use the --set-variable or -O option. On the command line, set a variable like this:
```
% mysqld --set-variable=table_cache=128
% mysqld -O table_cache=128
```
In option files, only the long-option form is allowable:
```
[mysqld]
set-variable=table_cache=128
```
This second syntax is older. It is still supported, but is deprecated.

Whichever syntax you use to set system variables, it's usually easier to do so in an option file than on the command line because you don't have to remember to set them each time you start the server.

For variables that represent buffer sizes or lengths, values are in bytes if specified as a number with no suffix, or may be specified with a suffix of 'K', 'M', or 'G', to indicate kilobytes, megabytes, or gigabytes, respectively. Suffixes are not case sensitive, so you can also use 'k', 'm', or 'g'.

Some system variables cannot be set directly using a startup option. In such cases, there often is a related option. For example, you cannot set the storage_engine variable directly, but the --default-storage-engine option can be used instead. Appendix D indicates which global system variables can be set directly. For those that cannot, the appendix lists the related option for setting the variable if there is one.

Setting System Variables at Runtime

The syntax for setting system variables at runtime depends on whether you want to set a global variable or a session variable. To set a global variable named var_name, use a SET statement having one of these formats:

SET GLOBAL var_name = value;
SET @@GLOBAL.var_name = value;

To set a session variable, similar formats apply:

SET SESSION var_name = value;
SET @@SESSION.var_name = value;

If no level indicator is present at all, the SET statement modifies the session-level variable:

SET var_name = value;
SET @@var_name = value;

You can set several variables in a single SET statement by separating the assignments with commas:

SET SESSION sql_warnings = 0, GLOBAL storage_engine = InnoDB;

In a statement that sets multiple variables, an explicit GLOBAL or SESSION level indicator applies to following variable settings that do not include a level of their own. The following statement sets the global v1 and v2 variables, and the session v3 and v4 variables:

SET GLOBAL v1 = val1, v2 = val2, SESSION v3 = val3, v4 = val4;

In all cases where SESSION is allowed, you can substitute LOCAL as a synonym (this includes use of @@LOCAL for @@SESSION).

You must have the SUPER privilege to set a global variable. The setting persists until changed again or the server exits. No special privileges are needed to set a session variable. The setting persists until changed again or the current connection terminates.

Unlike variables that are set at startup time, you cannot specify runtime values using suffix letters of 'K', 'M', or 'G'. However, you can use expressions, and expressions can refer to the values of other variables. The following statements set the global read_buffer_size value to 2MB, and the session value to twice that:

SET GLOBAL read_buffer_size = 2*1024*1024;
SET SESSION read_buffer_size = 2*@@GLOBAL.read_buffer_size;

Many system variables can be set to the special value DEFAULT. For those variables that understand this syntax, assigning DEFAULT to a global variable sets it to the compiled-in default (even if a different value was given with a startup option). Assigning DEFAULT to a session variable sets it to current value of the corresponding global variable.

MySQL 4.1.1 introduces the concept of a structured system variable, which consists of a set of related system variables that are grouped and accessed as components of the structured variable. Currently, the only structured variables that exist are used for configuring MyISAM key caches, so their syntax is discussed in "Configuring the MyISAM Storage Engine."

Checking System Variable Values

To see the current values of system variables, use SHOW VARIABLES. This statement allows you to display all system variables or just those with names that match a given SQL pattern:

SHOW VARIABLES;
SHOW VARIABLES LIKE 'pattern';

The statement returns each variable's session value if one exists at that level and the global value if not. To specifically request the values of global or session variables, add GLOBAL or SESSION to the statement:

SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'pattern';
SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE 'pattern';

The mysqladmin variables command displays the current values of the server's global system variables.

Individual variable values can be selected using @@GLOBAL.var_name syntax for a global variable, or @@SESSION.var_name or @@LOCAL.var_name for a session variable. If you use @@var_name syntax without a level qualifier, the session variable is used if it exists and the global value if not.

The @@-syntax is general and can be used in SET, SELECT, or other SQL statements:

SELECT 'Default storage engine:', @@storage_engine;

Most of the session-only variables are not displayed at all by SHOW VARIABLES, but you can select their values by name:

SELECT @@autocommit, @@warning_count;

General Purpose System Variables

The following list describes several system variables that are useful for general performance tuning:

delayed_queue_size
This variable determines the number of rows from INSERT DELAYED statements that can be queued per MyISAM or MEMORY table before clients performing additional INSERT DELAYED statements get blocked. If you have many clients that use INSERT DELAYED to avoid being blocked but find that they are being blocked anyway because too many rows are being queued, increasing the value of this variable can be useful. That allows a larger queue and reduces the amount of client blocking that occurs. (INSERT DELAYED is discussed in the section "Scheduling and Locking Issues," of Chapter 4, "Query Optimization.")
max_allowed_packet
The maximum size to which the buffer used for client communications can grow. The largest value to which this variable can be set is 1GB.
The default buffer size for the server is 1MB. Some clients also have their own max_allowed_packet variable. If you have clients that send very long statements to the server (for example, statements that include large BLOB or TEXT values), this variable may need to be increased both on the server end and on the client end. For example, to start the server with a 64MB packet limit, you could add these lines to the server option file:
```
[mysqld]
max_allowed_packet=64M
```
For occasions when you need to invoke mysql or mysqldump with a 64MB packet limit, do so like this:
```
% mysql --max_allowed_packet=64M ...other options...
% mysqldump --max_allowed_packet=64M ...other options...
```
To use these settings all the time, add these lines to your option file:
```
[mysql]
max_allowed_packet=64M

[mysqldump]
max_allowed_packet=64M
```
max_connections
The maximum number of simultaneous client connections the server will allow. If your server is busy, you might need to increase this value. For example, if your MySQL server is used by an active Web server to process lots of statements generated by DBI or PHP scripts, visitors to your site might 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 by reducing the number of file open and close operations 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 can try setting the open_files_limit variable for mysqld or using the --open-files-limit option for mysqld_safe. If you cannot set the open files limit high enough using one of those methods, you might need to configure your system to allow more file descriptors. Some systems can be configured simply by editing a system description file and restarting. 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, this strategy can result in complications. For example, you cannot access databases in different data directories from a single server, and you need to set up privileges in the grant tables of multiple servers for users that need access to more than one server.

Another possibility for reducing file-descriptor requirements is to set up replication of your main MySQL server to one or more slave servers. All updates should be directed to the main server, but requests from clients that perform only retrievals can be distributed among all the servers. This lessens the client load of the main server and reduces its file descriptor requirements.

Some variables control resources that are allocated on a per-client basis. Increasing these variables has the potential to increase the server's resource requirements dramatically if many clients connect to it simultaneously. For example, two values that you might increase in hopes of improving performance are those of the read_buffer_size and sort_buffer_size variables, which determine the size of the buffers that are used during read and sort operations. However, these buffers are allocated for each connection, so if you make the values of the corresponding variables quite large, performance may actually suffer due to exorbitant system resource consumption. Be cautious about changing the sizes of per-connection buffers. Increase them incrementally and then test your changes rather than bumping them up by a large amount all at once. This will allow you to assess the effect of each change with less likelihood of serious performance degradation. Be sure to use realistic test conditions as well. These buffers are allocated only as needed rather than as soon as a client connects. For example, the sort buffer is not allocated for a client unless it performs a query that requires a sort operation. The join_buffer_size variable controls the size of the buffer used for non-indexed joins between tables, but a client that runs no joins needs no join buffer. (Conversely, a client that runs complex many-table joins might need multiple join buffers simultaneously.) Your test conditions should use clients that connect at the same time and run complex statements so that you can see the real effect on the server's memory requirements.