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

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?

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

On Tue, 3 Jun 2008, andrew klassen wrote:
> Basically, I have a somewhat constant rate of inserts/updates that go
> into a work queue and then get passed to postgres.

> The cpu load is not that high, i.e. plenty of idle cpu. I am running an older
> version of freebsd and the iostat output is not very detailed.

If you're running a "work queue" architecture, that probably means you
only have one thread doing all the updates/inserts? It might be worth
going multi-threaded, and issuing inserts and updates through more than
one connection. Postgres is designed pretty well to scale performance by
the number of simultaneous connections.

Matthew

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