Description and optimization methods of mysql startup parameters

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.

Important MySQL startup options

Modify back_log if a large number of new connections are required.

Thread_cache_size if a large number of new connections are required, modify it.

Key_buffer_size can be set to a large index page pool.

Bdb_cache_size the records and keys used by the BDB table are cached at high speed.

If table_cache has many tables and concurrent connections, modify it.

Delay_key_write If You Need To Cache all key code writes, set it.

Log_slow_queries.

Max_heap_table_size is used for GROUP

Sort_buffer is used for order by and GROUP

Myisam_sort_buffer_size is used for REPAIR TABLE

Join_buffer_size is used for join without keys.

MySQL high-speed cache (All threads are shared and allocated at one time)

Key code cache: key_buffer_size. The default value is 8 Mb.

Table cache: table_cache. The default value is 64.

Thread cache: thread_cache_size. The default value is 0.

Host name cache: it can be modified during compilation. The default value is 128.

Memory ing table: currently only used to compress tables.

Note: The operating system can handle the problem because MySQL does not have a high-speed cache.

MySQL cache zone variables (non-shared, on-demand allocation)

Sort_buffer: order by/GROUP

Record_buffer: scans tables.

Join_buffer_size: No Key Connection

Myisam_sort_buffer_size: REPAIR TABLE

Net_buffer_length: reads SQL statements and caches results.

Tmp_table_size: size of the temporary HEAP table.