Databases Reference
In-Depth Information
• The
join_buffer_size
variable has a global default and can be set per-session, but
a single query that joins several tables can allocate one join buffer
per join
, so there
might be several join buffers per query.
In addition to setting variables in the configuration files, you can also change many
(but not all) of them while the server is running. MySQL refers to these as
dynamic
configuration variables. The following statements show different ways to change the
session and global values of
sort_buffer_size
dynamically:
SET sort_buffer_size =
<value>
;
SET GLOBAL sort_buffer_size =
<value>
;
SET @@sort_buffer_size :=
<value>
;
SET @@session.sort_buffer_size :=
<value>
;
SET @@global.sort_buffer_size :=
<value>
;
If you set variables dynamically, be aware that those settings will be lost when MySQL
shuts down. If you want to keep the settings, you'll have to update your configuration
file as well.
If you set a variable's global value while the server is running, the values for the current
session and any other existing sessions are not affected. This is because the session
values are initialized from the global value when the connections are created. You
should inspect the output of
SHOW GLOBAL VARIABLES
after each change to make sure it's
had the desired effect.
Variables use different kinds of units, and you have to know the correct unit for each
variable. For example, the
table_cache
variable specifies the number of tables that can
be cached, not the size of the table cache in bytes. The
key_buffer_size
is specified in
bytes, whereas still other variables are specified in number of pages or other units, such
as percentages.
Many variables can be specified with a suffix, such as
1M
for one megabyte. However,
this works only in the configuration file or as a command-line argument. When you
use the SQL
SET
command, you must use the literal value
1048576
, or an expression
such as
1024 * 1024
. You can't use expressions in configuration files.
There is also a special value you can assign to variables with the
SET
command: the
keyword
DEFAULT
. Assigning this value to a session-scoped variable sets that variable to
the corresponding globally scoped variable's value; assigning it to a globally scoped
variable sets the variable to the compiled-in default (not the value specified in the con-
figuration file). This is useful for resetting session-scoped variables back to the values
they had when you opened the connection. We advise you not to use it for global
variables, because it probably won't do what you want—that is, it doesn't set the values
back to what they were when you started the server.
