Re: scaling multiple connections

Tom Lane wrote:
>
> mlw <markw(at)mohawksoft(dot)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