如何以编程方式确定连接池大小？

Question

有没有办法以编程方式确定数据库连接池大小（已使用的连接/连接池中剩余的连接）？我们使用 Hibernate 和 C3P0。

我们在连接到数据库时遇到问题。抛出以下异常，并且数据未保存在数据库中。

1005,MA,19/09/11 09:39:14,com.novosys.gtw.business.frontend.SnapshotMessageBusiness.save, Major: Cannot open connection
org.hibernate.exception.GenericJDBCException: Cannot open connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
    at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
    at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354)
    at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:342)
    at $Proxy0.beginTransaction(Unknown Source)
    at com.novosys.gtw.util.base.BaseBusiness.save(BaseBusiness.java:199)
    at com.novosys.gtw.business.backend.receivesnapshotmessage.filter.SaveMessageFilter.decode(SaveMessageFilter.java:102)
    at org.apache.mina.filter.codec.demux.DemuxingProtocolCodecFactory$ProtocolDecoderImpl.doDecode(DemuxingProtocolCodecFactory.java:292)
    at org.apache.mina.filter.codec.CumulativeProtocolDecoder.decode(CumulativeProtocolDecoder.java:133)
    at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:158)
    at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
    at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
    at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
    at com.novosys.gtw.business.backend.receivesnapshotmessage.filter.WhitelistFilter.messageReceived(WhitelistFilter.java:231)
    at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
    at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
    at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
    at com.novosys.gtw.business.backend.receivesnapshotmessage.filter.MoniterFilter.messageReceived(MoniterFilter.java:92)
    at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
    at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
    at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
    at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java:220)
    at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(ExecutorFilter.java:264)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
    at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)
    at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:78)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    ... 31 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
    ... 34 more

我们尝试通过增加连接池大小并增加连接数来解决这个问题。 MySQL 级别可用的连接数，但没有用。我们现在正在尝试对其进行排序，看看是由于连接池大小还是由于 MySQL 连接大小所致。我们想记录没有。连接池大小中可用/正在使用的连接数，但无法从谷歌获得任何帮助。

环境：Java、Hibernate、C3P0、MySQL

Session session = null;
Transaction transaction = null;

try {
            session = HibernateUtil.getSessionFactory(datasource).getCurrentSession();
            transaction = session.beginTransaction();
            // db save called here
            session.getTransaction().commit();
        } catch (Exception e) {
            Logger.write(LoggerConstant.MAJOR_ERROR, e.getMessage(), e, methodName);
        } finally {
            try {
                if ((transaction != null) && (transaction.isActive())) {
                    transaction.rollback();
                }
            } catch (Exception e) {
                Logger.write(LoggerConstant.CRITICAL_ERROR, e.getMessage(), e, methodName);
            }
            try {
                if ((session != null) && (session.isOpen())) {
                    session.close();
                }
            } catch (Exception e) {
                Logger.write(LoggerConstant.CRITICAL_ERROR, e.getMessage(), e, methodName);
            }
        }

Answer 1

我不认为您的问题本身是连接池，但更普遍的是连接泄漏。此问题通常与使用 HibernateDaoSupport.getSession() 未与 HibernateDaoSupport.releaseSession() 正确配对有关。一般来说，您想要这样的东西

public SomeObject getSomething() 
{ 
    Session session = null;
    try 
    {
        session = this.getSession();
        Query query = session.createSQLQuery("SELECT * FROM SomeTable WHERE SomeClause").addEntity(SomeObject.class); 

        // extract object from query 

        return someObject; 
    } 
    finally 
    {
        if (session != null) 
            this.releaseSession(session);
    }        
}

可以通过使用 HibernateCallback 来自动化。您可以通过向 this.getHibernateTemplate().executeFind 提供查询来完成此操作，该查询将在 Hibernate 中使用具有自动资源管理功能的会话。

Answer 2

除了 ex0du5 所建议的之外，异常跟踪还建议以下内容：

Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)

• 这意味着连接池无法从 DATABASE 获取新连接。
• 请检查 MySQL 日志是否有任何错误。
• 检查 mysql 配置中的最大连接池大小和最大连接数设置。 （连接池大小不太可能大于 mysql 配置上的最大连接，但请确保这一点）

另外 有一种方法可以监控C3P0连接池的所有参数（包括最大连接设置）。

Answer 3

要正确配置连接池大小，您需要有指标来调查连接使用模式。

FlexyPool 旨在帮助您确定正确的连接池大小，因为它可以监控以下指标：

• 并发连接数直方图
• 并发连接请求数直方图
• 数据源 连接获取时间直方图
• 连接租用时间直方图
• 最大池大小直方图
• 总连接获取时间直方图
• 溢出池大小直方图
  重试次数直方图

您可以查看以下文章：

• FlexyPool，反应式连接池< /a>

• 专业连接池大小调整

• 简单的可扩展性方程