MySQL connection related parameters and status values

For MySQL connection parameters and status values, this article to do some introduction and comparison

One, mysql connection parameter variable

1, common connection number limit parameter

Show variables like '%connect% ';

999999999 6000　　　　 　　　　　0 | # #但用户连接最大限制, default 0 means no limit, adherence to total number of connections is less than or equal to max_connections|　　　　　　　　　Connect_timeout       | # #用户连接超时限制, over 10 seconds, if you still can't connect to MySQL, terminate the connection

2. Timeout parameters

Mysql-e "Show variables like '%timeout% '"

+-----------------------------+----------+|Connect_timeout| Ten       | # #连接超时, 10 seconds|Delayed_insert_timeout|  -      | # #延迟插入超时时间, 300 seconds|Have_statement_timeout|YES| ##|Innodb_flush_log_at_timeout| 1        | # #刷新redo Log Buffer timeout time, 1 seconds|Innodb_lock_wait_timeout|  -      | # #事务等待获取资源等待的最长时间, a resource that has not been allocated over this time will return an app failure, 120 seconds|Innodb_rollback_on_timeout|  on       ||Interactive_timeout| 28800    | # #mysql客户端交互连接超时时间, default 8 hours, for controlling sleep timeouts|Lock_wait_timeout| 31536000 | # #主要针对DDL产生的Metadata locks time Out |Net_read_timeout|  -       | # #网络读取数据超时时间, 60 seconds|Net_write_timeout|  -       | # #为网络写入数据超时间60秒|Rpl_stop_slave_timeout| 31536000 | # #停止从库服务超时时间|Slave_net_timeout|  -       | # #slave网络超时时间|Thread_pool_idle_timeout|  -       ||Wait_timeout| 28800    | # #jdbc/ODBC Connection time-out, default 8 hours, to control sleep timeout+-----------------------------+----------+

Second, MySQL connection state variable

1. Common Link state variables

Show global status like '%connect% ';

+-----------------------------------------------+----------+|Connection_errors_max_connections| 1906160  | # #用户最大错误连接数|Connections| 87341259 | # #历史用户连接累积值|Max_used_connections| 10242    | # #历史最大连接用户数, processlist see the number of connections|threads_connected| 298      | # #当前用户连接数, current number of processlist

Show global status like '%threads_running% ';

| | 2     | # #当前用户连接处于running状态的数目, non-sleep

Show global status like '%abort% ';

+ -- ----------------+----------+ | Aborted_clients  | 85050948　　　　　　　　　　　　　　　　　　　　　　| # #非法终止客户端连接的状态统计, such as terminating a connection by kill | | 1905528  | # #非法连接操作状态统计, such as user password error

Third, the analysis of common problems

1. Maximum number of connections exceeded limit

2. The number of user connection errors exceeds the limit

3, the Abort value is very high

4, the number of connections continues to increase

5.

MySQL connection related parameters and status values