From: | Christopher Petrilli <petrilli(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Sustained inserts per sec ... ? |
Date: | 2005-04-01 20:46:40 |
Message-ID: | 59d991c4050401124638c4ab5d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Apr 1, 2005 3:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Christopher Petrilli <petrilli(at)gmail(dot)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(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2005-04-01 20:53:38 | Re: Sustained inserts per sec ... ? |
Previous Message | Tom Lane | 2005-04-01 20:42:08 | Re: Sustained inserts per sec ... ? |