(mysql.info) server-system-variables
Info Catalog
(mysql.info) server-options
(mysql.info) mysqld
(mysql.info) using-system-variables
5.2.2 Server System Variables
-----------------------------
The `mysql' server maintains many system variables that indicate how it
is configured. Each system variable has a default value. System
variables can be set at server startup using options on the command
line or in an option file. Most of them can be changed dynamically
while the server is running by means of the `SET' statement, which
enables you to modify operation of the server without having to stop
and restart it. You can refer to system variable values in expressions.
There are several ways to see the names and values of system variables:
* To see the values that a server will use based on its compiled-in
defaults and any option files that it reads, use this command:
mysqld --verbose --help
* To see the values that a server will use based on its compiled-in
defaults, ignoring the settings in any option files, use this
command:
mysqld --no-defaults --verbose --help
* To see the current values used by a running server, use the `SHOW
VARIABLES' statement.
This section provides a description of each system variable. Variables
with no version indicated are present in all MySQL 5.0 releases. For
historical information concerning their implementation, please see
`MySQL 3.23, 4.0, 4.1 Reference Manual'.
For additional system variable information, see these sections:
* using-system-variables, discusses the syntax for setting
and displaying system variable values.
* dynamic-system-variables, lists the variables that can be
set at runtime.
* Information on tuning sytem variables can be found in
server-parameters.
* innodb-parameters, lists `InnoDB' system variables.
_Note_: Some of the following variable descriptions refer to `enabling'
or `disabling' a variable. These variables can be enabled with the
`SET' statement by setting them to `ON' or `1', or disabled by setting
them to `OFF' or `0'. However, to set such a variable on the command
line or in an option file, you must set it to `1' or `0'; setting it to
`ON' or `OFF' will not work. For example, on the command line,
-delay_key_write=1 works but -delay_key_write=ON does not.
Values for buffer sizes, lengths, and stack sizes are given in bytes
unless otherwise specified.
* `auto_increment_increment'
`auto_increment_increment' and `auto_increment_offset' are
intended for use with master-to-master replication, and can be
used to control the operation of `AUTO_INCREMENT' columns. Both
variables can be set globally or locally, and each can assume an
integer value between 1 and 65,535 inclusive. Setting the value of
either of these two variables to 0 causes its value to be set to 1
instead. Attempting to set the value of either of these two
variables to an integer greater than 65,535 or less than 0 causes
its value to be set to 65,535 instead. Attempting to set the value
of `auto_increment_increment' or `auto_increment_offset' to a
non-integer value gives rise to an error, and the actual value of
the variable remains unchanged.
These two variables effect `AUTO_INCREMENT' column behavior as
follows:
* `auto_increment_increment' controls the interval between
successive column values. For example:
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc1
-> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
+-----+
4 rows in set (0.00 sec)
(Note how `SHOW VARIABLES' is used here to obtain the current
values for these variables.)
* `auto_increment_offset' determines the starting point for the
`AUTO_INCREMENT' column value. Consider the following,
assuming that these statements are executed during the same
session as the example given in the description for
`auto_increment_increment':
mysql> SET @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc2
-> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
| 35 |
+-----+
4 rows in set (0.02 sec)
If the value of `auto_increment_offset' is greater than that
of `auto_increment_increment', the value of
`auto_increment_offset' is ignored.
Should one or both of these variables be changed and then new rows
inserted into a table containing an `AUTO_INCREMENT' column, the
results may seem counterintuitive because the series of
`AUTO_INCREMENT' values is calculated without regard to any values
already present in the column, and the next value inserted is the
least value in the series that is greater than the maximum
existing value in the `AUTO_INCREMENT' column. In other words, the
series is calculated like so:
`auto_increment_offset + N × auto_increment_increment'
where N is a positive integer value in the series [1, 2, 3, ...].
For example:
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
+-----+
4 rows in set (0.00 sec)
mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
| 35 |
| 45 |
| 55 |
| 65 |
+-----+
8 rows in set (0.00 sec)
The values shown for `auto_increment_increment' and
`auto_increment_offset' generate the series 5 + N × 10, that is,
[5, 15, 25, 35, 45, ...]. The greatest value present in the `col'
column prior to the `INSERT' is 31, and the next available value
in the `AUTO_INCREMENT' series is 35, so the inserted values for
`col' begin at that point and the results are as shown for the
`SELECT' query.
It is important to remember that it is not possible to confine the
effects of these two variables to a single table, and thus they do
not take the place of the sequences offered by some other database
management systems; these variables control the behavior of all
`AUTO_INCREMENT' columns in *all* tables on the MySQL server. If
one of these variables is set globally, its effects persist until
the global value is changed or overridden by setting them locally,
or until `mysqld' is restarted. If set locally, the new value
affects `AUTO_INCREMENT' columns for all tables into which new
rows are inserted by the current user for the duration of the
session, unless the values are changed during that session.
The `auto_increment_increment' variable was added in MySQL 5.0.2.
Its default value is 1. See replication-auto-increment.
* `auto_increment_offset'
This variable was introduced in MySQL 5.0.2. Its default value is
1. For particulars, see the description for
`auto_increment_increment'.
* `back_log'
The number of outstanding connection requests MySQL can have. This
comes into play when the main MySQL thread gets very many
connection requests in a very short time. It then takes some time
(although very little) for the main thread to check the connection
and start a new thread. The `back_log' value indicates how many
requests can be stacked during this short time before MySQL
momentarily stops answering new requests. You need to increase
this only if you expect a large number of connections in a short
period of time.
In other words, this value is the size of the listen queue for
incoming TCP/IP connections. Your operating system has its own
limit on the size of this queue. The manual page for the Unix
`listen()' system call should have more details. Check your OS
documentation for the maximum value for this variable. `back_log'
cannot be set higher than your operating system limit.
* `basedir'
The MySQL installation base directory. This variable can be set
with the -basedir option.
* `bdb_cache_size'
The size of the buffer that is allocated for caching indexes and
rows for `BDB' tables. If you don't use `BDB' tables, you should
start `mysqld' with -skip-bdb to not allocate memory for this
cache.
* `bdb_home'
The base directory for `BDB' tables. This should be assigned the
same value as the `datadir' variable.
* `bdb_log_buffer_size'
The size of the buffer that is allocated for caching indexes and
rows for `BDB' tables. If you don't use `BDB' tables, you should
set this to 0 or start `mysqld' with -skip-bdb to not allocate
memory for this cache.
* `bdb_logdir'
The directory where the `BDB' storage engine writes its log files.
This variable can be set with the -bdb-logdir option.
* `bdb_max_lock'
The maximum number of locks that can be active for a `BDB' table
(10,000 by default). You should increase this value if errors such
as the following occur when you perform long transactions or when
`mysqld' has to examine many rows to calculate a query:
bdb: Lock table is out of available locks
Got error 12 from ...
* `bdb_shared_data'
This is `ON' if you are using -bdb-shared-data to start Berkeley
DB in multi-process mode. (Do not use `DB_PRIVATE' when
initializing Berkeley DB.)
* `bdb_tmpdir'
The `BDB' temporary file directory.
* `binlog_cache_size'
The size of the cache to hold the SQL statements for the binary
log during a transaction. A binary log cache is allocated for each
client if the server supports any transactional storage engines
and if the server has the binary log enabled (-log-bin option). If
you often use large, multiple-statement transactions, you can
increase this cache size to get more performance. The
`Binlog_cache_use' and `Binlog_cache_disk_use' status variables
can be useful for tuning the size of this variable. See
binary-log.
* `bulk_insert_buffer_size'
`MyISAM' uses a special tree-like cache to make bulk inserts
faster for `INSERT ... SELECT', `INSERT ... VALUES (...), (...),
...', and `LOAD DATA INFILE' when adding data to non-empty tables.
This variable limits the size of the cache tree in bytes per
thread. Setting it to 0 disables this optimization. The default
value is 8MB.
* `character_set_client'
The character set for statements that arrive from the client.
* `character_set_connection'
The character set used for literals that do not have a character
set introducer and for number-to-string conversion.
* `character_set_database'
The character set used by the default database. The server sets
this variable whenever the default database changes. If there is
no default database, the variable has the same value as
`character_set_server'.
* `character_set_filesystem'
The filesystem character set. This variable is used to interpret
string literals that refer to filenames, such as in the `LOAD DATA
INFILE' and `SELECT ... INTO OUTFILE' statements and the
`LOAD_FILE()' function. Such filenames are converted from
`character_set_client' to `character_set_filesystem' before the
file opening attempt occurs. The default value is `binary', which
means that no conversion occurs. For systems on which multi-byte
filenames are allowed, a different value may be more appropriate.
For example, if the system represents filenames using UTF-8, set
`character_set_filesytem' to `'utf8''. This variable was added in
MySQL 5.0.19.
* `character_set_results'
The character set used for returning query results to the client.
* `character_set_server'
The server's default character set.
* `character_set_system'
The character set used by the server for storing identifiers. The
value is always `utf8'.
* `character_sets_dir'
The directory where character sets are installed.
* `collation_connection'
The collation of the connection character set.
* `collation_database'
The collation used by the default database. The server sets this
variable whenever the default database changes. If there is no
default database, the variable has the same value as
`collation_server'.
* `collation_server'
The server's default collation.
* `completion_type'
The transaction completion type:
* If the value is 0 (the default), `COMMIT' and `ROLLBACK' are
unaffected.
* If the value is 1, `COMMIT' and `ROLLBACK' are equivalent to
`COMMIT AND CHAIN' and `ROLLBACK AND CHAIN', respectively.
(A new transaction starts immediately with the same isolation
level as the just-terminated transaction.)
* If the value is 2, `COMMIT' and `ROLLBACK' are equivalent to
`COMMIT RELEASE' and `ROLLBACK RELEASE', respectively. (The
server disconnects after terminating the transaction.)
This variable was added in MySQL 5.0.3
* `concurrent_insert'
If `ON' (the default), MySQL allows `INSERT' and `SELECT'
statements to run concurrently for `MyISAM' tables that have no
free blocks in the middle. You can turn this option off by starting
`mysqld' with -safe or -skip-new.
In MySQL 5.0.6, this variable was changed to take three integer
values:
*Value* *Description*
0 Off
1 (Default) Enables concurrent insert for `MyISAM'
tables that don't have holes
2 Enables concurrent inserts for all `MyISAM'
tables. If table has a hole and is in use by
another thread the new row will be inserted at
end of table. If table is not in use then MySQL
does a normal read lock and inserts the new row
into the hole.
See also concurrent-inserts.
* `connect_timeout'
The number of seconds that the `mysqld' server waits for a connect
packet before responding with `Bad handshake'.
* `datadir'
The MySQL data directory. This variable can be set with the
-datadir option.
* `date_format'
This variable is not implemented.
* `datetime_format'
This variable is not implemented.
* `default_week_format'
The default mode value to use for the `WEEK()' function. See
date-and-time-functions.
* `delay_key_write'
This option applies only to `MyISAM' tables. It can have one of
the following values to affect handling of the `DELAY_KEY_WRITE'
table option that can be used in `CREATE TABLE' statements.
*Option**Description*
`OFF' `DELAY_KEY_WRITE' is ignored.
`ON' MySQL honors any `DELAY_KEY_WRITE' option
specified in `CREATE TABLE' statements. This is
the default value.
`ALL' All new opened tables are treated as if they were
created with the `DELAY_KEY_WRITE' option enabled.
If `DELAY_KEY_WRITE' is enabled for a table, the key buffer is not
flushed for the table on every index update, but only when the
table is closed. This speeds up writes on keys a lot, but if you
use this feature, you should add automatic checking of all
`MyISAM' tables by starting the server with the -myisam-recover
option (for example, -myisam-recover=BACKUP,FORCE). See
server-options, and myisam-start.
Note that enabling external locking with -external-locking offers
no protection against index corruption for tables that use delayed
key writes.
* `delayed_insert_limit'
After inserting `delayed_insert_limit' delayed rows, the `INSERT
DELAYED' handler thread checks whether there are any `SELECT'
statements pending. If so, it allows them to execute before
continuing to insert delayed rows.
* `delayed_insert_timeout'
How many seconds an `INSERT DELAYED' handler thread should wait
for `INSERT' statements before terminating.
* `delayed_queue_size'
This is a per-table limit on the number of rows to queue when
handling `INSERT DELAYED' statements. If the queue becomes full,
any client that issues an `INSERT DELAYED' statement waits until
there is room in the queue again.
* `div_precision_increment'
This variable indicates the number of digits of precision by which
to increase the result of division operations performed with the
`/' operator. The default value is 4. The minimum and maximum
values are 0 and 30, respectively. The following example
illustrates the effect of increasing the default value.
mysql> SELECT 1/7;
+--------+
| 1/7 |
+--------+
| 0.1429 |
+--------+
mysql> SET div_precision_increment = 12;
mysql> SELECT 1/7;
+----------------+
| 1/7 |
+----------------+
| 0.142857142857 |
+----------------+
This variable was added in MySQL 5.0.6.
* `engine_condition_pushdown'
This variable applies to NDB. By default it is 0 (`OFF'): If you
execute a query such as `SELECT * FROM t WHERE mycol = 42', where
`mycol' is a non-indexed column, the query is executed as a full
table scan on every NDB node. Each node sends every row to the
MySQL server, which applies the `WHERE' condition. If
`engine_condition_pushdown' is set to 1 (`ON'), the condition is
`pushed down' to the storage engine and sent to the NDB nodes.
Each node uses the condition to perform the scan, and only sends
back to the MySQL server the rows that match the condition.
This variable was added in MySQL 5.0.3. Before that, the default
`NDB' behavior is the same as for a value of `OFF'.
* `expire_logs_days'
The number of days for automatic binary log removal. The default
is 0, which means `no automatic removal.' Possible removals happen
at startup and at binary log rotation.
* `flush'
If `ON', the server flushes (synchronizes) all changes to disk
after each SQL statement. Normally, MySQL does a write of all
changes to disk only after each SQL statement and lets the
operating system handle the synchronizing to disk. See
crashing. This variable is set to `ON' if you start `mysqld'
with the -flush option.
* `flush_time'
If this is set to a non-zero value, all tables are closed every
`flush_time' seconds to free up resources and synchronize
unflushed data to disk. We recommend that this option be used only
on Windows 9x or Me, or on systems with minimal resources.
* `ft_boolean_syntax'
The list of operators supported by boolean full-text searches
performed using `IN BOOLEAN MODE'. See fulltext-boolean.
The default variable value is `'+ -><()~*:""&|''. The rules for
changing the value are as follows:
* Operator function is determined by position within the string.
* The replacement value must be 14 characters.
* Each character must be an ASCII non-alphanumeric character.
* Either the first or second character must be a space.
* No duplicates are allowed except the phrase quoting operators
in positions 11 and 12. These two characters are not required
to be the same, but they are the only two that may be.
* Positions 10, 13, and 14 (which by default are set to
‘`:'’, ‘`&'’, and ‘`|'’) are reserved for future
extensions.
* `ft_max_word_len'
The maximum length of the word to be included in a `FULLTEXT'
index.
* `FULLTEXT' indexes must be rebuilt after changing this
variable. Use `REPAIR TABLE TBL_NAME QUICK'.
* `ft_min_word_len'
The minimum length of the word to be included in a `FULLTEXT'
index.
* `FULLTEXT' indexes must be rebuilt after changing this
variable. Use `REPAIR TABLE TBL_NAME QUICK'.
* `ft_query_expansion_limit'
The number of top matches to use for full-text searches performed
using `WITH QUERY EXPANSION'.
* `ft_stopword_file'
The file from which to read the list of stopwords for full-text
searches. All the words from the file are used; comments are _not_
honored. By default, a built-in list of stopwords is used (as
defined in the `myisam/ft_static.c' file). Setting this variable
to the empty string (`''') disables stopword filtering.
* `FULLTEXT' indexes must be rebuilt after changing this
variable or the contents of the stopword file. Use `REPAIR TABLE
TBL_NAME QUICK'.
* `group_concat_max_len'
The maximum allowed result length for the `GROUP_CONCAT()'
function. The default is 1024.
* `have_archive'
`YES' if `mysqld' supports `ARCHIVE' tables, `NO' if not.
* `have_bdb'
`YES' if `mysqld' supports `BDB' tables. `DISABLED' if -skip-bdb
is used.
* `have_blackhole_engine'
`YES' if `mysqld' supports `BLACKHOLE' tables, `NO' if not.
* `have_compress'
`YES' if the `zlib' compression library is available to the server,
`NO' if not. If not, the `COMPRESS()' and `UNCOMPRESS()' functions
cannot be used.
* `have_crypt'
`YES' if the `crypt()' system call is available to the server,
`NO' if not. If not, the `ENCRYPT()' function cannot be used.
* `have_csv'
`YES' if `mysqld' supports `ARCHIVE' tables, `NO' if not.
* `have_example_engine'
`YES' if `mysqld' supports `EXAMPLE' tables, `NO' if not.
`have_federated_engine'
`YES' if `mysqld' supports `FEDERATED' tables, `NO' if not. This
variable was added in MySQL 5.0.3.
* `have_geometry'
`YES' if the server supports spatial data types, `NO' if not.
* `have_innodb'
`YES' if `mysqld' supports `InnoDB' tables. `DISABLED' if
-skip-innodb is used.
* `have_isam'
In MySQL 5.0, this variable appears only for reasons of backward
compatibility. It is always `NO' because `ISAM' tables are no
longer supported.
* `have_ndbcluster'
`YES' if `mysqld' supports `NDB Cluster' tables. `DISABLED' if
-skip-ndbcluster is used.
* `have_openssl'
`YES' if `mysqld' supports SSL (encryption) of the client/server
protocol, `NO' if not.
* `have_query_cache'
`YES' if `mysqld' supports the query cache, `NO' if not.
* `have_raid'
In MySQL 5.0, this variable appears only for reasons of backward
compatibility. It is always `NO' because `RAID' tables are no
longer supported.
* `have_rtree_keys'
`YES' if `RTREE' indexes are available, `NO' if not. (These are
used for spatial indexes in `MyISAM' tables.)
* `have_symlink'
`YES' if symbolic link support is enabled, `NO' if not. This is
required on Unix for support of the `DATA DIRECTORY' and `INDEX
DIRECTORY' table options, and on Windows for support of data
directory symlinks.
* `init_connect'
A string to be executed by the server for each client that
connects. The string consists of one or more SQL statements. To
specify multiple statements, separate them by semicolon
characters. For example, each client begins by default with
autocommit mode enabled. There is no global system variable to
specify that autocommit should be disabled by default, but
`init_connect' can be used to achieve the same effect:
SET GLOBAL init_connect='SET AUTOCOMMIT=0';
This variable can also be set on the command line or in an option
file. To set the variable as just shown using an option file,
include these lines:
[mysqld]
init_connect='SET AUTOCOMMIT=0'
Note that the content of `init_connect' is not executed for users
that have the `SUPER' privilege. This is done so that an erroneous
value for `init_connect' does not prevent all clients from
connecting. For example, the value might contain a statement that
has a syntax error, thus causing client connections to fail. Not
executing `init_connect' for users that have the `SUPER' privilege
enables them to open a connection and fix the `init_connect' value.
* `init_file'
The name of the file specified with the -init-file option when you
start the server. This should be a file containing SQL statements
that you want the server to execute when it starts. Each statement
must be on a single line and should not include comments.
* `init_slave'
This variable is similar to `init_connect', but is a string to be
executed by a slave server each time the SQL thread starts. The
format of the string is the same as for the `init_connect'
variable.
* `innodb_XXX'
`InnoDB' system variables are listed in innodb-parameters.
* `interactive_timeout'
The number of seconds the server waits for activity on an
interactive connection before closing it. An interactive client is
defined as a client that uses the `CLIENT_INTERACTIVE' option to
`mysql_real_connect()'. See also `wait_timeout'.
* `join_buffer_size'
The size of the buffer that is used for joins that do not use
indexes and thus perform full table scans. Normally, the best way
to get fast joins is to add indexes. Increase the value of
`join_buffer_size' to get a faster full join when adding indexes
is not possible. One join buffer is allocated for each full join
between two tables. For a complex join between several tables for
which indexes are not used, multiple join buffers might be
necessary.
* `key_buffer_size'
Index blocks for `MyISAM' tables are buffered and are shared by
all threads. `key_buffer_size' is the size of the buffer used for
index blocks. The key buffer is also known as the key cache.
The maximum allowable setting for `key_buffer_size' is 4GB. The
effective maximum size might be less, depending on your available
physical RAM and per-process RAM limits imposed by your operating
system or hardware platform.
Increase the value to get better index handling (for all reads and
multiple writes) to as much as you can afford. Using a value that
is 25% of total memory on a machine that mainly runs MySQL is
quite common. However, if you make the value too large (for
example, more than 50% of your total memory) your system might
start to page and become extremely slow. MySQL relies on the
operating system to perform filesystem caching for data reads, so
you must leave some room for the filesystem cache. Consider also
the memory requirements of other storage engines.
For even more speed when writing many rows at the same time, use
`LOCK TABLES'. See insert-speed.
You can check the performance of the key buffer by issuing a `SHOW
STATUS' statement and examining the `Key_read_requests',
`Key_reads', `Key_write_requests', and `Key_writes' status
variables. (See show.) The `Key_reads/Key_read_requests'
ratio should normally be less than 0.01. The
`Key_writes/Key_write_requests' ratio is usually near 1 if you are
using mostly updates and deletes, but might be much smaller if you
tend to do updates that affect many rows at the same time or if you
are using the `DELAY_KEY_WRITE' table option.
The fraction of the key buffer in use can be determined using
`key_buffer_size' in conjunction with the `Key_blocks_unused'
status variable and the buffer block size, which is available from
the `key_cache_block_size' system variable:
1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
This value is an approximation because some space in the key
buffer may be allocated internally for administrative structures.
It is possible to create multiple `MyISAM' key caches. The size
limit of 4GB applies to each cache individually, not as a group.
See myisam-key-cache.
* `key_cache_age_threshold'
This value controls the demotion of buffers from the hot sub-chain
of a key cache to the warm sub-chain. Lower values cause demotion
to happen more quickly. The minimum value is 100. The default
value is 300. See myisam-key-cache.
* `key_cache_block_size'
The size in bytes of blocks in the key cache. The default value is
1024. See myisam-key-cache.
* `key_cache_division_limit'
The division point between the hot and warm sub-chains of the key
cache buffer chain. The value is the percentage of the buffer
chain to use for the warm sub-chain. Allowable values range from 1
to 100. The default value is 100. See myisam-key-cache.
* `language'
The language used for error messages.
* `large_file_support'
Whether `mysqld' was compiled with options for large file support.
* `large_pages'
Whether large page support is enabled. This variable was added in
MySQL 5.0.3.
* `license'
The type of license the server has.
* `local_infile'
Whether `LOCAL' is supported for `LOAD DATA INFILE' statements. See
load-data-local.
* `locked_in_memory'
Whether `mysqld' was locked in memory with -memlock.
* `log'
Whether logging of all statements to the general query log is
enabled. See query-log.
* `log_bin'
Whether the binary log is enabled. See binary-log.
* `log_bin_trust_function_creators'
This variable applies when binary logging is enabled. It controls
whether stored function creators can be trusted not to create
stored functions that will cause unsafe events to be written to
the binary log. If set to 0 (the default), users are not allowed
to create or alter stored functions unless they have the `SUPER'
privilege in addition to the `CREATE ROUTINE' or `ALTER ROUTINE'
privilege. A setting of 0 also enforces the restriction that a
function must be declared with the `DETERMINISTIC' characteristic,
or with the `READS SQL DATA' or `NO SQL' characteristic. If the
variable is set to 1, MySQL does not enforce these restrictions on
stored function creation. See stored-procedure-logging.
This variable was added in MySQL 5.0.16.
* `log_bin_trust_routine_creators'
This is the old name for `log_bin_trust_function_creators'. Before
MySQL 5.0.16, it also applies to stored procedures, not just
stored functions. As of 5.0.16, this variable is deprecated. It is
recognized for backward compatibility but its use results in a
warning.
This variable was added in MySQL 5.0.6.
* `log_error'
The location of the error log.
* `log_slave_updates'
Whether updates received by a slave server from a master server
should be logged to the slave's own binary log. Binary logging
must be enabled on the slave for this to have any effect. See
replication-options.
* `log_slow_queries'
Whether slow queries should be logged. `Slow' is determined by the
value of the `long_query_time' variable. See
slow-query-log.
* `log_warnings'
Whether to produce additional warning messages. It is enabled (1)
by default. Aborted connections are not logged to the error log
unless the value is greater than 1.
* `long_query_time'
If a query takes longer than this many seconds, the server
increments the `Slow_queries' status variable. If you are using the
-log-slow-queries option, the query is logged to the slow query
log file. This value is measured in real time, not CPU time, so a
query that is under the threshold on a lightly loaded system might
be above the threshold on a heavily loaded one. The minimum value
is 1. See slow-query-log.
* `low_priority_updates'
If set to `1', all `INSERT', `UPDATE', `DELETE', and `LOCK TABLE
WRITE' statements wait until there is no pending `SELECT' or `LOCK
TABLE READ' on the affected table. This variable previously was
named `sql_low_priority_updates'.
* `lower_case_file_system'
This variable describes the case sensitivity of filenames on the
filesystem where the data directory is located. `OFF' means
filenames are case sensitive, `ON' means they are not case
sensitive.
* `lower_case_table_names'
If set to 1, table names are stored in lowercase on disk and table
name comparisons are not case sensitive. If set to 2 table names
are stored as given but compared in lowercase. This option also
applies to database names and table aliases. See
name-case-sensitivity.
If you are using `InnoDB' tables, you should set this variable to
1 on all platforms to force names to be converted to lowercase.
You should _not_ set this variable to 0 if you are running MySQL
on a system that does not have case-sensitive filenames (such as
Windows or Mac OS X). If this variable is not set at startup and
the filesystem on which the data directory is located does not have
case-sensitive filenames, MySQL automatically sets
`lower_case_table_names' to 2.
* `max_allowed_packet'
The maximum size of one packet or any generated/intermediate
string.
The packet message buffer is initialized to `net_buffer_length'
bytes, but can grow up to `max_allowed_packet' bytes when needed.
This value by default is small, to catch large (possibly
incorrect) packets.
You must increase this value if you are using large `BLOB' columns
or long strings. It should be as big as the biggest `BLOB' you want
to use. The protocol limit for `max_allowed_packet' is 1GB.
* `max_binlog_cache_size'
If a multiple-statement transaction requires more than this amount
of memory, the server generates a `Multi-statement transaction
required more than 'max_binlog_cache_size' bytes of storage' error.
* `max_binlog_size'
If a write to the binary log causes the current log file size to
exceed the value of this variable, the server rotates the binary
logs (closes the current file and opens the next one). You cannot
set this variable to more than 1GB or to less than 4096 bytes. The
default value is 1GB.
A transaction is written in one chunk to the binary log, so it is
never split between several binary logs. Therefore, if you have
big transactions, you might see binary logs larger than
`max_binlog_size'.
If `max_relay_log_size' is 0, the value of `max_binlog_size'
applies to relay logs as well.
* `max_connect_errors'
If there are more than this number of interrupted connections from
a host, that host is blocked from further connections. You can
unblock blocked hosts with the `FLUSH HOSTS' statement.
* `max_connections'
The number of simultaneous client connections allowed. Increasing
this value increases the number of file descriptors that `mysqld'
requires. See table-cache, for comments on file descriptor
limits. See also too-many-connections.
* `max_delayed_threads'
Do not start more than this number of threads to handle `INSERT
DELAYED' statements. If you try to insert data into a new table
after all `INSERT DELAYED' threads are in use, the row is inserted
as if the `DELAYED' attribute wasn't specified. If you set this to
0, MySQL never creates a thread to handle `DELAYED' rows; in
effect, this disables `DELAYED' entirely.
* `max_error_count'
The maximum number of error, warning, and note messages to be
stored for display by the `SHOW ERRORS' and `SHOW WARNINGS'
statements.
* `max_heap_table_size'
This variable sets the maximum size to which `MEMORY' tables are
allowed to grow. The value of the variable is used to calculate
`MEMORY' table `MAX_ROWS' values. Setting this variable has no
effect on any existing `MEMORY' table, unless the table is
re-created with a statement such as `CREATE TABLE' or altered with
`ALTER TABLE' or `TRUNCATE TABLE'.
* `max_insert_delayed_threads'
This variable is a synonym for `max_delayed_threads'.
* `max_join_size'
Do not allow `SELECT' statements that probably need to examine
more than `max_join_size' rows (for single-table statements) or
row combinations (for multiple-table statements) or that are
likely to do more than `max_join_size' disk seeks. By setting this
value, you can catch `SELECT' statements where keys are not used
properly and that would probably take a long time. Set it if your
users tend to perform joins that lack a `WHERE' clause, that take
a long time, or that return millions of rows.
Setting this variable to a value other than `DEFAULT' resets the
value of `SQL_BIG_SELECTS' to `0'. If you set the
`SQL_BIG_SELECTS' value again, the `max_join_size' variable is
ignored.
If a query result is in the query cache, no result size check is
performed, because the result has previously been computed and it
does not burden the server to send it to the client.
This variable previously was named `sql_max_join_size'.
* `max_length_for_sort_data'
The cutoff on the size of index values that determines which
`filesort' algorithm to use. See order-by-optimization.
* `max_relay_log_size'
If a write by a replication slave to its relay log causes the
current log file size to exceed the value of this variable, the
slave rotates the relay logs (closes the current file and opens
the next one). If `max_relay_log_size' is 0, the server uses
`max_binlog_size' for both the binary log and the relay log. If
`max_relay_log_size' is greater than 0, it constrains the size of
the relay log, which enables you to have different sizes for the
two logs. You must set `max_relay_log_size' to between 4096 bytes
and 1GB (inclusive), or to 0. The default value is 0. See
replication-implementation-details.
* `max_seeks_for_key'
Limit the assumed maximum number of seeks when looking up rows
based on a key. The MySQL optimizer assumes that no more than this
number of key seeks are required when searching for matching rows
in a table by scanning an index, regardless of the actual
cardinality of the index (see show-index). By setting this
to a low value (say, 100), you can force MySQL to prefer indexes
instead of table scans.
* `max_sort_length'
The number of bytes to use when sorting `BLOB' or `TEXT' values.
Only the first `max_sort_length' bytes of each value are used; the
rest are ignored.
* `max_tmp_tables'
The maximum number of temporary tables a client can keep open at
the same time. (This option does not yet do anything.)
* `max_user_connections'
The maximum number of simultaneous connections allowed to any
given MySQL account. A value of 0 means `no limit.'
Before MySQL 5.0.3, this variable has only global scope.
Beginning with MySQL 5.0.3, it also has a read-only session scope.
The session variable has the same value as the global variable
unless the current account has a non-zero `MAX_USER_CONNECTIONS'
resource limit. In that case, the session value reflects the
account limit.
* `max_write_lock_count'
After this many write locks, allow some pending read lock requests
to be processed in between.
* `myisam_data_pointer_size'
The default pointer size in bytes, to be used by `CREATE TABLE' for
`MyISAM' tables when no `MAX_ROWS' option is specified. This
variable cannot be less than 2 or larger than 7. The default value
is 6 (4 before MySQL 5.0.6). This variable was added in MySQL
4.1.2. See full-table.
* `myisam_max_extra_sort_file_size' (_DEPRECATED_)
If the temporary file used for fast `MyISAM' index creation would
be larger than using the key cache by the amount specified here,
prefer the key cache method. This is mainly used to force long
character keys in large tables to use the slower key cache method
to create the index. The value is given in bytes.
* This variable was removed in MySQL 5.0.6.
* `myisam_max_sort_file_size'
The maximum size of the temporary file MySQL is allowed to use
while re-creating a `MyISAM' index (during `REPAIR TABLE', `ALTER
TABLE', or `LOAD DATA INFILE'). If the file size would be larger
than this value, the index is created using the key cache instead,
which is slower. The value is given in bytes.
* `myisam_recover_options'
The value of the -myisam-recover option. See
server-options.
* `myisam_repair_threads'
If this value is greater than 1, `MyISAM' table indexes are
created in parallel (each index in its own thread) during the
`Repair by sorting' process. The default value is 1. *
Multi-threaded repair is still _beta-quality_ code.
* `myisam_sort_buffer_size'
The size of the buffer that is allocated when sorting `MyISAM'
indexes during a `REPAIR TABLE' or when creating indexes with
`CREATE INDEX' or `ALTER TABLE'.
* `myisam_stats_method'
How the server treats `NULL' values when collecting statistics
about the distribution of index values for `MyISAM' tables. This
variable has two possible values, `nulls_equal' and
`nulls_unequal'. For `nulls_equal', all `NULL' index values are
considered equal and form a single value group that has a size
equal to the number of `NULL' values. For `nulls_unequal', `NULL'
values are considered unequal, and each `NULL' forms a distinct
value group of size 1.
The method that is used for generating table statistics influences
how the optimizer chooses indexes for query execution, as
described in myisam-index-statistics.
This variable was added in MySQL 5.0.14. For older versions, the
statistics collection method is equivalent to `nulls_equal'.
* `multi_read_range'
Specifies the maximum number of ranges to send to a storage engine
during range selects. The default value is 256. Sending multiple
ranges to an engine is a feature that can improve the performance
of certain selects dramatically, particularly for `NDBCLUSTER'.
This engine needs to send the range requests to all nodes, and
sending many of those requests at once reduces the communication
costs significantly. This variable was added in MySQL 5.0.3.
* `named_pipe'
(Windows only.) Indicates whether the server supports connections
over named pipes.
* `net_buffer_length'
The communication buffer is reset to this size between SQL
statements. This variable should not normally be changed, but if
you have very little memory, you can set it to the expected length
of statements sent by clients. If statements exceed this length,
the buffer is automatically enlarged, up to `max_allowed_packet'
bytes.
* `net_read_timeout'
The number of seconds to wait for more data from a connection
before aborting the read. This timeout applies only to TCP/IP
connections, not to connections made via Unix socket files, named
pipes, or shared memory. When the server is reading from the
client, `net_read_timeout' is the timeout value controlling when
to abort. When the server is writing to the client,
`net_write_timeout' is the timeout value controlling when to
abort. See also `slave_net_timeout'.
* `net_retry_count'
If a read on a communication port is interrupted, retry this many
times before giving up. This value should be set quite high on
FreeBSD because internal interrupts are sent to all threads.
* `net_write_timeout'
The number of seconds to wait for a block to be written to a
connection before aborting the write. This timeout applies only to
TCP/IP connections, not to connections made via Unix socket files,
named pipes, or shared memory. See also `net_read_timeout'.
* `new'
This variable was used in MySQL 4.0 to turn on some 4.1 behaviors,
and is retained for backward compatibility. In MySQL 5.0, its
value is always `OFF'.
* `old_passwords'
Whether the server should use pre-4.1-style passwords for MySQL
user accounts. See old-client.
* `one_shot'
This is not a variable, but it can be used when setting some
variables. It is described in set-option.
* `open_files_limit'
The number of files that the operating system allows `mysqld' to
open. This is the real value allowed by the system and might be
different from the value you gave using the -open-files-limit
option to `mysqld' or `mysqld_safe'. The value is 0 on systems
where MySQL can't change the number of open files.
* `optimizer_prune_level'
Controls the heuristics applied during query optimization to prune
less-promising partial plans from the optimizer search space. A
value of 0 disables heuristics so that the optimizer performs an
exhaustive search. A value of 1 causes the optimizer to prune
plans based on the number of rows retrieved by intermediate plans.
This variable was added in MySQL 5.0.1.
* `optimizer_search_depth'
The maximum depth of search performed by the query optimizer.
Values larger than the number of relations in a query result in
better query plans, but take longer to generate an execution plan
for a query. Values smaller than the number of relations in a
query return an execution plan quicker, but the resulting plan may
be far from being optimal. If set to 0, the system automatically
picks a reasonable value. If set to the maximum number of tables
used in a query plus 2, the optimizer switches to the algorithm
used in MySQL 5.0.0 (and previous versions) for performing
searches. This variable was added in MySQL 5.0.1.
* `pid_file'
The pathname of the process ID (PID) file. This variable can be
set with the -pid-file option.
* `port'
The number of the port on which the server listens for TCP/IP
connections. This variable can be set with the -port option.
* `preload_buffer_size'
The size of the buffer that is allocated when preloading indexes.
* `protocol_version'
The version of the client/server protocol used by the MySQL server.
* `query_alloc_block_size'
The allocation size of memory blocks that are allocated for
objects created during statement parsing and execution. If you
have problems with memory fragmentation, it might help to increase
this a bit.
* `query_cache_limit'
Don't cache results that are larger than this number of bytes. The
default value is 1MB.
* `query_cache_min_res_unit'
The minimum size (in bytes) for blocks allocated by the query
cache. The default value is 4096 (4KB). Tuning information for
this variable is given in query-cache-configuration.
* `query_cache_size'
The amount of memory allocated for caching query results. The
default value is 0, which disables the query cache. Note that
this amount of memory is allocated even if `query_cache_type' is
set to 0. See query-cache-configuration, for more
information.
* `query_cache_type'
Set the query cache type. Setting the `GLOBAL' value sets the type
for all clients that connect thereafter. Individual clients can
set the `SESSION' value to affect their own use of the query
cache. Possible values are shown in the following table:
*Option* *Description*
`0' or Don't cache or retrieve results. Note that
`OFF' this does not deallocate the query cache
buffer. To do that, you should set
`query_cache_size' to 0.
`1' or Cache all query results except for those that
`ON' begin with `SELECT SQL_NO_CACHE'.
`2' or Cache results only for queries that begin with
`DEMAND' `SELECT SQL_CACHE'.
This variable defaults to `ON'.
* `query_cache_wlock_invalidate'
Normally, when one client acquires a `WRITE' lock on a `MyISAM'
table, other clients are not blocked from issuing statements that
read from the table if the query results are present in the query
cache. Setting this variable to 1 causes acquisition of a `WRITE'
lock for a table to invalidate any queries in the query cache that
refer to the table. This forces other clients that attempt to
access the table to wait while the lock is in effect.
* `query_prealloc_size'
The size of the persistent buffer used for statement parsing and
execution. This buffer is not freed between statements. If you are
running complex queries, a larger `query_prealloc_size' value
might be helpful in improving performance, because it can reduce
the need for the server to perform memory allocation during query
execution operations.
* `range_alloc_block_size'
The size of blocks that are allocated when doing range
optimization.
* `read_buffer_size'
Each thread that does a sequential scan allocates a buffer of this
size (in bytes) for each table it scans. If you do many sequential
scans, you might want to increase this value, which defaults to
131072.
* `read_only'
When the variable is set to `ON' for a replication slave server,
it causes the slave to allow no updates except from slave threads
or from users that have the `SUPER' privilege. This can be useful
to ensure that a slave server accepts updates only from its master
server and not from clients. As of MySQL 5.0.16, this variable
does not apply to `TEMPORARY' tables.
* `relay_log_purge'
Disables or enables automatic purging of relay log files as soon
as they are not needed any more. The default value is 1 (`ON').
* `read_rnd_buffer_size'
When reading rows in sorted order following a key-sorting
operation, the rows are read through this buffer to avoid disk
seeks. Setting the variable to a large value can improve `ORDER
BY' performance by a lot. However, this is a buffer allocated for
each client, so you should not set the global variable to a large
value. Instead, change the session variable only from within
those clients that need to run large queries.
* `secure_auth'
If the MySQL server has been started with the -secure-auth option,
it blocks connections from all accounts that have passwords stored
in the old (pre-4.1) format. In that case, the value of this
variable is `ON', otherwise it is `OFF'.
You should enable this option if you want to prevent all use of
passwords employing the old format (and hence insecure
communication over the network).
Server startup fails with an error if this option is enabled and
the privilege tables are in pre-4.1 format. See
old-client.
* `server_id'
The server ID. This value is set by the -server-id option. It is
used for replication to enable master and slave servers to identify
themselves uniquely.
* `shared_memory'
(Windows only.) Whether the server allows shared-memory
connections.
* `shared_memory_base_name'
(Windows only.) The name of shared memory to use for shared-memory
connections. This is useful when running multiple MySQL instances
on a single physical machine. The default name is `MYSQL'. The
name is case sensitive.
* `skip_external_locking'
This is `OFF' if `mysqld' uses external locking, `ON' if external
locking is disabled.
* `skip_networking'
This is `ON' if the server allows only local (non-TCP/IP)
connections. On Unix, local connections use a Unix socket file. On
Windows, local connections use a named pipe or shared memory. On
NetWare, only TCP/IP connections are supported, so do not set this
variable to `ON'. This variable can be set to `ON' with the
-skip-networking option.
* `skip_show_database'
This prevents people from using the `SHOW DATABASES' statement if
they do not have the `SHOW DATABASES' privilege. This can improve
security if you have concerns about users being able to see
databases belonging to other users. Its effect depends on the
`SHOW DATABASES' privilege: If the variable value is `ON', the
`SHOW DATABASES' statement is allowed only to users who have the
`SHOW DATABASES' privilege, and the statement displays all
database names. If the value is `OFF', `SHOW DATABASES' is allowed
to all users, but displays the names of only those databases for
which the user has the `SHOW DATABASES' or other privilege.
* `slave_compressed_protocol'
Whether to use compression of the slave/master protocol if both
the slave and the master support it.
* `slave_load_tmpdir'
The name of the directory where the slave creates temporary files
for replicating `LOAD DATA INFILE' statements.
* `slave_net_timeout'
The number of seconds to wait for more data from a master/slave
connection before aborting the read. This timeout applies only to
TCP/IP connections, not to connections made via Unix socket files,
named pipes, or shared memory.
* `slave_skip_errors'
The replication errors that the slave should skip (ignore).
* `slave_transaction_retries'
If a replication slave SQL thread fails to execute a transaction
because of an `InnoDB' deadlock or exceeded `InnoDB''s
`innodb_lock_wait_timeout' or NDBCluster's
`TransactionDeadlockDetectionTimeout' or
`TransactionInactiveTimeout', it automatically retries
`slave_transaction_retries' times before stopping with an error.
The default priot to MySQL 4.0.3 is 0. You must explicitly set the
value greater than 0 to enable the `retry' behavior, which is
probably a good idea. In MySQL 5.0.3 or newer, the default is 10.
* `slow_launch_time'
If creating a thread takes longer than this many seconds, the
server increments the `Slow_launch_threads' status variable.
* `socket'
On Unix platforms, this variable is the name of the socket file
that is used for local client connections. The default is
`/tmp/mysql.sock'. (For some distribution formats, the directory
might be different, such as `/var/lib/mysql' for RPMs.)
On Windows, this variable is the name of the named pipe that is
used for local client connections. The default value is `MySQL'
(not case sensitive).
* `sort_buffer_size'
Each thread that needs to do a sort allocates a buffer of this
size. Increase this value for faster `ORDER BY' or `GROUP BY'
operations. See temporary-files.
* `sql_mode'
The current server SQL mode, which can be set dynamically. See
server-sql-mode.
* `sql_slave_skip_counter'
The number of events from the master that a slave server should
skip. See set-global-sql-slave-skip-counter.
* `storage_engine'
The default storage engine (table type). To set the storage engine
at server startup, use the -default-storage-engine option. See
server-options.
* `sync_binlog'
If the value of this variable is positive, the MySQL server
synchronizes its binary log to disk (using `fdatasync()') after
every `sync_binlog' writes to the binary log. Note that there is
one write to the binary log per statement if autocommit is
enabled, and one write per transaction otherwise. The default
value is 0, which does no synchronizing to disk. A value of 1 is
the safest choice, because in the event of a crash you lose at most
one statement or transaction from the binary log. However, it is
also the slowest choice (unless the disk has a battery-backed
cache, which makes synchronization very fast).
If the value of `sync_binlog' is 0 (the default), no extra
flushing is done. The server relies on the operating system to
flush the file contents occasionaly as for any other file.
* `sync_frm'
If this variable is set to 1, when any non-temporary table is
created its `.frm' file is synchronized to disk (using
`fdatasync()'). This is slower but safer in case of a crash. The
default is 1.
* `system_time_zone'
The server system time zone. When the server begins executing, it
inherits a time zone setting from the machine defaults, possibly
modified by the environment of the account used for running the
server or the startup script. The value is used to set
`system_time_zone'. Typically the time zone is specified by the
`TZ' environment variable. It also can be specified using the
-timezone option of the `mysqld_safe' script.
The `system_time_zone' variable differs from `time_zone'. Although
they might have the same value, the latter variable is used to
initialize the tome zone for each client that connects. See
time-zone-support.
* `table_cache'
The number of open tables for all threads. Increasing this value
increases the number of file descriptors that `mysqld' requires.
You can check whether you need to increase the table cache by
checking the `Opened_tables' status variable. See
server-status-variables. If the value of `Opened_tables' is
large and you don't do `FLUSH TABLES' a lot (which just forces all
tables to be closed and reopened), then you should increase the
value of the `table_cache' variable. For more information about
the table cache, see table-cache.
* `table_type'
This variable is a synonym for `storage_engine'. In MySQL 5.0,
`storage_engine' is the preferred name.
* `thread_cache_size'
How many threads the server should cache for reuse. When a client
disconnects, the client's threads are put in the cache if there
are fewer than `thread_cache_size' threads there. Requests for
threads are satisfied by reusing threads taken from the cache if
possible, and only when the cache is empty is a new thread
created. This variable can be increased to improve performance if
you have a lot of new connections. (Normally, this doesn't provide
a notable performance improvement if you have a good thread
implementation.) By examining the difference between the
`Connections' and `Threads_created' status variables, you can see
how efficient the thread cache is. For details, see
server-status-variables.
* `thread_concurrency'
On Solaris, `mysqld' calls `thr_setconcurrency()' with this value.
This function enables applications to give the threads system a
hint about the desired number of threads that should be run at the
same time.
* `thread_stack'
The stack size for each thread. Many of the limits detected by the
`crash-me' test are dependent on this value. The default is large
enough for normal operation. See mysql-benchmarks. The
default is 192KB.
* `time_format'
This variable is not implemented.
* `time_zone'
The current time zone. This variable is used to initialize the
tome zone for each client that connects. By default, the initial
value of this is `'SYSTEM'' (which means, `use the value of
`system_time_zone''). The value can be specified explicitly at
server startup with the -default-time-zone option. See
time-zone-support.
* `tmp_table_size'
If an in-memory temporary table exceeds this size, MySQL
automatically converts it to an on-disk `MyISAM' table. Increase
the value of `tmp_table_size' if you do many advanced `GROUP BY'
queries and you have lots of memory.
* `tmpdir'
The directory used for temporary files and temporary tables. This
variable can be set to a list of several paths that are used in
round-robin fashion. Paths should be separated by colon characters
(‘`:'’) on Unix and semicolon characters (‘`;'’) on
Windows, NetWare, and OS/2.
The multiple-directory feature can be used to spread the load
between several physical disks. If the MySQL server is acting as a
replication slave, you should not set `tmpdir' to point to a
directory on a memory-based filesystem or to a directory that is
cleared when the server host restarts. A replication slave needs
some of its temporary files to survive a machine restart so that
it can replicate temporary tables or `LOAD DATA INFILE'
operations. If files in the temporary file directory are lost when
the server restarts, replication fails. However, if you are using
MySQL 4.0.0 or later, you can set the slave's temporary directory
using the `slave_load_tmpdir' variable. In that case, the slave
won't use the general `tmpdir' value and you can set `tmpdir' to a
non-permanent location.
* `transaction_alloc_block_size'
The amount in bytes by which to increase a per-transaction memory
pool which needs memory. See the description of
`transaction_prealloc_size'.
* `transaction_prealloc_size'
There is a per-transaction memory pool from which various
transaction-related allocations take memory. The initial size of
the pool in bytes is `transaction_prealloc_size'. For every
allocation that cannot be satisfied from the pool because it has
insufficient memory available, the pool is increased by
`transaction_alloc_block_size' bytes. When the transaction ends,
the pool is truncated to `transaction_prealloc_size' bytes.
By making `transaction_prealloc_size' sufficiently large to
contain all statements within a single transaction, you can avoid
many `malloc()' calls.
* `tx_isolation'
The default transaction isolation level. Defaults to
`REPEATABLE-READ'.
This variable is set by the `SET TRANSACTION ISOLATION LEVEL'
statement. See set-transaction. If you set `tx_isolation'
directly to an isolation level name that contains a space, the
name should be enclosed within quotes, with the space replaced by
a dash. For example:
SET tx_isolation = 'READ-COMMITTED';
* `updatable_views_with_limit'
This variable controls whether updates can be made using a view
that does not contain a primary key in the underlying table, if
the update contains a `LIMIT' clause. (Such updates often are
generated by GUI tools.) An update is an `UPDATE' or `DELETE'
statement. Primary key here means a `PRIMARY KEY', or a `UNIQUE'
index in which no column can contain `NULL'.
The variable can have two values:
* `1' or `YES': Issue a warning only (not an error message).
This is the default value.
* `0' or `NO': Prohibit the update.
This variable was added in MySQL 5.0.2.
* `version'
The version number for the server.
* `version_bdb'
The `BDB' storage engine version.
* `version_comment'
The `configure' script has a -with-comment option that allows a
comment to be specified when building MySQL. This variable
contains the value of that comment.
* `version_compile_machine'
The type of machine or architecture on which MySQL was built.
* `version_compile_os'
The type of operating system on which MySQL was built.
* `wait_timeout'
The number of seconds the server waits for activity on a
non-interactive connection before closing it. This timeout applies
only to TCP/IP connections, not to connections made via Unix
socket files, named pipes, or shared memory.
On thread startup, the session `wait_timeout' value is initialized
from the global `wait_timeout' value or from the global
`interactive_timeout' value, depending on the type of client (as
defined by the `CLIENT_INTERACTIVE' connect option to
`mysql_real_connect()'). See also `interactive_timeout'.
Info Catalog
(mysql.info) server-options
(mysql.info) mysqld
(mysql.info) using-system-variables
automatically generated byinfo2html