MySQL optimization connection optimization

In the article MySQL Optimization: Cache Optimization, a very important concept is mentioned, that is, show variables is used to indicate the variable values ​​compiled by the system or configured in my.cnf. Show status is called the status value, which displays the status information of the current service instance and is a dynamically changing value. Therefore, it is often used to observe whether the current MySQl operation is normal. If it is not normal, the performance of MySQL can be improved by adjusting static parameters. Therefore, understanding the difference between these two concepts is the basis for subsequent tuning.

MySQL connection optimization

I remember one time when I was connecting to MySQL within the company, I couldn’t connect. After finding the DBA, we investigated the cause and found that the current number of MySQL connections was full. After adjustments, the problem was solved. There are generally two reasons for the error of too many connections. The first is that there are indeed too many people connecting to MySQL, causing the number of connections to be exhausted. 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_connections refers to the maximum number of concurrent connections that a MySQL service instance can accept at the same time. MySQL actually supports an algorithm that increases the maximum number of connections by one, ensuring that when the number of connections is used up, the super administrator can still establish a connection with the server for management.

max_user_connections sets the maximum number of concurrent connections for the specified account.

max_connect_errors When an illegal host maliciously connects to the MySQL server and the number of errors encountered reaches the set value, MySQL will resolve all connections from this host. But it will be cleared after executing flush hosts.

2. Connection status (show status)

One thing to note is that variable values ​​(show variables) start with a lowercase letter, while status values ​​(show status) start with an uppercase letter. This distinction helps with memory and classification

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

Connection_errors_max_connections When the maximum number of concurrent connections of MySQL is greater than the maximum number of concurrent connections in the system variable (show variables), the number of rejections will be recorded in this variable. If the Connection_error_max_connections value is relatively large, it means that the current system concurrency is relatively high, and you should consider increasing the max_connections value.

Connections indicates the number of connections that have been successfully established since MySQL was started. This value is accumulated.

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

3. Connection thread parameters (thread variables 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_size sets the number of connection thread caches. This cache is equivalent to the MySQL thread cache pool, which caches idle connection threads in the connection pool instead of destroying them immediately. When there is a new connection request, if there is an idle connection in the connection pool, it will be used directly. Otherwise the thread must be recreated. Creating threads is a non-trivial system overhead. This part of MySQL's thread processing is similar to Nginx's thread processing. When introducing Nginx's thread processing later, we will use them for comparison.

The default value of thread_handling is: one-thread-per-connection, which means that a thread is provided or created for each connection to handle the request until the request is completed and the connection is destroyed or stored in the cache pool. When the value is no-threads, it means that only one thread is provided to handle the connection, which is usually used for stand-alone testing.

thread_stack stack means heap. From this blog, we know that both processes and threads have unique IDs. The process ID system will maintain the ID of the process, and the thread ID is maintained by the specific thread library area. When the process or thread is dormant, the process context information must open up an area in the memory to save the process context information so that the program can be quickly woken up. The default stack size set for each MySQL thread is: 262144/1024=256k

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_cached The number of threads in the current thread pool

Thread_connected Current number of connections

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

Thred_runnint : The number of currently active threads.

Connection request stack

When MySQL suddenly receives a lot of 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 processing by MySQL. The back_log parameter sets the size of the stack, which can be viewed with the following command:

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

Connection abnormality

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

Aborted_clients The number of times a MySQL client was abnormally closed.

Aborted_connects The number of failed attempts 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 If the time to create a thread is too long and exceeds the setting value of slow_launch_time, it will be recorded.

You can view the error status information of the connection by using Connection_error%:

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 occurred while looking up the MySQL client IP address.