MySQL optimization connection optimization sample code

MySQLConnection optimization mainly refers to the parameters involved in the process of the clientconnecting to the database and the database opening the data table and index in response to the client's request Adjustment. Let's discuss it in detail

MySQL optimization aboveCacheOptimization This article mentioned a very important concept, that is, show variables are used to indicate that the system is compiled or configured. Variablevalue in my.cnf. Show status is called the status value. It displays the status information of the current service instance running and is a dynamically changing value. Therefore, it is often used to observe whether the current MySQL is running normally. If it is not normal, then rely on adjusting the static parameters to improve the performance of MySQL. Therefore, understanding the difference between these two concepts is the basis for subsequent tuning.

MySQL connection optimization

I remember once when I connected to MySQL within the company, I could not always 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 errors that cause too many connections. The first is that there are indeed many people connecting to MySQL, causing the number of connections to run out. 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 means that the MySQL service instance can simultaneously The maximum number of concurrent connections accepted. MySQL actually supports the algorithm of adding one to the maximum number of connections, 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_connectionsSet 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 errors it encounters reach the set value, MySQL will resolve all connections from the host. But it will be cleared after executing flush hosts.

2. Connection status (show status)

One thing to note is that the variable value (show variables) starts with a lowercase letter, and the status value (show status) begins with a capital letter. This distinction is helpful for 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 concurrency of MySQL is greater than system variables (show variables) The maximum number of concurrency in max_connections, and therefore the number of rejections, will be recorded in this variable. If the value of Connection_error_max_connections is relatively large, it means that the current system concurrency is relatively high, and you should consider increasing the value of max_connections.

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

Max_used_connections represents the maximum 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 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_size Set the connection thread cache Number of. This cache is equivalent to the MySQL thread cache pool (thread cache pool), which puts idle connection threads into the connection pool and caches them instead of destroying them immediately. When there is a new connection request, if there is an idle connection in the connection pool, it is used directly. Otherwise, the thread must be re-created. Creating threads is a considerable system overhead. This part of MySQL's thread processing is similar to Nginx's thread processing. When I introduce Nginx's thread processing in the future, I will compare them.

thread_handling The default value is: one-thread-per-connection means providing or creating a thread for each connection to process the request until the request is completed, the connection is destroyed or stored in the cache pool. When the value is no-threads, it means that only one thread is always provided to handle the connection, which is generally used for testing on a single machine.

thread_stack stack means heap. This blog explains the PHP process in detail. You know that processes and threads have unique IDs. The ID system of the process will maintain it, and the ID of the second thread. , is maintained by a specific thread library area. When a process or thread sleeps, the context information of the process must open up an area in the memory to save the context information of the process in order to quickly wake up the program. The default stack size set for each thread of MySQL 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 current number of threads in the thread pool

Thread_connected

The current number of connections<p><code>Thread_cached: 当前连接线程创建数, 如果这个值过高，可以调整threadcachesize 也就是调整线程缓存池的大小。

Thred_runnint: 当前活跃的线程数。

连接请求堆栈

MySQL在很短的时间内，突然收到很多的连接请求时，MySQL会将不能来得及处理的连接请求保存在堆栈中，以便MySQL后续处理。back_log参数设置了堆栈的大小，可以通过如下命令查看：

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

连接异常

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

Aborted_clients MySQL 客户机被异常关闭的次数。

Aborted_connects 试图连接到MySQL服务器而失败的连接次数。

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 创建线程的时间过长，超过slow_launch_time的设定值，则会记录。

可以通过使用 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 查找MySQL客户机IP地址是发生的错误数。

The above is the detailed content of MySQL optimization connection optimization sample code. For more information, please follow other related articles on the PHP Chinese website!