Announcement Announcement Module
No announcement yet.
DataSource suggestion Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • DataSource suggestion

    Hi All,

    I need to run the batch contain more than 10 lakhs records. Total number of records are split into different list size and each list are executed in different threads.
    Example. total Record count = 3000, Total Thread count = 3
    List1 contain [1 -500 ] - Thread1
    List2 contain [501 - 1000] - Thread2
    List3 contain [1001 - 1500] - Thread3
    List4 contain [1501 - 2000] -
    List5 contain [2001 - 2500] -
    List6 contain [2501 - 3000] ....
    [Completed thread will pick the next list and process.]

    Currently i am using BasicDataSource. I have configure the MAX_Active connection to 100. So N number of connections are opened.
    I have tried DriverManagerDataSource also. The Number of connections opened in the DriverManagerDataSource is less compared to the BasicDataSource.

    Please suggest which dataSouce to use.
    I am using Spring Batch 2.1, Hibernate, Sql server 2005.

    Thanks for your reply in Advance.

  • #2
    NEVER use DriverManagerDataSource in production , unless you want performance issues. YOu want a connection pool (which commons dbcp is) DriverManagerDataSource isn't a connection pool it opens and closes a connection to the database when requested, and opening/closing a database connection is an expensive operation...


    • #3
      you can try it with c3p0:JDBC DataSources/Resource Pools


      • #4
        Thanks for your replay Marten.
        I have the following doubts. Can please clarify the same?

        1. How to manage the number of connection in this case. In Production we are planning to run 20 Threads in 2 to 3 machines.

        2. In year end we will have millions of records to process. Openning N number of connection (i.e more than 100 connections) in the pool is a correct? It will not create performance hit?

        3. In a Main thread i have loaded all beans. from the main thread i have started a child thread and call the
        launcher.start(jobName, jobParametersString). In this case 2 connections will be opened? one for Main Thread and another for child thread?

        4. How the connections are managed by the HibernateTransactionManger and SpringBatch using DataSource?


        • #5
          Hi Michael.
          I have made the following configuring changes in my hibernateProperties files.


          <property name="hibernate.c3p0.min_size">5</property>
          <property name="hibernate.c3p0.max_size">20</property>
          <property name="hibernate.c3p0.timeout">300</property>
          <property name="hibernate.c3p0.max_statements">50</property>
          <property name="hibernate.c3p0.idle_test_period">3000</property>


          I have kept the BasicDataSouce, Max_Acticve connection to default (i.e dafault Max_Active is 8). I have started 10 threads to process my lists. Batch get hang( Records are not Processing.)


          • #6
            please post your complete spring configuration, right now it looks like you configured some hibernate properties for c3p0 but still use a BasicDataSource


            • #7
              Please find the completed spring configuration file for my application.


              <?xml version="1.0" encoding="UTF-8"?>
              <beans xmlns=""
              xmlns:aop="" xmlns:tx=""
              xmlns="" xmlns:xsi=""

              <!-- Spring job launcher used by the Spring framework to start the jobs. -->
              <bean id="jobLauncher"
              class="org.springframework.batch.core.launch.suppo rt.SimpleJobLauncher">
              <property name="jobRepository">
              <ref bean="jobRepository" />

              <!-- Job registry post processor configuration used by the Spring framework -->
              <property name="jobRegistry">
              <ref bean="jobRegistry" />

              <!-- JobRespository configuration used by the Spring framework -->
              <bean id="jobRepository"
              class="org.springframework.batch.core.repository.s upport.JobRepositoryFactoryBean"
              p:dataSource-ref="dataSource" p:transactionManager-ref="coreTransactionManager"
              p:isolationLevelForCreate="ISOLATION_READ_COMMITTE D" />

              <!-- JobOperator configuration used by the Spring framework -->
              <bean id="jobOperator"
              class="org.springframework.batch.core.launch.suppo rt.SimpleJobOperator"
              p:jobLauncher-ref="jobLauncher" p:jobExplorer-ref="jobExplorer"
              p:jobRepository-ref="jobRepository" p:jobRegistry-ref="jobRegistry" />

              <!-- JobExplorer configuration used by the Spring framework -->
              <bean id="jobExplorer"
              class="org.springframework.batch.core.explore.supp ort.JobExplorerFactoryBean"
              p:dataSource-ref="dataSource" />

              <!-- JobRegistry configuration used by the Spring framework -->
              <bean id="jobRegistry"
              class="org.springframework.batch.core.configuratio" />

              <!-- For Application -->
              <bean id="transactionManager"
              class="org.springframework.orm.hibernate3.Hibernat eTransactionManager"
              <property name="sessionFactory">
              <ref bean="batchSessionFactory" />

              <!-- For Spring batch -->
              <bean id="coreTransactionManager"
              class="org.springframework.jdbc.datasource.DataSou rceTransactionManager"
              <property name="dataSource">
              <ref bean="dataSource" />

              <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
              <property name="driverClassName" value=" r" />
              <property name="url"
              <property name="username" value="uesername" />
              <property name="password" value="password" />

              <bean id="batchSessionFactory"
              class="org.springframework.orm.hibernate3.LocalSes sionFactoryBean">
              <property name="mappingLocations">
              <!-- Mapping file locations -->
              <property name="hibernateProperties">
              <prop key="hibernate.dialect">org.hibernate.dialect.SQLS erverDialect
              <prop key="hibernate.show_sql">false</prop>
              <prop key="hibernate.cache.use_second_level_cache">true</prop>
              <prop key="hibernate.cache.generate_statistics">true</prop>
              <prop key="hibernate.current_session_context_class">thre ad</prop>
              <prop key="hibernate.transaction.auto_close_session">tru e</prop>
              <!-- AS Per Michael suggestion I have added below configuration -->
              <prop key="hibernate.c3p0.min_size">5</prop>
              <prop key="hibernate.c3p0.max_size">20</prop>
              <prop key="hibernate.c3p0.timeout">300</prop>
              <prop key="hibernate.c3p0.max_statements">50</prop>
              <prop key="hibernate.c3p0.idle_test_period">3000</prop>
              <property name="dataSource">
              <ref bean="dataSource" />



              • #8
                from stackoverflow: configuring the built-in c3p0 pooling in hibernate using spring

                <bean id="dataSourceGlobal" class="com.mchange.v2.c3p0.ComboPooledDataSource"
                        <property name="driverClass" value="${driver}" />
                        <property name="jdbcUrl" value="${server}" />
                        <property name="user" value="${user}" />
                        <property name="password" value="${passw}" /> 


                • #9
                  Hi Michael. Thanks a lot for your reply.

                  As specified in the stackoverflow: configuring the built-in c3p0 pooling in hibernate using spring

                  I have modified the Hibernate Properties .

                  <prop key="hibernate.c3p0.min_size">2</prop>
                  <prop key="hibernate.c3p0.max_size">5</prop>
                  <prop key="hibernate.c3p0.timeout">600</prop>
                  <prop key="hibernate.c3p0.max_statements">0</prop>
                  <prop key="hibernate.c3p0.idle_test_period">300</prop>
                  <prop key="hibernate.c3p0.acquire_increment">1</prop>
                  <prop key="hibernate.connection.driver_class">com.micros oft.sqlserver.jdbc.SQLServerDriver</prop>
                  <prop key="hibernate.connection.url">url</prop>
                  <prop key="hibernate.connection.username">username</prop>
                  <prop key="hibernate.connection.password">password</prop>
                  As per the configuration the max pool size is 5 but N number of connection is opened. Please find the screenshot for your reference.
                  Attached Files


                  • #10
                    As specified in the stackoverflow: configuring the built-in c3p0 pooling in hibernate using spring
                    and i am almost 100% sure you are not getting a c3p0 datasource, because Hibernate is configured by Spring

                    if you would use hibernate pure, i bet it would work this way ;-)

                    to be sure, just set some c3p0 logging to see if it is a c3p0 datasource


                    • #11
                      Hi Michael,

                      You are 100% correct. I didn't getting a c3p0 datasource.
                      so I have removed the hibernate.c3p0.* property from the Hibernate Property files and moved to the DataSource . Now as per my configuration only 7 connections are opened(2 (Min_Size) + 5 (Max_size) = (Total) 7 ).

                      One more question michael.

                      I have started my batch with 10 Threads. my batch hangs there. Records are not processing.

                      10 Threads should share the 7 connections from the pool right? If the answer is yes, why the batch get hangs here?


                      <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
                      <property name="driverClass" value=" r" />
                      <property name="jdbcUrl" value="url" />
                      <property name="user" value="User" />
                      <property name="password" value="password" />
                      <property name="acquireIncrement" value="1" />
                      <property name="minPoolSize" value="2" />
                      <property name="maxPoolSize" value="5" />



                      • #12
                        Please use [ code][/code ] tags when posting..

                        7 connections, 10 threads that isn't enough... The 8th thread is blocking and waiting for a connection to be available.

                        so I have removed the hibernate.c3p0.* property from the Hibernate Property files and moved to the DataSource . Now as per my configuration only 7 connections are opened(2 (Min_Size) + 5 (Max_size) = (Total) 7 ).
                        Which is strange because only the max number should be opened and 2 at startup... So where those 6th and 7th connection come from is a bit of a riddle.


                        • #13
                          Thanks for your reply Marten.

                          I am running 3 JVM in 3 different machine(10 threads are running for each JVM).

                          In this case Can you suggest What should be the MinPoolSize and MaxPoolSize ?