0

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;
    }
}
  • I think the answer to [this question](http://stackoverflow.com/questions/4209454/a-persistent-connection-with-jdbc-to-mysql) might be what you are looking for. – ujulu Aug 01 '16 at 06:05
  • Don't allow Java to create so many connections that it threatens max_connections. – Rick James Aug 01 '16 at 06:25

0 Answers0