MySQL Connector/J randomly hanging at com.mysql.jdbc.util.ReadAheadInputStream.fill
In the past months we struggled with large SELECT queries just get stuck at:
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
- locked com.mysql.jdbc.util.ReadAheadInputStream@cb9a81c
com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2494)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2949)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2938)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3481)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
- locked java.lang.Object@70cbccca
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:782)
- locked java.lang.Object@70cbccca
com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:625)
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:260)
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:260)
Whenever this happened we just restarted the Tomcat server and everything was fine again for some days or weeks. But today it struck us very hard so we finally took the time to hunt this down. It seems to be related to this bug report. Some comments suggested to use SQL_NO_CACHE with your queries.
A lot of people (including me) suggest to disable the MySQL query cache since it may cause severe problems. To disable the query cache at server startup, set the query_cache_size system variable to 0.
This is what we usually do, but one of our servers had query cache turned on. Disabling it solved this problem.
Improve performance on small hadoop clusters Integrating MySQL and Hadoop – or – A different approach on using CSV files in MySQL