Re: scaling multiple connections - Mailing list pgsql-hackers

Tom Lane wrote:
> 
> mlw <markw@mohawksoft.com> writes:
> > I am getting a bit concerned about Postgres 7.1 performance with
> > multiple connections. Postgres does not seem to scaling very
> > well. Below there is a list of outputs from pgbench with different
> > number of clients, you will see that postgres' performance in the
> > benchmark drops with each new connection.  Shouldn't the tps stay
> > fairly constant?
> 
> There was quite a long thread about this in pghackers back in Jan/Feb
> (or so).  You might want to review it.  One thing I recall is that
> you need a "scaling factor" well above 1 if you want meaningful results
> --- at scale factor 1, all of the transactions want to update the same
> row, so of course there's no parallelism and a lot of lock contention.
> 
> The default WAL tuning parameters (COMMIT_DELAY, WAL_SYNC_METHOD, and
> friends) are probably not set optimally in 7.1.  We are hoping to hear
> about some real-world performance results so that we can tweak them in
> future releases.  I do not trust benchmarks as simplistic as pgbench for
> doing that kind of tweaking, however.
> 
I agree with you about the benchmarks, but it does behave similar to what I
have in my app, which is why I used it for an example.

If you are familiar with cddb (actually freedb.org) I am taking that data in
putting it into postgres. The steps are: (pseudo code)

select nextval('cdid_seq');

begin;

insert into titles (...) values (...);

for(i=0; i < tracks; i++)insert into tracks (...) values (...);

commit;


When running stand alone on my machine, it will hovers around 130 full CDs per
second. When I start two processes it drops to fewer than 100 inserts per
second. When I add another, it drops even more. The results I posted with
pgbench pretty much showed what I was seeing in my program.

I hacked the output of pgbench to get me tabbed delimited fields to chart, but
it is easier to look at, see the results below. This is the same build and same
startup scripts on the two different machines. I know this isn't exactly
scientific, but I have a few bells going off suggesting that postgres has some
SMP scaling issues.


My Dual PIII 600MHZ, 500M RAM, Linux 2.4.3 SMP
pg_xlog is pointed to a different drive than is base.
I/O Promise dual IDE/66, xlog on one drive, base on another.

count transaction    time (excluding connection)
1       32      175.116
2       32      138.288
3       32      102.890
4       32      88.243
5       32      77.024
6       32      62.648
7       32      61.231
8       32      60.017
9       32      56.034
10      32      57.854
11      32      50.812
12      32      53.019
13      32      50.289
14      32      46.421
15      32      44.496
16      32      45.297
17      32      41.725
18      32      46.048
19      32      45.007
20      32      41.584
21      32      43.420
22      32      39.640
23      32      43.250
24      32      41.617
25      32      42.511
26      32      38.369
27      32      38.919
28      32      38.813
29      32      39.242
30      32      39.859
31      32      37.938
32      32      41.516


Single processor PII 450, 256M, Linux 2.2.16
pg_xlog pointing to different drive than base
I/O Adaptec 2940, Two seagate barracudas.

count transaction    time (excluding connection)

1       32      154.539
2       32      143.609
3       32      144.608
4       32      141.718
5       32      128.759
6       32      154.388
7       32      144.097
8       32      149.828
9       32      143.092
10      32      146.548
11      32      141.613
12      32      139.692
13      32      137.425
14      32      137.227
15      32      134.669
16      32      128.277
17      32      127.440
18      32      121.224
19      32      121.915
20      32      120.740
21      32      118.562
22      32      116.271
23      32      113.883
24      32      113.558
25      32      109.293
26      32      108.782
27      32      108.796
28      32      105.684
29      32      103.614
30      32      102.232
31      32      100.514
32      32      99.339