投稿者: yuya

なんかMariaDBでmysql-connector-java-5.1.42-bin.jarでJDBCするときにコネクションを貼りまくって、executeUpdateでいっぱい更新しようとしたとき、なんかMariaDBがもういいっぱいだよ～（dirty page だよ～）みたいな感じでぜんぜんSQLのInsertができなくなった

最初はMariaDBの問題かと思って色々チューニングしたりしたけど、解決せず

原因はJDBCのexecuteUpdateでした

これ1秒に100リクエストとかめっちゃ送るときにいちいちコネクション貼り直したりする感じで効率が悪いっぽい

めっちゃいっぱい更新したいときは

executeBatch

を使いましょう

そうしたら恐ろしいほどクエリが改善した。

ついでにいうとBulk insertにするとより早いとおもう！

public void doSQL(String sql) {
start();
try {
stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

から

}
public void doSQLBig(String sql) {
start();
try {
//conn.setAutoCommit(false);
stmt.addBatch(sql);
int result[]=stmt.executeBatch();

for(int i=0;i<result.length;i++){
System.out.println(“result”+i+”: “+result[i]);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

に変更した

出てたエラー↓

at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.coin.java.web.SQL.doSQL(SQL.java:198)
at com.coin.java.web.CommonApi.insertBalanceSQL(CommonApi.java:3256)
at com.coin.java.web.RateCallback.getBalance(RateCallback.java:479)
… 1 more
Caused by: java.net.SocketException: Unrecognized Windows Sockets error: 0: recv failed
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(Unknown Source)
at java.net.SocketInputStream.read(Unknown Source)
at java.net.SocketInputStream.read(Unknown Source)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3008)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3469)
… 12 more
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.GeneratedConstructorAccessor61.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1187)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1182)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2377)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2361)
at com.coin.java.web.SQL.start(SQL.java:81)
at com.coin.java.web.SQL.doSQL(SQL.java:196)
at com.coin.java.web.CommonApi.insertBalanceSQL(CommonApi.java:3256)
at com.coin.java.web.RateCallback.getBalance(RateCallback.java:482)
at com.coin.java.web.RateCallback$4.run(RateCallback.java:166)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 22,566 milliseconds ago. The last packet sent successfully to the server was 22,566 milliseconds ago.
at sun.reflect.GeneratedConstructorAccessor60.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3459)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3900)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.coin.java.web.SQL.doSQL(SQL.java:198)
at com.coin.java.web.CommonApi.insertBalanceSQL(CommonApi.java:3256)
at com.coin.java.web.RateCallback.getBalance(RateCallback.java:479)
… 1 more