About MYSQL SHOW variables

Original address: http://www.2cto.com/database/201108/100546.html

The MYSQLD server maintains two variables. Global variables affect the global operation of the server. Session variables affect specific client connection-related operations.

When the server starts, all global variables are initialized to the default values. You can change these defaults by specifying options in the options file or on the command line. After the server is started, dynamic global variables can be changed by connecting to the server and executing the SET global var_name statement. To change global variables, you must have super permissions.

The server also maintains session variables for each client connection. Initializes a client session variable with the current value of the corresponding global variable when connected. The client can change the dynamic session variable by using the SET session Var_name statement. You do not need special permissions to set session variables, but customers can change only their session variables without changing the session variables of other clients.

Any client accessing a global variable can see changes to the global variable. However, it only affects clients that are connected after a change to initialize the corresponding session variable from that global variable. It does not affect the session variables of the clients that are already connected (even the client that executes the SET global statement).

When you use the startup option to set a variable, the variable value can use the suffix K, m, or G to represent kilobytes, megabytes, or gigabytes, respectively. For example, the following command starts the server when the key-value buffer size is megabytes:

Mysqld--key_buffer_size=16m

The relationship between the uppercase and lowercase of the suffix, 16M and 16m are the same.

At run time, use the SET statement to set system variables. At this point, the suffix cannot be used, but the value can take the following expression:

mysql> SET sort_buffer_size = 10 * 1024 * 1024;

To explicitly specify whether to set global or session variables, use the global or the session option:

mysql> SET GLOBAL sort_buffer_size = 10 * 1024 * 1024;

mysql> SET SESSION sort_buffer_size = 10 * 1024 * 1024;

If none of the two options are true, the statement sets a session variable.

You can view system variables and their values through the show variables statement.

Mysql> SHOW VARIABLES;

1. Back_log

Specifies the number of possible connections for MySQL. When the MySQL main thread gets very many connection requests in a short period of time, the parameter works, and the main thread takes some time (albeit very short) to check the connection and start a new thread.

The value of the Back_log parameter indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size. Attempting to set a limit of back_log above your operating system will be invalid.

When observing the MySQL process list, find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | The Back_log value is increased when the NULL pending process is to be connected. The default value for Back_log is 50.

2. Basedir

The path to the MySQL main program, that is: the value of the--basedir parameter.

3. Bdb_cache_size

The buffer size assigned to the cache index and row arrangement of the BDB type data table, and if the DBD type data table is not used, the--SKIP-BDB parameter should be loaded when MySQL is started to avoid memory wastage.

4.bdb_log_buffer_size

The buffer size assigned to the cache index and row arrangement of the BDB type data table, if the DBD type data table is not used, the parameter value should be set to 0, or the--SKIP-BDB parameter should be loaded when MySQL is started to avoid memory wastage.

5.bdb_home

See--bdb-home options.

6. Bdb_max_lock

Specifies the maximum number of lock table processes (default is 10000), which can be used if the BDB type data table is used. If you find Bdb:lock table is out of available locks or Got error when performing a large-scale transaction or query ... Error, you should increase the parameter value.

7. Bdb_logdir

Specifies the location where the log is stored when the service is serviced using the BDB type data table. That is the value of--bdb-logdir.

8. Bdb_shared_data

If you use the--bdb-shared-data option, the parameter value is on.

9. Bdb_tmpdir

The temp file directory for the BDB type data table. That is the value of--bdb-tmpdir.

Ten. Binlog_cache_size

Specifies the cache size used by SQL query statements during query request processing for binary log. If you frequently apply to large, complex SQL expression processing, you should increase the parameter value to gain performance gains.

Bulk_insert_buffer_size.

Specifies that the MyISAM Type data table table uses a special tree-structured cache. Using the whole block method (bulk) can speed up the insert operation (INSERT ... SELECT, INSERT ... The speed and efficiency of the VALUES (...), (...), ..., and LOAD DATA INFILE. This parameter restricts the tree-structured cache size used by each thread, and if set to 0 disables the accelerated caching feature. Note: The cache operation for this parameter can only be performed by the user into a non-empty data table! The default value is 8MB.

Character_set.

The default character set for MySQL.

Character_sets.

The character set that MySQL can provide support for.

Concurrent_inserts.

If this parameter is turned on, MySQL allows the INSERT operation while performing the SELECT operation. If you want to turn off this parameter, you can load the--safe option when you start mysqld, or use the--skip-new option. The default is on.

Connect_timeout.

Specifies the maximum number of seconds that the MySQL service waits to answer a connection message, beyond which MySQL returns bad handshake to the client.

DataDir.

Specifies the database path. That is the value of the--datadir option.

Delay_key_write.

This parameter is valid only for MyISAM type data tables. The following types of values are:

OFF: If the CREATE table is used in the build tables statement ... Delayed_key_writes, then ignore All

Delayed_key_writes;

On: If the CREATE table is used in the building tables statement ... Delayed_key_writes, the option is used (default);

All: All open data tables will be processed according to Delayed_key_writes.

If Delayed_key_writes is turned on, the data table that is already open does not flush with the update for each index

The Delayed_key_writes option is a KEY buffer for the data table, unless the data table is closed. This parameter greatly increases the speed at which the key value is written.

Degree. If you use this parameter, you should check all data tables: Myisamchk--fast--force.

18.delayed_insert_limit

After inserting the Delayed_insert_limit line, the Insert delayed processing module checks to see if there are any non-executed SELECT statements. If there are, execute the Allow these statements before proceeding with the processing.

Delayed_insert_timeout.

The time at which an insert delayed thread should wait for an INSERT statement before terminating.

Delayed_queue_size.

The queue size (in behavior units) allocated for processing insert delayed. If the queue is full, any customer who makes an insert delayed must wait for the queue space to be freed before continuing.

. Flush

Load the--flush parameter when you start MySQL to turn on this feature.

Flush_time.

If this is set to a value other than 0, then every flush_time second, all open tables will be closed to release resources and sync to disk. Note: This parameter is only recommended if you are using windows9x/me or if your current operating system resources are critically low!

Ft_boolean_syntax.

Search engine maintainers want to change the operators that are allowed for logical full-text searches. These are controlled by the variable ft_boolean_syntax.

Ft_min_word_len.

Specifies the minimum length of the keyword being indexed. Note: After changing the parameter value, the index must be rebuilt!

Ft_max_word_len.

Specifies the maximum length of the keyword being indexed. Note: After changing the parameter value, the index must be rebuilt!

Ft_max_word_len_for_sort.

Specifies the maximum length of keywords that can be used during fast full-text index reconstruction using repair, CREATE INDEX, or ALTER table. Keywords that exceed this length limit are inserted in a low-speed manner. By increasing the value of this parameter, MySQL will build a larger temporary file (which will reduce the CPU load, but the efficiency will depend on disk I/O efficiency) and store less key values within a sort fetch.

Ft_stopword_file.

Reads the list from the file specified by the Ft_stopword_file variable. After you modify the Stopword list, you must rebuild the fulltext index.

Have_innodb.

Yes:mysql supports INNODB type data sheet; DISABLE: Use--skip-innodb to turn off support for InnoDB type data tables.

Have_bdb.

Yes:mysql supports the Berkeley type data sheet; DISABLE: Use--SKIP-BDB to turn off support for Berkeley type data tables.

Have_raid.

YES: Enables MySQL to support RAID functionality.

Have_openssl.

YES: Enables MySQL to support the SSL encryption protocol.

Init_file.

Specifies a file that contains a SQL query statement that will be loaded when MySQL starts and the SQL statements in the file will be executed.

Interactive_timeout.

The number of seconds the server waits for an action on an interactive connection before shutting it down. An interactive customer is defined as a client that uses the client_interactive option for MySQL_real_connect (). Also visible wait_timeout.

Join_buffer_size.

The size of the buffer used for all unions (join) (not an indexed junction). The buffer allocates a buffer to each of the 2 tables, and when the index is not possible, increase the value to get a faster full join. (usually the best way to get a quick coupling is to increase the index.) )

Key_buffer_size.

The buffer size used for the index block increases it to get better processing of the index (for all read and multiple writes), as much as you can afford. If you make it too big, the system will start to slow down. You must leave some space for the OS file system cache. To get more speed when writing multiple rows.

Language.

The language in which the user outputs the error message.

Panax Large_file_support.

Open large file support.

Locked_in_memory.

Use--memlock to lock the mysqld in memory.

. log

Records all query operations.

Log_update.

Turn on update log.

Log_bin.

Turn on binary log.

Log_slave_updates.

You need to turn on this parameter if you are using chain synchronization or synchronizing between multiple slave.

Long_query_time.

If a query takes longer than the parameter value, the query operation is recorded in Slow_queries.

Lower_case_table_names.

1:mysql always use lowercase letters for SQL operations;

0: Turn off the feature.

Note: If this parameter is used, all data tables should be converted to lowercase letters before they are enabled.

Max_allowed_packet.

The maximum size of a query statement package. The message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes when needed. This value is too small to produce an error when processing large packets. If you use a large BLOB column, you must increase the value.

Net_buffer_length.

The communication buffer is reset to that size during the query. You do not usually change the value of the parameter, but if you have insufficient memory, you can set it to the size that the query expects. (That is, the length that the client expects from the SQL statement.) If the statement exceeds this length, the buffer is automatically enlarged until Max_allowed_packet bytes. )

Max_binlog_cache_size.

Specifies the maximum capacity of the binary log cache, and if the settings are too small, MySQL will error when executing complex query statements.

Max_binlog_size.

Specifies the maximum capacity of a binary log file, which defaults to 1GB.

Max_connections.

The number of customers that are allowed to connect to the MySQL server simultaneously. If this value is exceeded, MySQL will return too many connections error, but normally MySQL is able to resolve it itself.

Max_connect_errors.

For the same host, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. If you need to unblock the host, execute: FLUSH host;.

Wuyi Max_delayed_threads.

Do not start a thread with more than this number to handle the insert delayed statement. If you try to insert data into a new table after all insert delayed threads are used, the row is inserted, as if the delayed property was not specified.

Max_heap_table_size.

The maximum capacity that the memory table can use.

Max_join_size.

If you are querying a union that has more than Max_join_size records, an error is returned. If you want to execute a statement without a where and spend a lot of time, and return the join of millions of rows, you need to increase the parameter value.

Max_sort_length.

The number of bytes used when sorting the Blob or text value (only the first max_sort_length bytes per value are used; the rest is ignored).

Max_user_connections.

Specifies the maximum number of connections from the same user. A setting of 0 means no limit.

Max_tmp_tables.

(This parameter does not currently work). The maximum number of temporary tables that a customer can keep open at the same time.

Max_write_lock_count.

When the number of Max_write_lock_count write locks is present, some read operations that are locked are allowed to begin execution. Avoid too many write locks, and read operations are in a long wait state.

Myisam_recover_options.

About MYSQL SHOW variables