You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Martin Kalén <mk...@apache.org> on 2005/04/14 19:06:22 UTC
PostgreSQL default JDBC fetch size hint
Greetings,
on the user list Andreas Bohnert has pointed out some memory problems
when using the PostgreSQL JDBC driver and a large ResultSet, since
the PostgreSQL JDBC client will fetch _all_ returned rows over the wire
even when the ResulSet is iterated one row at a time with eg
OJB getIteratorByQuery.
See this thread:
http://www.mail-archive.com/ojb-user%40db.apache.org/msg13380.html
I have done some testing and it seems that explicit setting of the
fetch size (a JDBC hint most drivers will ignore) does not cause
any trouble with the OJB tests. See PB and ODMG results here:
http://people.apache.org/~mkalen/ojb/postgresql-fetchsize-tests.html
However, making fetchSize=1 a global PostgreSQL default in OJB will
probably cause slightly more network traffic and JDBC overhead so
there might be objections to checking in the new afterStatementCreate
shown on my ASF webpage above.
Any thoughts/comments on this?
Does anyone know if PG7 vs PG8 JDBC-client and/or servers will
do things differently wrt server-side cursors?
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Martin Kalén <mk...@apache.org>.
Martin Kalén wrote:
>>> How about adding a fetchSize attribute to the JdbcConnectionDescriptor
>>> and perhaps also to the query ? This way, the fetch size is tunable
>>> for jdbc drivers that support this. Any JDBC driver should implement
>>> this method (its defined since JDBC 1.2) regardless of whether it
>>> actually does something.
>>> Also, we should perhaps have a reasonable default, e.g. 50 or
>>> something, for PostgreSQL ?!
>>
>> +1 would be easily to integrate in 1.0.x and 1.x
>
> I'm currently looking at the branch and trunk jcd-connection-descriptor
> configuration mechanisms.
>
> fetchSize (and maxRows) could be considered similar to eg batch-mode,
> and therefore they seem to belong as attributes directly on
> <jdbc-connection-descriptor/>.
>
> However, two things:
> 1) fetchSize (and/or maxRows) hints should not be set by OJB in managed
> environments
> 2) the <attribute/> generic config mechanism in 1.1.x is much more
> easily maintained than hardcoded attributes
>
> Both these would be easier to achieve if fechSize was an attribute of
> the ConnectionFactory (ie ConnectionFactoryManagedImpl could ignore such
> settings).
I went ahead and added fetchSize as a custom attribute of the connection pool.
I think it fits nicely there, since the hint is somewhat connection-centric
and we want to move away from hardcoded attributes.
Making ConnectionPoolDescriptor (actually PoolConfiguration) an AttributeContainer
made it easy to integrate custom JDBC properties for DriverManager in the same way.
I'm going to summarize all my changes in another thread.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Martin Kalén <mk...@apache.org>.
Armin Waibel wrote:
>> How about adding a fetchSize attribute to the JdbcConnectionDescriptor
>> and perhaps also to the query ? This way, the fetch size is tunable
>> for jdbc drivers that support this. Any JDBC driver should implement
>> this method (its defined since JDBC 1.2) regardless of whether it
>> actually does something.
>> Also, we should perhaps have a reasonable default, e.g. 50 or
>> something, for PostgreSQL ?!
>
> +1 would be easily to integrate in 1.0.x and 1.x
I'm currently looking at the branch and trunk jcd-connection-descriptor
configuration mechanisms.
fetchSize (and maxRows) could be considered similar to eg batch-mode,
and therefore they seem to belong as attributes directly on
<jdbc-connection-descriptor/>.
However, two things:
1) fetchSize (and/or maxRows) hints should not be set by OJB in managed
environments
2) the <attribute/> generic config mechanism in 1.1.x is much more
easily maintained than hardcoded attributes
Both these would be easier to achieve if fechSize was an attribute of
the ConnectionFactory (ie ConnectionFactoryManagedImpl could ignore such
settings).
Any comments?
Wherever we decide to put fetchSize hints I will (at least in the trunk)
try to put settings for the currently hard-coded Oracle extensions for
row pre-fetching and statement caching on the JDBC level.
It would be good to put all of this in a single (configurable) spot
and I would like some feedback as to where people think they belong.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Armin Waibel <ar...@apache.org>.
Thomas Dudziak wrote:
>>Any thoughts/comments on this?
>
>
> How about adding a fetchSize attribute to the JdbcConnectionDescriptor
> and perhaps also to the query ? This way, the fetch size is tunable
> for jdbc drivers that support this. Any JDBC driver should implement
> this method (its defined since JDBC 1.2) regardless of whether it
> actually does something.
> Also, we should perhaps have a reasonable default, e.g. 50 or
> something, for PostgreSQL ?!
>
+1 would be easily to integrate in 1.0.x and 1.x
Armin
> Tom
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Martin Kalén <mk...@apache.org>.
Jakob Braeuchi wrote:
> you got me wrong. ojb does not use sql hints such as limit, rownum etc.
> the PagingIterator is based on a pure select-statement and cursorable
> resultset.
Yes, I was confusing the DB paging stuff (that's not complete?) with this.
Anyway, that just makes it more interesting to check out fetch size.
Will probably have a look at this next week.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi martin,
you got me wrong. ojb does not use sql hints such as limit, rownum etc.
the PagingIterator is based on a pure select-statement and cursorable
resultset.
jakob
Martin Kalén schrieb:
> Martin Kalén wrote:
>
>>> How about adding a fetchSize attribute to the JdbcConnectionDescriptor
>>> and perhaps also to the query ?
>>
>>
>> Good idea.
>
>
> ...also worth noting is Jakob's hint that OJB has a proprietary way of
> setting start- and endIndex for queries (which will set "fetch size"
> directly through SQL). This will work for every DB supported by OJB.
>
> The benefit of adding a configurable JDBC fetchSize hint would be
> that (at least for PostgreSQL) one can limit fetch block size
> without any programmatic changes.
>
> Regards,
> Martin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Martin Kalén <mk...@apache.org>.
Martin Kalén wrote:
>> How about adding a fetchSize attribute to the JdbcConnectionDescriptor
>> and perhaps also to the query ?
>
> Good idea.
...also worth noting is Jakob's hint that OJB has a proprietary way of
setting start- and endIndex for queries (which will set "fetch size"
directly through SQL). This will work for every DB supported by OJB.
The benefit of adding a configurable JDBC fetchSize hint would be
that (at least for PostgreSQL) one can limit fetch block size
without any programmatic changes.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Martin Kalén <mk...@apache.org>.
Thomas Dudziak wrote:
> How about adding a fetchSize attribute to the JdbcConnectionDescriptor
> and perhaps also to the query ?
Good idea.
> This way, the fetch size is tunable for jdbc drivers that support this.
> Any JDBC driver should implement this method (its defined since JDBC 1.2)
> regardless of whether it actually does something.
Yep, they all do but PostgreSQL is one of the few that will actually
care at all about the hint.
> Also, we should perhaps have a reasonable default, e.g. 50 or
> something, for PostgreSQL ?!
That's the tricky bit - there is no reasonable global default since
an application using only queries by iterator and such would benefit
from fetchSize="1" always and an application that wants to get as much
data as fast possible (with low JDBC overhead and minimal net traffic)
wants fetchSize="<as huge as current JVM's -Xmx permits>".
OJB has no clue about max heap size in user's target environments
and also knows nothing about the application profile (iterating vs fetch
huge results vs everything in between).
But I guess "50" could be seen as "somewhere between 1 and huge". :-)
If it's configurable and documented everyone can at least tune it
at will.
I'll have a look at this.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: PostgreSQL default JDBC fetch size hint
Posted by Thomas Dudziak <to...@gmail.com>.
On 4/14/05, Martin Kalén <mk...@apache.org> wrote:
> Greetings,
> on the user list Andreas Bohnert has pointed out some memory problems
> when using the PostgreSQL JDBC driver and a large ResultSet, since
> the PostgreSQL JDBC client will fetch _all_ returned rows over the wire
> even when the ResulSet is iterated one row at a time with eg
> OJB getIteratorByQuery.
>
> See this thread:
> http://www.mail-archive.com/ojb-user%40db.apache.org/msg13380.html
>
> I have done some testing and it seems that explicit setting of the
> fetch size (a JDBC hint most drivers will ignore) does not cause
> any trouble with the OJB tests. See PB and ODMG results here:
> http://people.apache.org/~mkalen/ojb/postgresql-fetchsize-tests.html
>
> However, making fetchSize=1 a global PostgreSQL default in OJB will
> probably cause slightly more network traffic and JDBC overhead so
> there might be objections to checking in the new afterStatementCreate
> shown on my ASF webpage above.
>
> Any thoughts/comments on this?
How about adding a fetchSize attribute to the JdbcConnectionDescriptor
and perhaps also to the query ? This way, the fetch size is tunable
for jdbc drivers that support this. Any JDBC driver should implement
this method (its defined since JDBC 1.2) regardless of whether it
actually does something.
Also, we should perhaps have a reasonable default, e.g. 50 or
something, for PostgreSQL ?!
Tom
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org