目录

28 MySQL 连接管理

MySQL 的连接管理

2. mysql 连接管理

在 MySQL 中有两个 kill 命令:

  1. kill query + 线程 id: 表示终止这个线程中正在执行的语句;
  2. kill connection + 线程 id: connection 可缺省,表示断开这个线程的连接,如果这个线程有语句正在执行,也是要先停止正在执行的语句。

2.1 收到 kill 以后,线程做什么

当用户执行 kill query thread_id_B 时,MySQL 里处理 kill 命令的线程做了两件事:

  1. 把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY);
  2. 给 session B 的执行线程发一个信号,让 session B 退出等待或终止执行,来处理这个 THD::KILL_QUERY 状态

上面的分析中,隐含了这么三层意思:

  1. 一个语句执行过程中有多处“埋点”,在这些“埋点”的地方判断线程状态,如果发现线程状态是 THD::KILL_QUERY,才开始进入语句终止逻辑;
  2. 如果处于等待状态,必须是一个可以被唤醒的等待,否则根本不会执行到“埋点”处;
  3. 语句从开始进入终止逻辑,到终止逻辑完全完成,是有一个过程的。

因此不是“说停就停的”。我们来看另种kill 无效的情况:

  1. 线程没有执行到判断线程状态的逻辑
  2. 终止逻辑耗时较长

2.2 线程没有执行到判断线程状态的逻辑

执行 set global innodb_thread_concurrency=2,将 InnoDB 的并发线程上限数设置为 2;然后,执行下面的序列:

/images/mysql/MySQL45%E8%AE%B2/kill_failure.png

session D 执行的 kill query C 命令却没什么效果,直到 session E 执行了 kill connection 命令,才断开了 session C 的连接,提示“Lost connection to MySQL server during query”,此时执行 show processlist

/images/mysql/MySQL45%E8%AE%B2/killed_query.png

id=12 这个线程的 Commnad 列显示的是 Killed。也就是说,客户端虽然断开了连接,但实际上服务端上这条语句还在执行过程中。

killed 原因分析

在这个例子里,12 号线程的等待逻辑是这样的:每 10 毫秒判断一下是否可以进入 InnoDB 执行,如果不行,就调用 nanosleep 函数进入 sleep 状态。

虽然 12 号线程的状态已经被设置成了 KILL_QUERY,但是在这个等待进入 InnoDB 的循环过程中,并没有去判断线程的状态,因此根本不会进入终止逻辑阶段。

当 session E 执行 kill connection 命令时,是这么做的:

  1. 把 12 号线程状态设置为 KILL_CONNECTION;
  2. 关掉 12 号线程的网络连接。因为有这个操作,所以你会看到,这时候 session C 收到了断开连接的提示。

如果一个线程的状态是KILL_CONNECTION,show processlist 就把Command列显示成Killed。

所以其实,即使是客户端退出了,这个线程的状态仍然是在等待中。那这个线程什么时候会退出呢?答案是,只有等到满足进入 InnoDB 的条件后,session C 的查询语句继续执行,然后才有可能判断到线程状态已经变成了 KILL_QUERY 或者 KILL_CONNECTION,再进入终止逻辑阶段。

2.3 终止逻辑耗时较长

到这里,我们来小结一下。kill 无效有两种情况:

  1. 第一种就是上面所说的线程没有执行到判断线程状态的逻辑。跟这种情况相同的,还有由于 IO 压力过大,读写 IO 的函数一直无法返回,导致不能及时判断线程的状态。
  2. 另一类情况是,终止逻辑耗时较长。这时候,从 show processlist 结果上看也是 Command=Killed,需要等到终止逻辑完成,语句才算真正完成。这类情况,比较常见的场景有以下几种:
    • 超大事务执行期间被 kill。这时候,回滚操作需要对事务执行期间生成的所有新数据版本做回收操作,耗时很长。大查询回滚。
    • 如果查询过程中生成了比较大的临时文件,加上此时文件系统压力大,删除临时文件可能需要等待 IO 资源,导致耗时较长。
    • DDL 命令执行到最后阶段,如果被 kill,需要删除中间过程的临时文件,也可能受 IO 资源影响耗时较久。

2.4 killed 线程的处理

如果你发现一个线程处于 Killed 状态,你可以做的事情就是,通过影响系统环境,让这个 Killed 状态尽快结束。

  1. InnoDB 并发度的问题,你就可以临时调大 innodb_thread_concurrency 的值,或者停掉别的线程
  2. 如果是回滚逻辑由于受到 IO 资源限制执行得比较慢,就通过减少系统压力让它加速

对于回滚大事务导致的 killed 线程,重启服务是没用的,为重启之后该做的回滚动作还是不能少的。最好还是等待它自己执行完成。如果这个语句可能会占用别的锁,或者由于占用 IO 资源过多,从而影响到了别的语句执行的话,就需要先做主备切换,切到新主库提供服务。切换之后别的线程都断开了连接,自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的,减少系统压力,加速终止逻辑。

3. mysql 客户端行为

关于 mysql 的客户端,有以下几个常见的误解:

3.1 客户端终止连接

客户端通过 Ctrl+C 命令,是不是就可以直接终止线程

  1. Ctrl+C 操作的是客户端进程,与服务器端没有直接关系
  2. MySQL 是停等协议,所以这个线程执行的语句还没有返回的时候,再往这个连接里面继续发命令也是没有用的。实际上,执行 Ctrl+C 的时候,是 MySQL 客户端另外启动一个连接,然后发送一个 kill query 命令

3.2 如果库里面的表特别多,连接就会很慢

/images/mysql/MySQL45%E8%AE%B2/client_slow.png

有些线上的库,会包含很多表(我见过最多的一个库里有 6 万个表)。这时候,你就会发现,每次用客户端连接都会卡在下面这个界面上。

当使用默认参数连接的时候,MySQL 客户端会提供一个本地库名和表名补全的功能。为了实现这个功能,客户端在连接成功后,需要多做一些操作:

  1. 执行 show databases;
  2. 切到 db1 库,执行 show tables;
  3. 把这两个命令的结果用于构建一个本地的哈希表

最花时间的就是第三步在本地构建哈希表的操作,我们感知到的连接过程慢,其实并不是连接慢,也不是服务端慢,而是客户端慢。提示也说了,如果在连接命令中加上 -A,就可以关掉这个自动补全的功能。

除了加 -A 以外,加–quick(或者简写为 -q) 参数,也可以跳过这个阶段。但是,这个–quick 是一个更容易引起误会的参数,也是关于客户端常见的一个误解。

3.3 客户端的 -quick 参数

设置了这个参数可能会降低服务端的性能,而不是加速连接。

MySQL 客户端发送请求后,接收服务端返回结果的方式有两种:

  1. 一种是本地缓存,也就是在本地开一片内存,先把结果存起来。如果你用 API 开发,对应的就是 mysql_store_result 方法。
  2. 另一种是不缓存,读一个处理一个。如果你用 API 开发,对应的就是 mysql_use_result 方法。

MySQL 客户端默认采用第一种方式,而如果加上–quick 参数,就会使用第二种不缓存的方式。采用不缓存的方式时,如果本地处理得慢,就会导致服务端发送结果被阻塞,因此会让服务端变慢。

为什么要给这个参数取名叫作 quick 呢?这是因为使用这个参数可以达到以下三点效果:

  1. 第一点,就是前面提到的,跳过表名自动补全功能。
  2. 第二点,mysql_store_result 需要申请本地内存来缓存查询结果,如果查询结果太大,会耗费较多的本地内存,可能会影响客户端本地机器的性能;
  3. 第三点,是不会把执行命令记录到本地的命令历史文件

–quick 参数的意思,是让客户端变得更快