休眠：C3p0池配置使整个服务器速度变慢。 [英] Hibernate : C3p0 pool configuration slowing down entire server.

问题描述

我正在开发一个Spring-MVC应用程序，在该应用程序中，我们使用Hibernate和c3p0进行数据库事务和连接池。在大多数情况下，它的确运行良好，没有问题。但是在某些情况下，我必须在当前事务中复制很多对象和文件。发生这种情况时，整个服务器速度变慢，最后我开始得到无法回滚异常的信息。我的c3p0设置有问题吗？谢谢。

I am working on a Spring-MVC application in which we are using Hibernate and c3p0 for Database transactions and connection pooling. Most of the time it works really good, no issues. But in certain situations I have to copy a lot of objects and files in the current transaction. When that happens, the entire server slows down and finally I start getting could not rollback exception . Anything wrong with my c3p0 settings? Thank you.

pom.xml：

 <!--- Hibernate dependencies -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-c3p0</artifactId>
            <version>4.3.9.Final</version>
        </dependency>

root-context.xml：

root-context.xml :

<beans:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
            destroy-method="close">
    <beans:property name="driverClassName" value="org.postgresql.Driver"/>
    <beans:property name="url"
                    value="jdbc:postgresql://localhost:PORT/DB_NAME"/>
    <beans:property name="username" value="USERNAME"/>
    <beans:property name="password" value="PASSWORD"/>
    <beans:property name="removeAbandoned" value="true"/>
    <beans:property name="removeAbandonedTimeout" value="20"/>
    <beans:property name="defaultAutoCommit" value="false"/>
</beans:bean>

<!-- Hibernate 4 SessionFactory Bean definition -->
<beans:bean id="hibernate4AnnotatedSessionFactory"
            class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <beans:property name="dataSource" ref="dataSource"/>
    <beans:property name="packagesToScan" value="com.ourapp.spring.model"/>
    <beans:property name="hibernateProperties">
        <beans:props>
            <beans:prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQL9Dialect</beans:prop>
            <beans:prop key="hibernate.show_sql">false</beans:prop>
               <!--<beans:prop key="hibernate.jdbc.batch_size">1000</beans:prop>-->
            <beans:prop key="hibernate.hbm2ddl.auto">update</beans:prop>
            <beans:prop key="cache.use_second_level_cache">true</beans:prop>
            <beans:prop key="cache.use_query_cache">true</beans:prop>
            <beans:prop key="hibernate.order_updates">true</beans:prop>
            <beans:prop key="show_sql">false</beans:prop>
            <beans:prop key="hibernate.c3p0.min_size">1</beans:prop>
            <beans:prop key="hibernate.c3p0.max_size">750</beans:prop>
            <beans:prop key="hibernate.c3p0.acquire_increment">1</beans:prop>
            <beans:prop key="hibernate.c3p0.idle_test_period">1000</beans:prop>
            <beans:prop key="hibernate.c3p0.max_statements">150</beans:prop>
            <beans:prop key="hibernate.c3p0.timeout">1200</beans:prop>
            <beans:prop key="hibernate.connection.release_mode">auto</beans:prop>
        </beans:props>
    </beans:property>

</beans:bean>

谢谢。

推荐答案

对于初学者来说，您使用C3P0并不是仅仅因为您已经配置了 org.apache.commons .dbcp.BasicDataSource 作为 DataSource ，并将其注入到 LocalSessionFactoryBean 中。基本上，所有 hibernate.c3p0 设置均无用，因为它们将被忽略。

For starters you aren't using C3P0 simply because you have configured the org.apache.commons.dbcp.BasicDataSource as a DataSource and are injecting that into your LocalSessionFactoryBean. This basically renders all the hibernate.c3p0 settings useless as they will be ignored.

接下来，您在处理大量数据时遇到问题，我非常怀疑问题是您的 DataSource 还是连接池而是处理实体的方式以及如何配置Hibernate。

Next you have issues with processing large amounts of data and I highly doubt the issue is your DataSource or connection pool but rather the way you are processing your entities and how you have configured Hibernate.

要加快批处理的速度，您希望将每x条记录刷新到数据库并清除第一级缓存。您可能想知道为什么要这样做。这与Hibernate的工作方式有关，当您持久保存实体时，Hibernate会将其添加到第一级缓存（ Session 或如果是JPA，则为EntityManager 。每次将项目添加到第一级缓存时，它将对第一级缓存中的所有实体进行脏检查，以确定是否需要刷新某些内容。现在，这对于前几个实体将很快，但是将变得越来越慢。

To speedup batch processing you want to flush every x records to the database and clear the first level cache. Why do you want to do this you might wonder. It has all to do with how Hibernate works, when you persist an entity what Hibernate does it will add it to the first level cache (the Session or EntityManager in case of JPA). Each time you add an item to the first level cache it will do a dirty check on ALL of the entities in the first level cache to determine if something needs to be flushed. Now this will be fast for the first few entities but will become increasingly slower and slower.

让配置和编码的批处理大小为50。

Lets configure and code things for a batch size of 50.

首先，您要配置休眠模式适当的批处理大小，并且您要订购插入和更新语句。如果您这样做，则可以从JDBC现在可以进行批处理更新的事实中受益（即，一个插入或更新语句可以修改50条记录，而不是50条单个插入/更新语句）。

First you want to configure hibernate to have a proper batch size and you want to order the insert and update statements. If you do this you can benefit from the fact that JDBC can now do a Batch Update (i.e. one insert or update statement to modify 50 records instead of 50 single insert/update statement).

<beans:bean id="hibernate4AnnotatedSessionFactory"
            class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <beans:property name="dataSource" ref="dataSource"/>
    <beans:property name="packagesToScan" value="com.ourapp.spring.model"/>
    <beans:property name="hibernateProperties">
        <beans:props>
            <beans:prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQL9Dialect</beans:prop>
            <beans:prop key="hibernate.show_sql">false</beans:prop>
               <!--<beans:prop key="hibernate.jdbc.batch_size">1000</beans:prop>-->
            <beans:prop key="hibernate.hbm2ddl.auto">update</beans:prop>
            <beans:prop key="hibernate.cache.use_second_level_cache">true</beans:prop>
            <beans:prop key="hibernate.cache.use_query_cache">true</beans:prop>
            <beans:prop key="hibernate.jdbc.batch_size">50</beans:prop>
            <beans:prop key="hibernate.order_inserts">true</beans:prop>
            <beans:prop key="hibernate.order_updates">true</beans:prop>
            <!-- If you use versioned entities set this to true as well -->
            <beans:prop key="hibernate.jdbc.batch_versioned_data">true<beans:prop> 
        </beans:props>
    </beans:property>
</beans:bean>

代码修改

Code Modification

public void yourLargeDataSetProcessingMethod() {
    Session session = sessionFactory.getCurrentSession();

    int i = 0;
    for (YourItem item : List<YourItem> items) {
        i++:
        // Here will be processing / creation

        if (i % 50 == 0) {
            session.flush();
            session.clear();
        }
    }
    session.flush();
    session.clear();
} 

这可能会加快处理速度和数据库锁定。

This will probably speed up your processing and database locking.

最后一点，而不是Commons DBCP或C3P0，我建议使用 HikariCP 作为连接池。它很小，非常快，并且维护活跃（而C3P0已经休眠了一段时间）。

A final note, instead of Commons DBCP or C3P0 I would suggest to use HikariCP as the connection pool. It is very small and very fast and actively maintained (whereas C3P0 has been dormant for quite some time already).

此处是一个不错的资源（包含基准测试等），用于说明每种设置的作用以及添加或添加去除了明智的性能以及如何正确配置功能。

Here is a nice resource (with benchmarks etc.) on what each setting does and add or removes performance wise and how to configure things properly.

这篇关于休眠：C3p0池配置使整个服务器速度变慢。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！