pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed8.2 -> 8.3 performance numbers
10+ messages / 6 participants
[nested] [flat]
* 8.2 -> 8.3 performance numbers
@ 2007-07-20 04:58 Jim Nasby <decibel@decibel.org>
0 siblings, 1 reply; 10+ messages in thread
From: Jim Nasby @ 2007-07-20 04:58 UTC (permalink / raw)
To: PostgreSQL List Advocacy <pgsql-advocacy@postgresql.org>; pgsql-performance
Sorry for the cross-post, but this is performance and advocacy
related...
Has anyone benchmarked HEAD against 8.2? I'd like some numbers to use
in my OSCon lightning talk. Numbers for both with and without HOT
would be even better (I know we've got HOT-specific benchmarks, but I
want complete 8.2 -> 8.3 numbers).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] 8.2 -> 8.3 performance numbers
@ 2007-07-20 17:03 Josh Berkus <josh@agliodbs.com>
parent: Jim Nasby <decibel@decibel.org>
0 siblings, 3 replies; 10+ messages in thread
From: Josh Berkus @ 2007-07-20 17:03 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: PostgreSQL List Advocacy <pgsql-advocacy@postgresql.org>; pgsql-performance
Jim,
> Has anyone benchmarked HEAD against 8.2? I'd like some numbers to use in
> my OSCon lightning talk. Numbers for both with and without HOT would be
> even better (I know we've got HOT-specific benchmarks, but I want
> complete 8.2 -> 8.3 numbers).
We've done it on TPCE, which is a hard benchmark for PostgreSQL. On
that it's +9% without HOT and +13% with HOT. I think SpecJ would show a
greater difference, but we're still focussed on benchmarks we can
publish (i.e. 8.2.4) right now.
--Josh
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] 8.2 -> 8.3 performance numbers
@ 2007-07-20 18:32 Jim Nasby <decibel@decibel.org>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 10+ messages in thread
From: Jim Nasby @ 2007-07-20 18:32 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: PostgreSQL List Advocacy <pgsql-advocacy@postgresql.org>; pgsql-performance
On Jul 20, 2007, at 1:03 PM, Josh Berkus wrote:
> Jim,
>
>> Has anyone benchmarked HEAD against 8.2? I'd like some numbers to
>> use in my OSCon lightning talk. Numbers for both with and without
>> HOT would be even better (I know we've got HOT-specific
>> benchmarks, but I want complete 8.2 -> 8.3 numbers).
>
> We've done it on TPCE, which is a hard benchmark for PostgreSQL.
> On that it's +9% without HOT and +13% with HOT. I think SpecJ
> would show a greater difference, but we're still focussed on
> benchmarks we can publish (i.e. 8.2.4) right now.
Bleh, that's not a very impressive number.
Anyone else have something better?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] 8.2 -> 8.3 performance numbers
@ 2007-07-25 01:28 Merlin Moncure <mmoncure@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2007-07-25 01:28 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jim Nasby <decibel@decibel.org>; PostgreSQL List Advocacy <pgsql-advocacy@postgresql.org>; pgsql-performance
On 7/20/07, Josh Berkus <josh@agliodbs.com> wrote:
> Jim,
>
> > Has anyone benchmarked HEAD against 8.2? I'd like some numbers to use in
> > my OSCon lightning talk. Numbers for both with and without HOT would be
> > even better (I know we've got HOT-specific benchmarks, but I want
> > complete 8.2 -> 8.3 numbers).
>
> We've done it on TPCE, which is a hard benchmark for PostgreSQL. On
> that it's +9% without HOT and +13% with HOT. I think SpecJ would show a
> greater difference, but we're still focussed on benchmarks we can
> publish (i.e. 8.2.4) right now.
Are there any industry standard benchmarks that you know of which
PostgreSQL excels at?
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers
@ 2007-07-25 12:52 Simon Riggs <simon@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 2 replies; 10+ messages in thread
From: Simon Riggs @ 2007-07-25 12:52 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jim Nasby <decibel@decibel.org>; pgsql-performance
On Fri, 2007-07-20 at 10:03 -0700, Josh Berkus wrote:
> Jim,
>
> > Has anyone benchmarked HEAD against 8.2? I'd like some numbers to use in
> > my OSCon lightning talk. Numbers for both with and without HOT would be
> > even better (I know we've got HOT-specific benchmarks, but I want
> > complete 8.2 -> 8.3 numbers).
>
> We've done it on TPCE, which is a hard benchmark for PostgreSQL. On
> that it's +9% without HOT and +13% with HOT. I think SpecJ would show a
> greater difference, but we're still focussed on benchmarks we can
> publish (i.e. 8.2.4) right now.
Josh,
Should you get the chance I would appreciate a comparative test for
TPC-E.
1. Normal TPC-E versus
2. TPC-E with all FKs against Fixed tables replaced with CHECK( col IN
(VALUES(x,x,x,...))) constraints on the referencing tables.
I have reasonable evidence that Referential Integrity is the major
performance bottleneck and would like some objective evidence that this
is the case.
No rush, since it will be an 8.4 thing to discuss and improve this
substantially in any of the ways I envisage.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers
@ 2007-07-25 13:07 Mario Weilguni <mweilguni@sime.com>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 10+ messages in thread
From: Mario Weilguni @ 2007-07-25 13:07 UTC (permalink / raw)
To: pgsql-performance; +Cc: Simon Riggs <simon@2ndquadrant.com>; Josh Berkus <josh@agliodbs.com>; Jim Nasby <decibel@decibel.org>
Am Mittwoch 25 Juli 2007 schrieb Simon Riggs:
> I have reasonable evidence that Referential Integrity is the major
> performance bottleneck and would like some objective evidence that this
> is the case.
Just curious, will 8.3 still check FK constraints (and use locks) even if the
referencing column value does not change?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers
@ 2007-07-25 14:09 Simon Riggs <simon@2ndquadrant.com>
parent: Mario Weilguni <mweilguni@sime.com>
0 siblings, 0 replies; 10+ messages in thread
From: Simon Riggs @ 2007-07-25 14:09 UTC (permalink / raw)
To: Mario Weilguni <mweilguni@sime.com>; +Cc: pgsql-performance; Josh Berkus <josh@agliodbs.com>; Jim Nasby <decibel@decibel.org>
On Wed, 2007-07-25 at 15:07 +0200, Mario Weilguni wrote:
> Am Mittwoch 25 Juli 2007 schrieb Simon Riggs:
> > I have reasonable evidence that Referential Integrity is the major
> > performance bottleneck and would like some objective evidence that this
> > is the case.
>
> Just curious, will 8.3 still check FK constraints (and use locks) even if the
> referencing column value does not change?
That is optimised away in 8.0+
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers
@ 2007-07-25 14:09 Merlin Moncure <mmoncure@gmail.com>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 10+ messages in thread
From: Merlin Moncure @ 2007-07-25 14:09 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Jim Nasby <decibel@decibel.org>; pgsql-performance
On 7/25/07, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Fri, 2007-07-20 at 10:03 -0700, Josh Berkus wrote:
> > Jim,
> >
> > > Has anyone benchmarked HEAD against 8.2? I'd like some numbers to use in
> > > my OSCon lightning talk. Numbers for both with and without HOT would be
> > > even better (I know we've got HOT-specific benchmarks, but I want
> > > complete 8.2 -> 8.3 numbers).
> >
> > We've done it on TPCE, which is a hard benchmark for PostgreSQL. On
> > that it's +9% without HOT and +13% with HOT. I think SpecJ would show a
> > greater difference, but we're still focussed on benchmarks we can
> > publish (i.e. 8.2.4) right now.
>
> Josh,
>
> Should you get the chance I would appreciate a comparative test for
> TPC-E.
>
> 1. Normal TPC-E versus
> 2. TPC-E with all FKs against Fixed tables replaced with CHECK( col IN
> (VALUES(x,x,x,...))) constraints on the referencing tables.
>
> I have reasonable evidence that Referential Integrity is the major
> performance bottleneck and would like some objective evidence that this
> is the case.
>
> No rush, since it will be an 8.4 thing to discuss and improve this
> substantially in any of the ways I envisage.
just a small 'me too' here, the RI penalty seems higher than it should
be...especially when the foreign key table is very small, and I can
see how this would impact benchmarks.
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers
@ 2007-07-25 14:22 Simon Riggs <simon@2ndquadrant.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Simon Riggs @ 2007-07-25 14:22 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Jim Nasby <decibel@decibel.org>; pgsql-performance
On Wed, 2007-07-25 at 10:09 -0400, Merlin Moncure wrote:
> On 7/25/07, Simon Riggs <simon@2ndquadrant.com> wrote:
> >
> > Should you get the chance I would appreciate a comparative test for
> > TPC-E.
> >
> > 1. Normal TPC-E versus
> > 2. TPC-E with all FKs against Fixed tables replaced with CHECK( col IN
> > (VALUES(x,x,x,...))) constraints on the referencing tables.
> >
> > I have reasonable evidence that Referential Integrity is the major
> > performance bottleneck and would like some objective evidence that this
> > is the case.
> >
> > No rush, since it will be an 8.4 thing to discuss and improve this
> > substantially in any of the ways I envisage.
>
> just a small 'me too' here, the RI penalty seems higher than it should
> be...especially when the foreign key table is very small, and I can
> see how this would impact benchmarks.
Any measurements to back that up would be appreciated. "Turning it off"
isn't really a valid comparison because we do want to make the checks
and expect there to be some cost to that. We just want to quantify the
cost to allow prioritising our efforts to improve performance on that.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers
@ 2007-07-25 14:43 Gregory Stark <stark@enterprisedb.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 10+ messages in thread
From: Gregory Stark @ 2007-07-25 14:43 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Josh Berkus <josh@agliodbs.com>; Jim Nasby <decibel@decibel.org>; pgsql-performance
> On Wed, 2007-07-25 at 10:09 -0400, Merlin Moncure wrote:
>>
>> just a small 'me too' here, the RI penalty seems higher than it should
>> be...especially when the foreign key table is very small, and I can
>> see how this would impact benchmarks.
>
> Any measurements to back that up would be appreciated. "Turning it off"
> isn't really a valid comparison because we do want to make the checks
> and expect there to be some cost to that. We just want to quantify the
> cost to allow prioritising our efforts to improve performance on that.
If anyone's interested in this I would be very interested in seeing the
results of your application benchmarks with various parts of the RI checking
code turned off.
Attached is a patch which adds three gucs for profiling purposes which cut off
the RI checks at various stages. To use them you would want to benchmark your
application five times in comparable conditions:
all variables set to 'no'
skip_ri_locks set to 'yes'
skip_ri_queries set to 'yes'
skip_ri_triggers set to 'yes'
no RI constraints at all
The last ought to be nearly identical to the fourth case. Note that it's
really important to repeat your benchmarks several times to ensure that you're
seeing repeatable results. Measuring CPU overhead is pretty tricky since a
single checkpoint or autovacuum run can completely throw off your results.
In my limited testing I found a *huge* effect for batch loads where many
inserts are done in a single transaction. I only see about a 20% hit on
pgbench with RI checks half of which comes from the trigger overhead and about
a quarter of which comes from each of the SPI queries and the locks. I have
some ideas for tackling the SPI queries which would help the batch loading
case but I'm not sure how much resources it makes sense to expend to save 5%
in the OLTP case.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Attachments:
ri-profiling-v2.patch.gz (application/octet-stream, 2.3K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-07-25 14:43 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-20 04:58 8.2 -> 8.3 performance numbers Jim Nasby <decibel@decibel.org>
2007-07-20 17:03 ` Josh Berkus <josh@agliodbs.com>
2007-07-20 18:32 ` Jim Nasby <decibel@decibel.org>
2007-07-25 01:28 ` Merlin Moncure <mmoncure@gmail.com>
2007-07-25 12:52 ` Simon Riggs <simon@2ndquadrant.com>
2007-07-25 13:07 ` Mario Weilguni <mweilguni@sime.com>
2007-07-25 14:09 ` Simon Riggs <simon@2ndquadrant.com>
2007-07-25 14:09 ` Merlin Moncure <mmoncure@gmail.com>
2007-07-25 14:22 ` Simon Riggs <simon@2ndquadrant.com>
2007-07-25 14:43 ` Gregory Stark <stark@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox