Message-ID: <4676DD57.7070304@metacarta.com>
Date: Mon, 18 Jun 2007 15:30:31 -0400
From: Karl Wright <kwright@metacarta.com>
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Performance query about large tables, lots of concurrent access
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I have an application which really exercises the performance of 
postgresql in a major way, and I am running into a performance 
bottleneck with Postgresql 8.1 that I do not yet understand.

Here are the details:

- There is a primary table, with some secondary tables
- The principle transaction consists of a "SELECT...FOR UPDATE", 
followed by either an INSERT or an UPDATE on the primary table
- INSERTs, DELETEs, and UPDATEs may occur on the secondary table 
depending on what happens with the primary table, for any given 
transaction.  The secondary table has about 10x the number of rows as 
the primary.
- All operations are carefully chosen so that highly discriminatory 
indexes are used to locate the record(s) in question.  The execution 
plans show INDEX SCAN operations being done in all cases.
- At any given time, there are up to 100 of these operations going on at 
once against the same database.

What I am seeing:

- In postgresql 7.4, the table activity seems to be gated by locks, and 
runs rather slowly except when the sizes of the tables are small.
- In postgresql 8.1, locks do not seem to be an issue, and the activity 
runs about 10x faster than for postgresql 7.4.
- For EITHER database version, the scaling behavior is not the log(n) 
behavior I'd expect (where n is the number of rows in the table), but 
much more like linear performance.  That is, as the tables grow, 
performance drops off precipitously.  For a primary table size up to 
100,000 rows or so, I get somewhere around 700 transactions per minute, 
on average.  Between 100,000 and 1,000,000 rows I got some 150 
transactions per minute.  At about 1,500,000 rows I get about 40 
transactions per minute.
- Access to a row in the secondary table (which right now has 13,000,000 
rows in it) via an index that has extremely good discriminatory ability 
on a busy machine takes about 90 seconds elapsed time at the moment - 
which I feel is pretty high.

I tried increasing the shared_buffers parameter to see if it had any 
impact on overall throughput.  It was moderately helpful going from the 
small default value up to 8192, but less helpful when I increased it 
beyond that.  Currently I have it set to 131072.

Question:  Does anyone have any idea what bottleneck I am hitting?  An 
index's performance should in theory scale as the log of the number of 
rows - what am I missing here?

Thanks very much!
Karl