Re: The Free Space Map: Problems and Opportunities

On Mon, Aug 16, 2021 at 5:15 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> This is actually quite simple -- the chaos that follows is where it
> gets truly complicated (and not necessarily worth getting into just
> yet). The update of a given order and its order lines entries takes
> place hours later, within a delivery transaction.

Update on this: aborted transactions play an important role in the
chaos that we see with BenchmarkSQL/TPC-C.

This is surprising, in one way. Only 1% of all transactions are
aborted (per the TPC-C spec). But in another way it's not surprising.
It's totally consistent with what I've been saying about open and
closed pages. When a page that's initially allocated and opened by a
backend becomes closed following some inserts, the page should be left
in a more or less pristine state -- owning backends that open and
close pages need to be "good citizens.

The definition of "pristine" must also include "no already-aborted
tuples" -- aborted xact tuples are a problem that we can and should
nip in the bud. This is really an extension of what I've been saying
about not disrupting naturally occuring temporal and spatial locality.
We're far better off if the space reused by freeing aborted heap
tuples is reused by the very next transaction running in the very same
backend. Plus it's not that hard; we can know for sure that this space
is immediately available, which is not generally true of pruning. And
so this free space from aborted xact tuples is quite special.

Even with the master branch the problems with the FSM are much less
noticeable once you configure BenchmarkSQL to not abort any
transactions [1]. You need to consider the whole picture to understand
how the issue of aborted transactions has such an outsized negative
impact on performance and space efficiency.

It's a whole *chain* of events:

* If just 1% of all transactions abort, that's enough to leave an
average of about 1 aborted tuple on every "order" table page (which
naturally has small heap tuples [2]), and about 10 on a minority of
the really big "new order" table's pages. Maybe 1 in 10 or 12 "order
line" table heap pages are affected in this way.

* The overall impact is that small (but not tiny) pockets of free
space are left randomly strewn all over the place. Not just LP_DEAD
line pointer stubs -- whole entire aborted heap tuples.

* In theory we could address this problem using opportunistic pruning.
But in practice that simply cannot happen today, because we don't
touch the inserted rows until literally hours later.

The same rows will eventually have updates and selects, but that won't
help -- too little, too late. That's just how opportunistic pruning
works: currently you need some kind of scan node for opportunistic
pruning to kick in, so continually inserting transactions (from new
orders) are currently fundamentally unable to do any opportunistic
pruning. Much less opportunistic pruning that kicks in at exactly the
right time.

* When an autovacuum worker runs against these tables, it will of
course notice this diffuse free space from aborted tuples, and put it
in the FSM.

That free space will be reused, but by totally unrelated logical rows.

What we really seem to need here is some rudimentary form of "eager
physical rollback"; the really important thing seems to be to not
allow the problem to happen in the first place. I have a basic
prototype implementation, built on top of the larger prototype patch.
It opportunistically prunes-away aborted heap tuples on target/open
pages, bypassing the usual pd_prune_xid check we have in
heap_page_prune_opt() -- that doesn't make sense with the new form of
targeted, abort-orientated pruning. The new pruning mechanism makes a
really big difference on its own, without even involving the FSM. The
best cure for all kinds of bloat is prevention, which this comes
pretty close to.

I think that this general idea can be pushed further. I've talked
about aborts, but what about the commit path? I don't see why we can't
make inserting backends responsible for setting hint bits on recently
inserted rows -- the cost of putting off that work can only go up, no
matter what (unless maybe the user drops the table). Again, the
missing piece seems to be a general sense of ownership of heap pages
by transactions and/or backends. In order for a backend to be able to
clean up its own mess after itself while it's still cheap, it has to
understand what that actually means.

Don't forget that the TPC-C workload doesn't have that many updates
(at least not for the tables I'm focussed on here). Nothing that we
tend to think of as an adversarial case for Postgres, except perhaps
the updates against the "order" table, which are always non-HOT
updates due to an issue with the indexes. That's the extent of it,
though -- every other table easily has 99%+ of all updates use HOT
(albeit with some heap fill factor tuning). We should *expect* long
term stability here, based on the particulars of the workload; we only
need to update every "order" row and every "order lines" row exactly
once. After that they can just age out of shared_buffers forever
(actually they might be read again, but never modified again, but
reads were never the problem). The big "order line" table is by far
the biggest problem, even though it manages to use HOT for practically
all updates.

[1] https://github.com/wieck/benchmarksql/commit/66b8db073545026dc76ef513d2b0e318d2f3d5a2
[2] https://apps.dtic.mil/dtic/tr/fulltext/u2/a264793.pdf -- "Table 1:
Summary of Logical Database"
--
Peter Geoghegan