GNU Info
(mysql.info)SHOW VARIABLES
`SHOW VARIABLES'
................
SHOW VARIABLES [LIKE wild]
`SHOW VARIABLES' shows the values of some MySQL system variables. You
can also get this information using the `mysqladmin variables' command.
If the default values are unsuitable, you can set most of these
variables using command-line options when `mysqld' starts up. Note:
Command-line options.
The output resembles that shown below, though the format and numbers may
differ somewhat:
+-------------------------+---------------------------+
| Variable_name | Value |
+-------------------------+---------------------------+
| ansi_mode | OFF |
| back_log | 50 |
| basedir | /my/monty/ |
| bdb_cache_size | 16777216 |
| bdb_log_buffer_size | 32768 |
| bdb_home | /my/monty/data/ |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /my/monty/data/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | YES |
| have_innodb | YES |
| have_raid | YES |
| have_ssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16776192 |
| language | /my/monty/share/english/ |
| large_files_support | ON |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_recover_options | DEFAULT |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /my/monty/data/donna.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097116 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 4 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.23.29a-gamma-debug |
| wait_timeout | 28800 |
+-------------------------+---------------------------+
Each option is described below. Values for buffer sizes, lengths, and
stack sizes are given in bytes. You can specify values with a suffix
of `K' or `M' to indicate kilobytes or megabytes. For example, `16M'
indicates 16 megabytes. The case of suffix letters does not matter;
`16M' and `16m' are equivalent:
``ansi_mode'.'
Is `ON' if `mysqld' was started with `--ansi'. Note: ANSI mode.
``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(2)' system call should have more details. Check your OS
documentation for the maximum value for this variable. Attempting
to set `back_log' higher than your operating system limit will be
ineffective.
``basedir''
The value of the `--basedir' option.
``bdb_cache_size''
The buffer that is allocated to cache index and rows for `BDB'
tables. If you don't use `BDB' tables, you should start `mysqld'
with `--skip-bdb' to not waste memory for this cache.
``bdb_log_buffer_size''
The buffer that is allocated to cache index 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 waste memory for this
cache.
``bdb_home''
The value of the `--bdb-home' option.
``bdb_max_lock''
The maximum number of locks (1000 by default) you can have active
on a BDB table. You should increase this if you get errors of type
`bdb: Lock table is out of available locks' or `Got error 12 from
...' when you have do long transactions or when `mysqld' has to
examine a lot of rows to calculate the query.
``bdb_logdir''
The value of the `--bdb-logdir' option.
``bdb_shared_data''
Is `ON' if you are using `--bdb-shared-data'.
``bdb_tmpdir''
The value of the `--bdb-tmpdir' option.
``binlog_cache_size'. The size of the cache to hold the SQL'
statements for the binary log during a transaction. If you often
use big, multi-statement transactions you can increase this to get
more performance. Note: COMMIT.
``character_set''
The default character set.
``character_sets''
The supported character sets.
``concurrent_inserts''
If `ON' (the default), MySQL will allow you to use `INSERT' on
`MyISAM' tables at the same time as you run `SELECT' queries on
them. You can turn this option off by starting `mysqld' with
`--safe' or `--skip-new'.
``connect_timeout''
The number of seconds the `mysqld' server is waiting for a connect
packet before responding with `Bad handshake'.
``datadir''
The value of the `--datadir' option.
``delay_key_write''
If enabled (is on by default), MySQL will honor the
`delay_key_write' option `CREATE TABLE'. This means that the key
buffer for tables with this option will not get flushed on every
index update, but only when a table is closed. This will speed up
writes on keys a lot, but you should add automatic checking of all
tables with `myisamchk --fast --force' if you use this. Note that
if you start `mysqld' with the `--delay-key-write-for-all-tables'
option this means that all tables will be treated as if they were
created with the `delay_key_write' option. You can clear this flag
by starting `mysqld' with `--skip-new' or `--safe-mode'.
``delayed_insert_limit''
After inserting `delayed_insert_limit' rows, the `INSERT DELAYED'
handler will check if there are any `SELECT' statements pending.
If so, it allows these to execute before continuing.
``delayed_insert_timeout''
How long a `INSERT DELAYED' thread should wait for `INSERT'
statements before terminating.
``delayed_queue_size''
What size queue (in rows) should be allocated for handling `INSERT
DELAYED'. If the queue becomes full, any client that does `INSERT
DELAYED' will wait until there is room in the queue again.
``flush''
This is `ON' if you have started MySQL with the `--flush' option.
``flush_time''
If this is set to a non-zero value, then every `flush_time'
seconds all tables will be closed (to free up resources and sync
things to disk). We only recommend this option on Win95, Win98, or
on systems where you have very little resources.
``have_bdb''
`YES' if `mysqld' supports Berkeley DB tables. `DISABLED' if
`--skip-bdb' is used.
``have_innodb''
`YES' if `mysqld' supports InnoDB tables. `DISABLED' if
`--skip-innodb' is used.
``have_raid''
`YES' if `mysqld' supports the `RAID' option.
``have_ssl''
`YES' if `mysqld' supports SSL (encryption) on the client/server
protocol.
``init_file''
The name of the file specified with the `--init-file' option when
you start the server. This is a file of SQL statements you want
the server to execute when it starts.
``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 full joins (joins that do
not use indexes). The buffer is allocated one time for each full
join between two tables. Increase this value to get a faster full
join when adding indexes is not possible. (Normally the best way
to get fast joins is to add indexes.)
``key_buffer_size''
Index blocks are buffered and are shared by all threads.
`key_buffer_size' is the size of the buffer used for index blocks.
Increase this to get better index handling (for all reads and
multiple writes) to as much as you can afford; 64M on a 256M
machine that mainly runs MySQL is quite common. If you, however,
make this too big (more than 50% of your total memory?) your
system may start to page and become REALLY slow. Remember that
because MySQL does not cache data read, that you will have to
leave some room for the OS filesystem cache.
You can check the performance of the key buffer by doing `show
status' and examine the variables `Key_read_requests',
`Key_reads', `Key_write_requests', and `Key_writes'. The
`Key_reads/Key_read_request' ratio should normally be < 0.01. The
`Key_write/Key_write_requests' is usually near 1 if you are using
mostly updates/deletes but may be much smaller if you tend to do
updates that affect many at the same time or if you are using
`delay_key_write'. Note: SHOW.
To get even more speed when writing many rows at the same time, use
`LOCK TABLES'. Note: `LOCK TABLES'.
``language''
The language used for error messages.
``large_file_support''
If `mysqld' was compiled with options for big file support.
``locked_in_memory''
If `mysqld' was locked in memory with `--memlock'
``log''
If logging of all queries is enabled.
``log_update''
If the update log is enabled.
``log_bin''
If the binary log is enabled.
``log_slave_updates''
If the updates from the slave should be logged.
``long_query_time''
If a query takes longer than this (in seconds), the `Slow_queries'
counter will be incremented. If you are using
`--log-slow-queries', the query will be logged to the slow query
logfile. Note: Slow query log.
``lower_case_table_names''
If set to 1 table names are stored in lowercase on disk and table
names will be case-insensitive. Note: Name case sensitivity.
``max_allowed_packet''
The maximum size of one packet. The 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 big (possibly wrong) packets. You must increase
this value if you are using big `BLOB' columns. It should be as
big as the biggest `BLOB' you want to use. The current protocol
limits `max_allowed_packet' to 16M.
``max_binlog_cache_size''
If a multi-statement transaction requires more than this amount of
memory, one will get the error "Multi-statement transaction
required more than 'max_binlog_cache_size' bytes of storage".
``max_binlog_size''
Available after 3.23.33. If a write to the binary (replication)
log exceeds the given value, rotate the logs. You cannot set it to
less than 1024 bytes, or more than 1 GB. Default is 1 GB.
``max_connections''
The number of simultaneous clients allowed. Increasing this value
increases the number of file descriptors that `mysqld' requires.
See below for comments on file descriptor limits. Note: Too many
connections.
``max_connect_errors''
If there is more than this number of interrupted connections from
a host this host will be blocked from further connections. You
can unblock a host with the command `FLUSH HOSTS'.
``max_delayed_threads''
Don't 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 will be
inserted as if the `DELAYED' attribute wasn't specified.
``max_heap_table_size''
Don't allow creation of heap tables bigger than this.
``max_join_size''
Joins that are probably going to read more than `max_join_size'
records return an error. Set this value if your users tend to
perform joins that lack a `WHERE' clause, that take a long time,
and that return millions of rows.
``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_user_connections''
The maximum number of active connections for a single user (0 = no
limit).
``max_tmp_tables''
(This option doesn't yet do anything.) Maximum number of
temporary tables a client can keep open at the same time.
``max_write_lock_count''
After this many write locks, allow some read locks to run in
between.
``myisam_recover_options''
The value of the `--myisam-recover' option.
``myisam_sort_buffer_size''
The buffer that is allocated when sorting the index when doing a
`REPAIR' or when creating indexes with `CREATE INDEX' or `ALTER
TABLE'.
``myisam_max_extra_sort_file_size'.'
If the creating of the temporary file for fast index creation
would be this much bigger than using the key cache, then 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. *NOTE* that this parameter is given in megabytes!
``myisam_max_sort_file_size''
The maximum size of the temporary file MySQL is allowed to use
while recreating the index (during `REPAIR', `ALTER TABLE' or
`LOAD DATA INFILE'. If the file size would be bigger than this,
the index will be created through the key cache (which is slower).
*NOTE* that this parameter is given in megabytes!
``net_buffer_length''
The communication buffer is reset to this size between queries.
This should not normally be changed, but if you have very little
memory, you can set it to the expected size of a query. (That is,
the expected length of SQL statements sent by clients. If
statements exceed this length, the buffer is automatically
enlarged, up to `max_allowed_packet' bytes.)
``net_read_timeout''
Number of seconds to wait for more data from a connection before
aborting the read. Note that when we don't expect data from a
connection, the timeout is defined by `write_timeout'. See also
`slave_read_timeout'.
``net_retry_count''
If a read on a communication port is interrupted, retry this many
times before giving up. This value should be quite high on
`FreeBSD' as internal interrupts are sent to all threads.
``net_write_timeout''
Number of seconds to wait for a block to be written to a
connection before aborting the write.
``open_files_limit''
If this is not 0, then `mysqld' will use this value to reserve file
descriptors to use with `setrlimit()'. If this value is 0 then
`mysqld' will reserve `max_connections*5' or `max_connections +
table_cache*2' (whichever is larger) number of files. You should
try increasing this if `mysqld' gives you the error 'Too many open
files'.
``pid_file''
The value of the `--pid-file' option.
``port''
The value of the `--port' option.
``protocol_version''
The protocol version used by the MySQL server.
``record_buffer''
Each thread that does a sequential scan allocates a buffer of this
size for each table it scans. If you do many sequential scans, you
may want to increase this value.
``record_rnd_buffer''
When reading rows in sorted order after a sort, the rows are read
through this buffer to avoid a disk seeks. If not set, then it's
set to the value of `record_buffer'.
``query_buffer_size''
The initial allocation of the query buffer. If most of your
queries are long (like when inserting blobs), you should increase
this!
``safe_show_databases''
Don't show databases for which the user doesn't have any database
or table privileges. This can improve security if you're concerned
about people being able to see what databases other users have.
See also `skip_show_databases'.
``server_id''
The value of the `--server-id' option.
``skip_locking''
Is OFF if `mysqld' uses external locking.
``skip_networking''
Is ON if we only allow local (socket) connections.
``skip_show_databases''
This prevents people from doing `SHOW DATABASES' if they don't have
the `PROCESS_PRIV' privilege. This can improve security if you're
concerned about people being able to see what databases other users
have. See also `safe_show_databases'.
``slave_read_timeout''
Number of seconds to wait for more data from a master/slave
connection before aborting the read.
``slow_launch_time''
If creating the thread takes longer than this value (in seconds),
the `Slow_launch_threads' counter will be incremented.
``socket''
The Unix socket used by the server.
``sort_buffer''
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. Note: Temporary files.
``table_cache''
The number of open tables for all threads. Increasing this value
increases the number of file descriptors that `mysqld' requires.
MySQL needs two file descriptors for each unique open table. See
below for comments on file descriptor limits. You can check if you
need to increase the table cache by checking the `Opened_tables'
variable. Note: SHOW. If this variable is big and you don't do
`FLUSH TABLES' a lot (which just forces all tables to be closed and
reopenend), then you should increase the value of this variable.
Make sure that your operating system can handle the number of open
file descriptors implied by the `table_cache' setting. If
`table_cache' is set too high, MySQL may run out of file
descriptors and refuse connections, fail to perform queries, and
be very unreliable.
For information about how the table cache works, see Note: Table
cache.
``table_type''
The default table type
``thread_cache_size''
How many threads we should keep in a cache for reuse. When a
client disconnects, the client's threads are put in the cache if
there aren't more than `thread_cache_size' threads from before.
All new threads are first taken from the cache, 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 give a notable performance
improvement if you have a good thread implementation.) By examing
the difference between the `Connections' and `Threads_created' you
can see how efficient the current thread cache is for you.
``thread_concurrency''
On Solaris, `mysqld' will call `thr_setconcurrency()' with this
value. `thr_setconcurrency()' permits the application to give the
threads system a hint for 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. Note: MySQL Benchmarks.
``timezone''
The timezone for the server.
``tmp_table_size''
If an in-memory temporary table exceeds this size, MySQL will
automatically convert 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.
``version''
The version number for the server.
``wait_timeout''
The number of seconds the server waits for activity on a
connection before closing it. See also `interactive_timeout'.
The manual section that describes tuning MySQL contains some
information of how to tune the above variables. Note: Server
parameters.
automatically generated by info2www version 1.2.2.9