MySQL优化之连接优化

上文mysql优化之缓存优化 这篇文章中提到了一个很重要的概念，就是show variables是用来表示系统编译或者配置在my.cnf中的变量值。而show status则称之为状态值，显示的是当前服务实例运行所具有的状态信息，是一个动态改变的值。因此常用来观测当前mysql的运行是否正常，如果不正常那么依靠调整静态参数来提高mysql的性能。所以明白这两个概念的不同，是后面调优的基础。

mysql 连接优化

记得有一次在公司内部连接mysql的时候，总是连接不上。找到dba后，经过排查原因，是当前mysql连接数满了，经过调整后，解决了问题。引发连接数过多的错误原因一般有两个，第一的确是有很多人在连接mysql，造成连接数用完。第二种是max_connections值过小。

1、连接参数（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是指mysql服务实例能够同时接受的的最大并发连接数。mysql实际上支持最大连接数加一的算法，保障当连接数用完的时候，超级管理员依然可以和服务端建立连接，进行管理。

max_user_connections设置指定账号的最大并发连接数。

max_connect_errors 当某台非法主机恶意连接mysql服务端，遭到的错误达到设置值后，mysql会解决来自该主机的所有连接。但执行flush hosts后会清零。

2、连接状态(show status)

有一点需要注意的，变量值（show variables）是以小写字母开头的，而状态值（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 当mysql的最大并发数大于系统变量（show variables）中max_connections的最大并发数，因此而被拒绝的次数，将会记录在这个变量里。如果connection_error_max_connections值比较大，则说明当前系统并发比较高，要考虑调大max_connections的值。

connections表示mysql从启动至今，成功建立连接的连接数，这个值是不断累加的。

max_used_connections表示mysql从启动至今，同一时刻并发的连接数，取得是最大值。如果这个值大于 max_connections则表明系统经常处于高并发的状态，应该考虑调大最大并发连接数。

3、连接线程参数（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 设置连接线程缓存的数目。这个缓存相当于mysql线程的缓存池（thread cache pool）,将空闲的连接线程放入连接池中缓存起来，而非立即销毁。当有新的连接请求时，如果连接池中有空闲的连接，则直接使用。否则要重新创建线程。创建线程是一个不小的系统开销。mysql的这部分线程处理和nginx 的线程处理有异曲同工之妙，以后介绍nginx的线程处理时，会拿来做对比。

thread_handling 默认值是： one-thread-per-connection 表示为每个连接提供或者创建一个线程来处理请求，直至请求完毕，连接销毁或者存入缓存池。当值是no-threads 时，表示在始终只提供一个线程来处理连接，一般是单机做测试使用的。

thread_stack stack 是堆的意思，由php 进程详解这篇博客，知道进程和线程都是有唯一的id的，进程的id系统会维护，二线程的id,则由具体的线程库区维护，当进程或者线程休眠的时候，进程的上下文信息要在内存中开辟出一块区域，保存进程的上下文信息，以便于迅速唤醒程序。默认为mysql的每个线程设置的堆栈大小为：262144/1024=256k

查看线程状态信息

mysql> show status like 'thread%';
+-------------------+-------+
| variable_name   | value |
+-------------------+-------+
| threads_cached  | 1   |
| threads_connected | 1   |
| threads_created  | 2   |
| threads_running  | 1   |
+-------------------+-------+

thread_cached 当前线程池的线程数

thread_connected 当前的连接数

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地址是发生的错误数。