java - 关闭 Undertow 上的 MYSQL 连接

Question

我有以下代码从 Undertow 连接到远程 MYSQL 服务器。

    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));

Undertow 服务器运行一段时间后，我收到以下错误:

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)

我想知道如何继续关闭与

的连接

1. Mysql 数据源
2. 连接
3. 声明

应用程序有频繁的读取请求(每秒 10000 个 READ 请求)我应该如何继续？

我想在设置交换 header 之前使用mysql.getConnection().close();，但需要确保更改生产系统上的代码。

谢谢!

Answer 1

如果有人想知道:我将 MySQL 服务器中的 max_connections 重置为 256，现在一切都按预期运行。