MySQL Case-show processlist Sending to client状态详解

今天客户脱敏机器，访问MySQL数据库查询数据，show processlist状态一直处于Sending to client状态，时间持续了1.5h还没有结束，那么一直处于这个状态具体是在做什么呢？如何缩短这个状态时间呢？今天我们来看下这个案例，同时我也在自己的测试环境模拟了测试下。

客户的环境一直卡在这里，show processlist信息如下

我们知道，Sending to client 表示 sql已经执行完了，在网络传输，或者客户端在处理数据，那有哪些因素

影响此效率呢？

根据上述情况：

我自己创建了600万行的大表，执行全表查询操作，分别用datagrip客户端和crt客户端。crt登录命令如下，datagrip也都是tcp登录到mysql server

 mysql -u chongzi -pchongzi -P 3307 -h 192.168.239.51

测试开始，如下发起查询后语句一直处于sending to client状态，并且持续时间很久

mysql> show processlist;
+--------+--------+------------------+-----------------------------------------------------------------+
| Command| Time   | State            | Info                                                            |
+--------+--------+--------+------------------+-----------------------------------------------------------------+
| Query  |    110 | Sending to client| /* ApplicationName=DataGrip 2020.3.2 */ select * from test.test |
+--------+--------+------------------+-----------------------------------------------------------------+

引用MySQL实战45讲 林晓斌的部分文章内容：

1、MySQL 查询结果发送流程

那么，这个“结果集”存在哪里呢？

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。

2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去。

3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。

4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socketsend buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

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

从这个流程中，你可以看到：

1. 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，不会占用更多的内存空间；

2. socket send buffer 也不可能达到很大（ 默认定义/proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。

2、MySQL 是“边读边发的

也就是说，MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

比如sending to client这个状态，就是我故意让客户端不去读 socket receive buffer 中的内容，然后在服务端 show processlist 看到的结果。

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

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

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

为了方便阅读插入图片

从上面可以得知

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

第二个场景，控制单一变量 tcp receive buffer，即缩小tcp rmem，在/etc/sysctl.conf 中加入如下限制，最小值 默认值 最大值都为3000bytes

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

那么你会看到惊人的变化(当前net_buffer_length为16384)

使用crt客户端查询，网络带宽只有11.97M

到此测试结束

我们再回到这个实际案例中，改了tcp send buffer 和tcp recevie buffer 效率还是不变， Sending to Client 断断续续的，结果排查网络环境是路由有问题

更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码

作者：姚崇
Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。