From: Christopher Petrilli <petrilli@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Marc G. Fournier <scrappy@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sustained inserts per sec ... ?
Date: Fri, 1 Apr 2005 15:46:40 -0500
Message-ID: <59d991c4050401124638c4ab5d@mail.gmail.com> (raw)
In-Reply-To: <28353.1112388128@sss.pgh.pa.us>
References: <20050401140421.P18194@ganymede.hub.org>
<59d991c4050401113857086517@mail.gmail.com>
<28353.1112388128@sss.pgh.pa.us>
On Apr 1, 2005 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Christopher Petrilli <petrilli@gmail.com> writes:
> > I can start at about 4,000 rows/second, but at about 1M rows, it
> > plummets, and by 4M it's taking 6-15 seconds to insert 1000 rows.
> > That's only about 15 rows/second, which is quite pathetic. The
> > problem seems to be related to my indexes, since I have to keep them
> > online (the system in continually querying, as well).
>
> I doubt it has anything to do with your indexes. I'm wondering about
> foreign key checks, myself. What FKs exist on these tables? Is the
> "start" condition zero rows in every table? Which PG version exactly?
Sure, I'm going to post something on my web site when I get some
numbers that will make this more valuable. To answer your question:
1. No foreign keys (long story, but they just don't exist for this one table)
2. Start condition is zero. I'm using multiple inherited tables to
deal with the data "partitioning" since eventual size is billions of
rows. Each "partition" currently has 10M rows in it as a goal.
3. Version 8.0.2, however I started this experiment with 8.0.0.
4. fsync=off
What seems to happen is it slams into a "wall" of some sort, the
system goes into disk write frenzy (wait=90% CPU), and eventually
recovers and starts running for a while at a more normal speed. What
I need though, is to not have that wall happen. It is easier for me
to accept a constant degredation of 5%, rather than a 99% degredation
for short periods, as it can cause cascade problems in the system.
My goal is to gather some numbers, and post code + schema + analysis.
Chris
--
| Christopher Petrilli
| petrilli@gmail.com
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: petrilli@gmail.com, tgl@sss.pgh.pa.us, scrappy@postgresql.org
Subject: Re: Sustained inserts per sec ... ?
In-Reply-To: <59d991c4050401124638c4ab5d@mail.gmail.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