Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand the SHOW VARIABLES sentence in MYSQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

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, initialize all global variables to their default values. You can change these default values in the options file or the options specified on the command line. After the server starts, you can change the dynamic global variables by connecting to the server and executing the SET GLOBAL var_name statement. To change a global variable, you must have SUPER permission.

The server also maintains session variables for each client connection. The client session variable is initialized with the current value of the corresponding global variable when connecting. Customers can change dynamic session variables through SET SESSION var_name statements. No special permissions are required to set session variables, but customers can only change their own session variables without changing the session variables of other customers.

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

When setting a variable using the startup option, 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 with a key buffer size of 16 megabytes:

Mysqld-key_buffer_size=16M

The case of the suffix; 16m and 16m are the same.

At run time, use the set statement to set the system variable. At this point, you cannot use a suffix, 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 SESSION option:

Mysql > SET GLOBAL sort_buffer_size = 10 * 1024 * 1024

Mysql > SET SESSION sort_buffer_size = 10 * 1024 * 1024

If neither option is available, the statement sets the 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 to the MySQL. This parameter takes effect when the MySQL main thread gets a lot of connection requests in a very short period of time, and then the main thread takes some (albeit short) time to check the connection and start a new thread.

The value of the back_log parameter indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops responding to new requests. 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 their own limits on the queue size. Trying to set the limit that back_log is higher than your operating system will not be valid.

When you look at the list of MySQL processes and find a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL processes to be connected, increase the value of back_log. The back_ log defaults to 50.

2. Basedir

The path where the MySQL main program is located, that is, the value of the-- basedir parameter.

3. Bdb_cache_size

The cache index and row arrangement buffer size assigned to BDB type data tables, if you do not use DBD type data tables, the-skip-bdb parameter should be loaded when MySQL is started to avoid memory waste.

4.bdb_log_buffer_size

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

5.bdb_home

See-- bdb-home option.

6. Bdb_max_lock

Specifies the maximum number of lock table processes (default is 10000), which can be used if you are using BDB type data tables. If you find bdb: Lock table is out of available locks or Got error 12 from when performing large transaction processing or query. If there is an error, the parameter value should be increased.

7. Bdb_logdir

Specifies the location where logs are stored when services are provided using BDB-type data tables. This 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

Temporary file directory for data tables of type BDB. This is the value of-- bdb-tmpdir.

10. Binlog_cache_size

Specifies for binary log the cache size used by SQL query statements during query request processing. If it is frequently used in a large number of complex SQL expression processing, you should increase the value of this parameter to achieve a performance improvement.

11. Bulk_insert_buffer_size

Specifies that the MyISAM type data table table uses a special tree-structured cache. Using the whole block mode (bulk) can speed up the insertion operation (INSERT. SELECT, INSERT... The speed and efficiency of VALUES (...), (...), and LOAD DATA INFILE). This parameter limits the size of the tree cache used by each thread, and if set to 0, the accelerated cache feature is disabled. Note: the cache operation corresponding to this parameter can only be inserted into the non-empty data table by the user! The default is 8MB.

12. Character_set

The default character set for MySQL.

13. Character_sets

The character set supported by MySQL.

14. Concurrent_inserts

If this parameter is turned on, MySQL allows INSERT operations to occur at the same time as SELECT operations are performed. If you want to turn this parameter off, you can load the-- safe option when you start mysqld, or use the-- skip-new option. The default is On.

15. Connect_timeout

Specifies the maximum number of seconds that the MySQL service waits for a connection message to be answered, after which MySQL returns bad handshake to the client.

16. Datadir

Specify the path. This is the value of the-- datadir option.

17. Delay_key_write

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

Off: if you use CREATE TABLE in a table statement... DELAYED_KEY_WRITES, ignore all

DELAYED_KEY_WRITES

On: if you use CREATE TABLE in a table statement... DELAYED_KEY_WRITES, use this option (default)

All: all open tables will be processed according to DELAYED_KEY_WRITES.

If DELAYED_KEY_WRITES is on, data tables that are already open will not be refreshed with the

The key buffer of the datasheet for the DELAYED_KEY_WRITES option unless the datasheet is closed. This parameter greatly increases the speed of writing key values.

Degrees. If you use this parameter, you should check all the data tables: myisamchk-- fast-- force.

18.delayed_insert_limit

After inserting the delayed_insert_limit row, the INSERT DELAYED processing module checks to see if there are any outstanding SELECT statements. If so, execute allow these statements before continuing with processing.

19. Delayed_insert_timeout

The amount of time an INSERT DELAYED thread should wait for an INSERT statement before terminating.

20. Delayed_queue_size

The queue size, in behavioral units, allocated for processing INSERT DELAYED. If the queue is full, any customer doing INSERT DELAYED must wait for the queue space to be released before continuing.

21. Flush

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

twenty-two。 Flush_time

If this setting is non-0, then every flush_time second, all open tables will be closed to free resources and sync to disk. Note: it is recommended that you use this parameter only if you are using Windows9x/Me or if the current operating system is seriously out of resources!

23. Ft_boolean_syntax

The search engine maintainer wants to change the operators allowed for logical full-text search. These are controlled by the variable ft_boolean_syntax.

24. Ft_min_word_len

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

25. Ft_max_word_len

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

twenty-six。 Ft_max_word_len_for_sort

Specify the maximum length of keywords that can be used in the process of fast full-text index reconstruction using REPAIR, CREATE INDEX, or ALTER TABLE, etc. Keywords that exceed the length limit will be inserted at a low speed. By increasing the value of this parameter, MySQL will create larger temporary files (this will reduce the CPU load, but the efficiency will depend on the disk I and O efficiency) and store fewer keys in a sort fetch.

twenty-seven。 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.

twenty-eight。 Have_innodb

YES: MySQL supports InnoDB type tables; DISABLE: use-- skip-innodb to turn off support for InnoDB type tables.

twenty-nine。 Have_bdb

YES: MySQL supports Berkeley-type tables; DISABLE: use-- skip-bdb to turn off support for Berkeley-type tables.

thirty。 Have_raid

YES: enable MySQL to support RAID functionality.

thirty-one。 Have_openssl

YES: enable MySQL to support the SSL encryption protocol.

thirty-two。 Init_file

Specify a file that contains SQL query statements that will be loaded when MySQL starts, and the SQL statements in the file will also be executed.

thirty-three。 Interactive_timeout

The number of seconds the server waits for action on an interactive connection before shutting it down. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for _ real_connect (). Wait_timeout can also be seen.

thirty-four。 Join_buffer_size

Buffer size for all joins (join) (not indexed joins). The buffer allocates a buffer for each join between the two tables, and when it is not possible to increase the index, increasing this value results in a faster full join. (usually the best way to get a quick join is to add an index.)

thirty-five。 Key_buffer_size

The buffer size used for index blocks, increase it to get better-handled indexes (for all reads and multiple overrides), so much that you can afford it. 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.

thirty-six。 Language

The language in which the user outputs error information.

thirty-seven。 Large_file_support

Turn on large file support.

thirty-eight。 Locked_in_memory

Use-- memlock to lock the mysqld in memory.

thirty-nine。 Log

Record all query operations.

forty。 Log_update

Open update log.

forty-one。 Log_bin

Open binary log.

forty-two。 Log_slave_updates

You need to enable this parameter if you use chain synchronization or synchronization between multiple Slave.

forty-three。 Long_query_time

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

forty-four。 Lower_case_table_names

1: MySQL always uses lowercase letters for SQL operation

0: turn off the feature.

Note: if you use this parameter, you should convert all data tables to lowercase letters before enabling them.

forty-five。 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 if needed. If the value is too small, an error will occur when processing large packets. If you use a large BLOB column, you must increase this value.

forty-six。 Net_buffer_length

The communication buffer is reset to that size during the query. Usually do not change the parameter value, but if there is not enough memory, you can set it to the desired size of the query. (that is, the expected length of the SQL statement issued by the customer. If the statement exceeds this length, the buffer is automatically expanded to max_allowed_packet bytes.)

forty-seven。 Max_binlog_cache_size

Specifies the maximum capacity of the binary log cache. If the setting is too small, MySQL will make an error when executing complex query statements.

forty-eight。 Max_binlog_size

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

forty-nine。 Max_connections

The number of customers allowed to connect to the MySQL server at the same time. If this value is exceeded, MySQL returns a Too many connections error, but usually MySQL can resolve it on its own.

fifty。 Max_connect_errors

For the same host, if there is an incorrect connection that exceeds the number of values for this parameter, the host will be disabled from connecting. To unblock the host, execute: FLUSH HOST;.

fifty-one。 Max_delayed_threads

Do not start a thread with more than this number to process the INSERT DELAYED statement. 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 was not specified.

fifty-two。 Max_heap_table_size

The maximum capacity that the memory table can use.

fifty-three。 Max_join_size

If you want to query a union of more than max_join_size records, an error will be returned. If you want to execute a statement without a WHERE and take a lot of time and return joins of millions of rows, you need to increase the value of this parameter.

fifty-four。 Max_sort_length

The number of bytes used when sorting BLOB or text values (only the first max_sort_length bytes of each value are used; the rest are ignored).

fifty-five。 Max_user_connections

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

fifty-six。 Max_tmp_tables

(this parameter does not work yet). A customer can keep the maximum number of temporary tables open at the same time.

fifty-seven。 Max_write_lock_count

When the number of max_write_lock_count write locks occurs, some locked read operations are allowed to begin. Avoid too many write locks and read operations are waiting for a long time.

On how to understand the SHOW VARIABLES sentence in MYSQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.