scaling multiple connections

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.

regards, tom lane

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

At 08:39 AM 26-04-2001 -0400, mlw wrote:

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

My postmaster start line looks like:
/usr/local/pgsql/bin/postmaster -A0 -N 24 -B 4096 -i -S -D/sqlvol/pgdev -o -F
-fs -S 2048

Maybe it's the -fs in your start up line.

I tried a similar start line as yours but without -fs and I get consistent
tps values for pgbench.

./pgbench -v -c 1 -t 30 test
starting vacuum...end.
starting full vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 30
number of transactions actually processed: 30/30
tps = 161.938949(including connections establishing)
tps = 180.060140(excluding connections establishing)
[lylyeoh@nimbus pgbench]$ ./pgbench -v -c 3 -t 30 test
starting vacuum...end.
starting full vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 3
number of transactions per client: 30
number of transactions actually processed: 90/90
tps = 172.909666(including connections establishing)
tps = 189.845782(excluding connections establishing)
[lylyeoh@nimbus pgbench]$ ./pgbench -v -c 4 -t 30 test
starting vacuum...end.
starting full vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 4
number of transactions per client: 30
number of transactions actually processed: 120/120
tps = 172.909417(including connections establishing)
tps = 189.319538(excluding connections establishing)

Tested machine is a Dell Poweredge 1300 uniprocessor PIII 500MHz with 128MB
RAM, and a single 9GB HDD.

With -fs there's a decrease, but not as marked as your case. So not sure if
it's really the problem.

Try that out.

Cheerio,
Link.

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.

The above is a typical example of an application that will lose performance
when perfomed in parallel as long as the bottleneck is the db. The only way to make
above behave better when done in parallel is a "fragmented" tracks table.
The chance that two concurrent clients insert into the same table file needs to be
lowered, since above suffers from lock contention. Remember that for the non blocking
lock PostgreSQL currently uses the fastest possible approach optimized in assembler.

A valid design in PostgreSQL would involve n tracks tables tracks_1 .. tracks_n
a union all view "tracks" and some on insert and on update rules. Unfortunalely there
is currently no way to optimize the select with a select rule, that is based on the given where
clause. Nor would the optimizer regard any applicable check constraints for the union all
query. Thus if you don't have separate disks for the tracks_n's you will loose performance
on select.

When not doing the above, your best chance is to tweak the single inserter case,
since that will be fastest.

Andreas