Re: Performance query about large tables, lots of concurrent access

Karl Wright wrote:
> 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
>

I suppose I should also have noted that the postgresql processes that
are dealing with the transactions seem to be CPU bound. Here's a "top"
from the running system:

top - 15:58:50 up 4 days, 4:45, 1 user, load average: 17.14, 21.05, 22.46
Tasks: 194 total, 15 running, 177 sleeping, 0 stopped, 2 zombie
Cpu(s): 98.4% us, 1.5% sy, 0.0% ni, 0.0% id, 0.1% wa, 0.0% hi, 0.0% si
Mem: 16634256k total, 16280244k used, 354012k free, 144560k buffers
Swap: 8008360k total, 56k used, 8008304k free, 15071968k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15966 postgres 18 0 1052m 1.0g 1.0g R 66.5 6.3 0:18.64 postmaster
14683 postgres 17 0 1053m 1.0g 1.0g R 54.9 6.3 0:17.90 postmaster
17050 postgres 15 0 1052m 93m 90m S 50.3 0.6 0:06.42 postmaster
16816 postgres 18 0 1052m 166m 162m R 46.3 1.0 0:04.80 postmaster
16697 postgres 18 0 1052m 992m 988m R 42.3 6.1 0:15.49 postmaster
17272 postgres 16 0 1053m 277m 273m S 30.8 1.7 0:09.91 postmaster
16659 postgres 16 0 1052m 217m 213m R 29.8 1.3 0:06.60 postmaster
15509 postgres 18 0 1052m 1.0g 1.0g R 23.2 6.4 0:26.72 postmaster
16329 postgres 18 0 1052m 195m 191m R 16.9 1.2 0:05.54 postmaster
14019 postgres 20 0 1052m 986m 983m R 16.5 6.1 0:16.50 postmaster
17002 postgres 18 0 1052m 38m 35m R 12.6 0.2 0:02.98 postmaster
16960 postgres 15 0 1053m 453m 449m S 3.3 2.8 0:10.39 postmaster
16421 postgres 15 0 1053m 1.0g 1.0g S 2.3 6.2 0:23.59 postmaster
13588 postgres 15 0 1052m 1.0g 1.0g D 0.3 6.4 0:47.89 postmaster
24708 root 15 0 2268 1136 836 R 0.3 0.0 0:05.92 top
1 root 15 0 1584 520 452 S 0.0 0.0 0:02.08 init

Karl