MySql database 3 [optimization 4] Optimization of connection settings, mysql database

MySql database 3 [optimization 4] Optimization of connection settings, mysql database

1,Wait_timeout/interactive_timeout Connection timeout

The number of seconds before the server closes the connection. The maximum number of connections supported by MySQL is limited, because the creation of each connection consumes memory. Therefore, we hope that MySQL will disconnect and release the occupied memory after processing the corresponding operations. If your MySQL Server has a large number of idle connections, they will not only consume the memory in vain, but if the connections continue to accumulate and continue to open, it will eventually reach the maximum number of connections of the MySQL Server, this will report the 'too has connections' error. The value of wait_timeout should be determined based on the system running condition. After the system runs for a period of time, you can run the show processlist command to view the connection status of the current system. If a large number of connection processes in sleep status are found, this parameter is set too large, you can make some adjustments. Recommended 120 ~ 300

Wait_timeout specifies the maximum connection time of a request wait_timeout = 10. For servers with around 4 GB of memory, you can set it to 5-10.

 

2. skip-name-resolve Connection check

The skip-name-resolve parameter is used to disable reverse DNS resolution. MySQL enables reverse DNS resolution by default. When a new connection comes, MySQL will resolve the DNS of the connected host, which affects the connection speed. There is also a price to use this parameter, that is, if the IP address is used for each connection, localhost cannot be used and changed to 127.0.0.1.

 

3. max_connectionsThe maximum number of connected processes, that is, the number of customers allowed to connect simultaneously.

If the number of concurrent connection requests on the server is large, we recommend that you increase this value to increase the number of parallel connections. However, the larger the number of connections, MySQL will provide a connection buffer for each connection, and the higher the memory overhead, the more memory the server consumes, which may affect the server performance, therefore, you must adjust the value based on the server configuration, and do not blindly increase the value. The default value is 100. [Calculate the connection handling status when MySQL is busy. The recommended value is 50% ~ 80%] max_used_connections/max_connections * 100%

 

4. max_connect_errorsMaximum number of connection errors

Max_connect_errors = 10000000. If an error connection is interrupted for the same host that exceeds the value of this parameter, the connection to the host is disabled. To unban the HOST, run: flush host ;.

 

5. max_allowed_packetSet the maximum package,Restrict serverAccept the data packet size to avoid excessive SQLThere is a problem with the execution

The default value is 16 Mb. When the MySQL client or mysqld server receives an information packet larger than the value of max_allowed_packet, the "Information Package is too large" error is sent and the connection is closed. For some clients, if the communication information package is too large, a "lost connection to MySQL Server" error may occur during query.

It is safe to increase the value of this variable because additional memory is allocated only when necessary. For example, if you issue a long query or mysqld must return a large result, mysqld will allocate more memory. The small default value of this variable is a preventive measure to capture error packets between the client and the server, and ensure that memory overflow is not caused by accidental use of large information packets.

6. thread_concurrencyAllowed concurrencyKey optimization parameters

The correct value of thread_concurrency has a great impact on mysql performance. When multiple CPUs (or multiple cores) are used, the value of thread_concurrency is incorrectly set, as a result, mysql cannot make full use of multiple CPUs (or multiple cores), and only one cpu (or core) can work at the same time. Thread_concurrency should be set to 2 times the number of CPU cores. For example, if there is a dual-core CPU, thread_concurrency should be 4; if there are two dual-core CPUs, the value of thread_concurrency should be 8.

 

7. back_logSet MySQLNumber of connections that can be saved

It works when MySQL receives many connection requests in a short time. If the number of connections in MySQL reaches max_connections, the new request will be stored in the stack to wait for a connection to release the resource. The number of stacks is back_log, if the number of connections waiting exceeds back_log, connection resources are not granted. Setting back_log higher than your operating system is invalid. When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value is 50. You can change the value to 500 if the traffic volume is large. The default value is 50.

How can I optimize the mysql database?

I will list several familiar ones,
1. Storage engine: select an appropriate engine based on the Application
2. indexing-there are a lot of articles here. You need to understand them yourself.
3. SQL statement optimization, selection of query conditions, and so on
4. mysql system configuration, which must be customized for Applications
5. Select a table, temporary table, or partition table.

How to optimize the MYSQL database

1. select the most suitable field attribute, minimize the length of the defined field, and set the field not null as much as possible, such as 'province, gender'. It is best to set it to ENUM.
2. Use JOIN instead of subquery:
A. DELETE no order Customer: delete from customerinfo WHERE customerid NOT in (SELECT customerid FROM orderinfo)
B. Extract all customers without orders: SELECT FROM customerinfo WHERE customerid NOT in (SELECT customerid FROM orderinfo)
C. Speed up B optimization: SELECT FROM customerinfo LEFT JOIN orderid customerinfo. customerid = orderinfo. customerid
WHERE orderinfo. customerid IS NULL
3. Use UNION instead of creating a temporary table manually
A. Create a temporary table: SELECT name FROM 'nametest 'union select username FROM 'nametest2'
4. Transaction processing:
A. ensure data integrity. For example, if both the ADD and modify operations are performed, both operations fail.
Mysql_query ("BEGIN ");
Mysql_query ("insert into mermerinfo (name) VALUES ('$ name1 ')";
Mysql_query ("SELECT * FROM 'orderinfo' where customerid =". $ id ");
Mysql_query ("COMMIT ");
5. Lock the table and optimize transaction processing:
A. We use a SELECT statement to retrieve the initial data. Through some calculations, we use the UPDATE statement to UPDATE the new value to the table.
The lock table statement containing the WRITE keyword can ensure that before the unlock tables command is executed,
There will be no other access to insert, update, or delete inventory
Mysql_query ("lock table customerinfo READ, orderinfo WRITE ");
Mysql_query ("SELECT customerid FROM 'mermerinfo' where id =". $ id );
Mysql_query ("UPDATE 'orderinfo' SET ordertitle = '$ title' where mermerid =". $ id );
Mysql_query ("unlock tables ");
6. Use foreign keys to optimize table locking
A. Map customerid in customerinfo to customerid in orderinfo,
Any record without a valid mermerid will not be written to orderinfo
Create table customerinfo
(
Customerid int not null,
Primary key (customerid)
... The remaining full text>