2 Replies Latest reply on Sep 16, 2008 7:38 AM by aguizar

    The PostgreSQL Issue

    thomas.diesler

      Folks,

      I investigated this a little more. It seems that most of the time is spend in createSchema() per test. IMHO, createSchema() however should not be called at all.

      Running org.jbpm.graph.exe.ProcessInstanceDbTest
      
      super.setUp: 0ms
      createSchema: 3811ms
      createJbpmContext: 0ms
      initializeMembers: 0ms
      ProcessInstanceDbTest.testProcessInstanceProcessDefinition: 21ms
      resetMembers: 1ms
      closeJbpmContext: 0ms
      
      
      super.setUp: 0ms
      createSchema: 5162ms
      createJbpmContext: 0ms
      initializeMembers: 1ms
      ProcessInstanceDbTest.testProcessInstanceDates: 68ms
      resetMembers: 0ms
      closeJbpmContext: 1ms
      
      
      super.setUp: 0ms
      createSchema: 3397ms
      createJbpmContext: 1ms
      initializeMembers: 0ms
      ProcessInstanceDbTest.testProcessInstanceRootToken: 16ms
      resetMembers: 0ms
      closeJbpmContext: 1ms
      
      
      super.setUp: 0ms
      createSchema: 3420ms
      createJbpmContext: 0ms
      initializeMembers: 0ms
      ProcessInstanceDbTest.testProcessInstanceSuperProcessToken: 29ms
      resetMembers: 0ms
      closeJbpmContext: 1ms
      
      
      super.setUp: 0ms
      createSchema: 3346ms
      createJbpmContext: 0ms
      initializeMembers: 0ms
      ProcessInstanceDbTest.testProcessInstanceModuleInstances: 45ms
      resetMembers: 0ms
      closeJbpmContext: 1ms
      
      
      super.setUp: 0ms
      createSchema: 3342ms
      createJbpmContext: 1ms
      initializeMembers: 0ms
      ProcessInstanceDbTest.testProcessInstanceRuntimeActions: 15ms
      resetMembers: 0ms
      closeJbpmContext: 1ms
      
      Tests run: 6, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 22.702 sec
      


      The time is increasing as the test suite progresses.

      It also seems that tearDown() is not always called, which would result in the context not being closed properly.

      Running org.jbpm.db.DeleteProcessInstanceDbTest
      
      super.setUp: 0ms
      createSchema: 789ms
      createJbpmContext: 0ms
      
      super.setUp: 0ms
      createSchema: 790ms
      createJbpmContext: 0ms
      
      super.setUp: 0ms
      createSchema: 755ms
      createJbpmContext: 0ms
      
      super.setUp: 0ms
      createSchema: 4666ms
      createJbpmContext: 1ms
      Tests run: 4, Failures: 0, Errors: 4, Skipped: 0, Time elapsed: 7.028 sec <<< FAILURE!
      


      Another concern is that the number of postgresql processes is increasing unexpectedly to over 100, which would very likely not be correct and a potential source of major performance impact.

      [tdiesler@tdvaio java]$ ps ax | grep postgres
       3177 ? Ss 0:00 postgres: logger process
       3192 ? Ss 0:07 postgres: writer process
       3193 ? Ss 0:00 postgres: wal writer process
       3194 ? Ss 0:00 postgres: autovacuum launcher process
       3195 ? Ss 0:04 postgres: stats collector process
       7901 pts/2 Sl+ 0:10 /usr/java/jdk1.6/bin/java -Xmx256m -classpath /usr/java/maven/boot/classworlds-1.1.jar -Dclassworlds.conf=/usr/java/maven/bin/m2.conf -Dmaven.home=/usr/java/maven org.codehaus.classworlds.Launcher "-o" "-Ddatabase=postgresql" "test"
       7964 ? Ss 0:01 postgres: jbpmtest jbpmtest 127.0.0.1(45926) idle in transaction
       7973 ? Ss 0:01 postgres: jbpmtest jbpmtest 127.0.0.1(45928) idle in transaction
       7977 ? Ss 0:01 postgres: jbpmtest jbpmtest 127.0.0.1(45930) idle in transaction
      ...
       8599 ? Ss 0:00 postgres: jbpmtest jbpmtest 127.0.0.1(57772) idle in transaction
       8607 ? Ss 0:00 postgres: jbpmtest jbpmtest 127.0.0.1(57774) idle in transaction
       8611 ? Ss 0:00 postgres: jbpmtest jbpmtest 127.0.0.1(57777) idle in transaction
      [tdiesler@tdvaio java]$ ps ax | grep -c postgres
      107
      


        • 1. Re: The PostgreSQL Issue
          aguizar

           

          Another concern is that the number of postgresql processes is increasing unexpectedly to over 100

          This is probably related to connection pooling. See the comments on JBPM-1131. I cannot tell whether the leaked connections are causing PostgreSQL to fork new processes, but is definitely a starting point.

          • 2. Re: The PostgreSQL Issue
            aguizar

            I just found evidence that the leaked connections can certainly be related to the growing number of processes.

            The PostgreSQL server uses one process per connection so you should provide for at least as many processes as allowed connections, in addition to what you need for the rest of your system. This is usually not a problem but if you run several servers on one machine things might get tight.