Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 288C537B9D3 for ; Sun, 28 Sep 2008 13:24:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 55617-10 for ; Sun, 28 Sep 2008 13:24:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.172]) by postgresql.org (Postfix) with ESMTP id D186E37BA28 for ; Sun, 28 Sep 2008 13:24:08 -0300 (ADT) Received: by wf-out-1314.google.com with SMTP id 25so1816570wfc.28 for ; Sun, 28 Sep 2008 09:24:06 -0700 (PDT) Received: by 10.142.174.18 with SMTP id w18mr1726417wfe.202.1222619046149; Sun, 28 Sep 2008 09:24:06 -0700 (PDT) Received: by 10.143.159.7 with HTTP; Sun, 28 Sep 2008 09:24:06 -0700 (PDT) Message-ID: Date: Sun, 28 Sep 2008 09:24:06 -0700 From: "Scott Carey" To: "John Huttley" Subject: Re: Slow updates, poor IO Cc: "Scott Marlowe" , pgsql-performance@postgresql.org In-Reply-To: <6110.1222612388@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1913_27180538.1222619046152" References: <48DBE587.3050406@mib-infotech.co.nz> <20080926131556.GA1005@commandprompt.com> <48DD6A4A.10209@mib-infotech.co.nz> <48DEB4D4.1020100@mib-infotech.co.nz> <6110.1222612388@sss.pgh.pa.us> X-Google-Sender-Auth: 5ac1733e690b59d7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200809/243 X-Sequence-Number: 31556 ------=_Part_1913_27180538.1222619046152 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have had great success using FILLFACTOR on certain tables where big updates like this occur and improving performance. It is still not as fast as I would like, but there are significant gains. A big disk array won't help you as much as it should -- yes it will be faster, but it will still be chugging during one of these sorts of large updates and very inefficiently at that. On some of my cases, a FILLFACTOR of 95 or 98 is enough to do the trick. On others, 80 or 70 works. It depends on the size of your rows versus the size of the modifications you make. A fillfactor of 99 holds between ~80 bytes and one row-width worth of free space in every page, and is all that is needed if you have larger rows and only modify small fields such as ints. I'm not sure why FILLFACTOR = 99 isn't the default, to be honest. The size difference on disk is far less than 1% since most tables can't fit an exact number of rows in one page, and the benefit for updates is huge in certain cases. On the other hand, your table has a narrow row width and will fit many rows on one page, and if you are modifying text or varchars, you may need more space for those reserved in the fillfactor void and a smaller FILLFACTOR setting on the table, down to about 50 for updates where the updated rows account for a big fraction of the row width. A second benefit of using a fillfactor is that you can CLUSTER on an index and the table will retain that ordering for longer while inserts/updates/deletes occur. A fillfactor setting, REINDEX, then CLUSTER sequence can have a big impact. On Sun, Sep 28, 2008 at 7:33 AM, Tom Lane wrote: > John Huttley writes: > > Scott Marlowe wrote: > >> was... was a part of the trade-offs. > > > You are thinking of HOT? > > I don't think it applies in the case of full table updates?? > > ------=_Part_1913_27180538.1222619046152 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I have had great success using FILLFACTOR on certain tables where big updates like this occur and improving performance.  It is still not as fast as I would like, but there are significant gains.  A big disk array won't help you as much as it should -- yes it will be faster, but it will still be chugging during one of these sorts of large updates and very inefficiently at that.

On some of my cases, a FILLFACTOR of 95 or 98 is enough to do the trick.  On others, 80 or 70 works.
It depends on the size of your rows versus the size of the modifications you make.  A fillfactor of 99 holds between ~80 bytes and one row-width worth of free space in every page, and is all that is needed if you have larger rows and only modify small fields such as ints.  I'm not sure why FILLFACTOR = 99 isn't the default, to be honest.  The size difference on disk is far less than 1% since most tables can't fit an exact number of rows in one page, and the benefit for updates is huge in certain cases.
On the other hand, your table has a narrow row width and will fit many rows on one page, and if you are modifying text or varchars, you may need more space for those reserved in the fillfactor void and a smaller FILLFACTOR setting on the table, down to about 50 for updates where the updated rows account for a big fraction of the row width.

A second benefit of using a fillfactor is that you can CLUSTER on an index and the table will retain that ordering for longer while inserts/updates/deletes occur.  A fillfactor setting, REINDEX, then CLUSTER sequence can have a big impact.


On Sun, Sep 28, 2008 at 7:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
John Huttley <John@mib-infotech.co.nz> writes:
> Scott Marlowe wrote:
>> was...  was a part of the trade-offs.

> You are thinking of HOT?
> I don't think it applies in the case of full table updates??


------=_Part_1913_27180538.1222619046152--