1、 问题现象

客户脱敏业务在访问MySQL数据库查询数据时，执行show processlist状态一直处于Sending to client状态，时间持续了1.5h还没有结束。

 Sending to client 表示 SQL已经执行完成，在网络传输，或者客户端在处理数据。

2、 问题分析

当一个线程处于等待客户端接收结果的状态，就会显示“Sending to client”状态。

2.1 MySQL 查询结果发送流程

MySQL 服务端并不需要保存一个完整的结果集，取数据和发数据的流程是下面的流程：

 

  1. 获取一行，写到 net_buffer 中，这块内存大小是由参数 net_buffer_length 定义的，默认是 16k。
  2. 重复获取行，直到 net_buffer 写满，调用网络接口发送给客户端处理数据。
  3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
  4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待，直到网络栈重新可写，再继续发送。

 

这个过程对应的流程图如下所示

从上面的流程可以看出：

  • 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；
  • socket send buffer 也不可能达到 200G（ 默认定义/proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。
  • MySQL 是“边读边发”的，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间就会变长，比如sending to client这个状态，就是故意让客户端不去读 socket receive buffer 中的内容，然后在服务端 show processlist 看到的结果。
 2.2 测试用例

 改变单一变量net_buffer_length，感受下net_buffer_length对查询后数据网络传输的影响。

用nload ens33 -u -m 监控，执行开始到执行20秒时的最大、平均、最小网络传输速度，测试数据如下

 

  • 相同的net_buffer_length，如果客户端处理数据的速度不同，是可以影响到网络数据的传输速度的
  • 相同的客户端情况下，增加net_buffer_length大小 是可以提高传输效率的，因为每发送一次是要写满net_buffer后才发送。
  • 带宽充足，延迟相同情况下，查询大量数据时（1000万行数据），确实要全表进行访问的情况下，三个因素共同决定了sending to clent的时间：net_buffer_length大小（net_buffer）、socket send buffer（ /proc/sys/net/core/wmem_default，当然这个值我还没有测）、客户端的处理速度

场景二：缩小tcp rmem，在/etc/sysctl.conf 中加入如下限制（当前net_buffer_length为16384，网络带宽只有11.97M），最小值 默认值 最大值都为3000bytes

net.ipv4.tcp_wmem = 3000 3000 3000
net.ipv4.tcp_rmem = 3000 3000 3000