Re: truncating pg_multixact/members

Alvaro Herrera wrote:

> 1. slru.c doesn't consider file names longer than 4 hexadecimal chars.

> For 9.3, I propose we skip this and tweak the code to consider files
> whose names are 4 or 5 chars in length, to remain compatible with
> existing installations that have pg_multixact/member having a mixture of
> 4-char and 5-char file names.

Attached is a patch for this.

> 2. pg_multixact/members truncation requires more intelligence to avoid
> removing files that are still needed.  Right now we use modulo-2^32
> arithmetic, but this doesn't work because the useful range can span
> longer than what we can keep within that range.

> #2c At start of truncation, save end-of-range in MultiXactState.  This
> state is updated by GetNewMultiXactId as new files are created.  That
> way, before each new file is created, the truncation routine knows to
> skip it.

Attached is a patch implementing this.

I also attach a patch implementing a "burn multixact" utility, initially
coded by Andres Freund, tweaked by me.  I used it to run a bunch of
wraparound cycles and everything seems to behave as expected.  (I don't
recommend applying this patch; I'm posting merely because it's a very
useful debugging tool.)

One problem I see is length of time before freezing multis: they live
for far too long, causing the SLRU files to eat way too much disk space.
I ran burnmulti in a loop, creating multis of 3 members each, with a min
freeze age of 50 million, and this leads to ~770 files in
pg_multixact/offsets and ~2900 files in pg_multixact/members.  Each file
is 32 pages long. 256kB apiece.  Probably enough to be bothersome.

I think for computing the freezing point for multis, we should slash
min_freeze_age by 10 or something like that.  Or just set a hardcoded
one million.


> 3. New pg_multixact/members generation requires more intelligence to
> avoid stomping on files from the previous wraparound cycle.  Right now
> there is no defense against this at all.

I still have no idea how to attack this.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services