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: Fri, 11 Nov 2005 16:48:25 -0600
Message-ID: <1131749305.14024.32.camel@krb06.tradebot.com> (raw)
In-Reply-To: <13167.1131668008@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>
On Thu, 2005-11-10 at 19:13 -0500, Tom Lane wrote:
> Kelly Burkhart <kelly@tradebotsystems.com> writes:
> > ... A graph showing the performance
> > characteristics is here:
>
> > <http://kkcsm.net/pgcpy.jpg;
>
> I hadn't looked at this chart till just now, but it sure seems to put a
> crimp in my theory that you are running out of room to hold the indexes
> in RAM. That theory would predict that once you fall over the knee of
> the curve, performance would get steadily worse; instead it gets
> markedly worse and then improves a bit. And there's another cycle of
> worse-and-better around 80M rows. I have *no* idea what's up with that.
> Anyone? Kelly, could there be any patterns in the data that might be
> related?
I modified my original program to insert generated, sequential data.
The following graph shows the results to be flat:
<http://kkcsm.net/pgcpy_20051111_1.jpg;
Thus, hardware is sufficient to handle predictably sequential data.
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.
If I could identify a pattern, what could I do about it? Could I do
some kind of a reversible transform on the data? Is it better to insert
nearly random values? Or nearly sequential?
I now have an 8G and a 16G machine I'm loading the data into. I'll
report back after that's done.
I also want to try eliminating the order_main table, moving fields to
the transition table. This will reduce the number of index updates
significantly at the cost of some wasted space in the table...
-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: <1131749305.14024.32.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