1

I'm running a Java application that creates 50 threads as part of a threadpool; each thread running in an object that reads 1000 unique rows at a time from a MySQL table. So each object has it's own SqlManager class I created which can open an Sql connection.

wait_timeout is 28800, which would be 8 hours (am surprised it is that long), so I assume that a connection once established, won't expire until 8 hours are over. My program though, runs for more than 8 hours, and I've increased the max_connections variable to 5000 too.

Currently I've programmed each thread object to check if 2 hours have elapsed, and if it has, then close the MySQL connection and re-open the connection.

Is this an appropriate technique of dealing with the problem, or does the connection expire only if it hasn't been used for any query for a long time? My program constantly queries the database all through those hours.

Improve this question
3
  • The connection only expires if it hasn't been used for any query for a long time. See here, which says "The number of seconds the server waits for activity on a noninteractive connection before closing it."
    – Robert Harvey
    Oct 1, 2016 at 14:49
  • What "problem" are you referring to?
    – Robert Harvey
    Oct 1, 2016 at 14:50
  • I've faced the "problem" with MongoDB, that when iterating a huge database, the connection times out. A senior told me the same can happen with MySQL. That's why I asked.
    – Nav
    Oct 1, 2016 at 16:22

2 Answers 2

Reset to default
2

If the problem is that your query functions/etc stop working as you've disconnected, write your own database wrapper and have the query functions error handler check to see if the database is disconnected, if it is, attempt to reconnect and put the query in an array with others to be executed on database connection success.

If you're lazy, just have it SELECT every hour or so and it shouldn't time out.

Improve this answer
1

Use connection pool, use it as your DataSource and every time you want to do some meaningfull unit of work get connection from that datasource, do what you need and close that connection (it will not close, it will be returned to the pool).

Leave long term connection maintance on connection pool (and if your application is web/EE application, you can configure that pool on server).

Connection pools usually can check whether connection is healthy (so eg. restarting database mean only temporary unavailability instead of from that time using invalid connections), keep it alive when not used (but you must return connection to it when you are not actually using it).

Using connection pool makes your program focus on what you want to do, instead of handling these problems yourself.

datasource tutorial: https://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html question about pools: https://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.