MYSQL Environment Variables

You can use this command to get the default buffer size of the mysqld Server:

Shell> mysqld -- help

This command generates a table with all mysqld options and configurable variables. The output includes the default value and looks like this:

Possible variables for option -- set-variable (-O) are:
Back_log ??????? Current value: 5
Connect_timeout ??? Current value: 5
Delayed_insert_timeout? Current value: 300
Delayed_insert_limit? Current value: 100
Delayed_queue_size ?? Current value: 1000
Flush_time ?????? Current value: 0
Interactive_timeout? Current value: 28800
Join_buffer_size ??? Current value: 131072
Key_buffer_size ??? Current value: 1048540
Lower_case_table_names? Current value: 0
Long_query_time ??? Current value: 10
Max_allowed_packet ?? Current value: 1048576
Max_connections ??? Current value: 100
Max_connect_errors ?? Current value: 10
Max_delayed_threads? Current value: 20
Max_heap_table_size? Current value: 16777216
Max_join_size ???? Current value: 4294967295
Max_sort_length ??? Current value: 1024
Max_tmp_tables ???? Current value: 32
Max_write_lock_count? Current value: 4294967295
Net_buffer_length ?? Current value: 16384
Query_buffer_size ?? Current value: 0
Record_buffer ???? Current value: 131072
Sort_buffer ????? Current value: 2097116
Table_cache ????? Current value: 64
Thread_concurrency ?? Current value: 10
Tmp_table_size ???? Current value: 1048576
Thread_stack ????? Current value: 131072
Wait_timeout ????? Current value: 28800

If a mysqld server is running, you can see the value of the variable actually used by executing this command:

Shell> mysqladmin variables

Each option is described below. The buffer size, length, and stack size values are given in bytes. You can use the suffix "K" or "M" to indicate that the values are displayed in K bytes or MB. For example, 16 MB indicates 16 Mb. It does not matter if the suffix is uppercase or lowercase. 16M and 16m are the same.

You can also use the show status command to view statistics from a running server. See the 7.21 SHOW syntax (get the table and column information ).

Back_log
The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. The Unix listen (2) System Call manual page should have more details. Check your OS document to find the maximum value of this variable. Trying to set back_log to be higher than your operating system limit will be invalid.
Connect_timeout
The mysqld server is waiting for the number of seconds for a connection packet before responding with Bad handshake (Bad handshake.
Delayed_insert_timeout
The time for an Insert DELAYED thread to wait for the Insert statement before termination.
Delayed_insert_limit
After the delayed_insert_limit row is inserted, the Insert DELAYED processor checks whether any Select statement is not executed. In this case, execute the allow statements before continuing.
Delayed_queue_size
An extra large Queue (by number of rows) should be allocated for processing Insert DELAYED ). If the queue is full, any Insert DELAYED client will wait until the queue has space.
Flush_time
If this value is set to a non-zero value, all tables are closed every flush_time second (to release resources and sync to disk ).
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.
Join_buffer_size
The buffer size used for 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 .)
Key_buffer_size
The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. Remember that since MySQL does not cache read data, you must leave some space for the OS file system cache. To get more speed when writing multiple rows, use lock tables. See the 7.24 lock tables/unlock tables syntax.
Long_query_time
If a query takes more time than it (in seconds), The Slow_queries counter will be added.
Max_allowed_packet
The maximum size of a package. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. By default, if this value is too small, a large (possibly incorrect) package will be captured. If you are using a large BLOB column, you must add this value. It should be as big as the largest BLOB you want to use.
Max_connections
Number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. See the comments for file descriptor restrictions. See 18.2.4 Too connector connections error.
Max_connect_errors
If there are more than this number of connections interrupted from a host, this host blocks further connections. You can use the flush hosts command to clear a host.
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.
Max_join_size
An error may be returned if more than max_join_size records are to be read. If your user wants to execute a join that does not have a Where clause and takes a long time and returns millions of rows, set it.
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 ).
Max_tmp_tables
(This option does not do anything currently ). The maximum number of temporary tables that a customer can open at the same time.
Net_buffer_length
The communication buffer is reset to this size between queries. Normally this should not be changed, but if you have a small amount of memory, you can set it to the desired size for 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 .)
Record_buffer
Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value.
Sort_buffer
Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. See where MySQL 18.5 stores temporary files.
Table_cache
Number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique opened table. For more information, see the comments on file descriptor restrictions. For information on how the table cache works, see 10.2.4 how MySQL enables and disables the table.
Tmp_table_size
If a temporary table exceeds this size, MySQL generates an error in The table tbl_name is full format. If you do many advanced group by queries, increase The tmp_table_size value.
Thread_stack
The stack size of each thread. Many of the limits detected by the crash-me test depend on this value. By default, the team's general operations are large enough. See section 10.8 use your own benchmark.
Wait_timeout
The number of seconds that the server waits for action on a connection before closing it. You can also see interactive_timeout.
MySQL is a very scalable algorithm. Therefore, you can use a small amount of memory to run or store MySQL more for better performance.

If you have a lot of memory and many tables and have a medium number of customers and want the maximum performance, you should have something like this:

Shell> safe_mysqld-O key_buffer = 16 M-O table_cache = 128 \
????? -O sort_buffer = 4 M-O record_buffer = 1 M &

If you have less memory and a large number of connections, use the following:
Shell> safe_mysqld-O key_buffer = 512 k-O sort_buffer = 100 k \
????? -O record_buffer = 100 k &

Or even:
Shell> safe_mysqld-O key_buffer = 512 k-O sort_buffer = 16 k \
????? -O table_cache = 32-O record_buffer = 8 k-O net_buffer = 1 K &

If there are many connections, the "SWAp Problem" may occur unless mysqld has been configured to use a small amount of memory for each connection. Of course, if you have enough memory for all connections, mysqld performs better.

Note: If you change one of the mysqld options, it actually only keeps the example of the server.

To understand the effect of a parameter change, do the following:

Shell> mysqld-O key_buffer = 32 m -- help

Ensure that the -- help option is the last one; otherwise, the effects of any options listed after it on the command line will not be reflected in the output.