ConnectionPool-Features

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ConnectionPool-Features

michael_w
Hello Ludovic,

i'm refering to a post from your old forum concerning 'Connection Pool Configuration'.

Alas, shrinking of the pool is a must have for our product. Customers want to shutdown (backup) their database without quitting our server-side application. This happens at night when they can assure that there is no action going on...

Till now we are using jotm with xapool that supports all the nice connection-pool-features. But that piece of software is so buggy, problems all the time...

For now i was able to get btm to work very easily and really like to use that library. Maybe you can give a hint, where to start, if one would implement these features? (If i decide to do so, i contribute that of course. :-))

Minimum features i see for now: support of min-size, max-size and live-time (for unused connections); creating a connection when required; closing the connection when live-time reached...

Thanks for Your reply! :-)
Michael
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Ludovic Orban
Administrator
Hi,

First, thank you for the feedback. It's nice to get some users' comments.

Implementing a resizable connection pool is no easy task. It is so complex that it motivated my decision to leave that feature out to keep code easier to maintain and more stable.

I'd recommend you to first read http://www.bitronix.be/BtmInternals/ResourcesPools and try to get a bit familiar with the basics.

The JDBC pool code lies in the bitronix.tm.resource.jdbc package, plus some generic code in bitronix.tm.resource.common. The most important classes in there are PoolingDataSource, JdbcPooledConnection and XAPool.

The XAPool is really the class that contains the list of pooled connections: it creates a set of XAStatefulHolder's (JdbcPooledConnection's) using a XAResourceProducer (PoolingDataSource).


The XAPool method you need to change to grow the pool is:

 public synchronized Object getConnectionHandle(boolean recycle) throws Exception {}

To shrink it, you would need to use the task scheduler (bitronix.tm.timer.TaskScheduler), create a new type of task that would tell an XAPool object to check all its XAStatefulHolder's for the last acquisition date and to close itself in case the configured lifetime expired.

After that, new configuration settings should be added. Creating new properties in bitronix.tm.resource.common.ResourceBean should be enough as the configuration code uses reflection to map ResourceLoader settings to the XAResourceProducer.

Last but not least, this should be heavily tested. This is the tricky part: first because the acceptance tests are not yet available publicly. Not that I do not want to release them but I needed more time to polish them, time that I'm lacking. Second because you're going to touch code that is common to JDBC and JMS so you need to ensure you haven't broken any. Third you need to think about all the corner cases you potentially created and test them.

If you need more guidance, let me know.
Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Ludovic Orban
Administrator
In reply to this post by michael_w
Hi again,

I have a prototype implementation that is able to grow and shrink pools. If you want to give it a try and test it let me know.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

michael_w
Hi Ludovic,

did you implement it this weekend? - It really need'nt be that fast! ;-) Of course it would be wonderful if you let me take a look.

As you already have written, this is a fundamental part in your library and i would only reluctantly extend the functionality without knowing all possible side-effects. A revision of my code would have been wise... - yes, this way it's much better!

I might be able to test the new feature in the upcoming days. But that would be without special transactional behaviour... only begin, commit, rollback for one single connection.

Thanks a lot,
Michael

PS: If this code proves to be stable, are you willing to add this feature to your library in general? (Of course it needs much more testing to take that step.)


Ludovic Orban wrote
Hi again,

I have a prototype implementation that is able to grow and shrink pools. If you want to give it a try and test it let me know.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Ludovic Orban
Administrator
Hi,

While I checked the requirements it did not look that hard to implement anymore so I've quickly hacked the code as I described.

Here's the result: http://www.bitronix.be/uploads/Btm/btm-1.1-20070901.jar

The changes are as follow:

 - PoolingDataSource.setPoolSize() now sets both the minimal and maximal connections size. It is deprecated.
 - PoolingDataSource.setMinPoolSize() sets the minimal size which is also the initial size.
 - PoolingDataSource.setMaxPoolSize() sets the maximum size.
 - PoolingDataSource.setAcquireIncrement() specifies how many connections are created at once when the pool needs to grow, defaults to 1.
 - PoolingDataSource.setMaxIdleTime() specifies how long a connection can stay inactive before it is closed, defaults to 60.

Those changes are also valid for PoolingConnectionFactory.

I haven't yet thoroughly tested this change so it might be that this version is not very reliable under stress, I would not rely on it for production usage at least for now. But it looked fine with simple tests I ran.

If it proves to be OK at your side then I'll clean up the code, run the stress test suite and include that feature in the next release.

Please report any problem / inconsistency / extra wish you might encounter.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Wim Goossens
In reply to this post by michael_w
Hi Ludovic,

Does btm 1.1 addresses Michael's backup problem ?
I yesterday started testing btm 1.0 and when the database went down for backup, btm apparantly still used the invalid datasource  when the database was back online, no connections could be retrieved.
(application server never shuts down)

error :
SQLException ds.getConnection on datasource: 'java:comp/env/db' Message: unable to get a connection from pool of a PoolingDataSource with uniqueName jdbc/dev1 and an XAPool of resource jdbc/dev1 with 0 connection(s) (0 still available)

I will try 1.1 now

Regards
Wim



michael_w wrote
... Customers want to shutdown (backup) their database without quitting our server-side application. This happens at night when they can assure that there is no action going on...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Ludovic Orban
Administrator
Hi,

It looks like you've stepped on a bug. In theory BTM 1.0 should be able to recover from database downtime but in practice you've found a way to make it fail.

The new dynamic pool should solve the problem you've described. Please give it a try and let me know if it helped.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Wim Goossens
Ludovic,

Indeed, version 1.1 recovers fine from database downtime.
I tested it against 3 oracle 9.2.0.4 databases with driver 10.2.0.3 and a derby 10.2.2.0 database
We will do more testing the next 2 weeks.

But I ran into something strange. (with version btm 1.0 and btm 1.1)
I also tested against oracle 10.2.0.2 with driver 10.2.0.3
According to your documentation you must set the deferconnection parameter to true.
This indeed works with oracle 10 (although setting it to false does not yet seem to be a problem),
BUT with oracle 9, I must set it to false ??
Setting it to true gives some strange errors. I am attaching them.

Maybe you can shed a light on this.
It is important for me, we have over 200 oracle 9 databases running, and only a few oracle 10s.

In any case, it looks like BTM works very well.
Until now we are using jotm with xapool but BTM looks so nice ...stdout_20070905.log

Regards,
Wim

Ludovic Orban wrote
Hi,

It looks like you've stepped on a bug. In theory BTM 1.0 should be able to recover from database downtime but in practice you've found a way to make it fail.

The new dynamic pool should solve the problem you've described. Please give it a try and let me know if it helped.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Ludovic Orban
Administrator
Wim,

The version you're referring to as 1.1 is just a snapshot build I made after quickly hacking the code (this is why the version is 1.1-<build-timestamp>). As I said previously I did not run the full test suite against it. I've started running the tests yesterday and found out that I actually broke something up, the pool quickly gets corrupted when running under stress. I'm busy fixing it but it might take some time.


deferConnectionRelease should be set to true both against Oracle 10 and 9 as far as I know. If you don't you get XAER_RMFAIL or XAER_PROTO or XAER_RMERR errors back from Oracle during either of enlistment, delistment, commit or rollback. If can imagine that Oracle changed something in the DB in the latest version 10 but I cannot believe that version 9 would suddenly work.

Oracle 9 is supposed to be working fine when deferConnectionRelease is enabled, I've tested it in the past and had no problem with it. Can you try again with 1.0 to see if that version gives better results ? The 'unknown XAResource' message in your log files seems to be related to the changes I made to the pool in the 1.1-snapshot version.

If you get better results with version 1.0 you should stick with it for now. If not, you've found a very serious issue in the release and I'll attempt to fix it ASAP. Unfortunately I do not have access to an Oracle 9 database for now. I'll try to get one up and running but that will certainly take some more time.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Ludovic Orban
Administrator
Wim,

I managed to get my hands on an Oracle 9 server and I could reproduce your problem. It is very nasty as it involves connection testing, connection recycling and a race condition.

It also affects BTM 1.0 so downgrading won't help but as a workaround you can remove the testQuery of  all your PoolingDataSource definitions. If testing is disabled, you won't run into that bug.

I'll fix it tonight and release both a 1.0 patched version and a 1.1 patched version and keep you posted.

Thanks for pointing out this bug,
Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Ludovic Orban
Administrator
In reply to this post by Wim Goossens
Wim,

I've opened a bug in JIRA to track this issue's progress: http://jira.codehaus.org/browse/BTM-1

As I promised, here are the two fixed versions:

1.0: http://www.bitronix.be/uploads/Btm/btm-1.0.1-rc1.jar
1.1: http://www.bitronix.be/uploads/Btm/btm-1.1-20070905.jar

If that fixes the problem at your side as well I will commit the patch and immediately release 1.0.1 (without dynamic pool) and continue with the testing of 1.1.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ConnectionPool-Features

Wim Goossens
Ludovic,

Sorry for my late response but i had an external meeting this morning.
But, good news, deferconnectionrelease=true now works with oracle 9 and 10 with a testQuery=select 1 from dual
I tested with both btm-1.0.1-rc1.jar and btm-1.1-20070905.jar
Further testing will be done with btm-1.1-20070905.jar release (on a system with few workloads) because our dba's demand that extra functionality.
If everything is ok, I will move everything to the internal testserver in 2 weeks.
Nice work BTW.
Waiting for the btm-1.1-rc1 but take your time :-)

Wim

Ludovic Orban wrote
Wim,

I've opened a bug in JIRA to track this issue's progress: http://jira.codehaus.org/browse/BTM-1

As I promised, here are the two fixed versions:

1.0: http://www.bitronix.be/uploads/Btm/btm-1.0.1-rc1.jar
1.1: http://www.bitronix.be/uploads/Btm/btm-1.1-20070905.jar

If that fixes the problem at your side as well I will commit the patch and immediately release 1.0.1 (without dynamic pool) and continue with the testing of 1.1.

Ludovic
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Just a little feedback

michael_w
In reply to this post by michael_w
Hello Ludovic,

i was able to run BTM (1.1-20070901) the last couple of days on the following DBMS: Oracle 9.2.0.1.0 (driver 10.2.0.1.0), Derby 10.3.1.4 and on MS SQL-Server 2000 SP4 (with LrcXADataSource and JtdsDataSource but without the stored procedures and special-DLL installed). That was by using Hibernate (3.2.5ga) but also by using PoolingDataSources directly.

In both situations BTM was functioning without problems. Hibernate was only working on tables of one database, so i added a simple function for updating tables in two different databases. The results were as i had expected. By using 2 LrcXADataSources for SQLServer an exception was thrown... By using the jTDS driver instead (deferConnectionRelease == false) it was working fine.

With connection pooling also no problems. Perhaps i didn't have such stress tests as you had... ;-) I was  following the creation and destruction of the connections from the db-side and everything looked just fine. It is even possible to change the parameters at runtime. Nice feature!

As we are only starting with user transactions in our components, i am bold enough to implement this functionality in general. (By now i'm more concerned about being able to support all the different databases and versions by using xa-datasources instead of the DriverManager-mechanism...)

Well, let's see what's happening... Thanks for your support Ludovic! I'm already looking forward to your release of version 1.1. ;-)

Cheers,
Michael
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

More feedback

Wim Goossens
In reply to this post by michael_w
Ludovic,

We are running Bitronix Transaction Manager version 1.1-20070905 for nearly 2 weeks now without any problems.

I also wrote a small xa test program against an oracle 8.1.7.1.1 , oracle 9.2.0.4.0 and oracle 10.2.0.2.0
database. Used own ObjectFactory with bitronix.tm.resource.jdbc.PoolingDataSource
No problem at all.

Wim





Loading...