本文先描述了我的项目遇到的问题,以及解决的思路,引出了部分MySQL网络协议的分析(代码样例跟我的项目无关,仅供参考)。行文有点乱,见谅
背景
项目中使用mysql-connector-java读取MySQL数据,卡在SocketInputStream.socketRead0
"xx-6" #135 prio=5 os_prio=0 tid=0x00007fc4e047a800 nid=0x4c80 runnable [0x00007fc4c3ffe000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.net.SocketInputStream.read(SocketInputStream.java:171)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
- locked <0x00000006c00229c0> (a com.mysql.jdbc.util.ReadAheadInputStream)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1994)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:611)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:386)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2076)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1451)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1787)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
- locked <0x00000006c0022fe0> (a java.lang.Object)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
- locked <0x00000006c0022fe0> (a java.lang.Object)
// ...
环境
jdk1.8.0_201
MySQL 5.5.34-log
mysql-connector-java-bin-5.0.7.jar
centos 6
其他信息未记录
分析
(1)CPU、内存、负载占用率都不高,有足够磁盘空间
(2)试过内网IP,可排除网络原因
(3)jstack显示当前线程RUNNABLE,MySQL调用show processlist显示SLEEP
(4)数据库链接未加任何参数,未使用游标useCursorFetch=true,代码中只使用了setFetchSize(100)(实际上不会生效)
初步方案
(1)读取的数据量可能过大:增大读缓冲tcpRcvBuf=1024000,不使用order by
(2)三次重试:增加socketTimeout=60000,超时自动重新创建Connection
(3)打印网络协议:加traceProtocol=true,用于跟踪问题
初步方案的结果
(1)没啥用,只是读数据快了点
(2)重试之后,就正常了
(3)一直没机会试,内网复现不了;外网加了三次重试后,也不复现
解决问题过程中,了解到MySQL网络协议。因为一直没办法拿到线上的协议数据,所以这里用样例数据
样例代码
public class MysqlJdbcTest {
private Connection connection;
public static void main( String[] args ) throws Exception {
MysqlJdbcTest test = new MysqlJdbcTest();
test.testQuery();
System.out.println( "Done." );
}
public Connection getConnection() throws SQLException {
if ( connection != null ) {
return connection;
}
try {
Class.forName( "com.mysql.jdbc.Driver" );
} catch ( ClassNotFoundException e ) {
throw new IllegalStateException( e );
}
String url = "jdbc:mysql://localhost:3306/common?";
//not setting useCursorFetch , it will work
url += "useCursorFetch=true"; // 使用游标
url += "&traceProtocol=true"; // 打印网络协议
String user = "root";
String pwd = "123456";
return connection = DriverManager.getConnection( url, user, pwd );
}
public void createData() throws SQLException {
getConnection().createStatement().executeUpdate( "drop table if exists CURSOR_TEST" );
getConnection().createStatement().executeUpdate( "create table