Buffer MySQL queries or make multiple connections?

Question

so I am currently working on a java application that is supposed to log specific events into a database. I expect that there will be at most 15 to 20 inserts per minute, basically I was wondering if I should make a new connection for every insert statement or keep one open as long as the application is running.

What I am doing is:

public void logEvent(MyCustomEvent e) {
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {
        con = DriverManager.getConnection(url, user, password);
        st = con.createStatement();

        st.executeUpdate("INSERT INTO Table(" + e.data + ");");

    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(MySQLConnector.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);

    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(MySQLConnector.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
}

Is there no problem in making a new connection every time or can/should I buffer the inputs somehow?

score 1 · Accepted Answer · answered Jun 24 '13 at 12:41

Making connections is expensive so it's probably best not to keep making them. However, holding one connection open all the time has its own problems ( what happens if it closes for some reason? )

Why not have a look at database connection pooling - google will show up several competing connection pool implementations for you. You'll get the best of both worlds. To your application, the connection will appear to be permanently open, but if the connection fails for some reason, the connection pool will handle re-opening it for you.

score 1 · Answer 2 · edited May 23 '17 at 12:23

You should keep the connection open and reuse it. Starting up and tearing down a JDBC connection is expensive, especially if you are securing it with SSL.

The other option is to use what is known as a connection pool, where the application doesn't create the JDBC connections directly, but acquires one from a pool of preciously opened connections. When it is done, it returns the connection to the pool.

Your database provider could provide a connection pool library, or you could use something like C3PO. This Q&A lists some other options: Java JDBC connection pool library choice in 2011/2012?

There is a gotcha with keeping connections open for a long time in MySQL. The problem is that MySQL has a default "idle connection" timeout of a few hours (10 or so I think). So if your application sits idle for long periods of time, it can find that its connection is broken. A connection pool may take care of reconnecting for you. Otherwise, the standard workaround for this problem is to increase the default timeout to something REALLY large. For example: MySQL connection timeout

Buffer MySQL queries or make multiple connections?

2 Answers2