Re: pg_multixact not getting truncated

On 11/03/2014 05:24 PM, Josh Berkus wrote:
> BTW, the reason I started poking into this was a report from a user that
> they have a pg_multixact directory which is 21GB in size, and is 2X the
> size of the database.
>
> Here's XID data:
>
> Latest checkpoint's NextXID: 0/1126461940
> Latest checkpoint's NextOID: 371135838
> Latest checkpoint's NextMultiXactId: 162092874
> Latest checkpoint's NextMultiOffset: 778360290
> Latest checkpoint's oldestXID: 945761490
> Latest checkpoint's oldestXID's DB: 370038709
> Latest checkpoint's oldestActiveXID: 1126461940
> Latest checkpoint's oldestMultiXid: 123452201
> Latest checkpoint's oldestMulti's DB: 370038709
>
> Oldest mxid file is 29B2, newest is 3A13
>
> No tables had a relminmxid of 1 (some of the system tables were 0,
> though), and the data from pg_class and pg_database is consistent.

More tidbits:

I just did a quick check on customer systems (5 of them). This only
seems to be happening on customer systems where I specifically know
there is a high number of FK lock waits (the system above gets around
1000 per minute that we know of). Other systems with higher transaction
rates don't exhibit this issue; I checked a 9.3.5 database which
normally needs to do XID wraparound once every 10 days, and it's
pg_multixact is only 48K (it has one file, 0000).

Note that pg_clog on the bad machine is only 64K in size.

How many IDs are there per mxid file?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com