MySQL JDBC StreamResult 和 net_write_timeout

MySQL JDBC StreamResult 和 net_write_timeoutMySQL JDBC 拉取数据的三种方式

MySQL JDBC 在从 MySQL 拉取数据的时候有三种方式：

1. 简单模式，也就是默认模式，数据都先要从MySQL Server发到client的OS TCP buffer，然后JDBC把 OS buffer读取到JVM内存中，读取到JVM内存的过程中憋着不让client读取，全部读完再通知inputStream.read(). 数据大的话容易导致JVM OOM
2. useCursorFetch=true，配合FetchSize，也就是MySQL Server把查到的数据先缓存到本地磁盘，然后按照FetchSize挨个发给client。这需要占用MySQL很高的IOPS（先写磁盘缓存），其次每次Fetch需要一个RTT，效率不高。
3. Stream读取，Stream读取是在执行SQL前设置FetchSize：statement.setFetchSize(Integer.MIN_VALUE)，同时确保游标是只读、向前滚动的（为游标的默认值），MySQL JDBC内置的操作方法是将Statement强制转换为：com.mysql.jdbc.StatementImpl，调用其方法：enableStreamingResults()，这2者达到的效果是一致的，都是启动Stream流方式读取数据。这个时候MySQL不停地发数据，inputStream.read()不停地读取。一般来说发数据更快些，很快client的OS TCP recv buffer就满了，这时MySQL停下来等buffer有空闲就继续发数据。等待过程中如果超过 net_write_timeout MySQL就会报错，中断这次查询。

从这里的描述来看，数据小的时候第一种方式还能接受，但是数据大了容易OOM，方式三看起来不错，但是要特别注意 net_write_timeout。

1和3对MySQL Server来说处理上没有啥区别，也感知不到这两种方式的不同。只是对1来说从OS Buffer中的数据复制到JVM内存中速度快，JVM攒多了数据内存就容易爆掉；对3来说JDBC一条条将OS Buffer中的数据复制到JVM(内存复制速度快)同时返回给execute挨个处理（慢），一般来说挨个处理要慢一些，这就导致了从OS Buffer中复制数据较慢，容易导致 TCP Receive Buffer满了，那么MySQL Server感知到的就是TCP 传输窗口为0了，导致暂停传输数据。

在数据量很小的时候方式三没什么优势，因为总是多一次set net_write_tiemout，也就是多了一次RTT。

MySQL timeout

1. Creates a statement by callingConnection.createStatement().
2. CallsStatement.executeQuery().
3. The statement transmits the Query to MySqlServer by using the internal connection.
4. The statement creates a new timeout-execution thread for timeout process.
5. For version 5.1.x, it changes to assign 1 thread for each connection.
6. Registers the timeout execution to the thread.
7. Timeout occurs.
8. The timeout-execution thread creates a connection that has the same configurations as the statement.
9. Transmits the cancel Query (KILL QUERY “connectionId“) by using the connection.

参考《揭秘JDBC超时机制》

net_read_timeout

The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client,net_read_timeoutis the timeout value controlling when to abort.

如下图，MySQL Server监听3017端口，195228号包 客户端发一个SQL 给 MySQL Server，但是似乎这个时候正好网络异常，30秒钟后（从 SQL 请求的前一个 ack 开始算，Server应该一直都没有收到），Server 端触发 net_read_timeout 超时异常（疑问：这里没有 net_read_timeout 描述的读取了一半的现象）

解决方案：建议调大 net_read_timeout 以应对可能出现的网络丢包

net_write_timeout

show processlist 看到的State的值一直处于“Sending to client”，说明SQL这个语句已经执行完毕，而此时由于请求的数据太多，MySQL不停写入net buffer，而net buffer又不停的将数据写入服务端的网络棧，服务器端的网络栈（socket send buffer）被写满了，又没有被客户端读取并消化，这时读数据的流程就被MySQL暂停了。直到客户端完全读取了服务端网络棧的数据，这个状态才会消失。

先看下net_write_timeout的解释：The number of seconds to wait for a block to be written to a connection before aborting the write. 只针对执行查询中的等待超时，网络不好，tcp buffer满了（应用迟迟不读走数据）等容易导致mysql server端报net_write_timeout错误，指的是mysql server hang在那里长时间无法发送查询结果。

报这个错就是RDS等了net_write_timeout这么久没写数据，可能是客户端卡死没有读走数据，也可能是从多个分片挨个拉取，还没开始拉第7片前面6片拉取耗时就超过了net_write_timeout。

案例：DRDS 到 MySQL 多个分片拉取数据生成了许多 cursor 并发执行,但拉数据的时候是串行拉取的,如果用户端拉取数据过慢会导致最后一个 cursor 执行完成之后要等待很久.会超过 MySQL 的 net_write_timeout 配置从而引发报错. 也就是最后一个cursor打开后一直没有去读取数据，直到MySQL Server 触发 net_write_timeout 异常

首先可以尝试在 DRDS jdbcurl 配置 netTimeoutForStreamingResults 参数,设置为 0 可以使其一直等待,或设置一个合理的值(秒).

从JDBC驱动中可以看到，当调用PreparedStatement的executeQuery() 方法的时候，如果我们是去获取流式resultset的话，就会默认执行SET net_write_timeout= ？ 这个命令去重新设置timeout时间。源代码如下：

而 this.connection.getNetTimeoutForStreamingResults() 默认是600秒，或者在JDBC连接串种通过属性 netTimeoutForStreamingResults 来指定。

netTimeoutForStreamingResults 默认值：

What value should the driver automatically set the server setting ‘net_write_timeout’ to when the streaming result sets feature is in use? Value has unit of seconds, the value “0” means the driver will not try and adjust this value.

一般在数据导出场景中容易出现 net_write_timeout 这个错误，比如这个错误堆栈：

或者：

特别注意

JDBC驱动报如下错误

Application was streaming results when the connection failed. Consider raising value of ‘net_write_timeout’ on the server. – com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of ‘net_write_timeout’ on the server.

不一定是net_write_timeout设置过小导致的，JDBC 在 streaming 流模式下只要连接异常就会报如上错误，比如：

• 连接被 TCP reset
• 连接因为某种原因(比如 QueryTimeOut、比如用户监控kill 慢查询) 触发 kill Query导致连接中断

比如出现内核bug，内核卡死不发包的话，客户端同样报 net_write_timeout 错误

max_allowed_packet: 单个SQL或者单条记录的最大大小

一些其他的 Timeout

connectTimeout：表示等待和MySQL数据库建立socket链接的超时时间，默认值0，表示不设置超时，单位毫秒，建议30000。 JDBC驱动连接属性

queryTimeout：超时后jdbc驱动触发新建一个连接来发送一个 kill 给DB

socketTimeout：JDBC参数，表示客户端发送请求给MySQL数据库后block在read的等待数据的超时时间，linux系统默认的socketTimeout为30分钟，可以不设置。socketTimeout 超时不会触发发kill，只会断开连接。

要特别注意socketTimeout仅仅是指等待socket数据时间，如果在传输数据那么这个值就没有用了。socketTimeout通过mysql-connector中的NativeProtocol最终设置在socketOptions上

static final int SO_TIMEOUT。Set a timeout on blocking Socket operations:

ServerSocket.accept();
SocketInputStream.read();
DatagramSocket.receive();

The option must be set prior to entering a blocking operation to take effect. If the timeout expires and the operation would continue to block,java.io.InterruptedIOExceptionis raised. The Socket is not closed in this case.

Statement Timeout：用来限制statement的执行时长，timeout的值通过调用JDBC的java.sql.Statement.setQueryTimeout(int timeout) API进行设置。不过现在开发者已经很少直接在代码中设置，而多是通过框架来进行设置。

max_execution_time：The execution timeout forSELECTstatements, in milliseconds. If the value is 0, timeouts are not enabled. MySQL 属性，可以set修改，一般用来设置一个查询最长不超过多少秒，避免一个慢查询一直在跑，跟statement timeout对应。

wait_timeoutThe number of seconds the server waits for activity on a noninteractive connection before closing it. MySQL 属性，一般设置tcp keepalive后这个值基本不会超时（这句话存疑 202110）。

On thread startup, the sessionwait_timeoutvalue is initialized from the globalwait_timeoutvalue or from the globalinteractive_timeoutvalue, depending on the type of client (as defined by theCLIENT_INTERACTIVEconnect option tomysql_real_connect()). See alsointeractive_timeout.

一般来说应该设置： max_execution_time/statement timeout < Tranction Timeout < socketTimeout

SocketTimeout

这个 httpclient 的bug就是在 TCP 连接握手成功(只受ConnectTimeout影响，SocketTimeout还不起作用)后，还需要进行 SSL的数据交换(HandShake)，但因为httpclient是在连接建立后(含 SSL HandShake)才设置的 SocketTimeout，导致在SSL HandShake的时候卡在了读数据，此时恰好还没设置SocketTimeout，导致连接永久卡死在SSL HandShake的读数据

所以代码的fix方案就是在建连接前就设置好 SocketTimeout。

一次PreparedStatement 执行

useServerPrepStmts=true&cachePrepStmts=true

5.0.5版本后的驱动 useServerPrepStmts 默认值是false；另外跨Statement是没法重用PreparedStatement预编译的，还需要设置cachePrepStmts 才可以。

对于打开预编译的URL（String url = “jdbc:mysql://localhost:3306/studb?useServerPrepStmts=true&cachePrepStmts=true“）获取数据库连接之后，本质是获取预编译语句pstmt = conn.prepareStatement(sql)时会向MySQL服务端发送一个RPC，发送一个预编译的SQL模板（驱动会拼接MySQL预编译语句prepare s1 from ‘selectfrom user where id = ?’），然会MySQL服务端会编译好收到的SQL模板，再会为此预编译模板语句分配一个serverStatementId发送给JDBC驱动，这样以后PreparedStatement就会持有当前预编译语句的服务端的serverStatementId,并且会把此 PreparedStatement缓存在当前数据库连接中，以后对于相同SQL模板的操作pstmt.executeUpdate()，都用相同的PreparedStatement，执行SQL时只需要发送serverStatementId*和参数，节省一次SQL编译, 直接执行。并且对于每一个连接(驱动端及MySQL服务端)都有自己的prepare cache,具体的源码实现是在com.mysql.jdbc.ServerPreparedStatement中实现。

根据SQL模板和设置的参数，解析成一条完整的SQL语句，最后根据MySQL协议，序列化成字节流，RPC发送给MySQL服务端

准备好需要发送的MySQL协议的字节流（sendPacket）后，就可以一路通过ConnectionImpl.execSQL –> MysqlIO.sqlQueryDirect –> MysqlIO.send – > OutPutStram.write把字节流数据通过Socket发送给MySQL服务器，然后线程阻塞等待服务端返回结果数据，拿到数据后再根据MySQL协议反序列化成我们熟悉的ResultSet对象。

案例

设置JDBC参数不合理（不设置的话默认值是：queryTimeout=10s，socketTimeout=10s），会导致在异常情况下，第二条get获得了第一条的结果，拿到了错误的数据，数据库则表现正常

socketTimeout触发后，连接抛CommunicationsException（严重异常，触发后连接应该断开）, 但JDBC会检查请求是否被cancle了，如果cancle就会抛出MySQLTimeoutException异常，这是一个普通异常，连接会被重新放回连接池重用（导致下一个获取这个连接的线程可能会得到前一个请求的response）。

queryTimeout（queryTimeoutKillsConnection=True–来强制关闭连接）会触发启动一个新的连接向server发送 kill id的命令，MySQL5.7增加了max_statement_time/max_execution_time来做到在server上直接检测到这种查询，然后结束掉。

jdbc 和 RDS之间 socket_timeout

jdbc驱动设置socketTimeout=1459，如果是socketTimeout触发客户端断开后，server端的SQL会继续执行，如果是client被kill则server端的SQL会被终止

对应抓包，没有 kill动作

CN 和 DN 间socket_timeout案例

设置CN到DN的socket_timeout为2秒，然后执行一个sleep

CN上抓包分析(stream 5是客户端到CN、stream6是CN到DN）如下，首先CN会计时2秒钟后发送quit给DN，然后断开和DN的连接，并返回一个错误给client，client发送quit断开连接：

CN完整报错堆栈：

应用和 DB 间丢包导致 keepalive 心跳失败

应用使用了 Druid 连接池来维护到 DB 间的所有长连接

应用和 DB 间丢包导致 keepalive 心跳失败，进而 OS会断开这个连接

一个连接归还给Druid连接池都要做清理动作，就是第一个红框的rollback/autocommit=1

归还后OS 层面会探活TCP 连接，DB(4381)多次后多次不响应后，OS 触发reset tcp断开连接，此时上次应用(比如Druid连接池、比如Tomcat)还不知道此连接在OS 层面已经断开

继续过来一个新连接，业务取到这个连接执行查询就会报如下错误：

这个错误就是因为OS层面连接断开了，并且断开了162秒(和截图时间戳能对应上)

对应的错误堆栈：

kill 案例kill mysql client

mysql client连cn执行一个很慢的SQL，然后kill掉mysql client

cn报错：

mysqld报错：

172.16.40.214是客户端IP

抓包看到CN收到mysql client发过来的fin，CN回复fin断开连接

CN会给DN在新的连接上发Kill Query（stream 1596），同时会在原来的连接(stream 583)上发fin，然后原来的连接收到DN的response（被kill），然后CN发reset给DN

下图是sleep 连接的收发包

Kill jdbc client

Java jdbc client被kill后没有错误堆栈，kill后触发socket.close(对应client发送fin断开连接），kill后server端SQL也被立即中断

抓包：

server端报错信息：

Statement timeout

statement会设置一个timer，到时间还没有返回结果就创建一个新连接发送kill query

server 端收到kill后终止SQL执行，抓包看到Server端主动提前返回了错误

参考资料

MySQL JDBC StreamResult通信原理浅析