Re: 8.x index insert performance - Mailing list pgsql-performance

I'm surprised that no one seems to have yet suggested the following
simple experiment:

Increase the RAM 4GB -> 8GB, tune for best performance, and
repeat your 100M row insert experiment.

Does overall insert performance change?  Does the performance
drop <foo> rows in still occur?  Does it occur in ~ the same place?
Etc.

If the effect does seem to be sensitive to the amount of RAM in the
server, it might be worth redoing the experiment(s) with 2GB and
16GB as well...

ron

-----Original Message-----
From: Kelly Burkhart <kelly@tradebotsystems.com>
Sent: Oct 31, 2005 12:12 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] 8.x index insert performance

Greetings,

We are running some performance tests in which we are attempting to
insert about 100,000,000 rows in a database at a sustained rate.  About
50M rows in, our performance drops dramatically.

This test is with data that we believe to be close to what we will
encounter in production.  However in tests with purely generated,
sequential data, we did not notice this slowdown.  I'm trying to figure
out what patterns in the "real" data may be causing us problems.

I have log,data and indexes on separate LUNs on an EMC SAN.  Prior to
slowdown, each partition is writing at a consistent rate.  Index
partition is reading at a much lower rate.  At the time of slowdown,
index partition read rate increases, all write rates decrease.  CPU
utilization drops.

The server is doing nothing aside from running the DB.  It is a dual
opteron (dual core, looks like 4 cpus) with 4GB RAM.  shared_buffers =
32768.  fsync = off.  Postgres version is 8.1.b4.  OS is SuSE Enterprise
server 9.

My leading hypothesis is that one indexed column may be leading to our
issue.  The column in question is a varchar(12) column which is non-null
in about 2% of the rows.  The value of this column is 5 characters which
are the same for every row, followed by a 7 character zero filled base
36 integer.  Thus, every value of this field will be exactly 12 bytes
long, and will be substantially the same down to the last bytes.

Could this pattern be pessimal for a postgresql btree index?  I'm
running a test now to see if I can verify, but my runs take quite a long
time...

If this sounds like an unlikely culprit how can I go about tracking down
the issue?

Thanks,

-K

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org