Thread
-
7.2 is slow?
Tatsuo Ishii <t-ishii@sra.co.jp> — 2001-12-17T06:46:37Z
With the freshly retrieved current source, now PostgreSQL is running fine on an AIX 5L box. Thanks Tom. BTW, I have done some benchmarking using pgbench on this machine and found that 7.2 is almost two times slower than 7.1. The hardware is a 4way machine. Since I thought that 7.2 improves the performance for SMP machines, I'm now wondering why 7.2 is so slow. postgresql.conf paramters changed from default values are: max_connections = 1024 wal_sync_method = fdatasync shared_buffers = 4096 deadlock_timeout = 1000000 configure option is: --enable-multibyte=EUC_JP Of cousre, these setting are identical for both 7.1 and 7.2. See attached graph...
-
Re: 7.2 is slow?
Bruce Momjian <pgman@candle.pha.pa.us> — 2001-12-17T07:40:42Z
> With the freshly retrieved current source, now PostgreSQL is running > fine on an AIX 5L box. Thanks Tom. > > BTW, I have done some benchmarking using pgbench on this machine and > found that 7.2 is almost two times slower than 7.1. The hardware is a > 4way machine. Since I thought that 7.2 improves the performance for ^^^^ > SMP machines, I'm now wondering why 7.2 is so slow. Ewe. I will remind people that this multi-cpu setup is exactly the type of machine we wanted to speed up with the new light-weight locking code that reduced spinlock looping. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
-
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> — 2001-12-17T08:54:14Z
Tatsuo Ishii wrote: > > With the freshly retrieved current source, now PostgreSQL is running > fine on an AIX 5L box. Thanks Tom. > > BTW, I have done some benchmarking using pgbench on this machine and > found that 7.2 is almost two times slower than 7.1. Is this an AIX specific problem or do all/all SMP/all 4way computers have it ? Is this a bug that needs to be addresse before release of final ? Or would we just prominently warn people that the new release is 2x slower and advise upgrading only if they have powerful enough computers (system load < 0.5 during normal operation)? ------------------------ Hannu
-
Re: 7.2 is slow?
Tatsuo Ishii <t-ishii@sra.co.jp> — 2001-12-17T09:26:44Z
> > BTW, I have done some benchmarking using pgbench on this machine and > > found that 7.2 is almost two times slower than 7.1. > > Is this an AIX specific problem or do all/all SMP/all 4way computers > have it ? Not sure. As far as I can tell, nobody except me has tested 7.2 on big boxes. > Is this a bug that needs to be addresse before release of final ? I hope this would be solved before final. At least I would like to know what's going on. Anyway, I will do some testings on a smaller machine (that is my laptop) to see if I see the same performance degration on it. -- Tatsuo Ishii
-
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> — 2001-12-17T10:43:05Z
Tatsuo Ishii wrote: > > > > BTW, I have done some benchmarking using pgbench on this machine and > > > found that 7.2 is almost two times slower than 7.1. > > > > Is this an AIX specific problem or do all/all SMP/all 4way computers > > have it ? > > Not sure. As far as I can tell, nobody except me has tested 7.2 on big > boxes. > > > Is this a bug that needs to be addresse before release of final ? > > I hope this would be solved before final. At least I would like to > know what's going on. > > Anyway, I will do some testings on a smaller machine (that is my > laptop) to see if I see the same performance degration on it. How did you test ? I could do the same test on Dual Pentium III / 800 w/1024 MB with IBM 45 G/7200 IDE disk. So we could compare different platforms as well :) ------------- Hannu
-
Re: 7.2 is slow?
Mathijs Brands <mathijs@ilse.nl> — 2001-12-17T12:30:02Z
On Mon, Dec 17, 2001 at 12:43:05PM +0200, Hannu Krosing allegedly wrote: > Tatsuo Ishii wrote: > > > > > > BTW, I have done some benchmarking using pgbench on this machine and > > > > found that 7.2 is almost two times slower than 7.1. > > > > > > Is this an AIX specific problem or do all/all SMP/all 4way computers > > > have it ? > > > > Not sure. As far as I can tell, nobody except me has tested 7.2 on big > > boxes. > > > > > Is this a bug that needs to be addresse before release of final ? > > > > I hope this would be solved before final. At least I would like to > > know what's going on. > > > > Anyway, I will do some testings on a smaller machine (that is my > > laptop) to see if I see the same performance degration on it. > > How did you test ? > > I could do the same test on Dual Pentium III / 800 w/1024 MB > with IBM 45 G/7200 IDE disk. > > So we could compare different platforms as well :) I could do some testing on a Sun 450 / 4x400 MHz / 4 GB, if that's helpful. Cheers, Mathijs
-
Re: 7.2 is slow?
bpalmer <bpalmer@crimelabs.net> — 2001-12-17T13:06:54Z
> > > > Is this an AIX specific problem or do all/all SMP/all 4way computers > > > > have it ? I'll have 4 way and 8 way xeon boxes tues evening that I can test this against (though I won't get to test till wed unless I don't sleep) - Brandon ---------------------------------------------------------------------------- c: 646-456-5455 h: 201-798-4983 b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5
-
Re: 7.2 is slow?
Tatsuo Ishii <t-ishii@sra.co.jp> — 2001-12-17T14:12:53Z
> > How did you test ? > > > > I could do the same test on Dual Pentium III / 800 w/1024 MB > > with IBM 45 G/7200 IDE disk. > > > > So we could compare different platforms as well :) > > I could do some testing on a Sun 450 / 4x400 MHz / 4 GB, if that's helpful. > > Cheers, > > Mathijs > > I'll have 4 way and 8 way xeon boxes tues evening that I can test this > against (though I won't get to test till wed unless I don't sleep) > > - Brandon Thanks to everyone. Here are the methods I used for testings including generating graphs (actually very simple). (1) Tweak postgresql.conf to allow large concurrent users. I tested up to 1024 on AIX, but for the comparison I think testing up to 128 users is enough. Here are example settings: max_connections = 128 shared_buffers = 4096 deadlock_timeout = 100000 You might want to tweak wal_sync_method to get the best performance. However this should not affect the comparison between 7.1 and 7.2. (2) Run: sh bench.sh It will invoke pgbench for various concurrent users. So you need to install pgbench beforehand (it's in contrib/pgbench. Just type make install there to install pgbench). This will take while. (3) (2) will generate a file named "bench.data". The file have rows where the first column is the number of concurrent users and second one is the tps. Rename it to bench-7.2.data. (4) Do (1) and (2) for PostgreSQL 7.1 and rename bench.data to bench-7.1.data. (5) Run plot.sh to see the result graph. Note that plot.sh requires gnuplot. --- Tatsuo Ishii -
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> — 2001-12-17T15:18:18Z
It seems that on dual PIII we are indeed faster than 7.1.3 for small number of clients but slower for large number (~ 40) My initial results on dual PIII/800 are as follows 7.1.3 7.2b4 7.2b4-FULL ================================================================== ./pgbench -i -p 5433 ./pgbench -p 5433 -c 1 -t 100 240/251 217/223 177/181 ./pgbench -p 5433 -c 5 -t 100 93/ 94 211/217 207/212 ./pgbench -p 5433 -c 10 -t 100 57/ 58 145/148 160/163 ------------------------------------------------------------------ ./pgbench -i -s 10 -p 5433 ./pgbench -p 5433 -c 1 -t 100 171/177 162/166 169/173 ./pgbench -p 5433 -c 5 -t 100 140/143 191/196 202/207 ./pgbench -p 5433 -c 10 -t 100 132/135 165/168 159/163 ./pgbench -p 5433 -c 25 -t 100 65/ 66 60/ 60 75/ 76 ./pgbench -p 5433 -c 50 -t 100 60/ 61 43/ 43 55/ 59 ./pgbench -p 5433 -c 100 -t 100 48/ 48 23/ 23 34/ 34 ------------------------------------------------------------------ One of thereasons seems to be that vacuum has chaged after oding psql -p 5433 -c 'vacuum full' the result of ./pgbench -p 5433 -c 100 -t 100 was 34/34 - still ~25% slower than 7.1.3 but much better than with non-full vacuum (which I guess is used by pgbench The third column 7.2b4-FULL is done by running "psql -p 5433 -c 'vacuum full'" between each pgbench run - now the lines cross somwhere between 25 and 50 concurrent users One of the reasons pg is slower on last limes of my test is that postgres is slower when vacuum is not done often enough - on fresh db "./pgbench -p 5433 -c 100 -t 10" gives 67/75 as result indicating that one reason is just our non-overwriting storage manager. I also tried to outsmart pg by running the new vacuum concurrently, but was disappointed. vacuuming in 'normal' psql gave me 20/20 tps and running with nice psql gave 21/21 tps running ./pgbench -p 5433 -c 100 -t 100 as first benchmark gave the same result as running it after vacuum full ----------------------------------------------------------------------- PS. I hope to get single-processor results from the same computer in about 6 hours as well (after my co-worker arrives home and can reboot his computer to single-user) Inxc - after you have rebooted to single-processor mode, pleas start the postgres daemon by su - hannu cd db/7.2b4/ bin/pg_ctl -D data -l logfile and ther run above pgbench commands from cd /home/hannu/src/postgresql-7.1.3/contrib/pgbench/ ----------------------------------------------------------------------- -
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> — 2001-12-17T15:37:16Z
Tatsuo Ishii wrote: > > > Thanks to everyone. Here are the methods I used for testings including > generating graphs (actually very simple). > > (1) Tweak postgresql.conf to allow large concurrent users. I tested up > to 1024 on AIX, but for the comparison I think testing up to 128 > users is enough. Here are example settings: > > max_connections = 128 > shared_buffers = 4096 > deadlock_timeout = 100000 > > You might want to tweak wal_sync_method to get the best > performance. However this should not affect the comparison between > 7.1 and 7.2. > > (2) Run: > > sh bench.sh I have no more time today, but I'll redo the tests with your script tomorrow (after I have found where to stick database name and port :) ---------------- Hannu
-
Re: 7.2 is slow?
Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-17T15:53:39Z
Hannu Krosing <hannu@tm.ee> writes: > ./pgbench -i -s 10 -p 5433 > ./pgbench -p 5433 -c 1 -t 100 171/177 162/166 169/173 > ./pgbench -p 5433 -c 5 -t 100 140/143 191/196 202/207 > ./pgbench -p 5433 -c 10 -t 100 132/135 165/168 159/163 > ./pgbench -p 5433 -c 25 -t 100 65/ 66 60/ 60 75/ 76 > ./pgbench -p 5433 -c 50 -t 100 60/ 61 43/ 43 55/ 59 > ./pgbench -p 5433 -c 100 -t 100 48/ 48 23/ 23 34/ 34 You realize, of course, that when the number of clients exceeds the scale factor you're not really measuring anything except update contention on the "branch" rows? Every transaction tries to update the balance for its branch, so if you have more clients than branches then there will be lots of transactions blocked waiting for someone else to commit. With a 10:1 ratio, there will be several transactions blocked waiting for *each* active transaction; and when that guy commits, all the others will waken simultaneously and contend for the chance to update the branch row. One will win, the others will go back to sleep, having done nothing except wasting CPU time. Thus a severe falloff in measured TPS is inevitable when -c >> -s. I don't think this scenario has all that much to do with real-world loads, however. I think you are right that the difference between 7.1 and 7.2 may have more to do with the change in VACUUM strategy than anything else. Could you retry the test after changing all the "vacuum" commands in pgbench.c to "vacuum full"? regards, tom lane
-
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> — 2001-12-17T16:57:03Z
Tom Lane wrote: > > Hannu Krosing <hannu@tm.ee> writes: > > ./pgbench -i -s 10 -p 5433 > > ./pgbench -p 5433 -c 1 -t 100 171/177 162/166 169/173 > > ./pgbench -p 5433 -c 5 -t 100 140/143 191/196 202/207 > > ./pgbench -p 5433 -c 10 -t 100 132/135 165/168 159/163 > > ./pgbench -p 5433 -c 25 -t 100 65/ 66 60/ 60 75/ 76 > > ./pgbench -p 5433 -c 50 -t 100 60/ 61 43/ 43 55/ 59 > > ./pgbench -p 5433 -c 100 -t 100 48/ 48 23/ 23 34/ 34 > > You realize, of course, that when the number of clients exceeds the > scale factor you're not really measuring anything except update > contention on the "branch" rows? Oops! I thought that the deciding table would be tellers and this -s 10 would be ok for up to 100 users I will retry this with Tatsuos using -s 128(if it still fits on disk - taking about 160MB/1Mtuple needs 1.6GB for test with -s 100 and I currently have only 1.3G free) I re-run some of them with -s 50 (on 7.2b4) each one after running "psql -p 5433 -c 'vacuum full;checkpoint;'" tps ./pgbench -p 5433 -i -s 50 ./pgbench -p 5433 -c 1 -t 1000 93/ 93 ./pgbench -p 5433 -c 3 -t 333 106/107 ./pgbench -p 5433 -c 5 -t 200 106/107 ./pgbench -p 5433 -c 8 -t 125 112/113 ./pgbench -p 5433 -c 10 -t 100 94/ 95 ./pgbench -p 5433 -c 25 -t 40 98/ 91 ./pgbench -p 5433 -c 50 -t 20 70/ 74 > Every transaction tries to update > the balance for its branch, so if you have more clients than branches > then there will be lots of transactions blocked waiting for someone > else to commit. With a 10:1 ratio, there will be several transactions > blocked waiting for *each* active transaction; and when that guy > commits, all the others will waken simultaneously and contend for the > chance to update the branch row. One will win, the others will go > back to sleep, having done nothing except wasting CPU time. Thus a > severe falloff in measured TPS is inevitable when -c >> -s. I don't > think this scenario has all that much to do with real-world loads, > however. It probably models a real-world ill-tuned database :) And it seems that we fall off more rapidly on 7.2 than we did on 7.1 , even so much so that we will be slower in the end. > I think you are right that the difference between 7.1 and 7.2 may have > more to do with the change in VACUUM strategy than anything else. Could > you retry the test after changing all the "vacuum" commands in pgbench.c > to "vacuum full"? The third column should be the equivalent of doing so (I did run 'vacuum full' between each pgbench and AFACT pgbencg runs vacuun only before each run) -------------- Hannu -
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> — 2001-12-21T12:21:12Z
Tom Lane wrote: > > Hannu Krosing <hannu@tm.ee> writes: > > ./pgbench -i -s 10 -p 5433 > > ./pgbench -p 5433 -c 1 -t 100 171/177 162/166 169/173 > > ./pgbench -p 5433 -c 5 -t 100 140/143 191/196 202/207 > > ./pgbench -p 5433 -c 10 -t 100 132/135 165/168 159/163 > > ./pgbench -p 5433 -c 25 -t 100 65/ 66 60/ 60 75/ 76 > > ./pgbench -p 5433 -c 50 -t 100 60/ 61 43/ 43 55/ 59 > > ./pgbench -p 5433 -c 100 -t 100 48/ 48 23/ 23 34/ 34 > > You realize, of course, that when the number of clients exceeds the > scale factor you're not really measuring anything except update > contention on the "branch" rows? Every transaction tries to update > the balance for its branch, so if you have more clients than branches > then there will be lots of transactions blocked waiting for someone > else to commit. With a 10:1 ratio, there will be several transactions > blocked waiting for *each* active transaction; and when that guy > commits, all the others will waken simultaneously and contend for the > chance to update the branch row. One will win, the others will go > back to sleep, having done nothing except wasting CPU time. Thus a > severe falloff in measured TPS is inevitable when -c >> -s. I don't > think this scenario has all that much to do with real-world loads, > however. I did some benchmarking and the interesting part is that 7.2b4 is up to 2.5X faster than 7.1.3 for _small_ scale factors and up to 25% slower when there is no contention (-s128, clients <= 128) Perhaps the waiting on lock somehow organizes things to happen in some order that avoids some stupidity in some other locking logic ? I run benchmark (with added vacuum full for 7.2b4) on Dual PIII 800MHz with 1 G of RAM and an IDE disk. The results are mean from six runs with two slowes removed (there was other activity going on sometimes) they are for scale factors 1, 10 and 128 in order to measure real performance of roughly the _same_ dataset each test run did the same total number of transactions 512 with each client doing 512/nr_of_trx.
-
Re: 7.2 is slow?
Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-21T16:00:01Z
Hannu Krosing <hannu@tm.ee> writes: > in order to measure real performance of roughly the _same_ dataset each > test run did the same total number of transactions 512 with each client > doing 512/nr_of_trx. That means you're only measuring a few transactions per backend (as few as 4, near the upper end of the scale). I think the results may say more about backend-startup transients than true peak throughput. Could you try it again with a run about ten times that long? regards, tom lane
-
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> — 2001-12-21T21:31:12Z
Tom Lane wrote: >Hannu Krosing <hannu@tm.ee> writes: > >>in order to measure real performance of roughly the _same_ dataset each >>test run did the same total number of transactions 512 with each client >>doing 512/nr_of_trx. >> > >That means you're only measuring a few transactions per backend (as few >as 4, near the upper end of the scale). I think the results may say >more about backend-startup transients than true peak throughput. >Could you try it again with a run about ten times that long? > I did run 4096trx on 7.2b4 with -s 1, best 4-of-6 512trx 4096trx ratio 1 180.59 90.15 2.00 2 221.52 80.92 2.74 4 203.72 75.60 2.69 8 179.54 69.29 2.59 16 156.68 63.15 2.48 32 123.48 57.73 2.14 64 89.99 54.14 1.66 128 61.84 48.97 1.26 so it seems that large number of of transactions degrades tps performance faster than connection setup overhead. the I'll try running the whole suite again with higher number of transactions i a few days ------------------ Hannu