Forgot to mention an important thing: I set enable_seqscan = off in postgresql.conf.
> I found following mail in my mail archive and tried the same test with > 7.4 current. Contrary to my expectation 7.4 showed some performance > degration with continuous pgbench runs: > > $ pgbench -c 5 -t 1000 -n test > tps = 57.444037 (including connections establishing) > tps = 57.455300 (excluding connections establishing) > > $ pgbench -c 5 -t 1000 -n test > tps = 54.125785 (including connections establishing) > tps = 54.134871 (excluding connections establishing) > > $pgbench -c 5 -t 1000 -n test > tps = 51.116465 (including connections establishing) > tps = 51.124878 (excluding connections establishing) > > $ pgbench -c 5 -t 1000 -n test > tps = 50.410659 (including connections establishing) > tps = 50.420215 (excluding connections establishing) > > $ pgbench -c 5 -t 1000 test > tps = 46.791980 (including connections establishing) > tps = 46.799837 (excluding connections establishing) > > Any idea? > > data is initialized by pgbench -i -s 10. > -- > Tatsuo Ishii > > > From: Tom Lane <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED] > > Date: Fri, 24 May 2002 16:42:55 -0400 > > > > Per previous discussion, I have committed changes that cause the btree > > and hash index methods to mark index tuples "killed" the first time they > > are fetched after becoming globally dead. Subsequently the killed > > entries are not returned out of indexscans, saving useless heap fetches. > > (I haven't changed rtree and gist yet; they will need some internal > > restructuring to do this efficiently. Perhaps Oleg or Teodor would like > > to take that on.) > > > > This seems to make a useful improvement in pgbench results. Yesterday's > > CVS tip gave me these results: > > > > (Running postmaster with "-i -F -B 1024", other parameters at defaults, > > and pgbench initialized with "pgbench -i -s 10 bench") > > > > $ time pgbench -c 5 -t 1000 -n bench > > tps = 26.428787(including connections establishing) > > tps = 26.443410(excluding connections establishing) > > real 3:09.74 > > $ time pgbench -c 5 -t 1000 -n bench > > tps = 18.838304(including connections establishing) > > tps = 18.846281(excluding connections establishing) > > real 4:26.41 > > $ time pgbench -c 5 -t 1000 -n bench > > tps = 13.541641(including connections establishing) > > tps = 13.545646(excluding connections establishing) > > real 6:10.19 > > > > Note the "-n" switches here to prevent vacuums between runs; the point > > is to observe the degradation as more and more dead tuples accumulate. > > > > With the just-committed changes I get (starting from a fresh start): > > > > $ time pgbench -c 5 -t 1000 -n bench > > tps = 28.393271(including connections establishing) > > tps = 28.410117(excluding connections establishing) > > real 2:56.53 > > $ time pgbench -c 5 -t 1000 -n bench > > tps = 23.498645(including connections establishing) > > tps = 23.510134(excluding connections establishing) > > real 3:33.89 > > $ time pgbench -c 5 -t 1000 -n bench > > tps = 18.773239(including connections establishing) > > tps = 18.780936(excluding connections establishing) > > real 4:26.84 > > > > The remaining degradation is actually in seqscan performance, not > > indexscan --- unless one uses a much larger -s setting, the planner will > > think it ought to use seqscans for updating the "branches" and "tellers" > > tables, since those nominally have just a few rows; and there's no way > > to avoid scanning lots of dead tuples in a seqscan. Forcing indexscans > > helps some in the former CVS tip: > > > > $ PGOPTIONS="-fs" time pgbench -c 5 -t 1000 -n bench > > tps = 28.840678(including connections establishing) > > tps = 28.857442(excluding connections establishing) > > real 2:53.9 > > $ PGOPTIONS="-fs" time pgbench -c 5 -t 1000 -n bench > > tps = 25.670674(including connections establishing) > > tps = 25.684493(excluding connections establishing) > > real 3:15.7 > > $ PGOPTIONS="-fs" time pgbench -c 5 -t 1000 -n bench > > tps = 22.593429(including connections establishing) > > tps = 22.603928(excluding connections establishing) > > real 3:42.7 > > > > and with the changes I get: > > > > $ PGOPTIONS=-fs time pgbench -c 5 -t 1000 -n bench > > tps = 29.445004(including connections establishing) > > tps = 29.463948(excluding connections establishing) > > real 2:50.3 > > $ PGOPTIONS=-fs time pgbench -c 5 -t 1000 -n bench > > tps = 30.277968(including connections establishing) > > tps = 30.301363(excluding connections establishing) > > real 2:45.6 > > $ PGOPTIONS=-fs time pgbench -c 5 -t 1000 -n bench > > tps = 30.209377(including connections establishing) > > tps = 30.230646(excluding connections establishing) > > real 2:46.0 > > > > > > This is the first time I have ever seen repeated pgbench runs without > > substantial performance degradation. Not a bad result for a Friday > > afternoon... > > > > regards, tom lane > > > > ---------------------------(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 > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
