On 11/10/14, 12:16 AM, Josh Berkus wrote:
On 11/09/2014 08:00 PM, Josh Berkus wrote:
On 11/08/2014 01:46 PM, Andres Freund wrote:
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.
Me too: https://github.com/pgexperts/flexible-freeze
It turns out that not even a program of preventative scheduled vacuuming
helps. This is because the template0 database anchors the minmxid and
prevents it from being advanced until autovacuum gets around to that
database, at whatever the minmxid threshold is.
How did template0 even get a MultiXact? That sounds like they're really abusing
the template databases. :( (Do keep in mind that MXID 1 is a special value.)
Regarding linking the two settings, I agree with others that XIDs and MXIDs are
basically completely independent (as your customer apparently has discovered).
If you set both of the min_age parameters fairly small then it doesn't matter
which max limit (the table_age parameters) you hit; you'll get a full scan and
the low min_age limits will mean you'll get good freezing of both.
The only other thing I can think of would be having yet another set of minimum
age limits that come into play when you're doing a full scan as opposed to a
partial one, but that seems like overkill to me. I guess another option would
be to get more aggressive depending on the size of pg_multixact/...
BTW, the only reason I know of not to set both min_age parameters to zero is to
prevent loss of forensic information. If that's not a concern you can always
just set them to zero. Even if it is a concern, I suspect that the forensic
info you could gather from a MultiXact is a lot more limited than for an XID,
so it's probably pretty safe setting that to zero.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
