pooling of prepared statements / statement-cache

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

pooling of prepared statements / statement-cache

michael_w
Hi Ludovic,

let's talk about another implementation request... :-/ ;-)

I just ran into performance problems concerning the postgres jdbc driver. Prepared statements aren't executed as that, only if you add a specific property, so called server side prepared statements are used.
On the client-side there is generally no caching of prepared statements (there is an extension of the default driver available though). I looked into this and found out that many jdbc drivers have this functionality, but one important for example not: Microsoft SQL-Server. There is an option in the connection properties for that (disableStatementPooling), but you must not set it. I think it's okay as statement pooling is not a feature for the jdbc driver. Uhhh, it's the same with connection pooling. Many drivers have this feature but why? There are enough good implementations out there...

So what do You think? BTM is already a mature connection pool and statement pooling would be very useful too. I know, this is yet another thing that a transaction manager shouldn't care about. But as you statet already in another post: transaction managers and connection pools are strongly coupled. Of course the application could care about the cache by itself, but it wouldn't be transparent...

May it be easy to implement? For every open connection there had to be a map with the sql-query as key and the created statement object as value. Con.prepareStatement() would create and/or return the statement from the map, statement.close() would leave the statement open and by closing the connection, all statements from the map are implicitly closed. A necessary property for BTM might be the maximum size of the map... Hhmmm, are there side effects to think about?

Would it be a nice feature for BTM?


Many greetings,
Michael

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

Re: pooling of prepared statements / statement-cache

Ludovic Orban
Administrator
Michael,

Statement caching is definitely a feature that should be part of the connection pool. I've been considering it in the very early days but decided to postpone it until a user requests it which seems now to be the case.

At first look it does not seem too hard to implement, only the bitronix.tm.resource.jdbc.JdbcConnectionHandle would have to be touched, at least according to your proposed design.

JdbcConnectionHandle is the object returned by a call to PoolingDataSource.getConnection(). It is on this object that you call prepareStatement(). When close() is called, the object is dropped: BTM won't try to recycle it. So in a nutshell, one would just have to add a PreparedStatement map in that class, look up the statement or populate it when prepareStatement() is called and clean it when close() is called. I currently have no idea how statement caching should work so I'm guessing your design is right here but it might turn up that in fact cached statements should be kept on the pooled object instance. There could also be some side-effects as you said. Does anybody know for sure how statement caching works in other connection pools ?

I'm also a bit afraid to introduce hard-to-reproduce bugs with this new feature. At the very least, it should be possible to disable it and it should be by default. Then it needs to be tested in a real situation, that will make all the most obvious show stoppers show up so that it will work fine most of the time.

Do you want to try implementing this ? It seems that you already have a real scenario and a testbed for that new feature so you look like the ideal guinea pig, err... I mean candidate.

Ludovic


2007/11/13, michael_w <[hidden email]>:

Hi Ludovic,

let's talk about another implementation request... :-/ ;-)

I just ran into performance problems concerning the postgres jdbc driver.
Prepared statements aren't executed as that, only if you add a specific
property, so called server side prepared statements are used.
On the client-side there is generally no caching of prepared statements
(there is an extension of the default driver available though). I looked
into this and found out that many jdbc drivers have this functionality, but
one important for example not: Microsoft SQL-Server. There is an option in
the connection properties for that (disableStatementPooling), but you must
not set it. I think it's okay as statement pooling is not a feature for the
jdbc driver. Uhhh, it's the same with connection pooling. Many drivers have
this feature but why? There are enough good implementations out there...

So what do You think? BTM is already a mature connection pool and statement
pooling would be very useful too. I know, this is yet another thing that a
transaction manager shouldn't care about. But as you statet already in
another post: transaction managers and connection pools are strongly
coupled. Of course the application could care about the cache by itself, but
it wouldn't be transparent...

May it be easy to implement? For every open connection there had to be a map
with the sql-query as key and the created statement object as value.
Con.prepareStatement() would create and/or return the statement from the
map, statement.close() would leave the statement open and by closing the
connection, all statements from the map are implicitly closed. A necessary
property for BTM might be the maximum size of the map... Hhmmm, are there
side effects to think about?

Would it be a nice feature for BTM?


Many greetings,
Michael


--
View this message in context: http://www.nabble.com/pooling-of-prepared-statements---statement-cache-tf4797174.html#a13723896
Sent from the BTM Users mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

     http://xircles.codehaus.org/manage_email



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

Re: pooling of prepared statements / statement-cache

michael_w
Hi Ludovic,

there is also a small chapter in the jdbc3 api specification about connection and statement pooling. I didn't know that. The thing that was interesting for me: Finally it's up to the developers to implement the features either in the jdbc driver or in the application server component... So this is the cause of my confusion.

Let us implement statement pooling in BTM as an optional feature! But give me some (a lot of?) time. At first i also want to look into what other connection pools are doing. Real scenarios and testbeds are available, so no problem for being a guinea pig. ;-)

I'm back again if i have some results and then code review is up to you. :-)

Cheers, Michael


Ludovic Orban wrote
Michael,

Statement caching is definitely a feature that should be part of the
connection pool. I've been considering it in the very early days but decided
to postpone it until a user requests it which seems now to be the case.

At first look it does not seem too hard to implement, only the
bitronix.tm.resource.jdbc.JdbcConnectionHandle would have to be touched, at
least according to your proposed design.

JdbcConnectionHandle is the object returned by a call to
PoolingDataSource.getConnection(). It is on this object that you call
prepareStatement(). When close() is called, the object is dropped: BTM won't
try to recycle it. So in a nutshell, one would just have to add a
PreparedStatement map in that class, look up the statement or populate it
when prepareStatement() is called and clean it when close() is called. I
currently have no idea how statement caching should work so I'm guessing
your design is right here but it might turn up that in fact cached
statements should be kept on the pooled object instance. There could also be
some side-effects as you said. Does anybody know for sure how statement
caching works in other connection pools ?

I'm also a bit afraid to introduce hard-to-reproduce bugs with this new
feature. At the very least, it should be possible to disable it and it
should be by default. Then it needs to be tested in a real situation, that
will make all the most obvious show stoppers show up so that it will work
fine most of the time.

Do you want to try implementing this ? It seems that you already have a real
scenario and a testbed for that new feature so you look like the ideal
guinea pig, err... I mean candidate.

Ludovic


2007/11/13, michael_w <michael.weigmann@hsh-berlin.com>:
>
>
> Hi Ludovic,
>
> let's talk about another implementation request... :-/ ;-)
>
> I just ran into performance problems concerning the postgres jdbc driver.
> Prepared statements aren't executed as that, only if you add a specific
> property, so called server side prepared statements are used.
> On the client-side there is generally no caching of prepared statements
> (there is an extension of the default driver available though). I looked
> into this and found out that many jdbc drivers have this functionality,
> but
> one important for example not: Microsoft SQL-Server. There is an option in
> the connection properties for that (disableStatementPooling), but you must
> not set it. I think it's okay as statement pooling is not a feature for
> the
> jdbc driver. Uhhh, it's the same with connection pooling. Many drivers
> have
> this feature but why? There are enough good implementations out there...
>
> So what do You think? BTM is already a mature connection pool and
> statement
> pooling would be very useful too. I know, this is yet another thing that a
> transaction manager shouldn't care about. But as you statet already in
> another post: transaction managers and connection pools are strongly
> coupled. Of course the application could care about the cache by itself,
> but
> it wouldn't be transparent...
>
> May it be easy to implement? For every open connection there had to be a
> map
> with the sql-query as key and the created statement object as value.
> Con.prepareStatement() would create and/or return the statement from the
> map, statement.close() would leave the statement open and by closing the
> connection, all statements from the map are implicitly closed. A necessary
> property for BTM might be the maximum size of the map... Hhmmm, are there
> side effects to think about?
>
> Would it be a nice feature for BTM?
>
>
> Many greetings,
> Michael
>
>
> --
> View this message in context:
> http://www.nabble.com/pooling-of-prepared-statements---statement-cache-tf4797174.html#a13723896
> Sent from the BTM Users mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>     http://xircles.codehaus.org/manage_email
>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: pooling of prepared statements / statement-cache

Ludovic Orban
Administrator
Michael,

JDBC v3 spec was way more interesting to read than v2, thanks for the tip. It actually contains useful information on how connection pools should work and how XA should be handled. It even describes how to perform statement pooling.

That's actually more complex than I first expected. Statement pool must be kept per physical connection, not logical one.

This means JdbcPooledConnection should contain some kind of map that will contain statement-string as key and PreparedStatement object as value. That map should ideally be bounded to a specific size and should provide some kind of LRU eviction policy.

The JdbcConnectionHandle will also need to be changed, when prepareStatement is called a lookup should first be made in the JdbcPooledConnection's cache before creating a new one.

Finally, a new class must be added. It should be called CacheablePreparedStatement or something like that. Objects of this class will actually be returned to the application code as the call to PreparedStatement.close() must be intercepted and not passed to the real PreparedStatement.

Finally a new configurable property should be added to PoolingDataSource as to control the statement pool's size, something like statementPoolSize.

I estimate this to be 1 or 2 days of work plus intensive testing. As soon as I manage to make some time I'll start working on it (hint: buying support will help me find the required time quicker).

Ludovic

2007/11/14, michael_w <[hidden email]>:

Hi Ludovic,

there is also a small chapter in the jdbc3 api specification about
connection and statement pooling. I didn't know that. The thing that was
interesting for me: Finally it's up to the developers to implement the
features either in the jdbc driver or in the application server component...
So this is the cause of my confusion.

Let us implement statement pooling in BTM as an optional feature! But give
me some (a lot of?) time. At first i also want to look into what other
connection pools are doing. Real scenarios and testbeds are available, so no
problem for being a guinea pig. ;-)

I'm back again if i have some results and then code review is up to you. :-)

Cheers, Michael



Ludovic Orban wrote:

>
> Michael,
>
> Statement caching is definitely a feature that should be part of the
> connection pool. I've been considering it in the very early days but
> decided
> to postpone it until a user requests it which seems now to be the case.
>
> At first look it does not seem too hard to implement, only the
> bitronix.tm.resource.jdbc.JdbcConnectionHandle would have to be touched,
> at
> least according to your proposed design.
>
> JdbcConnectionHandle is the object returned by a call to
> PoolingDataSource.getConnection(). It is on this object that you call
> prepareStatement(). When close() is called, the object is dropped: BTM
> won't
> try to recycle it. So in a nutshell, one would just have to add a
> PreparedStatement map in that class, look up the statement or populate it
> when prepareStatement() is called and clean it when close() is called. I
> currently have no idea how statement caching should work so I'm guessing
> your design is right here but it might turn up that in fact cached
> statements should be kept on the pooled object instance. There could also
> be
> some side-effects as you said. Does anybody know for sure how statement
> caching works in other connection pools ?
>
> I'm also a bit afraid to introduce hard-to-reproduce bugs with this new
> feature. At the very least, it should be possible to disable it and it
> should be by default. Then it needs to be tested in a real situation, that
> will make all the most obvious show stoppers show up so that it will work
> fine most of the time.
>
> Do you want to try implementing this ? It seems that you already have a
> real
> scenario and a testbed for that new feature so you look like the ideal
> guinea pig, err... I mean candidate.
>
> Ludovic
>
>
> 2007/11/13, michael_w <[hidden email]>:
>>
>>
>> Hi Ludovic,
>>
>> let's talk about another implementation request... :-/ ;-)
>>
>> I just ran into performance problems concerning the postgres jdbc driver.
>> Prepared statements aren't executed as that, only if you add a specific
>> property, so called server side prepared statements are used.
>> On the client-side there is generally no caching of prepared statements
>> (there is an extension of the default driver available though). I looked
>> into this and found out that many jdbc drivers have this functionality,
>> but
>> one important for example not: Microsoft SQL-Server. There is an option
>> in
>> the connection properties for that (disableStatementPooling), but you
>> must
>> not set it. I think it's okay as statement pooling is not a feature for
>> the
>> jdbc driver. Uhhh, it's the same with connection pooling. Many drivers

>> have
>> this feature but why? There are enough good implementations out there...
>>
>> So what do You think? BTM is already a mature connection pool and
>> statement
>> pooling would be very useful too. I know, this is yet another thing that
>> a
>> transaction manager shouldn't care about. But as you statet already in
>> another post: transaction managers and connection pools are strongly
>> coupled. Of course the application could care about the cache by itself,
>> but
>> it wouldn't be transparent...
>>
>> May it be easy to implement? For every open connection there had to be a
>> map
>> with the sql-query as key and the created statement object as value.
>> Con.prepareStatement() would create and/or return the statement from the
>> map, statement.close() would leave the statement open and by closing the
>> connection, all statements from the map are implicitly closed. A
>> necessary
>> property for BTM might be the maximum size of the map... Hhmmm, are there
>> side effects to think about?
>>
>> Would it be a nice feature for BTM?
>>
>>
>> Many greetings,
>> Michael
>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/pooling-of-prepared-statements---statement-cache-tf4797174.html#a13723896
>> Sent from the BTM Users mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>>     http://xircles.codehaus.org/manage_email
>>
>>
>>
>
>

--
View this message in context: http://www.nabble.com/pooling-of-prepared-statements---statement-cache-tf4797174.html#a13743449
Sent from the BTM Users mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



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

Re: pooling of prepared statements / statement-cache

Ludovic Orban
Administrator
The first prototype that contains a statement cache is ready: http://btm.codehaus.org/snapshot/btm-1.2-20071116.jar

To enable it, set the new preparedStatementCacheSize property of the PoolingDataSource. Any value below 1 disables it and its default value is 0.

I had to slightly change the internal design of the JDBC pool to make this change so you might encounter subtle differences. Of course, this should be considered a lightly tested prototype.

Please test it and let me know if this helps with performance, if you find a bug or have any other remark.

Ludovic
Loading...