MySQL connection optimization and mysql connection Optimization

MySQL connection optimization and mysql connection Optimization

A very important concept mentioned in the above article about MySQL optimization Cache Optimization is that show variables is used to indicate the variable values of system compilation or configuration in my. cnf. The show status is called the status value, which displays the status information of the current service instance running and is a dynamically changed value. Therefore, it is often used to observe whether MySQl is running normally. If it is abnormal, You can adjust static parameters to improve MySQL performance. Understanding the differences between these two concepts is the basis for subsequent optimization.

MySQL connection Optimization

I remember that I couldn't connect to MySQL once in my company. After the DBA is found, the cause is that the current MySQL connection is full. After adjustment, the problem is solved. There are usually two reasons for the error of too many connections. First, there are indeed many people connecting to MySQL, resulting in the use of the number of connections. The second is that the max_connections value is too small.

1. Connection parameters (show variables)

mysql> show variables like '%connect%';+-----------------------------------------------+-----------------+| Variable_name                 | Value      |+-----------------------------------------------+-----------------+| character_set_connection           | utf8      || collation_connection             | utf8_general_ci || connect_timeout                | 10       || disconnect_on_expired_password        | ON       || init_connect                 |         || max_connect_errors              | 100       || max_connections                | 151       || max_user_connections             | 0        || performance_schema_session_connect_attrs_size | 512       |+-----------------------------------------------+-----------------+ 

max_connectionsIt refers to the maximum number of concurrent connections that MySQL service instances can accept at the same time. MySQL actually supports the maximum number of connections plus one algorithm to ensure that when the number of connections is used up, the super administrator can still establish a connection with the server for management.

max_user_connectionsSets the maximum number of concurrent connections of the specified account.

max_connect_errors When an illegal host maliciously connects to the MySQL server and the error reaches the set value, MySQL will resolve all connections from the host. But flush hosts will be cleared after execution.

2. show status)

Note that the show variables value starts with a lowercase letter and the show status value starts with an uppercase letter. This differentiation will help to remember and classify

mysql> show status like '%connections%';+-----------------------------------+-------+| Variable_name           | Value |+-----------------------------------+-------+| Connection_errors_max_connections | 0   || Connections            | 197  || Max_used_connections       | 2   |+-----------------------------------+-------+

Connection_errors_max_connectionsWhen the maximum concurrency of MySQL is greater than the maximum concurrency of max_connections in the system variable (show variables), the number of denied times will be recorded in this variable. If the Connection_error_max_connections value is large, it indicates that the current system concurrency is relatively high. You need to increase the value of max_connections.

ConnectionsIndicates the number of connections established successfully since MySQL was started. This value is continuously accumulated.

Max_used_connections indicates the number of concurrent connections at the same time since MySQL was started. If the value is greater than max_connections, it indicates that the system is often in a high-concurrency state. You should increase the maximum number of concurrent connections.

3. connection thread parameters (thread variabls and status)

mysql> show variables like 'thread%';+--------------------+---------------------------+| Variable_name   | Value           |+--------------------+---------------------------+| thread_cache_size | 9             || thread_concurrency | 10            || thread_handling  | one-thread-per-connection || thread_stack    | 262144          |+--------------------+---------------------------+

thread_cache_sizeSets the number of connection thread caches. This cache is equivalent to the cache pool of the MySQL thread. It caches idle connection threads in the connection pool rather than destroying them immediately. When a new connection request is sent, it is directly used if there is an idle connection in the connection pool. Otherwise, re-create the thread. Creating a thread is not a small system overhead. This part of MySQL thread processing is similar to Nginx thread processing. It will be compared later when we introduce Nginx thread processing.

thread_handlingThe default value is one-thread-per-connection, which indicates that each connection is provided or a thread is created to process the request until the request is complete, and the connection is destroyed or stored in the cache pool. When the value is no-threads, it indicates that only one thread is provided to process the connection, which is generally used for testing on a single machine.

thread_stack stackIt means heap. The PHP process explains this blog, knowing that both processes and threads have a unique ID, and the process ID system will maintain the ID of the two threads, it is maintained by the specific thread library. When the process or thread is sleeping, the context information of the process should be opened in the memory to save the context information of the process, so as to quickly wake up the program. The stack size set for each thread in MySQL is 262144/1024 = 256 kb by default.

View thread status information

mysql> show status like 'Thread%';+-------------------+-------+| Variable_name   | Value |+-------------------+-------+| Threads_cached  | 1   || Threads_connected | 1   || Threads_created  | 2   || Threads_running  | 1   |+-------------------+-------+

Thread_cachedNumber of threads in the current thread pool

Thread_connectedCurrent number of connections

Thread_cached: Number of threads created for the current connection. If this value is too high, you can adjust the threadcachesize, that is, to adjust the size of the thread cache pool.

Thred_runnint: Number of active threads.

Connection Request Stack

When MySQL suddenly receives many connection requests in a short period of time, MySQL will save the connection requests that cannot be processed in time in the stack for subsequent MySQL processing. The back_log parameter sets the stack size. You can run the following command to view the size:

mysql> show variables like 'back_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| back_log   | 80  |+---------------+-------+

Connection exception

mysql> show status like 'Aborted%';+------------------+-------+| Variable_name  | Value |+------------------+-------+| Aborted_clients | 0   || Aborted_connects | 219  |+------------------+-------+

Aborted_clientsThe number of times the MySQL client is shut down unexpectedly.

Aborted_connectsThe number of connection attempts that failed to connect to the MySQL server.

Other

mysql> show status like 'Slow%';+---------------------+-------+| Variable_name    | Value |+---------------------+-------+| Slow_launch_threads | 0   || Slow_queries    | 0   |+---------------------+-------+

mysql> show variables like 'slow_launch_time';+------------------+-------+| Variable_name  | Value |+------------------+-------+| slow_launch_time | 2   |+------------------+-------+

Slow_lunch_threads takes too long to create a thread. It is recorded if it exceeds the slow_launch_time value.

You can use Connection_error % to view the connection error status:

mysql> show status like 'Connection_error%';+-----------------------------------+-------+| Variable_name           | Value |+-----------------------------------+-------+| Connection_errors_accept     | 0   || Connection_errors_internal    | 0   || Connection_errors_max_connections | 0   || Connection_errors_peer_address  | 0   || Connection_errors_select     | 0   || Connection_errors_tcpwrap     | 0   |+-----------------------------------+-------+

Connection_errors_peer_address: the number of errors that occur when querying the IP address of the MySQL client.