Thread: scaling multiple connections
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)
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.