Re: pg_multixact not getting truncated - Mailing list pgsql-hackers

On 2014-11-08 12:10:48 -0800, Josh Berkus wrote:
> On 11/07/2014 05:29 PM, Alvaro Herrera wrote:
> > Josh Berkus wrote:
> >> Of course, this will lead to LOTs of additional vacuuming ...
> > 
> > There's a trade-off here: more vacuuming I/O usage for less disk space
> > used.  How stressed your customers really are about 1 GB of disk space?
> 
> These customers not so much.  The users I encountered on chat whose
> pg_multixact was over 20GB, and larger than their database?  Lots.


> On 11/08/2014 03:54 AM, Andres Freund wrote:
> > On 2014-11-07 17:20:44 -0800, Josh Berkus wrote:
> >> So the basic problem is that multixact files are just huge, with an
> >> average of 35 bytes per multixact?
> >
> > Depends on the concurrency. The number of members is determined by the
> > number of xacts concurrenly locking a row..
> 
> Yeah, that leads to some extreme inflation for databases where FK
> conflicts are common though.

On the other hand, those are the ones benefitting most from the gain in
concurrency.

> On 11/08/2014 03:54 AM, Andres Freund wrote:
> > On 2014-11-07 17:20:44 -0800, Josh Berkus wrote:
> >> Of course, this will lead to LOTs of additional vacuuming ...
> >
> > Yes. And that's likely to cause much, much more grief.
> >
> > Also. Didn't you just *vehemently* oppose making these values tunable at
> > all?
> 
> Yes, I opposed adding a *user* tunable with zero information on how it
> should be tuned or why. I always do and always will.

I think that's primarily naive. We don't always *have* that knowledge
ahead of time. There's interactions in the real world that we are not
able to predict. And people are usually more happy to find that their
problem can be fixed by tuning a somewhat obscure GUC than having to
patch their server or, much worse, upgrade to a newer major version that
just came out. *No* user knows all our GUCs, even the really experienced
ones only know half or so. And that's not because there's too
many. Unless there are only three, they'll never.

> I also think our
> defaults for multixact freezing should be tied to the ones for xid
> freezing, and should not by default be completely independent numbers;

I think it'd be a good idea to tune them more automatedly in the
future. But I think the current situation where you can vastly increase
multivacuum_freeze_max_age while having
multivacuum_multixact_freeze_max_age is *much* more useful in practice
than when they always were the same.

> I'm still not convinced that it makes sense to have a separate multixact
> threshold at all **since the same amount of vacuuming needs to be done
> regardless of whether we're truncating xids or mxids**.

That's just plain wrong. The growth rate of one can be nearly
independent of the other. It can e.g. be very sensible to have a huge
xid freeze limit, but a much smaller multixact limit.

> Certainly when I play with tuning this for customers, I'm going to lower
> vacuum_freeze_table_age as well.

I'm these days suggesting that people should add manual vacuuming for
"older" relations during off peak hours on busy databases. There's too
many sites which service degrades noticeably during a full table vacuum.

If you actually mean autovacuum_freeze_max_age - I don't generally
agree. It very often can be a good idea to significantly increase it.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services