Structured system variables are supported beginning with MySQL 4.1.1. 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.
In MySQL 4.1 (4.1.1 and above), MySQL supports one structured variable type. It specifies parameters that govern the operation of key caches. A key cache structured variable has these components:
The purpose of this section is to describe 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 7.5.1, “The MyISAM Key Cache”.
To refer to a component of a structured variable instance, you
can use a compound name in
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
default is always predefined. If you refer
to a component of a structured variable without any instance
default instance is used. Thus,
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
default is not unique across variable
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,
is not legal, but
local are not legal instance names. This
avoids a conflict with notation such as
for referring to nonstructured system variables.
At the moment, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules will assume greater significance if some other type of structured variable is created in the future.
With one exception, it is permissible to 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:
In an option file, use this syntax:
If you start the server with such an option, it creates a key
hot_cache with a size of 64KB in
addition to the default key cache that has a default size of
Suppose that you start the server as follows:
mysqld --key_buffer_size=256K \
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
The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:
mysqld --key_buffer_size=6M \
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
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:
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:
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.