I have the following code that connects to a remote MYSQL server from Undertow.
static DataSource newDataSource(String uri, String user, String password) {
GenericObjectPool connectionPool = new GenericObjectPool();
connectionPool.setMaxActive(256);
connectionPool.setMaxIdle(256);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
uri, user, password);
// This constructor modifies the connection pool, setting its connection
// factory to this. (So despite how it may appear, all of the objects
// declared in this method are incorporated into the returned result.)
//
new PoolableConnectionFactory(connectionFactory, connectionPool, null,
null, true, true); //defaultReadOnly = true, defaultAutoCommit = true
return new PoolingDataSource(connectionPool);
}
}
final DataSource mysql = dbHelper.newDataSource(
properties.getProperty("mysql.uri"),
properties.getProperty("mysql.user"),
properties.getProperty("mysql.password"));
try (Connection connection = mysql.getConnection();
PreparedStatement statement = connection.prepareStatement(
"SELECT userid FROM test.table WHERE id >= "
+ m + " AND id <= " + n + ";",
ResultSet.CONCUR_READ_ONLY,
ResultSet.CLOSE_CURSORS_AT_COMMIT)) {
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
count += 1;
}
}
}
exchange.getResponseHeaders().put(Headers.CONTENT_TYPE, TEXT_PLAIN);
exchange.getResponseSender().send(buildTweet(count));
After the Undertow server runs for sometime, I receive the following error:
15/04/10 10:08:00 ERROR undertow.request: Blocking request failed HttpServerExchange{ GET /q6}
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2506)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2539)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2321)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.GeneratedConstructorAccessor4.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:75)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1148)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
at Project.Q6Handler.handleRequest(Q6Handler.java:39)
at io.undertow.server.Connectors.executeRootHandler(Connectors.java:177)
at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:727)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
I would like to know how to proceed ahead with closing the connections to
- Mysql datasource
- Connection
- Statement
The application has frequent read requests (10000 READ requests per second) How should I proceed ahead?
I thought of using mysql.getConnection().close(); right before setting the exchange headers but need to be sure to change code on the production system.
Thanks!
