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

Rapidly decaying performance repopulating a large table

From: "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Rapidly decaying performance repopulating a large table
Date: 2008-04-22 20:31:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
I have a fairly simple table (a dozen real/integer columns, a few
indexes, one foreign key reference) with ~120m rows. Periodically the
table is truncated or dropped and recreated and the data is
regenerated (slightly different data, of course, or the exercise would
be rather pointless). The regeneration occurs in batches of ~4000 data
points at a time, which are inserted into the table via COPY, and are
coming from several simultaneous processes.

The first several million data points are quite quick (the copy
executes in well under a quarter second). By the time the table
reaches 10-15m rows, however, each individual COPY is taking >20
seconds to execute. Is there anything I can do to improve this
performance? I can't drop/recreate the indices because some of the
data points rely on points generated already in the run, and dropping
the indices would make the required joins ridiculously expensive once
the table starts growing. The foreign key reference *is* droppable for
this regeneration, but I wouldn't expect it to be a performance

The autovacuum daemon is running in the background, with these
settings: (All autovacuum-specific settings are still at defaults)
vacuum_cost_delay = 50                  # 0-1000 milliseconds
vacuum_cost_page_hit = 1                # 0-10000 credits
vacuum_cost_page_miss = 10              # 0-10000 credits
vacuum_cost_page_dirty = 20             # 0-10000 credits
vacuum_cost_limit = 200         # 1-10000 credits

My gut feeling is that better autovacuum settings would help, but I'm
not really sure what to modify to get the improvement I'm looking for.

- David T. Wilson


pgsql-general by date

Next:From: Scott MarloweDate: 2008-04-22 20:38:29
Subject: Re: Rapidly decaying performance repopulating a large table
Previous:From: John DeSoiDate: 2008-04-22 20:09:21
Subject: Re: Schema migration tools?

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