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

Re: Terrible Write Performance of a Stored Procedure

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Scott Mead <scott(dot)lists(at)enterprisedb(dot)com>
Cc: Brian Troutwine <goofyheadedpunk(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Terrible Write Performance of a Stored Procedure
Date: 2009-06-26 22:34:20
Message-ID: alpine.GSO.2.01.0906261816530.22738@westnet.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
On Fri, 26 Jun 2009, Scott Mead wrote:

>     Having those settings enabled basically does the following:
>    " Do not complete the I/O for a commit until you have either commit_siblings commits also ready, or you have waited .55 seconds."
> 
>   Basically, if you make 1 commit, you will sit there waiting until either 99 other commits take place, or ~ 1/2 second goes by.

You're right that it should be removed, but this explanation is wrong. 
The behavior as configured is actually "if there are >=100 other 
transactions in progress, wait 0.1 second before committing after the 
first one gets committed", in hopes that one of the other 100 might also 
join along in the disk write.

Since in this case max_connections it set to 100, it's actually impossible 
for the commit_delay/commit_siblings behavior to trigger give this 
configuration.  That's one reason it should be removed.  The other is that 
i general, if you don't exactly what you're doing, you shouldn't be 
touching either parameters; they don't do what people expect them to and 
it's extremely unlikely you'll encounter any of the rare use cases where 
they might help.

I don't think any of the sync or write parameters have anything to do with 
this problem though, it seems like a problem with the referential bits 
taking too long to execute.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>From pgsql-performance-owner(at)postgresql(dot)org  Fri Jun 26 20:19:10 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6AF6A632BE8
	for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Fri, 26 Jun 2009 20:19:10 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
 by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
 with ESMTP id 16732-02
 for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
 Fri, 26 Jun 2009 20:19:07 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 2F383631873
	for <pgsql-performance(at)postgresql(dot)org>; Fri, 26 Jun 2009 20:19:07 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n5QNJ5Tu011765;
	Fri, 26 Jun 2009 19:19:05 -0400 (EDT)
To: bob_lunney(at)yahoo(dot)com
cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Insert performance and multi-column index order 
In-reply-to: <155063(dot)67512(dot)qm(at)web39705(dot)mail(dot)mud(dot)yahoo(dot)com> 
References: <155063(dot)67512(dot)qm(at)web39705(dot)mail(dot)mud(dot)yahoo(dot)com>
Comments: In-reply-to bob_lunney(at)yahoo(dot)com
	message dated "Fri, 26 Jun 2009 10:25:29 -0700"
Date: Fri, 26 Jun 2009 19:19:05 -0400
Message-ID: <11764(dot)1246058345(at)sss(dot)pgh(dot)pa(dot)us>
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200906/371
X-Sequence-Number: 34615

bob_lunney(at)yahoo(dot)com writes:
> Why would changing the column order on a unique index cause data loading or index servicing to slow down?  Page splits in the b-tree, maybe?

Yeah, perhaps.  Tell us about the data distributions in the columns?
Is there any ordering to the keys that're being inserted?

It's not in the least surprising that different column orders might be
better or worse suited for particular queries.  I'm mildly interested
in the question of why the bulk load speed is different, though.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Janet JacobsenDate: 2009-06-27 01:16:57
Subject: Re: slow DELETE on 12 M row table
Previous:From: Tom LaneDate: 2009-06-26 22:22:00
Subject: Re: Nested Loop "Killer" on 8.1

pgsql-general by date

Next:From: mobiledreamersDate: 2009-06-27 00:35:04
Subject: Re: horizontal sharding
Previous:From: Gerry RenoDate: 2009-06-26 21:47:59
Subject: Re: Replication

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