MySQL · 案例分析 · 大量连接状态 Writing to net

发表于 | 更新于 | 分类于 | 阅读次数：

问题现象

HDM 告警

您的自建DB实例

dbvipcenter@10.159.36.193:3307 ( 别名: HMCENTER_M, 集群: 互联互通 )

MySQL活跃会话数达到131

Show processlist 检查实时会话状态，发现出现大量的 "Writing to net"

1
2
3
4
5
6
7
8
9
10
11
12
13
| 41106 | canal  | 10.138.225.187:36737 | NULL | Binlog Dump | 14240 | Writing to net              | NULL |    0.000 |
| 41146 | canal  | 10.138.225.187:44157 | NULL | Binlog Dump | 14163 | Writing to net              | NULL |    0.000 |
| 41453 | canal  | 10.138.225.187:43588 | NULL | Binlog Dump | 13536 | Writing to net              | NULL |    0.000 |
| 41570 | canal  | 10.138.225.187:35880 | NULL | Binlog Dump | 13345 | Writing to net              | NULL |    0.000 |
| 41608 | canal  | 10.138.225.187:39624 | NULL | Binlog Dump | 13300 | Writing to net              | NULL |    0.000 |
| 41615 | canal  | 10.138.225.187:39629 | NULL | Binlog Dump | 13281 | Writing to net              | NULL |    0.000 |
| 41618 | canal  | 10.138.225.187:39632 | NULL | Binlog Dump | 13281 | Writing to net              | NULL |    0.000 |
| 41650 | canal  | 10.138.225.187:43390 | NULL | Binlog Dump | 13227 | Writing to net              | NULL |    0.000 |
| 41854 | canal  | 10.138.225.187:47054 | NULL | Binlog Dump | 12859 | Writing to net              | NULL |    0.000 |
| 41883 | canal  | 10.138.225.187:50815 | NULL | Binlog Dump | 12814 | Writing to net              | NULL |    0.000 |
| 42305 | canal  | 10.138.225.187:42869 | NULL | Binlog Dump | 12040 | Writing to net              | NULL |    0.000 |
| 42420 | canal  | 10.138.225.187:35098 | NULL | Binlog Dump | 11862 | Writing to net              | NULL |    0.000 |
| 42446 | canal  | 10.138.225.187:38964 | NULL | Binlog Dump | 11818 | Writing to net              | NULL |    0.000 |

原因分析

Writing to net：The server is writing a packet to the network.

SQL 语句查询流程：

  • 扫描一行写到 net_buffer，这里 net_buffer 是按照 net_buffer_length 初始化，但可以动态增长到 max_allowed_packet 配置值
  • 继续获取满足条件的行，直到写满 net_buffer，然后调用网络接口发送出去
  • 客户端接收网络包完成，清空 net_buffer
  • 重复上面 1 到 3 的步骤
  • 如果发送未完成，表示 socket send buffer 写满，这时会进入等待状态，显示 Writing to net，直到 socket send buffer 重新可写，再继续发送

由于 MySQL 是边扫描边发送数据的，当客户端接收慢时，就会阻塞整个查询进程，导致执行变慢。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> show variables like 'net_buffer_length';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> select @@max_allowed_packet/1024/1024;
+--------------------------------+
| @@max_allowed_packet/1024/1024 |
+--------------------------------+
| 20.00000000                    |
+--------------------------------+
1 row in set (0.08 sec)

解决方法

1
2
3
4
5
6
7
8
9
10
MariaDB [(none)]> set global max_allowed_packet=67108864;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like "max_allowed_packet";
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)