From: Laszlo Nagy <gandalf@shopzeus.com>
To: Gregory Williamson <Gregory.Williamson@digitalglobe.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Daniel Fekete <danieleff@gmail.com>
Subject: Re: Slow table update
Date: Mon, 22 Dec 2008 13:54:20 +0100
Message-ID: <494F8DFC.1000001@shopzeus.com> (raw)
In-Reply-To: <8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com>
References: <494F749A.5090603@shopzeus.com>
<494F7B48.6070204@shopzeus.com>
<8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com>
>
> If the table has some sort of FK relations it might be being slowed by
> the need to check a row meant to be deleted has any children.
>
If you look at my SQL, there is only one column to be updated. That
column has no foreign key constraint. (It should have, but we did not
want to add that constraint in order to speed up updates.)
>
>
> Perhaps triggers ?
>
Table "product" has no triggers.
>
>
> If the table is very bloated with lots of dead rows (but you did say
> you vacuum frequently and check the results to make sure they are
> effective?) that would slow it down.
>
I'm not sure how to check if the vacuum was effective. But we have
max_fsm_pages=1000000 in postgresql.conf, and I do not get any errors
from the daily vacuum script, so I presume that the table hasn't got too
many dead rows.
Anyway, the table size is only 4GB. Even if half of the rows are dead,
the update should run quite quickly. Another argument is that when I
"select count(*)" instead of "UPDATE", then I get the result in 10
seconds. I don't think that dead rows can make such a big difference
between reading and writing.
My other idea was that there are so many indexes on this table, maybe
the update is slow because of the indexes? The column being updated has
only one index on it, and that is 200MB. But I have heard somewhere that
because of PostgreSQL's multi version system, sometimes the system needs
to update indexes with columns that are not being updated. I'm not sure.
Might this be the problem?
>
>
> A long running transaction elsewhere that is blocking the delete ? Did
> you check the locks ?
>
Sorry, this was an update. A blocking transaction would never explain
why the disk I/O went up to 100% for 2600 seconds.
L
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: gandalf@shopzeus.com, Gregory.Williamson@digitalglobe.com, danieleff@gmail.com
Subject: Re: Slow table update
In-Reply-To: <494F8DFC.1000001@shopzeus.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