Simple configuration optimization for MySQL servers

As the company's website traffic grows, MySQL naturally becomes a bottleneck. Therefore, I have been studying MySQL optimization recently. The first step is to naturally think of MySQL system parameter optimization, as a database system for a website with a large access volume (more than 0.2 million people a day), it is impossible to expect MySQL's default system parameters to make MySQL run smoothly. The following article describes how to optimize the configuration of the MySQL server and how to optimize the configuration of the MySQL server, if you are interested in its practical application, you can click the following article to learn about it.
You can use this command to obtain the default buffer size of the MySQL server:

Shell> MySQL (the best combination with PHP) d -- help
This command generates a table of all MySQL (the best combination with PHP) options d and configurable variables. The output includes the default value and looks like this:
Possible variables for option -- set-variable (-O) are:Copy codeThe Code is as follows: 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 MySQL server is running, run this command to view the variable value it actually uses:Copy codeThe Code is as follows: shell> MySQL (the best combination with PHP) admin 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 required for MySQL (the best combination with PHP. When the main MySQL (best combination with PHP) threads get a lot of connection requests in a very short time, this works, and then the main thread takes some time (although very short) 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 period of time before MySQL (the best combination with PHP) 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

MySQL (the best combination with PHP) d server is waiting for a connection packet in seconds 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 MySQL server waits for action on an interactive connection before closing it. An interactive customer is defined as the customer who uses the CLIENT_INTERACTIVE option for MySQL (the best combination with PHP) _ 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 (the best combination with PHP) 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 MySQL (the best combination with PHP) d. 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 (the best combination with PHP) Stores temporary files.

Table_cache

Number of tables opened for all threads. Add this value to increase the number of file descriptors required by MySQL (the best combination with PHP) d. MySQL (the best combination with PHP) requires two file descriptors for each unique Open table. For more information, see the comments on file descriptor restrictions. For information on how the table cache works, see 10.2.4 MySQL (the best combination with PHP) how to open and close the table.

Tmp_table_size

If a temporary table exceeds this size, MySQL (The best combination with PHP) generates an error in The table tbl_name is full format. If you do a lot of 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 before the MySQL server closes a connection. You can also see interactive_timeout.

MySQL (the best combination with PHP) is a very scalable algorithm, so you can usually run with a small amount of memory or give MySQL (the best combination with PHP) more memory 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:Copy codeThe Code is as follows: shell> safe_MySQL (the best combination with PHP) d-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:Copy codeThe Code is as follows: shell> safe_MySQL (the best combination with PHP) d-O key_buffer = 512 k-O sort_buffer = 100 k \-O record_buffer = 100 k &

Or even:Copy codeThe Code is as follows: shell> safe_MySQL (the best combination with PHP) d-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, a "switching problem" may occur, unless MySQL (the best combination with PHP) d has been configured to use very little memory for each connection. Of course, if you have enough memory for all the connections, MySQL (the best combination with PHP) d performs better.
Note: If you change an option of MySQL (the best combination of PHP and MySQL) d, it will only maintain the example of the MySQL server.
To understand the effect of a parameter change, do the following:Copy codeThe Code is as follows: shell> MySQL (the best combination with PHP) d-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.