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.
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:
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
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';
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
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.