Re: 8.x index insert performance

Kelly wrote:
> 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?

well, can you defer index generation until after loading the set (or use
COPY?)

if that index is causing the problem, you may want to consider setting
up partial index to exclude null values.

One interesting thing to do would be to run your inserting process until
slowdown happens, stop the process, and reindex the table and then
resume it, and see if this helps.

Merlin