AW: [HACKERS] Re: scaling multiple connections
> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: scaling multiple connections
Tom Lane wrote: > > mlw <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster