6.1.5 System Variables

Starting from MySQL 4.0.3 we provide better access to a lot of system and connection variables. One can change most of them without having to take down the server.

There are two kind of system variables: Thread-specific (or connection-specific) variables that are unique to the current connection and global variables that are used to configure global events. Global variables also are used to set up the initial values of the corresponding thread-specific variables for new connections.

When mysqld starts, all global variables are initialised from command line arguments and option files. You can change the value with the SET GLOBAL command. When a new thread is created, the thread-specific variables are initialised from the global variables and they will not change even if you issue a new SET GLOBAL command.

To set the value for a GLOBAL variable, you should use one of the following syntaxes: (Here we use sort_buffer_size as an example variable)

SET GLOBAL sort_buffer_size=value;
SET @@global.sort_buffer_size=value;

To set the value for a SESSION variable, you can use one of the following syntaxes:

SET SESSION sort_buffer_size=value;
SET @@session.sort_buffer_size=value;
SET sort_buffer_size=value;

If you don't specify GLOBAL or SESSION then SESSION is used. See section 5.5.6 SET Syntax.

LOCAL is a synonym for SESSION.

To retrieve the value for a GLOBAL variable you can use one of the following commands:

SELECT @@global.sort_buffer_size;
SHOW GLOBAL VARIABLES like 'sort_buffer_size';

To retrieve the value for a SESSION variable you can use one of the following commands:

SELECT @@session.sort_buffer_size;
SHOW SESSION VARIABLES like 'sort_buffer_size';

When you retrieve a variable value with the @@variable_name syntax and you don't specify GLOBAL or SESSION then MySQL will return the thread-specific (SESSION) value if it exists. If not, MySQL will return the global value.

The reason for requiring GLOBAL for setting GLOBAL only variables but not for retrieving them is to ensure that we don't later run into problems if we later would introduce a thread-specific variable with the same name or remove a thread-specific variable. In this case, you could accidentally change the state for the server as a whole, rather than just for your own connection.

The following is a full list of all variables that you change and retrieve and if you can use GLOBAL or SESSION with them.

Variables that are marked with num can be given a numerical value. Variables that are marked with bool can be set to 0, 1, ON or OFF. Variables that are of type enum should normally be set to one of the available values for the variable, but can also be set to the number that correspond to the enum value. (The first enum value is 0).

Here is a description of some of the variables:

A description of the other variable definitions can be found in the startup options section, the description of SHOW VARIABLES and in the SET section. See section 4.1.1 mysqld Command-line Options. See section 4.5.7.4 SHOW VARIABLES. See section 5.5.6 SET Syntax.

User Comments

Add your own comment.