Josh Berkus wrote:
> So, just did a quick survey of our managed services customers. 4 out of
> 6 have pg_multixact bloat. Basically it looks like this:
>
> User1 (the one above): 1.1GB
> User2: 64K
> User3: 929MB
> User4: 1.3GB
> User5: 301MB
> User6: 48K
>
> Nobody (out of 6 more) running a version older than 9.3 had a
> pg_multixact larger than 128K.
9.3 multixacts are completely different from pre-9.3. We now depend on
the freezing parameters to determine how much is kept; previously they
were kept only for live trnsactions.
Quoting the pg_controldata output you pasted previously:
>>> Latest checkpoint's NextMultiXactId: 162092874
>>> Latest checkpoint's oldestMultiXid: 123452201
This says that the live multixact range goes from 123 million to 162
million; roughly 40 million values. (The default value for
vacuum_multixact_freeze_table_age is 150 million, which is what
determines how many values are kept.)
You gist.github paste tells us there are 4598 members files. Each file
has 32 pages, and each page hosts 2045 members; so there are 32 * 2045 *
4598 members, or somewhat about 300 million. For 40 million
multixacts, this means there are about 7 members per multixact, in
average, which seems a reasonable number to me.
If you want to have vacuum truncate pg_multixact more aggresively, you
need to decrease vacuum_multixact_freeze_table_age and
vacuum_multixact_freeze_min_age.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services