MySQL Connection Configuration

发表于 | 分类于 ， ，

参数说明

  1. skip-networking

开启该选项可以彻底关闭 MySQL 的 TCP/IP 连接方式，如果 WEB 服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！

  1. skip-name-resolve

禁止 MySQL 对外部连接进行 DNS 解析，使用这一选项可以消除 MySQL 进行 DNS 解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用 IP 地址方式，否则 MySQL 将无法正常处理连接请求！

  1. max_connections

max_connections 是指 MySql 的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在服务器能支撑的情况下，因为如果连接数越多，由于 MySQL 会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

  1. max_user_connections

max_user_connections 是指每个数据库用户的最大连接针对某一个账号的所有客户端并行连接到 MYSQL 服务的最大并行连接数。简单说是指同一个账号能够同时连接到 mysql 服务的最大连接数。设置为 0 表示不限制。

  1. max_connect_errors

设置每个主机的连接请求异常中断的最大次数，当超过该次数，MySQL 服务器将禁止 host 的连接请求，直到 mysql 服务器重启或通过 flush hosts 命令清空此 host 的相关信息

  1. thread_handing

Mysql 服务中线程处理模式包括两种：

  • no-threads(单线程处理，多用于 debug)
  • one- thread-per-connection（每个请求对应一个线程，目前被作为默认值 )；
  1. thread_cache_size

这个值表示可以重新利用保存在缓存中线程的数量, 当断开连接时如果缓存中还有空间, 那么客户端的线程将被放到缓存中, 如果线程重新被请求，那么请求将从缓存中读取。

如果缓存中是空的或者是新的请求，那么这个线程将被重新创建, 如果有很多新的线程，增加这个值可以改善系统性能. 因为当应用发起一个对数据库的操作时，在整个应用中是一个不小的开销，从建立连接之初，CPU 要给它划分一定的 thread stack，然后进行用户身份认证，建立上下文信息，最后请求完成，关闭连接，同时释放资源，可以称的上是秒级的过程。

在高并发的情况下，将给系统带来巨大的压力更不能保证性能。所以，采用线程重用，减小这部分的消耗。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

1
2
3
4
5
6
7
8
9
mysql> show status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 649   | <—当前被缓存的空闲线程的数量
| Threads_connected | 93    | <—正在使用（处于连接状态）的线程
| Threads_created   | 742   | <—服务启动以来，创建了多少个线程
| Threads_running   | 5     | <—正在忙的线程（正在查询数据，传输数据等等操作）
+-------------------+-------+

查看开机起来数据库被连接了多少次？

1
2
3
4
5
6
7
8
9
mysql> show global status like '%connections%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection\_errors\_max_connections | 0                   |
| Connections                       | 101395055             |   <–服务启动以来，历史连接数
| Max\_used\_connections              | 742                 |
| Max\_used\_connections_time         | 2018-08-21 15:42:38 |
+-----------------------------------+---------------------+

通过连接线程池的命中率来判断设置值是否合适？命中率超过90%以上, 设定合理。(Connections - Threads_created) / Connections * 100%

  1. thread_stack

每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的 Query 及自身的各种状态和处理信息。当然如果遇到下面的错误提示就应该考虑增加这个值了。mysql-debug: Thread stack overrun 如:

1
java.sql.SQLException: Thread stack overrun: 5456 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
  1. Connect_Timeout

字面上看意思是连接超时，指的是 MySQL 连接过程中握手的超时时间，在 5.0.52 以后默认为 10 秒，之前版本默认是 5 秒。

mysql的基本原理是有个监听线程循环接收请求，当有请求来时，创建线程（或者从线程池中取）来处理这个请求。由于 mysql 连接采用 TCP 协议，那么之前势必是需要进行TCP三次握手的。

TCP三次握手成功之后，客户端会进入阻塞，等待服务端的消息。服务端这个时候会创建一个线程 (或者从线程池中取一个线程) 来处理请求，主要验证部分包括host和用户名密码验证。host验证我们比较熟悉，因为在用grant命令授权用户的时候是有指定host的。用户名密码认证则是服务端先生成一个随机数发送给客户端，客户端用该随机数和密码进行多次 sha1 加密后发送给服务端验证。如果通过，整个连接握手过程完成。

由此可见，整个连接握手可能会有各种可能出错。所以这个 connect_timeout 值就是指这个超时时间了。可以简单测试下，运行下面的 telnet 命令会发现客户端会在 10 秒后超时返回。

1
2
3
4
5
6
7
8
9
10
11
$time telnet mysql\_ip\_addr port
$ time telnet 127.0.0.1 5051
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^\]'.
?
Connection closed by foreign
host.
real    0m5.005s #这里的5秒即mysql默认的连接超时
user    0m0.000s
sys 0m0.000s

Telnet 未退出前通过 show processlist 查看各线程状态可见，当前该连接处于授权认证阶段，此时的用户为“unauthenticated user”。

1
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL
  1. wait_timeout&interactive_timeout

等待超时，那MySQL等什么呢？确切的说是 MySQL 在等用户的请求 (query)，如果发现一个线程已经 sleep 的时间超过 wait_timeout 了那么这个线程将被清理掉，从文档上来看 wait_timeout 和 interactive_timeout 都是指不活跃的连接超时时间，而 interactive_timeout 针对交互式连接，wait_timeout 针对非交互式连接。

MySQL 连接线程启动的时候 wait_timeout 会根据是交互模式还是非交互模式被设置为这两个值中的一个。如果我们运行 MySQL -uroot -p 命令登陆到 MySQL，wait_timeout 就会被设置为 interactive_timeout 的值。如果我们在 wait_timeout 时间内没有进行任何操作，那么再次操作的时候就会提示超时，这是 MySQL client 会重新连接。

  1. net_read_timeout
  2. net_write_timeout
  3. net_retry_count

如果 MySQL 服务端在读写数据时，出现连接中断，会重试 net_retry_count 指定的次数。在系统 FreeBSD 中有效，Linux 中只有在 build 的时候指定 NO_ALARM 参数时 net_retry_count 才会起作用。

小结

connect_timeout在获取连接阶段（authenticate）起作用，interactive_timeout 和 wait_timeout 在连接空闲阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）起作用。

获取MySQL连接是多次握手的结果，除了用户名和密码的匹配校验外，还有 IP->HOST->DNS->IP 验证，任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上，超过connect_timeout的连接请求将会被拒绝。
即使没有网络问题，也不能允许客户端一直占用连接。对于保持 sleep 状态超过了 wait_timeout（或 interactive_timeout，取决于 CLIENT_INTERACTIVE 标志）的客户端，MySQL 会主动断开连接。

即使连接没有处于 sleep 状态，即客户端忙于计算或者存储数据，MySQL 也选择了有条件的等待。在数据包的分发过程中，客户端可能来不及响应（发送、接收、或者处理数据包太慢）。为了保证连接不被浪费在无尽的等待中，MySQL 也会选择有条件（net_read_timeout 和 net_write_timeout）地主动断开连接。比如我在客户端用 load data infile 的方式导入很大的一个文件到数据库中，然后中途用 iptables 禁用掉 mysql 的 3306 端口，这个时候服务器端该连接状态是 reading from net，在等待 net_read_timeout 后关闭该连接。同理，在程序里面查询一个很大的表时，在查询过程中同样禁用掉端口，制造网络不通的情况，这样该连接状态是 writing to net，然后在 net_write_timeout 后关闭该连接。

Refer to

MySQL 连接机制浅析及运维：https://hacpai.com/article/1536248398959