From: Kelly Burkhart <kelly@tradebotsystems.com>
To: pgsql-performance@postgresql.org
Subject: 8.x index insert performance
Date: Mon, 31 Oct 2005 11:12:05 -0600
Message-ID: <1130778725.7026.33.camel@krb06.tradebot.com> (raw)
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
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: kelly@tradebotsystems.com
Subject: Re: 8.x index insert performance
In-Reply-To: <1130778725.7026.33.camel@krb06.tradebot.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