(i) MySQL connection optimization

One, MySQL connection optimization

1. View 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             

2. View connection status (Show status)

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

Connection_errors_max_connections when MySQL's maximum concurrency is greater than the maximum number of concurrent max_connections in the system variable (show variables), the number of rejections is recorded in this variable. If the connection_error_max_connections value is relatively large, then the current system concurrency is relatively high, to consider the value of large max_connections.
Connections represents the number of connections that have been successfully established since the start of MySQL, and this value is constantly accumulating.
The max_used_connections represents the maximum number of simultaneous connections that MySQL has made since the start of the same time. If this value is greater than max_connections, it indicates that the system is often in a high concurrency state and should consider 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 sets the number of connection thread caches. This cache is equivalent to the MySQL thread's cache pool (thread cache), which caches idle connection threads in the connection pool rather than destroying them immediately. When there is a new connection request, it is used directly if there are idle connections in the connection pool. Otherwise, you re-create the thread. Creating a thread is a no-small system overhead.
The default value for thread_handling is: one-thread-per-connection means providing or creating a thread for each connection to process the request until the request is complete, and the connection is destroyed or deposited into the cache pool. When the value is no-threads, it means that only one thread is always available to handle the connection, usually a single machine for testing.
Thread_stack stack is the meaning of the heap, knowing that both the process and the thread have a unique ID, the process ID system will be maintained, the ID of the second thread is maintained by the specific thread library, when the process or thread hibernation, the context of the process to open up an area in memory, Saves the context information for the process so that the program can be awakened quickly. The default stack size set for each thread of MySQL is: 262144/1024=256k

4. 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 number of threads in the current thread pool
thread_connected Current number of connections
Thread_cached: The current connection thread creation number, if this value is too high, you can adjust the threadcachesize that is to adjust the size of the thread cache pool.
Thred_runnint: The number of threads currently active.

5. Connection request Stack
MySQL in a short period of time, suddenly received a lot of connection requests, MySQL will not be able to handle the connection request is saved in the stack, so that MySQL follow-up processing. The Back_log parameter sets the size of the stack and can be viewed with the following command:

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

6. Connection exceptions

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

Aborted_clients the number of times the MySQL client was shut down unexpectedly.
Aborted_connects the number of connections that failed while attempting to connect to the MySQL server.

7.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 the time that the thread was created is longer than the set value of Slow_launch_time, it is logged.

8. You can view the error status information for 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 find the MySQL client IP address is the number of errors that occurred.

(i) MySQL connection optimization