java code making extensive use of mysql

Question

I am currently running some java code which makes extensive calls to a mysql database. These calls are flooding the database and after a few hours of the code running I am getting a nested exception:

java.io.EOFException
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2572)


Last packet sent to the server was 8789 ms ago.
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2592)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2572)

Unfortunately I do not have much experience with this kind of problem, can anyone recommend ways to ensure all my queries can be processed?

I can appreciate this is a very broad question, but the queries themselves are not difficult, it is more the scale of the number of queries I am requesting.

Thanks

This sounds like a problem that may be helped by logging so you can see what your program is doing at various times, and in particular the program's state when it gets into trouble. Are you currently using logging for this app? — Hovercraft Full Of Eels, Aug 26 '12 at 03:03
I am using basic output logging yes, the problem is in the scope of the size of the users in the database, meaning it will fail at some arbitrary user due to hang in the sql database. `show processlist` will show the query which has caused the system to hang, but it is at an arbitrary point in the user set.. — user1220022, Aug 26 '12 at 03:06
Check the number of open connections that are there for your database. If you're opening an increasingly huge amount of connection, then I would suggest `connection pooling`. In the same context, what is the majority type of the queries to your DB? Are they `select` queries or you trying to perform database updates? — Sujay, Aug 26 '12 at 03:13

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

I would troubleshoot this at the OS/MySql/Web server level. In other words, this isn't a "Java" problem per se. It's probably much more a "system performance/system configuration" issue.

SUGGESTION:

1) You haven't specified your OS, Web server/application server. These are key.

2) Run basic performance checks (ps -eaf and iostat for Linux, Task Mgr/CPU/Memory/Networking stats for Windows). Pay particular attention to "netstat" (Linux and Windows).

3) Look at all the relevant logs (/var/log/messages etc for Linux; EventVwr, etc for Windows)

4) Check your OS, DB and Web server configuration settings.

5) Pay particular attention to possible "stale connections" and DB-related "connection pooling" parameters.

Check these links:

Communications link failure due to: java.io.EOFException <= Set Tomcat "autoReconnect=true" ... if you're even running Tomcat!
http://wordpress.transentia.com.au/wordpress/2009/09/30/tweaking-the-grails-datasource-to-cope-with-mysqls-foibles/
http://sacharya.com/grails-dbcp-stale-connections/ <= Tweak connection pooling parameters
http://communications-link-failure.blogspot.com/ <= Reduce mysql wait_timeout

edited May 23 '17 at 11:51

Community

1
1

answered Aug 26 '12 at 03:38

paulsm4

114,292
17
138
190

I am happy with my set up for 1-4, but I have read the linked articles and made updates. I will re-run my code now and hopefully get better results. Thanks and I will post back. – user1220022 Aug 26 '12 at 03:58
Q: What *is* your setup? Windows? Linux? Tomcat? JBoss? – paulsm4 Aug 26 '12 at 04:59
Q: Are you running a standalone Java applicaton? A Java servlet in a Tomcat container? An application server like JBoss or WebSphere? What *version(s)*? – paulsm4 Aug 26 '12 at 06:20
It's a standalone java app, no web interactions. Why? – user1220022 Aug 27 '12 at 01:35
1

Connection pooling and toying with the wait_timeout worked for me. Thanks Paul. – user1220022 Aug 27 '12 at 02:40

java code making extensive use of mysql

1 Answers1