Обсуждение: 8.2 -> 8.3 performance numbers
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)
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
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)
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
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
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?
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
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
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
"Simon Riggs" <simon@2ndquadrant.com> writes: > 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