Michael,
I think this possibility to overrride pool settings is a good idea. Not sure
whether I understood you correctly but if you plan to allow to override only
maxIdle then it can be not enough for some environments. In my case I had to
specify maxIdle but also eviction related properties. So, it would help to
allow to put something like that in ~/.m2/settings.xml:
<openjpa.custom.dbcpprops>
MaxActive=100,MaxIdle=10,MaxWait=10000,
timeBetweenEvictionRunsMillis=2000,minEvictableIdleTimeMillis=1000
</openjpa.custom.dbcpprops>
and the default dbcp properties could be as they are now in pom.xml.
Regards,
Milosz
> Hi Milosz,
>
> I looked into setting maxIdle in the past and I didn't see a difference in
> performance with DB2. Setting it to 0, 10, or 100 had the same results (for
> me at least). I think all that's really telling me is that connection
> creation is not a bottleneck on my systems.
>
> I also haven't seen a "too many connections" message with MySQL, probably
> since my system isn't swapping as heavily as yours.
>
> Many of the connection pool settings will end up being specific to your
> environment. The real problem here is that they're hard coded in pom.xml. We
> should move them to a variable which can be overriden in settings.xml or
> from the command line. I'll open a JIRA issue to update maxIdle, and make it
> configurable on a per client basis.
>
> -mike
>
> On Mon, Sep 29, 2008 at 9:38 AM, Miłosz Tylenda wrote:
>
> > Hello!
> >
> > I have also noticed that this issue is partly related to OPENJPA-454
> > [Better support for running unit tests on DB2]. But even before that
> > improvement has been made, the situation was not perfect since dbcp
> > properties were not enabling idle connection eviction (although they allowed
> > idle connections for databases other than Derby).
> >
> > Regards,
> > Milosz
> >
> >
> > > Hello!
> > >
> > > Now it's faster.
> > >
> > > I enabled connection eviction in dbcp by specifying MaxIdle,
> > timeBetweenEvictionRunsMillis and minEvictableIdleTimeMillis:
> > >
> > > DriverClassName=${connection.driver.name
> > },Url=${connection.url},MaxActive=100,MaxIdle=10,
> > >
> > MaxWait=10000,Username=${connection.username},Password=${connection.password},
> > > timeBetweenEvictionRunsMillis=2000,minEvictableIdleTimeMillis=1000
> > >
> > > The values I got after some try-and-error cycles. They are probably not
> > optimal and may require tuning to a particular system. I removed
> > TestOnBorrow=true since the dbcp documentation says it does nothing without
> > specifying a validation query.
> > >
> > > These settings also sped up the test suite on MySQL but slowed down the
> > Derby one.
> > >
> > > Maybe such information could be added to the Wiki page on testing.
> > >
> > > The test suite hangs on DB2 in TestException but this is a different
> > topic.
> > >
> > > Regards,
> > > Milosz
> > >
> > >
> > > > Michael,
> > > >
> > > > The timings were much better when I changed maxIdle from 0 to 10 in
> > dbcp settings in openjpa-persistence-jdbc/pom.xml:
> > > > DriverClassName=${connection.driver.name
> > },Url=${connection.url},MaxActive=100,
> > > >
> > MaxIdle=10,MaxWait=10000,TestOnBorrow=true,Username=${connection.username},
> > > > Password=${connection.password}
> > > >
> > > > The number of created connections dropped down considerably.
> > > >
> > > > However, I ran into another problem - I was not able to finish the
> > suite because after about 100 tests my laptop started swapping miserably
> > (has 1GB). I suspect this maxIdle change causes connection leaks - when
> > running the tests on MySQL, it ends after a while with "Too many
> > connections" message.
> > > >
> > > > Regards,
> > > > Milosz
> > > >
> > > >
> > > > > Hi Milosz,
> > > > >
> > > > > Actually you are using commons-dbcp as a connection pool. If you use
> > the
> > > > > built in test-custom2 profile it will automatically use commons-dbcp.
> > > > >
> > > > > WRT creating the tables, if you're using the default settings we'll
> > create
> > > > > the tables the first time you run the tests. Subsequent executions
> > will not
> > > > > recreate the tables. Derby is an exception to this rule, the Derby
> > database
> > > > > is found in the target directory. Maven removes the target directory
> > in the
> > > > > 'clean' goal so the Derby database may be regenerated from scratch
> > each time
> > > > > you run the tests.
> > > > >
> > > > > I'll kick off an entire test run with DB2 and see if I hit the same
> > problem.
> > > > > I ran some of the tests but I didn't see times quite as bad as the
> > ones you
> > > > > reported.
> > > > >
> > > > > -mike
> > > > >
> > > > >
> > > > >
> > > > > On Wed, Sep 24, 2008 at 2:07 PM, Miłosz Tylenda wrote:
> > > > >
> > > > > > Michael,
> > > > > >
> > > > > > Thanks for the reply. I did not modify the test configuration
> > except for
> > > > > > adding my db2 profile, so I suppose I am not using a connection
> > pool. I run
> > > > > > the test like this:
> > > > > >
> > > > > > mvn -f openjpa-persistence-jdbc/pom.xml test
> > -Ptest-custom2,test-db2-milosz
> > > > > >
> > > > > > and in ~/.m2/settings.xml I have:
> > > > > >
> > > > > >
> > > > > > test-db2-milosz
> > > > > >
> > > > > > true
> > > > > >
> > > > > > /alt/jdbc/db2jcc.jar
> > > > > >
> > > > > > /alt/jdbc/db2jcc_license_cu.jar
> > > > > >
> > > > > > com.ibm.db2.jcc.DB2Driver
> > > > > > jdbc:db2://localhost:50000/oj
> > > > > >
> > jdbc:db2://localhost:50000/oj:traceDirectory=/tmp/db2log;traceLevel=3;
> > > > > > -->
> > > > > > *
> > > > > > *
> > > > > >
> > > > > >
> > > > > > As for reusing/creating the tables I am not sure. I think I rely on
> > the
> > > > > > default behviour. Is there an option for that?
> > > > > >
> > > > > > I have recently uncommented the commented line above in
> > settings.xml and
> > > > > > run a single test (TestQueryPagination). This immediately produced
> > almost 80
> > > > > > files in diagnostic directory and if I understand them correctly,
> > this
> > > > > > corresponds to almost 80 connections. This suggests me that I
> > should try a
> > > > > > connection pool.
> > > > > >
> > > > > > Regards,
> > > > > > Milosz
> > > > > >
> > > > > >
> > > > > > > Hi Milosz
> > > > > > >
> > > > > > > Are you using a connection pool (commons dbcp) when you run the
> > tests?
> > > > > > Also
> > > > > > > are you using existing tables or creating new ones with each run?
> > > > > > >
> > > > > > >
> > > > > > > On Wed, Sep 24, 2008 at 12:43 PM, Miłosz Tylenda wrote:
> > > > > > >
> > > > > > > > Hello!
> > > > > > > >
> > > > > > > > When I run the OpenJPA test suite on DB2, it takes much more
> > time to
> > > > > > > > complete than with other databases. The DB2 process hogs the
> > CPU during
> > > > > > the
> > > > > > > > test. A quite default installation of DB2 9.1 Express-C runs on
> > the
> > > > > > same
> > > > > > > > Linux (2.6.18-1.2798.fc6) laptop as OpenJPA 1.3.0-SNAPSHOT.
> > Does
> > > > > > anybody
> > > > > > > > have a clue what is the reason? Can it be related to the number
> > of open
> > > > > > > > connections or schema generation?
> > > > > > > >
> > > > > > > > A log snippet follows.
> > > > > > > >
> > > > > > > > Running
> > org.apache.openjpa.persistence.jdbc.meta.TestEJBCustomMapping
> > > > > > > > 3 TestConv INFO [main] openjpa.Runtime - Starting OpenJPA
> > > > > > > > 1.3.0-SNAPSHOT
> > > > > > > > 3499 TestConv INFO [main] openjpa.jdbc.JDBC - Using
> > dictionary
> > > > > > class
> > > > > > > > "org.apache.openjpa.jdbc.sql.DB2Dictionary" (DB2/LIN
> > > > > > > > UX SQL09010 ,IBM DB2 JDBC Universal Driver Architecture
> > 3.1.57).
> > > > > > > > 3917 TestConv WARN [main] openjpa.MetaData - OpenJPA cannot
> > map
> > > > > > field "
> > > > > > > > org.apache.openjpa.persistence.jdbc.common.apps.Co
> > > > > > > > nstantJoinPC4.manyToMany" efficiently. It is of an unsupported
> > type.
> > > > > > The
> > > > > > > > field value will be serialized to a BLOB by default
> > > > > > > > .
> > > > > > > > 0 TestConv INFO [main] openjpa.Runtime - Starting OpenJPA
> > > > > > > > 1.3.0-SNAPSHOT
> > > > > > > > 2800 TestConv INFO [main] openjpa.jdbc.JDBC - Using
> > dictionary
> > > > > > class
> > > > > > > > "org.apache.openjpa.jdbc.sql.DB2Dictionary" (DB2/LIN
> > > > > > > > UX SQL09010 ,IBM DB2 JDBC Universal Driver Architecture
> > 3.1.57).
> > > > > > > > 2844 TestConv WARN [main] openjpa.MetaData - OpenJPA cannot
> > map
> > > > > > field "
> > > > > > > > org.apache.openjpa.persistence.jdbc.common.apps.Co
> > > > > > > > nstantJoinPC4.manyToMany" efficiently. It is of an unsupported
> > type.
> > > > > > The
> > > > > > > > field value will be serialized to a BLOB by default
> > > > > > > > .
> > > > > > > > Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed:
> > 46.161
> > > > > > sec
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Milosz
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
