You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Tassos Bassoukos <ab...@gmail.com> on 2006/06/12 16:05:50 UTC
Performance issues when using postgresql-8.1-404.jdbc3.jar
Greetings,
I've encountered an interesting performance disparity when using
torque, specifically requests from torque took 40+ms to complete
(based on the timing in BasePeer), while the database reported
completion in 1-2ms.
After some digging, it looks like a village-postgresql mismatch, in
the sense that village caches the metadata for all columns in a query,
but postgresql will emit an extra (internal) query to fetch two
metadata options: for the nullAllowed and autoIncrement properties.
That is, for a select with 6 fields, a total of 13 round-trips to the
server are made.
Now comes the interesting bit: These attributes aren't used anywhere.
I've removed these attributes (and their accessors), and everything
compiled and plays fine....
Tassos Bassoukos
P.S.: I'm not subscribed to the list, so you may want to CC me for a
fast response.
P.P.S.: This is all on stock torque 3.2
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Thomas Fischer <tf...@apache.org>.
On Thu, 15 Jun 2006, Thomas Vandahl wrote:
> Joe Carter wrote:
>
>> Yeah, I can see that would be the better approach.
>> But even with my quick look over the code I can see it's a big job!
>
> Your help will be appreciated... :-)
As always. But this specific case touches almost all internals of Torque
and some arcitectural decisions need to be taken by the Torque developers.
To be honest, I do not think this is a good area to start contributions
to Torque. There are better places.
Regards,
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Thomas Vandahl <th...@tewisoft.de>.
Joe Carter wrote:
> Yeah, I can see that would be the better approach.
> But even with my quick look over the code I can see it's a big job!
Your help will be appreciated... :-)
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Joe Carter <jo...@excite.com>.
On 14/06/06, Thomas Fischer <fi...@seitenbau.net> wrote:
>
>
> joe.carter@gmail.com schrieb am 14.06.2006 16:17:44:
>
> > I'd read some intention about moving away from Village and the problems
> with
> > doing that.
> > What I might be able to provide is something that starts the process by
> > handling simple
> > selects which could revert to village for more complex queries.
> > No promises though :-)
> > I should be able to suggest/rule out some approaches at the very least
> > though.
>
> I have already checked the approach to do that some time ago, and doing
> simple selects without village is not a problem indeed. The real problem
> is
> that without village, the behaviour of Torque is likely to change in some
> subtle way, and some users will have a hard time to make sure that their
> application still work. So one would like to make the transition in one
> step, so people have to resolve the issues with that change only one, not
> with every release.
>
> Thomas
>
>
Yeah, I can see that would be the better approach.
But even with my quick look over the code I can see it's a big job!
Joe
Re: Torque and Database Ingre
Posted by Thomas Fischer <tf...@apache.org>.
On Thu, 15 Jun 2006, Scott Eade wrote:
If you want to pursue this, please do. I would guess that it is not ASF
policy to include code for which we do not have permission by a copyright
holder to include it, but I may very well be wrong. If we get a CLA from
the author or from Ingres, I'm +1 for including it, but if we do not get
anything, I'm -0.1 (I do not know any Torque developer who would maintain
this code, and we already have to many untested databases).
Also you said it was Torque 3.1 code, so the db.properties need to be
rewritten as a platform class to be included in 3.2
Thomas
> Thomas Fischer wrote:
>> On Thu, 15 Jun 2006, Scott Eade wrote:
>>
>>> Scott Eade wrote:
>>>
>>> I know the developer had intended to submit this to as a path to torque
>>> but I am unaware of any reason why this was not done.
>>
>> I would guess it was not submitted because we asked the developer for a
>> CCLA from CA.
>>
> Yes, but recent discussion on a different list reveals that a CCLA would not
> in fact be required - just a CLA from the developer would have sufficed. I
> am unsure as to whether or not the developer moved over to Ingres
> Corporation. At the end of the day the code uses the ASL 2.0 license - if we
> wanted to bring it into torque it would be a simple matter of asking Ingres
> Corp if this is what they want (technically we wouldn't need to ask but it
> would be polite). My guess is that they would be fine with it. The nicest
> way would be with a CLA from the author, but at the end of the day the code
> is only really going to be a variation to one of the existing DB adaptors so
> there isn't really going to be that much to it - i.e. even without a CLA it
> would IMO be okay to bring in.
>
> Scott
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Torque and Database Ingre
Posted by Scott Eade <se...@backstagetech.com.au>.
Thomas Fischer wrote:
> On Thu, 15 Jun 2006, Scott Eade wrote:
>
>> Scott Eade wrote:
>> A message I sent to the CA contact bounced, but a quick search
>> revealed this
>> http://www.ingres.com/products/Prod_Download_Torque.html and
>> http://opensource.ingres.com/projects/ingres/files/z3.ingrestorque
>>
>> It is most likely the same code - the second link requires that you
>> login before you can download, the first does not. Looks like it is
>> was written for torque 3.1.1
>>
>> Not sure how this would need to be tweaked to being it up to 3.2.
>>
>> I know the developer had intended to submit this to as a path to
>> torque but I am unaware of any reason why this was not done.
>
> I would guess it was not submitted because we asked the developer for
> a CCLA from CA.
>
Yes, but recent discussion on a different list reveals that a CCLA would
not in fact be required - just a CLA from the developer would have
sufficed. I am unsure as to whether or not the developer moved over to
Ingres Corporation. At the end of the day the code uses the ASL 2.0
license - if we wanted to bring it into torque it would be a simple
matter of asking Ingres Corp if this is what they want (technically we
wouldn't need to ask but it would be polite). My guess is that they
would be fine with it. The nicest way would be with a CLA from the
author, but at the end of the day the code is only really going to be a
variation to one of the existing DB adaptors so there isn't really going
to be that much to it - i.e. even without a CLA it would IMO be okay to
bring in.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Torque and Database Ingre
Posted by Thomas Fischer <tf...@apache.org>.
On Thu, 15 Jun 2006, Scott Eade wrote:
> Scott Eade wrote:
> A message I sent to the CA contact bounced, but a quick search revealed this
> http://www.ingres.com/products/Prod_Download_Torque.html and
> http://opensource.ingres.com/projects/ingres/files/z3.ingrestorque
>
> It is most likely the same code - the second link requires that you login
> before you can download, the first does not. Looks like it is was written
> for torque 3.1.1
>
> Not sure how this would need to be tweaked to being it up to 3.2.
>
> I know the developer had intended to submit this to as a path to torque but I
> am unaware of any reason why this was not done.
I would guess it was not submitted because we asked the developer for a
CCLA from CA.
> Scott
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Torque and Database Ingre
Posted by Scott Eade <se...@backstagetech.com.au>.
Scott Eade wrote:
> Thomas Fischer wrote:
>> As far as I know, Torque does not support Ingres. I do not know of
>> any plans to do so.
> A while back I had contact with someone over at CA who was working on
> providing support - I will ping them off list and see if I can
> discover what happened.
>
A message I sent to the CA contact bounced, but a quick search revealed
this http://www.ingres.com/products/Prod_Download_Torque.html and
http://opensource.ingres.com/projects/ingres/files/z3.ingrestorque
It is most likely the same code - the second link requires that you
login before you can download, the first does not. Looks like it is was
written for torque 3.1.1
Not sure how this would need to be tweaked to being it up to 3.2.
I know the developer had intended to submit this to as a path to torque
but I am unaware of any reason why this was not done.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Torque and Database Ingre
Posted by Scott Eade <se...@backstagetech.com.au>.
Thomas Fischer wrote:
> As far as I know, Torque does not support Ingres. I do not know of any
> plans to do so.
A while back I had contact with someone over at CA who was working on
providing support - I will ping them off list and see if I can discover
what happened.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Torque and Database Ingre
Posted by Thomas Fischer <tf...@apache.org>.
As far as I know, Torque does not support Ingres. I do not know of any
plans to do so.
Thomas
On Wed, 14 Jun 2006, cedric.brajus@vallourec.fr wrote:
> Hello,
>
> I search documentation about the interface between torque and
> ingres database because I must to migrate the data in postgresql.
> have you already do this ?
>
> Thanks C�dric
Torque and Database Ingre
Posted by ce...@vallourec.fr.
Hello,
I search documentation about the interface between torque and
ingres database because I must to migrate the data in postgresql.
have you already do this ?
Thanks Cédric
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Thomas Fischer <fi...@seitenbau.net>.
joe.carter@gmail.com schrieb am 14.06.2006 16:17:44:
> I'd read some intention about moving away from Village and the problems
with
> doing that.
> What I might be able to provide is something that starts the process by
> handling simple
> selects which could revert to village for more complex queries.
> No promises though :-)
> I should be able to suggest/rule out some approaches at the very least
> though.
I have already checked the approach to do that some time ago, and doing
simple selects without village is not a problem indeed. The real problem is
that without village, the behaviour of Torque is likely to change in some
subtle way, and some users will have a hard time to make sure that their
application still work. So one would like to make the transition in one
step, so people have to resolve the issues with that change only one, not
with every release.
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Joe Carter <jo...@excite.com>.
On 14/06/06, Thomas Fischer <fi...@seitenbau.net> wrote:
>
> The torque developers are aware that usage of the village library is
> problematic. This specific problem was not known until now, but there are
> other well-known problems of using village.
> However, removing the dependency of torque on village it is a huge effort,
> so village is likely to stay in torque for the next future :-(
>
> Thomas
>
>
I'd read some intention about moving away from Village and the problems with
doing that.
What I might be able to provide is something that starts the process by
handling simple
selects which could revert to village for more complex queries.
No promises though :-)
I should be able to suggest/rule out some approaches at the very least
though.
Fortunately one of the reasons we use Torque is that we have the
"bare-metal" option
which is what I'm using now. We may have to resort to stored procedures in
some of
our hotspots to get the performance, though I'm hoping the use of prepared
statement
selects will avoid the need for that.
Joe
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Thomas Fischer <fi...@seitenbau.net>.
The torque developers are aware that usage of the village library is
problematic. This specific problem was not known until now, but there are
other well-known problems of using village.
However, removing the dependency of torque on village it is a huge effort,
so village is likely to stay in torque for the next future :-(
Thomas
joe.carter@gmail.com schrieb am 14.06.2006 15:45:06:
> On 14/06/06, Tassos Bassoukos <ab...@gmail.com> wrote:
> >
> > On 6/14/06, Joe Carter <jo...@excite.com> wrote:
> > > Hi,
> > >
> > > Thanks for the info.
> > > I've tried it and it makes no obvious difference to the speed.
> > >
> > > FWIW my timings for selects (for my particular test)
> > > Standard Torque: ~10 seconds
> > > Raw SQL: ~4 seconds
> > > SQL as Prepared Statement: ~ 3 seconds.
> > >
> > > Curiously inserts have no discernable difference.
> >
> > Interesting. Note that my fix probably applies only to postgres, due
> > to specific driver architecture. A way to log all SQL statements that
> > are emitted by the JVM (and their duration) would help.
>
>
> Agreed - p6spy returns the normal statements but I believe it doesn't
> handle metadata - which is the prime suspect here.
>
> Note that torque has overhead when lots and lots of rows are returned
> > (memory- and cpu-wise), so that may affect your timings.
>
>
> Yes, I was aware of that. My tests return single rows. Our application
> generally does that so it's representative for me.
>
> And yes, in my case inserts/updates have no timing difference either.
> >
> > Tassos Bassoukos
> >
>
> For now I'm manually coding my hotspot queries but hopefully a pattern
will
> fall out which would be suitable for generation. Getting rid of the need
> for the metadata would be nice. I think torque has all of the information
it
> needs
> not to require access to the metadata, so I'm sure this is possible.
>
> Cheers
>
> Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Joe Carter <jo...@excite.com>.
On 14/06/06, Tassos Bassoukos <ab...@gmail.com> wrote:
>
> On 6/14/06, Joe Carter <jo...@excite.com> wrote:
> > Hi,
> >
> > Thanks for the info.
> > I've tried it and it makes no obvious difference to the speed.
> >
> > FWIW my timings for selects (for my particular test)
> > Standard Torque: ~10 seconds
> > Raw SQL: ~4 seconds
> > SQL as Prepared Statement: ~ 3 seconds.
> >
> > Curiously inserts have no discernable difference.
>
> Interesting. Note that my fix probably applies only to postgres, due
> to specific driver architecture. A way to log all SQL statements that
> are emitted by the JVM (and their duration) would help.
Agreed - p6spy returns the normal statements but I believe it doesn't
handle metadata - which is the prime suspect here.
Note that torque has overhead when lots and lots of rows are returned
> (memory- and cpu-wise), so that may affect your timings.
Yes, I was aware of that. My tests return single rows. Our application
generally does that so it's representative for me.
And yes, in my case inserts/updates have no timing difference either.
>
> Tassos Bassoukos
>
For now I'm manually coding my hotspot queries but hopefully a pattern will
fall out which would be suitable for generation. Getting rid of the need
for the metadata would be nice. I think torque has all of the information it
needs
not to require access to the metadata, so I'm sure this is possible.
Cheers
Joe
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Tassos Bassoukos <ab...@gmail.com>.
On 6/14/06, Joe Carter <jo...@excite.com> wrote:
> Hi,
>
> Thanks for the info.
> I've tried it and it makes no obvious difference to the speed.
>
> FWIW my timings for selects (for my particular test)
> Standard Torque: ~10 seconds
> Raw SQL: ~4 seconds
> SQL as Prepared Statement: ~ 3 seconds.
>
> Curiously inserts have no discernable difference.
Interesting. Note that my fix probably applies only to postgres, due
to specific driver architecture. A way to log all SQL statements that
are emitted by the JVM (and their duration) would help.
Note that torque has overhead when lots and lots of rows are returned
(memory- and cpu-wise), so that may affect your timings.
And yes, in my case inserts/updates have no timing difference either.
Tassos Bassoukos
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Joe Carter <jo...@excite.com>.
Hi,
Thanks for the info.
I've tried it and it makes no obvious difference to the speed.
FWIW my timings for selects (for my particular test)
Standard Torque: ~10 seconds
Raw SQL: ~4 seconds
SQL as Prepared Statement: ~ 3 seconds.
Curiously inserts have no discernable difference.
Cheers
Joe
On 14/06/06, Tassos Bassoukos <ab...@gmail.com> wrote:
>
> On 6/14/06, Joe Carter <jo...@excite.com> wrote:
> > Hi,
> >
> > Interestingly I suspect we're seeing similar problems with Sybase.
> > When I manually implement a select instead of using Torque's save()
> > I seem to get about twice the performance.
> >
> > Can you tell me precisely what you changed?
> > I'd like to try it with our system.
> >
> > Thanks
>
> I simply removed the nullAllowed and autoIncerement properties (and
> their accessors, initializers etc) from the
> com.workingdogs.village.Column class, recompiled, and that's about it.
>
> Background: Village aggresively caches the metadata from the ResultSet
> of a query, this happening by calling some getters, which are
> implemented in the driver. In PostgreSQLs case, two of those getters
> perform an extra query on the database to provide the requested
> information. However, this information is not accessed by village or
> by Torque, but it is requested from the database anyway.
>
> I haven't used Sybase, but it may have a way to log all database
> requests; see if every request from Torque is accompanied by request
> for column metadata.
>
> Hope this helps,
> Tassos Bassoukos
>
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Tassos Bassoukos <ab...@gmail.com>.
On 6/14/06, Joe Carter <jo...@excite.com> wrote:
> Hi,
>
> Interestingly I suspect we're seeing similar problems with Sybase.
> When I manually implement a select instead of using Torque's save()
> I seem to get about twice the performance.
>
> Can you tell me precisely what you changed?
> I'd like to try it with our system.
>
> Thanks
I simply removed the nullAllowed and autoIncerement properties (and
their accessors, initializers etc) from the
com.workingdogs.village.Column class, recompiled, and that's about it.
Background: Village aggresively caches the metadata from the ResultSet
of a query, this happening by calling some getters, which are
implemented in the driver. In PostgreSQLs case, two of those getters
perform an extra query on the database to provide the requested
information. However, this information is not accessed by village or
by Torque, but it is requested from the database anyway.
I haven't used Sybase, but it may have a way to log all database
requests; see if every request from Torque is accompanied by request
for column metadata.
Hope this helps,
Tassos Bassoukos
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Performance issues when using postgresql-8.1-404.jdbc3.jar
Posted by Joe Carter <jo...@excite.com>.
Hi,
Interestingly I suspect we're seeing similar problems with Sybase.
When I manually implement a select instead of using Torque's save()
I seem to get about twice the performance.
Can you tell me precisely what you changed?
I'd like to try it with our system.
Thanks
Joe
On 12/06/06, Tassos Bassoukos <ab...@gmail.com> wrote:
>
> Greetings,
> I've encountered an interesting performance disparity when using
> torque, specifically requests from torque took 40+ms to complete
> (based on the timing in BasePeer), while the database reported
> completion in 1-2ms.
>
> After some digging, it looks like a village-postgresql mismatch, in
> the sense that village caches the metadata for all columns in a query,
> but postgresql will emit an extra (internal) query to fetch two
> metadata options: for the nullAllowed and autoIncrement properties.
>
> That is, for a select with 6 fields, a total of 13 round-trips to the
> server are made.
>
> Now comes the interesting bit: These attributes aren't used anywhere.
> I've removed these attributes (and their accessors), and everything
> compiled and plays fine....
>
> Tassos Bassoukos
> P.S.: I'm not subscribed to the list, so you may want to CC me for a
> fast response.
> P.P.S.: This is all on stock torque 3.2
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
>
>