4.5.7.4 SHOW VARIABLES

SHOW [GLOBAL | SESSION] VARIABLES [LIKE wild]

SHOW VARIABLES shows the values of some MySQL system variables. The options GLOBAL and SESSION are new in MySQL 4.0.3. With GLOBAL you will get the variables that will be used for new connections to MySQL. With SESSION you will get the values that are in effect for the current connection. If you are not using either option, SESSION is used.

If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts up. See section 4.1.1 mysqld Command-line Options. It is also possible to change most variables with the SET statement. See section 5.5.6 SET Syntax.

The output from SHOW VARIABLES resembles that shown in the following list, though the format and numbers may differ somewhat. You can also get this information using the mysqladmin variables command.

+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------|
| back_log                        | 50                           |
| basedir                         | /usr/local/mysql             |
| bdb_cache_size                  | 8388572                      |
| bdb_log_buffer_size             | 32768                        |
| bdb_home                        | /usr/local/mysql             |
| bdb_max_lock                    | 10000                        |
| bdb_logdir                      |                              |
| bdb_shared_data                 | OFF                          |
| bdb_tmpdir                      | /tmp/                        |
| bdb_version                     | Sleepycat Software: ...      |
| binlog_cache_size               | 32768                        |
| bulk_insert_buffer_size         | 8388608                      |
| character_set                   | latin1                       |
| character_sets                  | latin1 big5 czech euc_kr     |
| concurrent_insert               | ON                           |
| connect_timeout                 | 5                            |
| convert_character_set           |                              |
| datadir                         | /usr/local/mysql/data/       |
| delay_key_write                 | ON                           |
| delayed_insert_limit            | 100                          |
| delayed_insert_timeout          | 300                          |
| delayed_queue_size              | 1000                         |
| flush                           | OFF                          |
| flush_time                      | 0                            |
| ft_boolean_syntax               | + -><()~*:""&|               |
| ft_min_word_len                 | 4                            |
| ft_max_word_len                 | 254                          |
| ft_max_word_len_for_sort        | 20                           |
| ft_stopword_file                | (built-in)                   |
| have_bdb                        | YES                          |
| have_innodb                     | YES                          |
| have_isam                       | YES                          |
| have_raid                       | NO                           |
| have_symlink                    | DISABLED                     |
| have_openssl                    | YES                          |
| have_query_cache                | YES                          |
| init_file                       |                              |
| innodb_additional_mem_pool_size | 1048576                      |
| innodb_buffer_pool_size         | 8388608                      |
| innodb_data_file_path           | ibdata1:10M:autoextend       |
| innodb_data_home_dir            |                              |
| innodb_file_io_threads          | 4                            |
| innodb_force_recovery           | 0                            |
| innodb_thread_concurrency       | 8                            |
| innodb_flush_log_at_trx_commit  | 1                            |
| innodb_fast_shutdown            | ON                           |
| innodb_flush_method             |                              |
| innodb_lock_wait_timeout        | 50                           |
| innodb_log_arch_dir             |                              |
| innodb_log_archive              | OFF                          |
| innodb_log_buffer_size          | 1048576                      |
| innodb_log_file_size            | 5242880                      |
| innodb_log_files_in_group       | 2                            |
| innodb_log_group_home_dir       | ./                           |
| innodb_mirrored_log_groups      | 1                            |
| interactive_timeout             | 28800                        |
| join_buffer_size                | 131072                       |
| key_buffer_size                 | 16773120                     |
| language                        | /usr/local/mysql/share/...   |
| large_files_support             | ON                           |
| local_infile                    | ON                           |
| locked_in_memory                | OFF                          |
| log                             | OFF                          |
| log_update                      | OFF                          |
| log_bin                         | OFF                          |
| log_slave_updates               | OFF                          |
| log_slow_queries                | OFF                          |
| log_warnings                    | OFF                          |
| long_query_time                 | 10                           |
| low_priority_updates            | OFF                          |
| lower_case_table_names          | OFF                          |
| max_allowed_packet              | 1047552                      |
| max_binlog_cache_size           | 4294967295                   |
| max_binlog_size                 | 1073741824                   |
| max_connections                 | 100                          |
| max_connect_errors              | 10                           |
| max_delayed_threads             | 20                           |
| max_heap_table_size             | 16777216                     |
| max_join_size                   | 4294967295                   |
| max_relay_log_size              | 0                            |
| max_sort_length                 | 1024                         |
| max_user_connections            | 0                            |
| max_tmp_tables                  | 32                           |
| max_write_lock_count            | 4294967295                   |
| myisam_max_extra_sort_file_size | 268435456                    |
| myisam_repair_threads           | 1                            |
| myisam_max_sort_file_size       | 2147483647                   |
| myisam_recover_options          | force                        |
| 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                        | /usr/local/mysql/name.pid    |
| port                            | 3306                         |
| protocol_version                | 10                           |
| read_buffer_size                | 131072                       |
| read_rnd_buffer_size            | 262144                       |
| rpl_recovery_rank               | 0                            |
| query_cache_limit               | 1048576                      |
| query_cache_size                | 0                            |
| query_cache_type                | ON                           |
| safe_show_database              | OFF                          |
| server_id                       | 0                            |
| slave_net_timeout               | 3600                         |
| skip_external_locking           | ON                           |
| skip_networking                 | OFF                          |
| skip_show_database              | OFF                          |
| slow_launch_time                | 2                            |
| socket                          | /tmp/mysql.sock              |
| sort_buffer_size                | 2097116                      |
| sql_mode                        |                              |
| table_cache                     | 64                           |
| table_type                      | MYISAM                       |
| thread_cache_size               | 3                            |
| thread_stack                    | 131072                       |
| tx_isolation                    | READ-COMMITTED               |
| timezone                        | EEST                         |
| tmp_table_size                  | 33554432                     |
| tmpdir                          | /tmp/:/mnt/hd2/tmp/          |
| version                         | 4.0.4-beta                   |
| wait_timeout                    | 28800                        |
+---------------------------------+------------------------------+

Each option is described here. 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. See section 1.8.2 Running MySQL in 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 (10,000 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. See section 6.7.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax.
• bulk_insert_buffer_size (was myisam_bulk_insert_tree_size) MyISAM uses special tree-like cache to make bulk inserts (that is, INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE) faster. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 will disable this optimisation. Note: this cache is only used when adding data to non-empty table. Default value is 8 MB.
• 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 Option for MyISAM tables. Can have one of the following values:

  OFF	All CREATE TABLE ... DELAYED_KEY_WRITES are ignored.
  ON	(default) MySQL will honor the DELAY_KEY_WRITE option for CREATE TABLE.
  ALL	All new opened tables are treated as if they were created with the DELAY_KEY_WRITE option.

  If DELAY_KEY_WRITE is enabled 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.
• 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 Windows 9x/Me, or on systems where you have very little resources.
• ft_boolean_syntax List of operators supported by MATCH ... AGAINST(... IN BOOLEAN MODE). See section 6.8 MySQL Full-text Search.
• ft_min_word_len The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.)
• ft_max_word_len The maximum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.)
• ft_max_word_len_for_sort The maximum length of the word in a FULLTEXT index to be used in fast index recreation method in REPAIR, CREATE INDEX, or ALTER TABLE. Longer words are inserted the slow way. The rule of the thumb is as follows: with ft_max_word_len_for_sort increasing, MySQL will create bigger temporary files (thus slowing the process down, due to disk I/O), and will put fewer keys in one sort block (again, decreasing the efficiency). When ft_max_word_len_for_sort is too small, instead, MySQL will insert a lot of words into index the slow way, but short words will be inserted very quickly.
• ft_stopword_file The file from which to read the list of stopwords for full-text searches. All the words from the file will be used; comments are not honored. By default, built-in list of stopwords is used (as defined in `myisam/ft_static.c'). Setting this parameter to an empty string ("") will disable stopword filtering. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.10)
• have_innodb YES if mysqld supports InnoDB tables. DISABLED if --skip-innodb is used.
• have_bdb YES if mysqld supports Berkeley DB tables. DISABLED if --skip-bdb is used.
• have_raid YES if mysqld supports the RAID option.
• have_openssl 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 (for instance more than 50% of your total memory) your system may start to page and become extremely slow. Remember that because MySQL does not cache data reads, 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. See section 4.5.7 SHOW Syntax. To get even more speed when writing many rows at the same time, use LOCK TABLES. See section 6.7.3 LOCK TABLES and UNLOCK TABLES Syntax.
• 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. This value is measured in real time, not CPU time, so a query that may be under the threshold on a lightly loaded system may be above the threshold on a heavily loaded one. See section 4.9.5 The Slow Query Log.
• lower_case_table_names If set to 1 table names are stored in lowercase on disk and table name comparisons will be case-insensitive. From version 4.0.2, this option also applies to database names. From 4.1.1 this option also applies to table alias. See section 6.1.3 Case Sensitivity in Names.
• max_allowed_packet The maximum size of one packet. The message buffer is initialised 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 protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0.
• 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 4096 bytes (1024 in MySQL versions older than 4.0.14), or more than 1 GB. Default is 1 GB. Note if you are using transactions: a transaction is written in one chunk to the binary log, hence it is never split between several binary logs. Therefore, if you have big transactions, you may see binlogs bigger than max_binlog_size. If max_relay_log_size (available starting from MySQL 4.0.14) is 0, then max_binlog_size will apply to relay logs as well.
• 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. See section A.2.6 Too many connections Error.
• 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. If you set this to 0, MySQL will never create a max_delayed thread.
• 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_relay_log_size Available starting from 4.0.14. If a write to the relay log (a kind of log used by replication slaves, see section 4.10.3 Replication Implementation Details) exceeds the given value, rotate the relay log. This variable enables you to put different size constraints on relay logs and binary logs. However, setting the variable to 0 will make MySQL use max_binlog_size for both binary logs and relay logs. You have to set max_relay_log_size to 0 or more than 4096, and less than 1 GB. Default is 0.
• max_seeks_for_key Limit assumed max number of seeks when looking up rows based on a key. The MySQL optimiser will assume that when searching after matching rows in a table through scanning a key, we will not cause more than this number of key seeks independent of the cardinality of the key. By setting this to a low value (100 ?) you can force MySQL to prefer keys 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_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 temporary file used for fast index creation would be bigger than using the key cache by the amount specified here, 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 before 4.0.3 and in bytes beginning with this version.
• myisam_repair_threads. If this value is greater than one, MyISAM table indexes during Repair by sorting process will be created in parallel - each index in its own thread. Note: multi-threaded repair is still alpha quality code.
• 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 before 4.0.3 and in bytes beginning with this version.
• 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_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 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.
• read_buffer_size (was 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_size When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks. Can improve ORDER BY by a lot if set to a high value. As this is a thread-specific variable, one should not set this big globally, but just change this when running some specific big queries.
• query_cache_limit Don't cache results that are bigger than this. (Default 1M).
• query_cache_size The memory allocated to store results from old queries. If this is 0, the query cache is disabled (default).
• query_cache_type This may be set (only numeric) to

  Value	Alias	Comment
  0	OFF	Don't cache or retrieve results.
  1	ON	Cache all results except SELECT SQL_NO_CACHE ... queries.
  2	DEMAND	Cache only SELECT SQL_CACHE ... queries.

• safe_show_database 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_database.
• 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_database This prevents people from doing SHOW DATABASES if they don't have the PROCESS privilege. This can improve security if you're concerned about people being able to see what databases other users have. See also safe_show_database.
• slave_net_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_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 section A.4.4 Where MySQL Stores Temporary Files.
• 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 if you need to increase the table cache by checking the Opened_tables variable. See section 4.5.7.3 SHOW STATUS. 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. For more information about the table cache, see section 5.4.7 How MySQL Opens and Closes Tables.
• 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 status variables (see section 4.5.7.3 SHOW STATUS for details) you can see how efficient thread cache is.
• 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. See section 5.1.4 The MySQL Benchmark Suite.
• 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. Starting from MySQL 4.1, it can be set to a list of paths separated by colon : (semicolon ; on Windows). They will be used in round-robin fashion. This feature can be used to spread load between several physical disks.
• version The version number for the server.
• wait_timeout The number of seconds the server waits for activity on a not interactive connection before closing it. On thread startup SESSION.WAIT_TIMEOUT is initialised from GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option). See also interactive_timeout.

The manual section that describes tuning MySQL contains some information of how to tune the above variables. See section 5.5.2 Tuning Server Parameters.

User Comments

Add your own comment.