Description of some mysql startup parameters and optimization methods
- 2020-05-10 23:02:17
- OfStack
The back_log value indicates how many requests can be stored on the stack for a short period of time before MySQL temporarily stops answering new requests. Only if you expect a lot of connections in a short time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of the queue. The man page for the Unix listen(2) system call should have more details. Check your OS documentation to find the maximum value of this variable. Trying to set back_log higher than your operating system limit will not work.
connect_timeout
The number of seconds an mysqld server is waiting for a connection message before replying with Bad handshake (bad handshake).
delayed_insert_timeout
The amount of time an INSERT DELAYED thread should wait for an INSERT statement before terminating.
delayed_insert_limit
After inserting the delayed_insert_limit line, the INSERT DELAYED processor checks to see if any SELECT statements have not been executed. If so, execute the allow statements before proceeding.
delayed_queue_size
How many queues (in rows) should be allocated for processing INSERT DELAYED. If the queue is full, any customers doing INSERT DELAYED will wait until the queue is empty again.
flush_time
If this is set to a non-zero value, then every flush_time second all tables will be closed (to free up resources and sync to disk).
interactive_timeout
The number of seconds a server waits for action on an interactive connection before shutting it down. An interactive customer is defined as one that USES the CLIENT_INTERACTIVE option for mysql_real_connect(). You can also see wait_timeout.
join_buffer_size
Buffer size for all joins (join) (not joins with indexes). The buffer allocates a buffer once for each total join between the two tables. When increasing the index is not possible, increasing the value will result in a faster total join. (usually the best way to get fast joins is to add indexes.)
key_buffer_size
The index block is buffered and Shared by all threads. key_buffer_size is the buffer size for the index block, increase it to get better indexes (for all reads and overwrites) to the extent you can afford. If you make it too big, the system will start changing pages and really slow down. Remember that since MySQL does not cache the read data, you will have to leave some space for the OS filesystem cache. To get more speed when writing multiple lines, use LOCK TABLES. See 7.24LOCK TABLES/UNLOCK TABLES syntax.
long_query_time
If one query takes longer than it (in seconds), Slow_queries counter will be increased.
max_allowed_packet
Maximum size of 1 bag. The message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes as needed. By default, this value is too small to catch large (possibly incorrect) packages. If you are using a large BLOB column, you must increase this value. It should be as large as the maximum BLOB you want to use.
max_connections
The number of customers allowed at the same time. Increasing this value increases the number of file descriptors required by mysqld. See the comments below to limit file descriptors. See 18.2.4 Too many connections error.
max_connect_errors
If more than that number of connections are interrupted from one host, the host blocks the next step of the connection. You can unblock a host with the FLUSH HOSTS command.
max_delayed_threads
Do not start threads 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 have been used, it will be inserted as if the DELAYED property had not been specified.
max_join_size
A join that may read more than max_join_size records will return an error. If your user wants to perform a join without an WHERE clause, take a long time, and return millions of lines, set it.
max_sort_length
The number of bytes used when sorting BLOB or TEXT values (each value is used with only the first max_sort_length bytes; The rest is ignored.
max_tmp_tables
(the option doesn't do anything yet). The maximum number of open temporary tables can be maintained by one customer at a time.
net_buffer_length
The communication buffer is reset to this size between queries. Normally this should not be changed, but if you have very little memory, you can set it to the size the query expects. (that is, the length expected by the SQL statement issued by the customer. If the statement exceeds this length, the buffer is automatically expanded to max_allowed_packet bytes.
record_buffer
Each thread that makes 1 sequential scan allocates 1 buffer of this size for each table it scans. If you do a lot of sequential scans, you might want to increase the value.
sort_buffer
Each thread that needs to be sorted allocates 1 buffer of this size. Increasing this value speeds up ORDER BY or GROUP BY operations. See 18.5 where MySQL stores temporary files.
table_cache
The number of tables open for all threads. Increasing this value increases the number of file descriptors required by mysqld. MySQL requires two file descriptors for each table with only one open. See the comments below for the file descriptor limit. For information on how table caching works, see 10.2.4 MySQL how to open and close tables.
tmp_table_size
If a temporary table is larger than this size, MySQL generates an The table tbl_name is full error. If you do many advanced GROUP BY queries, increase the tmp_table_size value.
thread_stack
Stack size per thread. Many of the limitations detected by the crash-me test depend on this value. The default team 1-like operation is large enough. See 10.8 use your own benchmarks.
wait_timeout
The number of seconds a server waits for action on a connection before shutting it down. You can also see interactive_timeout.
MySQL USES very scalable algorithms, so you can usually run with less memory or give MySQL more storage for better performance.
If you have a lot of memory and a lot of tables and a medium number of clients, and want maximum performance, you should have something like this:
shell > safe_mysqld -O key_buffer=16M -O table_cache=128 \
-O sort_buffer=4M O record_buffer=1M &
If you have less memory and a lot of connections, use something like this:
shell > safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
- O record_buffer = 100 k &
Or even:
shell > safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache= 32-O record_buffer=8k O net_buffer=1K &
If there are many connections, "swap problems" can occur unless mysqld has been configured to use very little memory per connection. Of course, mysqld performs better if you have enough memory for all connections.
Note that if you change one of the options in mysqld, it actually only holds for the server example.
To understand the effect of a parameter change, do this:
shell > mysqld -O key_buffer=32m --help
Make sure --help option is the last one; Otherwise, the effect of any option listed after it on the command line will not be reflected in the output.
Important MySQL startup option
Modify back_log if you need a large number of new connections.
Modify thread_cache_size if you need a large number of new connections.
key_buffer_size index page pool, which can be set to large.
The bdb_cache_size BDB table USES records and keys that are not cached.
Modify table_cache if there are many tables and concurrent connections.
Set delay_key_write if you need to cache all keycode writes.
log_slow_queries finds queries that take a lot of time.
max_heap_table_size is used for GROUP BY
sort_buffer is used for ORDER BY and GROUP BY
myisam_sort_buffer_size is used for REPAIR TABLE
join_buffer_size is used for keyless joins.
MySQL cache (all threads Shared, one-time allocation)
Keycode cache: key_buffer_size, default 8M.
Table cache: table_cache, default 64.
Thread cache: thread_cache_size, default 0.
Hostname cache: can be modified at compile time, default 128.
Memory-mapped tables: currently only used to compress tables.
Note: MySQL does not have a row cache and lets the operating system process it
MySQL cache variable (non-shared, allocated on demand)
sort_buffer: ORDER BY/GROUP BY
record_buffer: scan the table.
join_buffer_size: keyless join
myisam_sort_buffer_size: REPAIR TABLE
net_buffer_length: cache the results for read SQL statements.
tmp_table_size: HEAP table size for temporary results.
connect_timeout
The number of seconds an mysqld server is waiting for a connection message before replying with Bad handshake (bad handshake).
delayed_insert_timeout
The amount of time an INSERT DELAYED thread should wait for an INSERT statement before terminating.
delayed_insert_limit
After inserting the delayed_insert_limit line, the INSERT DELAYED processor checks to see if any SELECT statements have not been executed. If so, execute the allow statements before proceeding.
delayed_queue_size
How many queues (in rows) should be allocated for processing INSERT DELAYED. If the queue is full, any customers doing INSERT DELAYED will wait until the queue is empty again.
flush_time
If this is set to a non-zero value, then every flush_time second all tables will be closed (to free up resources and sync to disk).
interactive_timeout
The number of seconds a server waits for action on an interactive connection before shutting it down. An interactive customer is defined as one that USES the CLIENT_INTERACTIVE option for mysql_real_connect(). You can also see wait_timeout.
join_buffer_size
Buffer size for all joins (join) (not joins with indexes). The buffer allocates a buffer once for each total join between the two tables. When increasing the index is not possible, increasing the value will result in a faster total join. (usually the best way to get fast joins is to add indexes.)
key_buffer_size
The index block is buffered and Shared by all threads. key_buffer_size is the buffer size for the index block, increase it to get better indexes (for all reads and overwrites) to the extent you can afford. If you make it too big, the system will start changing pages and really slow down. Remember that since MySQL does not cache the read data, you will have to leave some space for the OS filesystem cache. To get more speed when writing multiple lines, use LOCK TABLES. See 7.24LOCK TABLES/UNLOCK TABLES syntax.
long_query_time
If one query takes longer than it (in seconds), Slow_queries counter will be increased.
max_allowed_packet
Maximum size of 1 bag. The message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes as needed. By default, this value is too small to catch large (possibly incorrect) packages. If you are using a large BLOB column, you must increase this value. It should be as large as the maximum BLOB you want to use.
max_connections
The number of customers allowed at the same time. Increasing this value increases the number of file descriptors required by mysqld. See the comments below to limit file descriptors. See 18.2.4 Too many connections error.
max_connect_errors
If more than that number of connections are interrupted from one host, the host blocks the next step of the connection. You can unblock a host with the FLUSH HOSTS command.
max_delayed_threads
Do not start threads 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 have been used, it will be inserted as if the DELAYED property had not been specified.
max_join_size
A join that may read more than max_join_size records will return an error. If your user wants to perform a join without an WHERE clause, take a long time, and return millions of lines, set it.
max_sort_length
The number of bytes used when sorting BLOB or TEXT values (each value is used with only the first max_sort_length bytes; The rest is ignored.
max_tmp_tables
(the option doesn't do anything yet). The maximum number of open temporary tables can be maintained by one customer at a time.
net_buffer_length
The communication buffer is reset to this size between queries. Normally this should not be changed, but if you have very little memory, you can set it to the size the query expects. (that is, the length expected by the SQL statement issued by the customer. If the statement exceeds this length, the buffer is automatically expanded to max_allowed_packet bytes.
record_buffer
Each thread that makes 1 sequential scan allocates 1 buffer of this size for each table it scans. If you do a lot of sequential scans, you might want to increase the value.
sort_buffer
Each thread that needs to be sorted allocates 1 buffer of this size. Increasing this value speeds up ORDER BY or GROUP BY operations. See 18.5 where MySQL stores temporary files.
table_cache
The number of tables open for all threads. Increasing this value increases the number of file descriptors required by mysqld. MySQL requires two file descriptors for each table with only one open. See the comments below for the file descriptor limit. For information on how table caching works, see 10.2.4 MySQL how to open and close tables.
tmp_table_size
If a temporary table is larger than this size, MySQL generates an The table tbl_name is full error. If you do many advanced GROUP BY queries, increase the tmp_table_size value.
thread_stack
Stack size per thread. Many of the limitations detected by the crash-me test depend on this value. The default team 1-like operation is large enough. See 10.8 use your own benchmarks.
wait_timeout
The number of seconds a server waits for action on a connection before shutting it down. You can also see interactive_timeout.
MySQL USES very scalable algorithms, so you can usually run with less memory or give MySQL more storage for better performance.
If you have a lot of memory and a lot of tables and a medium number of clients, and want maximum performance, you should have something like this:
shell > safe_mysqld -O key_buffer=16M -O table_cache=128 \
-O sort_buffer=4M O record_buffer=1M &
If you have less memory and a lot of connections, use something like this:
shell > safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
- O record_buffer = 100 k &
Or even:
shell > safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache= 32-O record_buffer=8k O net_buffer=1K &
If there are many connections, "swap problems" can occur unless mysqld has been configured to use very little memory per connection. Of course, mysqld performs better if you have enough memory for all connections.
Note that if you change one of the options in mysqld, it actually only holds for the server example.
To understand the effect of a parameter change, do this:
shell > mysqld -O key_buffer=32m --help
Make sure --help option is the last one; Otherwise, the effect of any option listed after it on the command line will not be reflected in the output.
Important MySQL startup option
Modify back_log if you need a large number of new connections.
Modify thread_cache_size if you need a large number of new connections.
key_buffer_size index page pool, which can be set to large.
The bdb_cache_size BDB table USES records and keys that are not cached.
Modify table_cache if there are many tables and concurrent connections.
Set delay_key_write if you need to cache all keycode writes.
log_slow_queries finds queries that take a lot of time.
max_heap_table_size is used for GROUP BY
sort_buffer is used for ORDER BY and GROUP BY
myisam_sort_buffer_size is used for REPAIR TABLE
join_buffer_size is used for keyless joins.
MySQL cache (all threads Shared, one-time allocation)
Keycode cache: key_buffer_size, default 8M.
Table cache: table_cache, default 64.
Thread cache: thread_cache_size, default 0.
Hostname cache: can be modified at compile time, default 128.
Memory-mapped tables: currently only used to compress tables.
Note: MySQL does not have a row cache and lets the operating system process it
MySQL cache variable (non-shared, allocated on demand)
sort_buffer: ORDER BY/GROUP BY
record_buffer: scan the table.
join_buffer_size: keyless join
myisam_sort_buffer_size: REPAIR TABLE
net_buffer_length: cache the results for read SQL statements.
tmp_table_size: HEAP table size for temporary results.