I have an issue where calling my Java Servlet very often in a short timeframe results in "Too many connections" errors for my MariaDB database. If if use the DB command show processlist;
, I see many connections in the Sleep state. These seem to linger around for about a minute (I did not time them, but they don't linger a really long time, just enough for my server to go crazy). I very methodically close()
my connections after using them, but I get MySQLNonTransientConnectionExceptions
depending on how many connections are open and how many I am trying to open.
I realize I can increase the number of open connections, but I am trying to do something better than that because I figure there will always be a ceiling for the number of connections I will need as I expect my application to perform fast and with many connections. Note that I am testing this right now with a super-fast select statement, so it's not like my query or code is taking a long time. I think that my connection closes after about 240 seconds - I determined this by watching the show processlist;
output. The show variables like "%timeout%";
command outputs wait_timeout
and interactive_timeout
with values of 28800. I am not sure how the math works between 28800 (seconds?) and 240 seconds.
I think what I should be doing is using non-persistent connections instead of persistent connections, but I cannot find documentation how to do that in Java with MariaDB. Below is the pseudo-code I have now for creating a connection which I assume is persistent. Any help is appreciated.
private static final
JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static final
DB_PARAMS = "zeroDateTimeBehavior=convertToNull";
public static Connection getConnected() {
try {
Class.forName(JDBC_DRIVER);
final Connection
conn = DriverManager.getConnection(
DB_URL + "/" + DATABASE_NAME + "?" + DB_PARAMS,
USERNAME,
PASSWORD);
return conn;
} catch (lots of exceptions handled) {
return null;
}
}