Index tuple killing code committed
On Sat, 2002-05-25 at 02:38, Joe Conway wrote:
Tom Lane wrote:
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:This may qualify as a "way out there" idea, or more trouble than it's
worth, but what about a table option which provides a bitmap index of
tuple status -- i.e. tuple dead t/f. If available, a seqscan in between
vacuums could maybe gain some of the same efficiency.
I guess this would be only useful if it is a bitmap of dead _pages_ not
tuples (page reading is mostexpensive plus there is no way to know how
many tuples per page)
but for worst cases (small table with lots of updates) this can be a
great thing that can postpone fixing optimiser to account for dead
tuples.
one 8K pages can hold bits for 8192*8 = 65536 pages = 512 Mbytes and if
seqscan could skip first 500 of them it would definitely be worth it ;)
This is the first time I have ever seen repeated pgbench runs without
substantial performance degradation. Not a bad result for a Friday
afternoon...
Really good news!
-----------
Hannu
Import Notes
Reply to msg id not found: 3CEEB2E1.4090807@joeconway.comReference msg id not found: 21124.1022272975@sss.pgh.pa.usReference msg id not found: 3CEEB2E1.4090807@joeconway.com
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
This is the first time I have ever seen repeated pgbench runs without
substantial performance degradation. Not a bad result for a Friday
afternoon...
Congatulations :-) This sounds great !!!
Andreas
Import Notes
Resolved by subject fallback
Tom Lane wrote:
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:
This may qualify as a "way out there" idea, or more trouble than it's
worth, but what about a table option which provides a bitmap index of
tuple status -- i.e. tuple dead t/f. If available, a seqscan in between
vacuums could maybe gain some of the same efficiency.
This is the first time I have ever seen repeated pgbench runs without
substantial performance degradation. Not a bad result for a Friday
afternoon...
Nice work!
Joe
Joe Conway <mail@joeconway.com> writes:
This may qualify as a "way out there" idea, or more trouble than it's
worth, but what about a table option which provides a bitmap index of
tuple status -- i.e. tuple dead t/f. If available, a seqscan in between
vacuums could maybe gain some of the same efficiency.
Hmm. I'm inclined to think that a separate bitmap index wouldn't be
worth the trouble. Under most scenarios it'd just require extra I/O
and not buy much.
However ... we could potentially take over the LP_DELETED flag bit of
heap tuples for the same use as for index tuples: set it when the tuple
is known dead for all transactions. This would save calling
HeapTupleSatisfiesSnapshot in the inner loop of heapgettup, while not
adding much expense for the normal case where the tuple's not dead.
regards, tom lane