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
– Hovercraft Full Of Eels, Commented Aug 26, 2012 at 3: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
– user1220022, Commented Aug 26, 2012 at 3: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
– Sujay, Commented Aug 26, 2012 at 3:13

Community · Accepted Answer · 2017-05-23 11:51:44Z

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, 2017 at 11:51

CommunityBot

11 silver badge

answered Aug 26, 2012 at 3:38

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1220022 Over a year ago

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.

paulsm4 Over a year ago

Q: What is your setup? Windows? Linux? Tomcat? JBoss?

paulsm4 Over a year ago

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)?

user1220022 Over a year ago

It's a standalone java app, no web interactions. Why?

user1220022 Over a year ago

Connection pooling and toying with the wait_timeout worked for me. Thanks Paul.

Collectives™ on Stack Overflow

java code making extensive use of mysql

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related