Re: performance problems inserting random vals in index

From: Leonardo F <m_lists(at)yahoo(dot)it>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: performance problems inserting random vals in index
Date: 2010-05-06 12:27:22
Message-ID: 541118.25935.qm@web29013.mail.ird.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm sorry I have to come back at this, but the solution the list gave helped,
but didn't fully solved my problems...

To sum up:

I have a simple table that has indexes on 2 integer columns.
Data is inserted very often (no updates, no deletes, just inserts): at
least 4000/5000 rows per second. The input for the 2 indexed columns
is very random.

I was experiencing bad insertion times past 20M rows.

Some of you suggested that it might have to do with the indexes being
too big to fit in shared_buffers, so I raised it and, in fact, that solved the
issue... but for another 50M rows: basically at roughly 70M rows I'm
back at poor insertion times (disk always 100% busy).

The indexes at this point are 1.7GB each. My shared buffers is 10GB
(machine has 32GB). So I expect the whole indexes to fit in ram, yet
the disk array is 100% busy...

What's going on? What can I try to get back at good insertion
performance?

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Shoaib Mir 2010-05-06 12:40:35 Re: lobj_lock_table structure
Previous Message AI Rumman 2010-05-06 12:00:26 lobj_lock_table structure