pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (44+ messages)  latest in thread

Message-ID: <59d991c4050401124638c4ab5d@mail.gmail.com>
Permalink:  ../59d991c4050401124638c4ab5d@mail.gmail.com/
Also on:    postgresql.org/message-id/59d991c4050401124638c4ab5d@mail.gmail.com

 · 

reply

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