Skip site navigation (1) Skip section navigation (2)

Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
Cc: "Merlin Moncure" <mmoncure(at)gmail(dot)com>, "Josh Berkus" <josh(at)agliodbs(dot)com>, "Jim Nasby" <decibel(at)decibel(dot)org>, "postgresql performance list" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers
Date: 2007-07-25 14:43:29
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-advocacypgsql-performance
"Simon Riggs" <simon(at)2ndquadrant(dot)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.

Attachment: ri-profiling-v2.patch.gz
Description: application/octet-stream (2.3 KB)

In response to

pgsql-performance by date

Next:From: Jozsef SzalayDate: 2007-07-25 17:37:23
Subject: Re: Simple select hangs while CPU close to 100%
Previous:From: Simon RiggsDate: 2007-07-25 14:22:25
Subject: Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers

pgsql-advocacy by date

Next:From: Simon RiggsDate: 2007-07-25 20:10:58
Subject: Re: PostgreSQL vs. MySQL: fight
Previous:From: Geoff FosterDate: 2007-07-25 14:27:20
Subject: Projects wanted for the .ORG Village at LinuxExpo 2007 - Exhibit for FREE!

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group