From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: pg_multixact not getting truncated |
Date: | 2014-11-04 01:40:36 |
Message-ID: | 54582E94.2020302@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Demai Ni | 2014-11-04 01:40:38 | [PG-XC] how to pass a value(String) from coordinator node to all data nodes |
Previous Message | Josh Berkus | 2014-11-04 01:24:04 | Re: pg_multixact not getting truncated |