MySQL 连接管理

一、客户端连接线程

连接管理器为每个客户端连接分配一个线程，该线程用于处理身份验证和请求。连接结束时，如果高速缓存未满，这个线程不马上回收，而是被缓存。每次有连接请求的时候，会先查看线程缓存中是否有可用的线程，如果没有，才会创建新线程。

这种连接线程管理方式的缺点是，每个连接都要分配一个线程，资源开销大，性能消耗大。

企业版中提供了一个线程池插件，用来减少开销并提高性能。

1. 系统变量

（1）thread_cache_size

缓冲区保留的可重用线程数。0 表示禁用缓存

（2）thread_stack

线程栈大小。

当线程堆栈太小时，这将限制服务器可以处理的 SQL 语句的复杂性，存储过程的递归深度以及其他消耗内存的操作。

2. 状态变量

（1）Threads_cached

缓存中的线程数

（2）Threads_created

由于无法从缓存中获取线程而创建的线程数

二、连接数

1. 系统变量

（1）max_connections

最大连接数，默认是 151

组复制的内部会话与客户端连接分开处理，因此它们不计入 max_connections 限制，并且如果服务器达到此限制，也不会被拒绝。

（2）open_files_limit

操作系统的打开文件数量限制

2. 状态变量

（1）Connection_errors_max_connections

如果达到最大连接数，此状态变量将增加

这个状态变量的值还与其他变量有关，后续遇到再补充。

3. 查看连接线程

mysql> show processlist;
+----+-----------------+-----------------+--------------------+---------+--------+----
| Id | User            | Host            | db                 | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+--------------------+---------+--------+----
|  5 | event_scheduler | localhost       | NULL               | Daemon  | 173642 | Waiting on empty queue | NULL             |
| 60 | root            | localhost       | NULL               | Query   |      0 | init                   | show processlist |
| 61 | root            | 172.16.0.1:3189 | performance_schema | Sleep   |   3070 |                        | NULL             |
| 62 | root            | 172.16.0.1:3190 | performance_schema | Sleep   |   3055 |                        | NULL             |
| 63 | root            | 172.16.0.1:3486 | performance_schema | Sleep   |   3055 |                        | NULL             |
+----+-----------------+-----------------+--------------------+---------+--------+----

Note：MySQL 实际允许的连接数是 max_connections + 1 ，额外的那个连接是为管理员（需要 CONNECTION_ADMIN 权限）准备的。

4. 受影响因素

如果 RAM 充足，且每个连接的负载低，响应时间不高，Linux 至少支持 500 - 1000 个并发连接，最高 10000 个。

给定平台上线程库的质量
每个连接使用的 RAM 的大小
每个连接的负载
所需的响应时间
可用文件描述符的数量

三、连接管理（8.0 版本）

8.0 版本，服务器可以使用 TCP/IP 协议指定管理连接的接口。

管理连接的数量没有限制，但是需要 SERVICE_CONNECTION_ADMIN 权限。

管理连接接口有自己的配置选项，与主连接接口相对应。默认情况下，已启用加密连接，不需要特别指定。

1. 系统变量

[mysqld]
admin_address=127.0.0.1
admin_port=33064

（1）admin_address

指定 IP 地址

（2）admin_port

指定端口，默认是 33062

（3）create_admin_listener_thread

启动时可以选择管理接口是否具有自己的单独线程。默认为 OFF。

（4）连接加密

admin_tls_xxx、admin_ssl_xxx

与 tls_xxx、ssl_xxx 类似
admin_tls_version

TLS 规则，指定加密列表。空值，表示不使用加密连接
```
[mysqld]
admin_tls_version=''
```
加密连接可以通过 --admin-ssl 选项指定

2. 配置过程

编辑配置文件 /etc/my.cnf

[mysqld]
# 连接管理
thread-cache-size = 9
thread-stack      = 300K  # default 287620
max_connections   = 151
admin_address     = 127.0.0.1
admin_port        = 33062
#create_admin_listener_thread = on

# 连接加密
admin_ssl_ca=ca.pem
admin_ssl_cert =server-cert.pem
admin_ssl_key  =server-key.pem
admin_tls_version  =TLSv1,TLSv1.1,TLSv1.2,TLSv1.3

验证连接
```
mysql -uroot -p -h 127.0.0.1 -P33062
```
Note：虽然通过 33062 端口连接到服务器，但是服务器端的正常服务端口仍然是 3306，33062 只是用于管理连接的端口。
```
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
```

四、DNS 查找和主机缓存

MySQL 服务器在内存中维护了一个主机缓存信息，包含客户端信息，如 IP 地址、主机名、错误信息，会被缓存起来。

主机缓存只用于非本地登录的 TCP 连接。

如果缓存已满，则服务器将丢弃最近最少使用的缓存条目，以便为新条目腾出空间。如果内存不足，可能导致连接错误。

1. 查看主机缓存

performance_schema.host_cache

mysql> select * from performance_schema.host_cache\G
*************************** 1. row ***************************
                                        IP: 172.16.0.30
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2021-03-04 14:22:45
                                 LAST_SEEN: 2021-03-04 14:22:45
                          FIRST_ERROR_SEEN: 2021-03-04 14:22:45
                           LAST_ERROR_SEEN: 2021-03-04 14:22:45

Note：因为没有为 172.16.0.30 配置本地 DNS 解析，所以最后两列有值。

2. 作用

通过缓存 IP 和主机名的映射，避免对每个客户端连接做 DNS 解析

主机缓存中的错误信息可以用来判断是否阻塞，如果某主机连续多次连接失败，服务器端会阻止该主机的连接。

3. 配置主机缓存

（1）系统变量

max_connect_errors

连续失败次数。
```
[mysqld]
max_connect_errors=10000
```
如果存在网络问题，增加此变量的值是没有好处的，所以如果发生阻塞，应该首先确认 TCP/IP 连接是否正常
host_cache_size

主机缓存大小，默认开启。
```
[mysqld]
host_cache_size=200
```
动态修改这个变量会清除主机缓存
skip_name_resolve

禁用 DNS 解析。

如果 DNS 速度很慢且有很多主机，可以通过此变量禁用 DNS 解析，或者增加主机缓存 host_cache_size
禁止 TCP/IP 连接，可以启动时指定 skip_networking

4. 刷新主机缓存

（1）什么时候需要刷新

当客户端主机 IP 地址变更时
因为连续连接失败导致主机连接受阻时

（2）刷新主机缓存的影响

会把性能表 host_cache 的内容删除，相当于清空主机缓存，并解除所有受阻主机

5. 解锁主机的方法

改变系统变量 host_cache_size
truncate 性能表 host_cache
执行 FLUSH HOSTS 或 mysqladmin flush-hosts
还有一个临时方法，就是增加系统变量 max_connect_errors

红炉点雪