MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
A structured variable differs from a regular system variable in two respects:
Its value is a structure with components that specify server parameters considered to be closely related.
There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.
MySQL supports one structured variable type, which specifies parameters governing the operation of key caches. A key cache structured variable has these components:
This section describes the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in Section 8.10.2, “The MyISAM Key Cache”.
To refer to a component of a structured variable instance, you
can use a compound name in
instance_name.component_name
format.
Examples:
hot_cache.key_buffer_size hot_cache.key_cache_block_size cold_cache.key_cache_block_size
For each structured system variable, an instance with the name
of default
is always predefined. If you refer
to a component of a structured variable without any instance
name, the default
instance is used. Thus,
default.key_buffer_size
and
key_buffer_size
both refer to
the same system variable.
Structured variable instances and components follow these naming rules:
For a given type of structured variable, each instance must
have a name that is unique within
variables of that type. However, instance names need not be
unique across structured variable
types. For example, each structured variable has an instance
named default
, so
default
is not unique across variable
types.
The names of the components of each structured variable type must be unique across all system variable names. If this were not true (that is, if two different types of structured variables could share component member names), it would not be clear which default structured variable to use for references to member names that are not qualified by an instance name.
If a structured variable instance name is not legal as an
unquoted identifier, refer to it as a quoted identifier
using backticks. For example, hot-cache
is not legal, but `hot-cache`
is.
global
, session
, and
local
are not legal instance names. This
avoids a conflict with notation such as
@@GLOBAL.
for referring to nonstructured system variables.
var_name
Currently, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules may assume greater significance if some other type of structured variable is created in the future.
With one exception, you can refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option:
shell> mysqld --hot_cache.key_buffer_size=64K
In an option file, use this syntax:
[mysqld] hot_cache.key_buffer_size=64K
If you start the server with this option, it creates a key cache
named hot_cache
with a size of 64KB in
addition to the default key cache that has a default size of
8MB.
Suppose that you start the server as follows:
shell>mysqld --key_buffer_size=256K \
--extra_cache.key_buffer_size=128K \
--extra_cache.key_cache_block_size=2048
In this case, the server sets the size of the default key cache
to 256KB. (You could also have written
--default.key_buffer_size=256K
.) In addition,
the server creates a second key cache named
extra_cache
that has a size of 128KB, with
the size of block buffers for caching table index blocks set to
2048 bytes.
The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:
shell>mysqld --key_buffer_size=6M \
--hot_cache.key_buffer_size=2M \
--cold_cache.key_buffer_size=2M
Structured variable values may be set and retrieved at runtime
as well. For example, to set a key cache named
hot_cache
to a size of 10MB, use either of
these statements:
mysql>SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;
mysql>SET @@GLOBAL.hot_cache.key_buffer_size = 10*1024*1024;
To retrieve the cache size, do this:
mysql> SELECT @@GLOBAL.hot_cache.key_buffer_size;
However, the following statement does not work. The variable is
not interpreted as a compound name, but as a simple string for a
LIKE
pattern-matching operation:
mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';
This is the exception to being able to use structured variable names anywhere a simple variable name may occur.