Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Pgsql General > Rapidly decayin...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 18 Topic 15355 of 17602
Post > Topic >>

Rapidly decaying performance repopulating a large table

by david.t.wilson@[EMAIL PROTECTED] ("David Wilson") Apr 22, 2008 at 04:31 PM

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
problem.

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
david.t.wilson@[EMAIL PROTECTED]
 
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
 




 18 Posts in Topic:
Rapidly decaying performance repopulating a large table
david.t.wilson@[EMAIL PRO  2008-04-22 16:31:42 
Re: Rapidly decaying performance repopulating a large table
scott.marlowe@[EMAIL PROT  2008-04-22 14:38:29 
Re: Rapidly decaying performance repopulating a large table
david.t.wilson@[EMAIL PRO  2008-04-22 16:59:10 
Re: Rapidly decaying performance repopulating a large table
scott.marlowe@[EMAIL PROT  2008-04-22 15:04:00 
Re: Rapidly decaying performance repopulating a large table
david.t.wilson@[EMAIL PRO  2008-04-22 17:15:11 
Re: Rapidly decaying performance repopulating a large table
tgl@[EMAIL PROTECTED] (T  2008-04-22 18:10:55 
Re: Rapidly decaying performance repopulating a large table
scott.marlowe@[EMAIL PROT  2008-04-22 15:18:47 
Re: Rapidly decaying performance repopulating a large table
david.t.wilson@[EMAIL PRO  2008-04-22 17:46:42 
Re: Rapidly decaying performance repopulating a large table
erik@[EMAIL PROTECTED] (  2008-04-22 17:01:51 
Re: Rapidly decaying performance repopulating a large table
tometzky@[EMAIL PROTECTED  2008-04-23 13:10:54 
Re: Rapidly decaying performance repopulating a large table
david.t.wilson@[EMAIL PRO  2008-04-22 18:46:04 
Re: Rapidly decaying performance repopulating a large table
tgl@[EMAIL PROTECTED] (T  2008-04-22 19:33:11 
Re: Rapidly decaying performance repopulating a large table
david.t.wilson@[EMAIL PRO  2008-04-22 20:18:17 
Re: Rapidly decaying performance repopulating a large
gsmith@[EMAIL PROTECTED]   2008-04-23 01:53:20 
Re: Rapidly decaying performance repopulating a large table
tgl@[EMAIL PROTECTED] (T  2008-04-23 02:29:42 
Re: Rapidly decaying performance repopulating a large
gsmith@[EMAIL PROTECTED]   2008-04-23 01:59:23 
Re: Rapidly decaying performance repopulating a large
simon@[EMAIL PROTECTED]   2008-04-23 11:04:47 
Re: Rapidly decaying performance repopulating a large table
david.t.wilson@[EMAIL PRO  2008-04-24 16:55:21 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Mon Dec 1 18:54:26 CST 2008.