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秒时的最大、平均、最小网络传输速度,测试数据如下:

客户端工具

net_buffer_length

20秒Avg MBit/s

20秒Max MBit/s

20秒Min MBit/s

DataGrip

16384第一次

193.44

221.03

142.42

DataGrip

16384第二次

198.31

220.23

130.5

DataGrip

16384第三次

183.75

221.04

114.03

DataGrip

1024第一次

89.48

113.8

67.77

DataGrip

1024第二次

75.12

95.04

39.7

DataGrip

1024第三次

97.54

132.22

79.07

Crt

16384第一次

223.03

248.03

176.73

Crt

16384第二次

221.38

243.87

192.08

Crt

16384第三次

227.98

254.3

201.67

Crt

1024第一次

93.24

118.14

45.48

Crt

1024第二次

108.43

133.41

62.07

Crt

1024第三次

97.19

122.11

39.88

Crt

1048576第一次

240.91

264.16

199.95

Crt

1048576第二次

248.51

270.67

200.97

Crt

1048576第三次

256.96

275.09

229.72

为了方便阅读插入图片

从上面可以得知

  • 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数据库

本站文章资源均来源自网络,除非特别声明,否则均不代表站方观点,并仅供查阅,不作为任何参考依据!
如有侵权请及时跟我们联系,本站将及时删除!
如遇版权问题,请查看 本站版权声明
THE END
分享
二维码
海报
MySQL Case-show processlist Sending to client状态详解
今天客户脱敏机器,访问MySQL数据库查询数据,show processlist状态一直处于Sending to client状态,时间持续了1.5h还没有结束...
<<上一篇
下一篇>>