一、场景：

二、MySQL数据库查询结果数据发送流程：

首先，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。重复获取行，直到 net_buffer 写满(net_buffer自动扩展达到max_allowed_packet参数指定的大小)，调用网络接口发出去。如果发送成功，就清空 net_buffer，然后重置net_buffer的大小(net_buffer_length参数控制)，然后继续取下一行，并写入 net_buffer。如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。这个过程对应的流程图如下所示。    

从这个流程中，你可以看到：一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer最大值（也就是max_allowed_packet参数指定的大小），并不会达到 20G；本地网络栈(socket send buffer) 也不可能达到 20G（默认定义 /proc/sys/net/core/wmem_default），如果 本地网络栈（socket send buffer） 被写满，就会暂停读数据的流程(此时show  processlist看到会话状态为“Sending to client”)。     MySQL 是“边读边发的”，读取的慢会直接导致事务慢(这个不在本次讨论范围内)，发送和接收的慢也会导致事务慢，所以我们可以从发送和接收两个角度去优化相应的参数。

三、DB和OS参数层面优化

1、操作系统参数优化：

1）修改本地网络栈的发送缓冲的大小方法：

临时修改： 

echo 524288 | sudo tee /proc/sys/net/core/wmem_max 

echo 262144 | sudo tee /proc/sys/net/core/wmem_default 

持久化修改（需要重启服务器）： 

echo "net.core.wmem_max = 524288" |  tee -a /etc/sysctl.conf 

echo "net.core.wmem_default = 262144" |  tee -a /etc/sysctl.conf sysctl -p

2）修改客户端本地网络栈的接受缓冲的大小方法：

临时修改： 

echo 524288 | sudo tee /proc/sys/net/core/rmem_max 

echo 262144 | sudo tee /proc/sys/net/core/rmem_default 

持久修改（需要重启服务器）： 

echo "net.core.rmem_max = 524288" |  tee -a /etc/sysctl.conf 

echo "net.core.rmem_default = 262144" |  tee -a /etc/sysctl.conf sysctl -p

3）针对tcp协议如下参数的优先级更高，会覆盖wmem_default和rmem_default!

[root@node2 ~]# cat /proc/sys/net/ipv4/tcp_rmem   ----接收缓冲

8192 262144 6291456 （最小值 8KB，默认值 256KB，最大值 6MB）

[root@node2 ~]# cat /proc/sys/net/ipv4/tcp_wmem   ----发送缓冲

8192 32768 6291456 （最小值 8KB，默认值 32KB，最大值 6MB）

修改接收缓冲区 

echo "8192 262144 6291456" | sudo tee /proc/sys/net/ipv4/tcp_rmem 

修改发送缓冲区 

echo "8192 32768 6291456" | sudo tee /proc/sys/net/ipv4/tcp_wmem 

编辑配置文件 

sudo vim /etc/sysctl.conf 

TCP 接收缓冲区 

net.ipv4.tcp_rmem = 8192 262144 6291456 

TCP 发送缓冲区 

net.ipv4.tcp_wmem = 8192 32768 6291456 

保存后应用配置 

sudo sysctl -p 

验证 

sysctl net.ipv4.tcp_rmem net.ipv4.tcp_wmem

2、如何查看MySQL服务器的本地tcp发送缓冲是否充足：

MySQL 中查询到数据后，发送给另一个服务器的过程中， MySQL所在的服务器上的是占用发送缓冲区，不占用接收缓冲区，但是另一个服务器作为接收方，是需要占用接收缓冲区的，所以大部分情况下在MySQL服务端需要调整发送缓冲区的参数，在客户端服务器上可以调整接收缓冲区的参数。

1）如果发现会话状态长时间处于Sending to client的状态，此时可以查看dmesg中有没有tcp out of  memory的关键字(dmesg | grep -i "tcp out of memory")，如果有此类的报错，可以适当减小 tcp_wmem 的最大值。

2）通过 ss 命令查看活跃 TCP 连接的发送缓冲区分配情况（ESTAB状态的连接是活跃的）

[@CentOS_txyz_92_52 ~]$ watch -n 1 "ss -tmi | grep skmem"

skmem:(r0,rb1106872,t0,tb32768,f0,w0,o0,bl0,d4)   

rb1106872:  表示当前连接的接收缓冲区大小 1M, （若接近 tcp_rmem 的第三个值（最大值），说明缓冲区可能不足。需要增大 rcp_wmem 的第三个值（最大值）；

tb32768：表示当前连接的发送缓冲区大小为 32KB（若接近 tcp_wmem 的第三个值（最大值），说明缓冲区可能不足。需要增大 tcp_wmem 的第三个值（最大值）；

3、数据库参数优化：

• 默认值为16KB，最大值为1MB，缓冲区的初始大小由 net_buffer_length 决定，但当需要传输的数据量超过初始大小时，缓冲区会自动增长；通常情况下，net_buffer_length 不需要调整，因为其默认值（16KB）对于大多数场景已经足够

• 缓冲区可以动态增长，但不能超过 max_allowed_packet 的限制，当缓冲区的数据发送完毕后，又会重置成net_buffer_length的大小；
• 如果你经常需要处理大查询（如批量插入或大结果集），可以适当增加 net_buffer_length 的值，以减少缓冲区扩展的频率，从而提高性能；
• 如果服务器内存有限，可以将 net_buffer_length 设置为更小的值（如 8KB），以减少初始内存占用；

• 大查询或大结果集：如果经常处理大查询或大结果集，可以适当增加 net_buffer_length 的值（如 32KB 或 64KB），以减少缓冲区扩展的频率，从而提高性能；

• mysqldump 场景：在使用mysqldump 备份大表时，可以临时增加 net_buffer_length 的值，以提高导出效率。

4、MySQL 客户端发送请求后，接收服务端返回结果的方式有两种：

在 MySQL 客户端中，有两种处理查询结果集的方式：本地缓存（mysql_store_result） 和 不缓存（mysql_use_result）,

本地缓存: 也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。

不缓存:  读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。

MySQL 客户端默认采用第一种方式，而如果加上–quick 参数，就会使用第二种不缓存的方式。采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。mysql_store_result 需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能；

针对大结果集合的查询场景，可以加上–quick 参数，使用不缓存（mysql_use_result）的方式来减少本地内存撑爆的风险，但是如果本地内存充足，并且本地处理逻辑比较复杂，可以尝试使用默认的本地缓存（mysql_store_result）的方式。

四、总结：

针对大结果集合的优化场景：

1、关注MySQL服务端的发送缓冲区的使用情况；

2、关注客户端服务器的接收缓冲区的使用情况；

3、可以适度调大net_buffer_length和 max_allowed_packet；