From: Scott Carey <scott@richrelevance.com>
To: John Huttley <John@mib-infotech.co.nz>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow updates, poor IO
Date: Sun, 28 Sep 2008 09:24:06 -0700
Message-ID: <a1ec7d000809280924o421e3854s44ee4491e7c610c8@mail.gmail.com> (raw)
In-Reply-To: <6110.1222612388@sss.pgh.pa.us>
References: <48DBE587.3050406@mib-infotech.co.nz>
<20080926131556.GA1005@commandprompt.com>
<48DD6A4A.10209@mib-infotech.co.nz>
<dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com>
<48DEB4D4.1020100@mib-infotech.co.nz>
<6110.1222612388@sss.pgh.pa.us>
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??
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott@richrelevance.com, John@mib-infotech.co.nz, scott.marlowe@gmail.com
Subject: Re: Slow updates, poor IO
In-Reply-To: <a1ec7d000809280924o421e3854s44ee4491e7c610c8@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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