Description of some Mysql startup parameters and Optimization methods _mysql

The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Unix Listen (2) system calls to the hand album should have more details. Check your OS documentation to find the maximum value for this variable. Attempting to set Back_log above your operating system will be ineffective.

Connect_timeout

The number of seconds the MYSQLD server is waiting for a connection message before it answers with bad handshake (poor handshake).

Delayed_insert_timeout

An insert delayed thread should wait for the INSERT statement time 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, allow these statements to be executed before continuing.

Delayed_queue_size

How many queues (in rows) should be allocated for processing insert delayed. If the queue is full, any customer who inserts delayed will wait until there is room in the queue.

Flush_time

If this is set to a value other than 0, all tables will be closed for every flush_time second (to free resources and sync to disk).

Interactive_timeout

The number of seconds 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 (). Also visible wait_timeout.

Join_buffer_size

The buffer size (not an indexed join) for the full join (join). The buffer allocates a buffer to each of the 2 tables, and when the index is added it is not possible to increase the value to get a faster full join. (Often the best way to get a quick connection is to add an index.) ）

Key_buffer_size

The index block is buffered and is shared by all threads. Key_buffer_size is the size of the buffer used for the index block, adding that it can be better processed by indexing (for all read and multiple writes), to the extent that you can afford that much. If you make it too big, the system will start to change pages and it really slows down. Remember that since MySQL does not cache read data, you will have to leave some room for the OS file system cache. To get more speed when writing multiple rows, use lock TABLES. See 7.24LOCK tables/unlock TABLES syntax.

Long_query_time

If a query takes more time than it (in seconds), the Slow_queries register is incremented.

Max_allowed_packet

The maximum size of a package. The message buffer is initialized to net_buffer_length bytes, but can be incremented to max_allowed_packet bytes as needed. By default, this value is too small to catch large (possibly wrong) packages. If you are using a large BLOB column, you must increase the value. It should be as big as the largest blob you want to use.

Max_connections

The number of simultaneous customers allowed. Increase this value to increase the number of file descriptors required by mysqld. See below for comments with file descriptor restrictions. See 18.2.4 Too many connections error.

Max_connect_errors

If there are more than that number of disconnected connections from one host, this host blocks further connections. You can use flush hosts command to dredge a host.

Max_delayed_threads

Do not start a thread with more than this number to handle the insert delayed statement. If you attempt to insert data into a new table after all insert delayed threads are used, the row is inserted, just as the delayed attribute is not specified.

Max_join_size

A join that may be reading more than Max_join_size records will return an error. If your users want to perform a join that does not have a WHERE clause, takes a long time and returns millions of rows, set it up.

Max_sort_length

The number of bytes to use when ordering a blob or text value (each value is used only by the header max_sort_length byte; the rest is ignored).

Max_tmp_tables

(the option is not doing anything yet). The maximum number of temporary tables that a customer can keep open at the same time.

Net_buffer_length

The communication buffer is reset between queries to that size. Usually this should not be changed, but if you have very little memory, you can set it to the size of the query you want. (that is, the desired length of the SQL statement that the customer emits.) If the statement exceeds this length, the buffer is automatically enlarged until the Max_allowed_packet byte. ）

Record_buffer

Each thread that carries out a sequential scan assigns a buffer of this size to each table it scans. If you do a lot of sequential scans, you might want to add that value.

Sort_buffer

Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by action. See where 18.5 MySQL stores temporary files.

Table_cache

The number of tables opened for all threads. Increasing this value can increase the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table, see below for comments that face file descriptor restrictions. For information about how the table cache works, see how 10.2.4 MySQL opens and closes the table.

Tmp_table_size

If a temporary table exceeds that size, MySQL produces a full form of the table Tbl_name error, and 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 limitations detected by the CRASH-ME test depend on this value. The default team's general operation is large enough. See 10.8 using your own benchmark.

Wait_timeout

The number of seconds the server waits for action on a connection before closing it. Also visible interactive_timeout.

MySQL uses a scalable algorithm, so you can usually run with little memory or save MySQL for better performance.

If you have a lot of memory and a lot of tables and have a medium number of customers, 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=100k &

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 issues" can occur unless mysqld has been configured to use very little memory for each connection. Of course, if you have enough memory for all the connections, MYSQLD performs better.

Note that if you change the MYSQLD option, it is actually only maintained for that example of the server.

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

shell> Mysqld-o key_buffer=32m--help

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

Important MySQL Startup options

Back_log If you need a large number of new connections, modify it.

Thread_cache_size If you need a large number of new connections, modify it.

The Key_buffer_size index page pool can be set to a large size.

Bdb_cache_size the records and keys used by the BDB table cache.

Table_cache If there are a lot of tables and concurrent connections, modify it.

Delay_key_write If you need to cache all of the key code writes, set it.

Log_slow_queries find a query that takes a lot of time.

Max_heap_table_size for GROUP BY

Sort_buffer for order by and GROUP by

Myisam_sort_buffer_size for repair TABLE

Join_buffer_size is used when making a join without a key.

MySQL cache (all threads shared, one-time allocated)

Key-code caching: key_buffer_size, Default 8M.

Table cache: Table_cache, default 64.

Thread caching: thread_cache_size, default 0.

Host name caching: can be modified at compile time, default 128.

Memory-Mapped table: Currently used only for compressed tables.

Note: MySQL has no row cache and lets the operating system process

MySQL cache variables (unshared, on Demand)

Sort_buffer:order By/group by

Record_buffer: Scan table.

Join_buffer_size: Keyless Bonding

Myisam_sort_buffer_size:repair TABLE

Net_buffer_length: For reading SQL statements and caching results.

Tmp_table_size: Heap table size for temporary results.