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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
