database - 我的 H2/C3PO/Hibernate 设置似乎没有保留准备好的语句？

Question

我发现我的数据库是我的应用程序的瓶颈，作为其中的一部分，准备好的语句似乎没有被重用。

比如这里我用的方法

public static CoverImage findCoverImageBySource(Session session, String src)
{
    try
    {
        Query q = session.createQuery("from CoverImage t1 where t1.source=:source");
        q.setParameter("source", src, StandardBasicTypes.STRING);
        CoverImage result = (CoverImage)q.setMaxResults(1).uniqueResult();
        return result;
    }
    catch (Exception ex)
    {
        MainWindow.logger.log(Level.SEVERE, ex.getMessage(), ex);
    }
    return null;
}

但是使用 Yourkit profiler 它说

com.mchange.v2.c3po.impl.NewProxyPreparedStatemtn.executeQuery() 计数 511com.mchnage.v2.c3po.impl.NewProxyConnection.prepareStatement() 计数 511

而且我认为 prepareStatement() 调用的次数应该更低，因为看起来我们每次都创建一个新的准备好的语句而不是重复使用。

https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html

我正在使用 C3po 连接 poolng，这让事情有点复杂，但据我所知，我已经正确配置了它

公共(public)静态配置 getInitializedConfiguration(){ //见https://www.mchange.com/projects/c3p0/#hibernate-specific 配置config = new Configuration();

config.setProperty(Environment.DRIVER,"org.h2.Driver");
config.setProperty(Environment.URL,"jdbc:h2:"+Db.DBFOLDER+"/"+Db.DBNAME+";FILE_LOCK=SOCKET;MVCC=TRUE;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=50000");
config.setProperty(Environment.DIALECT,"org.hibernate.dialect.H2Dialect");
System.setProperty("h2.bindAddress", InetAddress.getLoopbackAddress().getHostAddress());
config.setProperty("hibernate.connection.username","jaikoz");
config.setProperty("hibernate.connection.password","jaikoz");
config.setProperty("hibernate.c3p0.numHelperThreads","10");
config.setProperty("hibernate.c3p0.min_size","1");
//Consider that if we have lots of busy threads waiting on next stages could we possibly have alot of active
//connections.
config.setProperty("hibernate.c3p0.max_size","200");
config.setProperty("hibernate.c3p0.max_statements","5000");
config.setProperty("hibernate.c3p0.timeout","2000");
config.setProperty("hibernate.c3p0.maxStatementsPerConnection","50");
config.setProperty("hibernate.c3p0.idle_test_period","3000");
config.setProperty("hibernate.c3p0.acquireRetryAttempts","10");
//Cancel any connection that is more than 30 minutes old.
//config.setProperty("hibernate.c3p0.unreturnedConnectionTimeout","3000");
//config.setProperty("hibernate.show_sql","true");
//config.setProperty("org.hibernate.envers.audit_strategy", "org.hibernate.envers.strategy.ValidityAuditStrategy");
//config.setProperty("hibernate.format_sql","true");

config.setProperty("hibernate.generate_statistics","true");
//config.setProperty("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
//config.setProperty("hibernate.cache.use_second_level_cache", "true");
//config.setProperty("hibernate.cache.use_query_cache", "true");
addEntitiesToConfig(config);
return config;

使用 H2 1.3.172、Hibernate 4.3.11 和该 hibernate 版本对应的 c3po

我们有可重现的测试用例

HibernateStats

• HibernateStatistics.getQueryExecutionCount() 28
• HibernateStatistics.getEntityInsertCount() 119
• HibernateStatistics.getEntityUpdateCount() 39
• HibernateStatistics.getPrepareStatementCount() 189

分析器，方法计数

• GooGooStaementCache.aquireStatement() 35
• GooGooStaementCache.checkInStatement() 189
• GooGooStaementCache.checkOutStatement() 189
• NewProxyPreparedStatement.init() 189

我不知道我应该将什么算作准备语句的创建而不是重用现有的准备语句？

我还尝试通过添加 c3p0 记录器启用 c3p0 日志记录，并使其在我的 LogProperties 中使用相同的日志文件，但没有效果。

            String logFileName = Platform.getPlatformLogFolderInLogfileFormat() + "songkong_debug%u-%g.log";
            FileHandler fe = new FileHandler(logFileName, LOG_SIZE_IN_BYTES, 10, true);
            fe.setEncoding(StandardCharsets.UTF_8.name());
            fe.setFormatter(new com.jthink.songkong.logging.LogFormatter());
            fe.setLevel(Level.FINEST);

            MainWindow.logger.addHandler(fe);

            Logger c3p0Logger = Logger.getLogger("com.mchange.v2.c3p0");
            c3p0Logger.setLevel(Level.FINEST);
            c3p0Logger.addHandler(fe);

Answer 1

现在我终于让基于 c3p0 的日志记录工作了，我可以确认@Stevewaldman 的建议是正确的。

如果你启用

public static  Logger  c3p0ConnectionLogger = Logger.getLogger("com.mchange.v2.c3p0.stmt");
c3p0ConnectionLogger.setLevel(Level.FINEST);
c3p0ConnectionLogger.setUseParentHandlers(false);

然后你得到表单的日志输出

24/08/2019 10.20.12:BST:FINEST: com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache ----> CACHE HIT
24/08/2019 10.20.12:BST:FINEST: checkoutStatement: com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 347; checked out: 1; num connections: 13; num keys: 347
24/08/2019 10.20.12:BST:FINEST: checkinStatement(): com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 347; checked out: 0; num connections: 13; num keys: 347

在缓存命中时明确说明。当没有缓存命中时，你不会得到第一行，但会得到另外两行。

这是使用 C3p0 9.2.1