Re: insert/update tps slow with indices on table > 1M rows

From: andrew klassen <aptklassen(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: insert/update tps slow with indices on table > 1M rows
Date: 2008-06-04 17:24:09
Message-ID: 880373.23550.qm@web37305.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


I agree that the discs are probably very busy. I do have 2 disks but they are
for redundancy. Would it help to put the data, indexes and xlog on separate
disk partitions?
I'll try adding more threads to update the table as you suggest.

----- Original Message ----
From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Sent: Wednesday, June 4, 2008 10:10:38 AM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

On Wed, 4 Jun 2008, andrew klassen wrote:
> I am using multiple threads, but only one worker thread for insert/updated to this table.
> I don't mind trying to add multiple threads for this table, but my guess is it would not
> help because basically the overall tps rate is decreasing so dramatically. Since
> the cpu time consumed by the corresponding postgres server process for my thread is
> small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else.
> Do you agree or is there some flaw in my reasoning?

There is indeed a flaw in your reasoning - there may be very little CPU
time consumed, but that just indicates that the discs are busy. Getting
Postgres to do multiple things at once will cause a more efficient use of
the disc subsystem, resulting in greater overall throughput. This is
especially the case if you have multiple discs in your box.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                -- Kyle Hearn

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message James Mansion 2008-06-04 20:15:45 Re: insert/update tps slow with indices on table > 1M rows
Previous Message John Beaver 2008-06-04 15:58:32 PgPool parallel query performance rules of thumb