A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
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;
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.
The path of the MySQL main program, that is, the value of the -- basedir parameter.
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.
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.
See the -- bdb-home option.
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.
Specifies the location where logs are stored when a BDB data table is used to provide services. That is, the value of -- bdb-logdir.
If the -- bdb-shared-data option is used, the value of this parameter is On.
Temporary file directory of the BDB type data table. That is, the value of -- bdb-tmpdir.
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.
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.
The default character set of MySQL.
The character set that MySQL can provide.
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.
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.
Specifies the database path. That is, the value of the -- datadir option.
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
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.
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.
The time for an insert delayed thread to wait for the INSERT statement before termination.
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.
Load the -- flush parameter when MySQL is started to enable this function.
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!
The search engine maintainer wants to change the operators allowed for logical full-text search. These are controlled by the ft_boolean_syntax variable.
Specify the minimum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!
Specifies the maximum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!
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.
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.
YES: MySQL supports InnoDB data tables; DISABLE: use -- skip-innodb to DISABLE InnoDB data tables.
YES: MySQL supports Berkeley data tables; DISABLE: use -- skip-bdb to DISABLE support for Berkeley data tables.
YES: enable MySQL to support RAID.
YES: enables MySQL to support the SSL encryption protocol.
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.
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.
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 .)
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.
The language in which the user outputs the error message.
Enable support for large files.
Use -- memlock to lock mysqld in the memory.
Record all query operations.
Enable update log.
Enable binary log.
This parameter must be enabled if chainchain synchronization or synchronization between multiple Slave instances is used.
If the time used by a query exceeds the value of this parameter, the query operation is recorded in Slow_queries.
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.
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.
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 .)
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.
Specifies the maximum size of binary log files. the default value is 1 GB.
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.
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 ;.
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.
The maximum capacity that can be used by the memory table.
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.
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 ).
Specifies the maximum number of connections from the same user. If it is set to 0, no restriction is imposed.
(This parameter does not work yet ). The maximum number of temporary tables that a customer can open at the same time.
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.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service