From: Richard Huxton <dev@archonet.com>
To: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: big transaction slows down over time - but disk seems
Date: Wed, 01 Nov 2006 16:34:12 +0000
Message-ID: <4548CC84.9000605@archonet.com> (raw)
In-Reply-To: <A054DD90-B18E-430E-B3B0-A9DA5DA41B26@silentmedia.com>
References: <Pine.LNX.4.64.0610312141430.5452@GRD.cube42.tai.silentmedia.com>
<45486959.60103@archonet.com>
<A054DD90-B18E-430E-B3B0-A9DA5DA41B26@silentmedia.com>
Ben wrote:
> My transaction calls the same stored procedure many times over. Over the
> lifetime of the transaction, that stored procedure slows down by roughly
> 2 orders of magnitude. The procedure itself tries to look up several
> strings in dictionary tables, and if the strings aren't there (most of
> them will be) it inserts them. All those dictionary tables have indexes.
> After it has converted most of the strings into ids, it does another
> lookup on a table and if it finds a matching row (should be the common
> case) it updates a timestamp column of that row; otherwise, it inserts a
> new row.
Which would suggest Heikki's guess was pretty much right and it's dead
rows that are causing the problem.
Assuming most updates are to this timestamp, could you try a test case
that does everything *except* update the timestamp. If that runs
blazingly fast then we've found the problem.
If that is the problem, there's two areas to look at:
1. Avoid updating the same timestamp more than once (if that's happening)
2. Update timestamps in one go at the end of the transaction (perhaps by
loading updates into a temp table).
3. Split the transaction in smaller chunks of activity.
> So.... there isn't much table size changing, but there are a lot of
> updates. Based on pg_stat_user_tables I suspect that the procedure is
> using indexes more than table scans. Is there a better way to know?
Not really. You can check the plans of queries within the function, but
there's no way to capture query plans of running functions.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, bench@silentmedia.com
Subject: Re: big transaction slows down over time - but disk seems
In-Reply-To: <4548CC84.9000605@archonet.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