From: Kelly Burkhart <kelly@tradebotsystems.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.x index insert performance
Date: Mon, 14 Nov 2005 08:43:30 -0600
Message-ID: <1131979410.14024.49.camel@krb06.tradebot.com> (raw)
In-Reply-To: <22258.1131750131@sss.pgh.pa.us>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD725@Herge.rcsinc.local>
<20051031193547.GA3311@mark.mielke.cc>
<18647.1130790629@sss.pgh.pa.us>
<1130792391.7026.55.camel@krb06.tradebot.com>
<19065.1130793527@sss.pgh.pa.us>
<1130852029.7026.88.camel@krb06.tradebot.com>
<24845.1130852730@sss.pgh.pa.us>
<1131660117.7514.57.camel@krb06.tradebot.com>
<13167.1131668008@sss.pgh.pa.us>
<1131749305.14024.32.camel@krb06.tradebot.com>
<22258.1131750131@sss.pgh.pa.us>
On Fri, 2005-11-11 at 18:02 -0500, Tom Lane wrote:
> > There very well could be a pattern in the data which could affect
> > things, however, I'm not sure how to identify it in 100K rows out of
> > 100M.
>
> I conjecture that the problem areas represent places where the key
> sequence is significantly "more random" than it is elsewhere. Hard
> to be more specific than that though.
>
OK, I understand the pattern now.
My two tables hold orders, and order state transitions. Most orders
have two transitions: creation and termination. The problem happens
when there is a significant number of orders where termination is
happening a long time after creation, causing order_transition rows with
old ord_id values to be inserted.
This is valid, so I have to figure out a way to accomodate it.
You mentioned playing with checkpointing and bgwriter earlier in this
thread. I experimented with the bgwriter through the weekend, but I
don't have a good idea what sensible parameter changes are...
Re: checkpointing, currently my checkpoints are happening every 5
minutes (if I turn on fsync, the graph shows checkpoints dramatically).
If I increase the checkpoint_timeout, could that be beneficial? Or
would I just have more time between larger spikes?
-K
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: kelly@tradebotsystems.com, tgl@sss.pgh.pa.us
Subject: Re: 8.x index insert performance
In-Reply-To: <1131979410.14024.49.camel@krb06.tradebot.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