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?

I am using pgbench because I saw this performance issue with a project I was
developing. I decided to try doing operations in parallel, thinking that
postgres would scale. What I found was the more machines I added to the task,
the slower the processing was. 

Anyone have any ideas? Is this how it is supposed to be?

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

The database is on a dedicated PCI-IDE/66 promise card, with a 5400rpm maxtor
drive, not the best hardware, I grant you, but that should have little to do
with the scaling aspect.

I am running redhat linux 7.0, kernel 2.4.3. 512M ram, dual PIII 600mhz.

[markw@snoopy pgbench]$ ./pgbench -v  -c 1  -t 30 pgbench
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 = 218.165952(including connections establishing)
tps = 245.062001(excluding connections establishing)
[markw@snoopy pgbench]$ ./pgbench -v  -c 2  -t 30 pgbench
starting vacuum...end.
starting full vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 2
number of transactions per client: 30
number of transactions actually processed: 60/60
tps = 200.861024(including connections establishing)
tps = 221.175326(excluding connections establishing)
[markw@snoopy pgbench]$ ./pgbench -v  -c 3  -t 30 pgbench
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 = 144.053242(including connections establishing)
tps = 154.083205(excluding connections establishing)
[markw@snoopy pgbench]$ ./pgbench -v  -c 4  -t 30 pgbench
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 = 129.709537(including connections establishing)
tps = 137.852284(excluding connections establishing)
[markw@snoopy pgbench]$ ./pgbench -v  -c 5  -t 30 pgbench
starting vacuum...end.
starting full vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 5
number of transactions per client: 30
number of transactions actually processed: 150/150
tps = 103.569559(including connections establishing)
tps = 108.535287(excluding connections establishing)


.......

[markw@snoopy pgbench]$ ./pgbench -v  -c 20  -t 30 pgbench
starting vacuum...end.
starting full vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 20
number of transactions per client: 30
number of transactions actually processed: 600/600
tps = 40.600209(including connections establishing)
tps = 41.352773(excluding connections establishing)

Thu, 26 Apr 2001 08:39:25 -0400
mlw <markw@mohawksoft.com>
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

Thu, 26 Apr 2001 11:53:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: scaling multiple connections 
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

Thu, 26 Apr 2001 13:45:48 -0400
mlw <markw@mohawksoft.com>
Re: scaling multiple connections
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.


Fri, 27 Apr 2001 18:24:30 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: scaling multiple connections