Optimization of MySQL database 3 "Optimization 4" Connection settings

1. wait_timeout/interactive_timeout Connection timed out

The number of seconds that the server waits for activity before shutting down the connection. The maximum number of connections that MySQL supports is limited because each connection is built to consume memory, so we want MySQL to disconnect and free up memory when it finishes processing the appropriate operation. If your MySQL server has a large number of idle connections, they will not only consume memory in vain, but if the connection keeps accumulating, it will eventually reach the maximum number of MySQL server connections, which will report the error of ' too many connections '. For the value setting of wait_timeout, it should be judged according to the operation condition of the system. After the system has been running for a period of time, you can view the current system's connection status through the show Processlist command, if you find a large number of connection processes in the sleep state, then the parameter settings are too large to make the appropriate adjustment smaller. Recommendation 120 ~ 300

WAIT_TIMEOUT Specifies the maximum connection time for a request Wait_timeout = 10, for a server with about 4GB of memory can be set to 5-10.

2. skip-name-resolve Connection Check

The Skip-name-resolve parameter is used to suppress reverse DNS parsing. MySQL turns on DNS reverse resolution by default, and when a new connection arrives, MySQL resolves the connection host's DNS, which affects the connection speed. The use of this parameter also has a price, that is, each connection to use the IP address, you can no longer use localhost, changed to 127.0.0.1

3, Max_connections maximum number of connection processes, that is, the number of clients allowed to connect simultaneously

If the server has a larger number of concurrent connection requests, it is recommended that you increase this value to add more concurrent connections. However, the larger the number of connections, MySQL will provide a connection buffer for each connection, the more memory overhead, the more memory the server consumes, may affect the performance of the server, so according to the configuration of the server to properly adjust the value, can not blindly increase the value set. The default value is 100. "Calculate when MySQL is busy handling connections, recommended value 50% ~ 80%" Max_used_connections/max_connections * 100%

4, Max_connect_errors Maximum number of connection errors

Max_connect_errors = 10000000. For the same host, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. If you need to unblock the host, execute: FLUSH host;.

5, Max_allowed_packet set the maximum package, Restrict Server accepts packet size, avoids extra-long SQL There is a problem with the implementation

The default value is 16M, and when the MySQL client or mysqld server receives packets that are larger than max_allowed_packet bytes, it issues a "packet too large" error and closes the connection. For some clients, if the communication packet is too large, you may encounter a "missing connection to the MySQL server" error during query execution.

It is safe to increase the value of this variable because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if mysqld must return a large result row. This variable takes a smaller default value as a precaution to capture the error message packets between the client and the server and to ensure that memory overflows are not caused by accidental use of large packets.

6, Thread_concurrency The number of concurrent numbers allowed to pass is the key optimization parameter

Setting the correct value of the thread_concurrency has a significant impact on MySQL performance, and in the case of multiple CPUs (or multicore), incorrectly setting the value of thread_concurrency will result in MySQL not taking full advantage of multi-CPU (or multicore) Occurs at the same moment only one CPU (or core) is in the working condition. The thread_concurrency should be set to twice times the number of CPU cores. For example, there is a dual-core CPU, then the thread_concurrency should be 4; 2 Dual-core CPUs, the value of thread_concurrency should be 8.

7, Back_log set up MySQL number of connections that can be staged

It works when MySQL receives very many connection requests in a very short period of time. If the number of connections to MySQL reaches Max_connections, the new request will be present in the stack waiting for a connection to release the resource, the number of that stack is back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be granted. Setting the Back_log limit above your operating system is not valid. When you look at your host process list, you find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | NULL to connect the process, it is necessary to increase the value of Back_log. The default value is 50, which can be changed to 500 if the amount of traffic is large. The default value is 50.

Optimization of MySQL database 3 "Optimization 4" Connection settings