MYSQLSHOWVARIABLES introduction _ MySQL

Introduction to MYSQLSHOWVARIABLES bitsCN.com

The mysqld server maintains two variables. Global variables affect global operations on the server. Session variables affect operations related to client connection.

When the server is started, all global variables are initialized to the default value. You can change these default values in the options file or the options specified in the command line. After the server is started, you can change the dynamic GLOBAL variables by connecting to the server and executing the set global var_name statement. To change global variables, you must have the SUPER permission.

The server also maintains session variables for each client connection. The client session variable is initialized using the current value of the corresponding global variable during connection. You can use the set session var_name statement to change the dynamic SESSION variable. You do not need special permissions to set session variables, but you can only change your session variables without changing the session variables of other customers.

Any client that accesses global variables can see changes to global variables. However, it only affects the client connected to which the corresponding session variable is initialized from the global variable after the change. It does not affect the session variables of connected clients (or even the clients that execute the set global statement ).

When you use the start option to set variables, you can use the suffix K, M, or G to indicate kilobytes, megabytes, or gigabytes. For example, the following command sets the buffer size of the key value to 16 megabytes when starting the server:

Mysqld -- key_buffer_size = 16 M

The suffix is case-sensitive. 16 m and 16 m are the same.

When running, use the SET statement to SET system variables. In this case, you cannot use a suffix, but you can use the following expressions for the value:

Mysql> SET sort_buffer_size = 10*1024*1024;

To explicitly specify whether to set GLOBAL or SESSION variables, use GLOBAL or SESSION options:

Mysql> set global sort_buffer_size = 10*1024*1024;

Mysql> set session sort_buffer_size = 10*1024*1024;

If none of the two options exist, the statement sets the session variable.

You can use the show variables statement to view system VARIABLES and their values.

Mysql> show variables;

1. back_log

Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests in a short time, this parameter takes effect. then the main thread takes some time (although very short) to check the connection and start a new thread.

The value of the back_log parameter indicates how many requests can be stored in the stack within a short 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 listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.

When you observe the MySQL process list, you will find a large number of 264084 | unauthenticated users | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value of back_log is 50.

2. basedir

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

3. bdb_cache_size

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

4. bdb_log_buffer_size

The cache index and row arrangement buffer size allocated to the BDB type data table. if the DBD type data table is not used, set this parameter to 0, or load the -- skip-bdb parameter when starting MySQL to avoid memory waste.

5. bdb_home

See the -- bdb-home option.

6. bdb_max_lock

Specifies the maximum number of lock table processes (10000 by default). This parameter can be used if a data table of the BDB type is used. If the bdb: Lock table is out of available locks or Got error 12 from... error is found during execution of large transaction processing or query, the value of this parameter should be increased.

7. bdb_logdir

Specifies the location where logs are stored when a BDB data table is used to provide services. That is, the value of -- bdb-logdir.

8. bdb_shared_data

If the -- bdb-shared-data option is used, the value of this parameter is On.

9. bdb_tmpdir

Temporary file directory of the BDB type data table. That is, the value of -- bdb-tmpdir.

10. binlog_cache_size

Specify the cache size used by SQL query statements during query request processing for binary logs. If it is frequently used for processing a large number of complex SQL expressions, you should increase the value of this parameter to improve performance.

11. bulk_insert_buffer_size

Specify that the MyISAM data table uses a special tree structure for caching. The whole bulk can accelerate the INSERT operation (INSERT... SELECT, INSERT... VALUES (...), (...),..., and load data infile. This parameter limits the cache size of the tree structure used by each thread. if it is set to 0, the cache acceleration function is disabled. Note: the cache operation corresponding to this parameter can only be performed by users into non-empty data tables! The default value is 8 MB.

12. character_set

The default character set of MySQL.

13. character_sets

The character set that MySQL can provide.

14. concurrent_inserts

If this parameter is enabled, MySQL allows the INSERT operation while executing the SELECT operation. If you want to disable this parameter, you can load the -- safe option when starting mysqld, or use the -- skip-new option. The default value is On.

15. connect_timeout

Specify the maximum number of seconds for the MySQL service to wait for a response to a connection message. after this time, MySQL returns bad handshake to the client.

16. datadir

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

17. delay_key_write

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

Off: if the create table... DELAYED_KEY_WRITES statement is used in the TABLE creation statement, all

DELAYED_KEY_WRITES;

On: If you use create table... DELAYED_KEY_WRITES in the TABLE creation statement, use this option (default );

All: all open data tables are processed according to DELAYED_KEY_WRITES.

If DELAYED_KEY_WRITES is enabled

The key buffer of the data table of the DELAYED_KEY_WRITES option, unless the data table is closed. This parameter greatly increases the speed of writing key values.

Degree. If this parameter is used, check all data tables: myisamchk -- fast -- force.

18. delayed_insert_limit

After the delayed_insert_limit row is inserted, the insert delayed processing module checks whether there are unexecuted SELECT statements. If yes, execute these statements before proceeding.

19. delayed_insert_timeout

The time for an insert delayed thread to wait for the INSERT statement before termination.

20. delayed_queue_size

The size of the queue allocated for processing insert delayed (in the unit of action ). If the queue is full, any insert delayed user must wait for the queue space to be released before continuing.

21. flush

Load the -- flush parameter when MySQL is started to enable this function.

22. flush_time

If this parameter is set to a non-zero value, all opened tables are shut down every flush_time second to release resources and sync to the disk. Note: this parameter is recommended only when Windows9x/Me is used or the current operating system resources are seriously insufficient!

23. ft_boolean_syntax

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

24. ft_min_word_len

Specify the minimum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!

25. ft_max_word_len

Specifies the maximum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!

26. ft_max_word_len_for_sort

Specify the maximum length of keywords that can be used in the process of rapid full-text INDEX reconstruction using REPAIR, create index, or alter table. Keywords that exceed the length limit will be inserted at low speed. Increase the value of this parameter, MySQL will create a larger temporary file (this will reduce the CPU load, but the efficiency will depend on the disk I/O efficiency ), and put less key values in the memory in a sort.

27. ft_stopword_file

Read the list from the file specified by the ft_stopword_file variable. After the stopword list is modified, the FULLTEXT index must be rebuilt.

28. have_innodb

YES: MySQL supports InnoDB data tables; DISABLE: use -- skip-innodb to DISABLE InnoDB data tables.

29. have_bdb

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

30. have_raid

YES: enable MySQL to support RAID.

31. have_openssl

YES: enables MySQL to support the SSL encryption protocol.

32. init_file

Specify a file containing an SQL query statement. The file will be loaded when MySQL is started, and the SQL statement in the file will also be executed.

33. interactive_timeout

The number of seconds that the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect. Wait_timeout is also visible.

34. join_buffer_size

The buffer size used for all join operations (not the join operation using indexes ). The buffer zone allocates a buffer for each join between two tables. when an index is increased, increasing this value will allow you to get a faster join. (The best way to get a quick join is to increase the index .)

35. key_buffer_size

The buffer size used for index blocks, increasing the size of indexes (for all reads and writes) that can be better processed, so that you can afford that much. If you make it too large, the system will start to grow slowly. You must leave some space for the OS file system cache. To get more speed when writing multiple rows.

36. language

The language in which the user outputs the error message.

37. large_file_support

Enable support for large files.

38. locked_in_memory

Use -- memlock to lock mysqld in the memory.

39. log

Record all query operations.

40. log_update

Enable update log.

41. log_bin

Enable binary log.

42. log_slave_updates

This parameter must be enabled if chainchain synchronization or synchronization between multiple Slave instances is used.

43. long_query_time

If the time used by a query exceeds the value of this parameter, the query operation is recorded in Slow_queries.

44. lower_case_table_names

1: MySQL uses lowercase letters for SQL operations;

0: disable this function.

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

45. max_allowed_packet

The maximum size of a query statement package. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. If the value is too small, an error will occur when processing the large package. This value must be added if a large BLOB column is used.

46. net_buffer_length

The communication buffer is reset to this size during query. Do not change the value of this parameter, but if the memory is insufficient, you can set it to the expected size of the query. (That is, the expected length of the SQL statement sent by the customer. If the statement exceeds this length, the buffer is automatically expanded until max_allowed_packet bytes .)

47. max_binlog_cache_size

Specify the maximum size of binary log cache. if the value is too small, MySQL will encounter an error when executing a complex query statement.

48. max_binlog_size

Specifies the maximum size of binary log files. the default value is 1 GB.

49. max_connections

Number of Customers allowed to connect to the MySQL server at the same time. If the value is exceeded, MySQL returns the Too connector connections error. However, MySQL can solve the problem by itself.

50. max_connect_errors

If an error connection is interrupted for the same host that exceeds the value of this parameter, the connection to the host is disabled. To unban the HOST, run: flush host ;.

51. max_delayed_threads

Do not start threads with more than this number to process the insert delayed statement. If you try to INSERT data to a new table after all the insert delayed threads are used, the row will be inserted, as if the DELAYED attribute was not specified.

52. max_heap_table_size

The maximum capacity that can be used by the memory table.

53. max_join_size

If you want to query the union of more than max_join_size records, an error is returned. If you want to execute a statement without a WHERE statement, which takes a lot of time and returns the join of millions of rows, you need to increase the value of this parameter.

54. max_sort_length

The number of bytes used for sorting BLOB or TEXT values (each value is used only in the header of max_sort_length; others are ignored ).

55. max_user_connections

Specifies the maximum number of connections from the same user. If it is set to 0, no restriction is imposed.

56. max_tmp_tables

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

57. max_write_lock_count

When max_write_lock_count write locks occur, some locked read operations are allowed to begin. Avoid too many write locks and read operations in a long wait state.

58. myisam_recover_options

BitsCN.com