Subject: 8.x index insert performance
From: Kelly Burkhart <kelly@tradebotsystems.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Mon, 31 Oct 2005 11:12:05 -0600
Message-Id: <1130778725.7026.33.camel@krb06.tradebot.com>
Mime-Version: 1.0

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