Re: pg_multixact issues

Καλημέρα Κυριάκο

We have been running 9.3.4 as our test system for quite some years, and 9.3.10 on production for a month or so (little less than 1T size, WAL changes worth of 2-5GB/day, about 250K xations/day) we
never experienced any problems with data/pg_multixact.

In our 9.3.4 :
% pg_controldata data | grep -i multi
Latest checkpoint's NextMultiXactId: 69
Latest checkpoint's NextMultiOffset: 135
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 0
% du -h data/pg_multixact/
10K data/pg_multixact/members
10K data/pg_multixact/offsets
22K data/pg_multixact/

In our prod 9.3.10:
~> pg_controldata data | grep -i multi
Latest checkpoint's NextMultiXactId: 12404
Latest checkpoint's NextMultiOffset: 356
Latest checkpoint's oldestMultiXid: 12232
Latest checkpoint's oldestMulti's DB: 16426
~> du -h data/pg_multixact/
12K data/pg_multixact/members
24K data/pg_multixact/offsets
40K data/pg_multixact/

Our system comprises of a JEE installation with ~ 500 uesrs, and at least 2 other applications hitting the same tables at the same time, we have about one case of deadlocks per week.

What could be different on yours?

On 10/02/2016 20:52, Kiriakos Georgiou wrote:
> Hello,
>
> Our pg_multixact directory keeps growing. I did a "vacuum freeze” which didn’t help. I also did a "vacuum full” which didn’t help either.
> We had this condition with 9.3.4 as well. When I upgraded our cluster to 9.4.5 (via plain sql dump and load) as expected the issue was resolved but now it’s happening again. Luckily it has no ill
> effect other than consuming 4G of space for an otherwise 1G database.
>
> Can you offer any hints as to how I can cure this?
>
> thanks,
> Kiriakos Georgiou
>
>
> pg_controldata output:
>
> pg_control version number: 942
> Catalog version number: 201409291
> Database system identifier: 6211781659140720513
> Database cluster state: in production
> pg_control last modified: Wed Feb 10 13:45:02 2016
> Latest checkpoint location: D/FB5FE630
> Prior checkpoint location: D/FB5FE558
> Latest checkpoint's REDO location: D/FB5FE5F8
> Latest checkpoint's REDO WAL file: 000000010000000D000000FB
> Latest checkpoint's TimeLineID: 1
> Latest checkpoint's PrevTimeLineID: 1
> Latest checkpoint's full_page_writes: on
> Latest checkpoint's NextXID: 0/3556219
> Latest checkpoint's NextOID: 2227252
> Latest checkpoint's NextMultiXactId: 2316566
> Latest checkpoint's NextMultiOffset: 823062151
> Latest checkpoint's oldestXID: 668
> Latest checkpoint's oldestXID's DB: 1
> Latest checkpoint's oldestActiveXID: 3556219
> Latest checkpoint's oldestMultiXid: 1
> Latest checkpoint's oldestMulti's DB: 1
> Time of latest checkpoint: Wed Feb 10 13:45:02 2016
> Fake LSN counter for unlogged rels: 0/1
> Minimum recovery ending location: 0/0
> Min recovery ending loc's timeline: 0
> Backup start location: 0/0
> Backup end location: 0/0
> End-of-backup record required: no
> Current wal_level setting: hot_standby
> Current wal_log_hints setting: off
> Current max_connections setting: 100
> Current max_worker_processes setting: 8
> Current max_prepared_xacts setting: 0
> Current max_locks_per_xact setting: 1024
> Maximum data alignment: 8
> Database block size: 8192
> Blocks per segment of large relation: 131072
> WAL block size: 8192
> Bytes per WAL segment: 16777216
> Maximum length of identifiers: 64
> Maximum columns in an index: 32
> Maximum size of a TOAST chunk: 1996
> Size of a large-object chunk: 2048
> Date/time type storage: 64-bit integers
> Float4 argument passing: by value
> Float8 argument passing: by value
> Data page checksum version: 0
>
> the offsets directory:
>
> -rw------- 1 postgres dba 262144 Nov 3 15:22 0000
> -rw------- 1 postgres dba 262144 Nov 5 12:45 0001
> -rw------- 1 postgres dba 262144 Nov 9 14:25 0002
> -rw------- 1 postgres dba 262144 Nov 13 10:10 0003
> -rw------- 1 postgres dba 262144 Nov 16 15:40 0004
> -rw------- 1 postgres dba 262144 Nov 20 09:55 0005
> -rw------- 1 postgres dba 262144 Dec 1 08:00 0006
> -rw------- 1 postgres dba 262144 Dec 9 11:50 0007
> -rw------- 1 postgres dba 262144 Dec 16 08:14 0008
> -rw------- 1 postgres dba 262144 Dec 21 09:40 0009
> -rw------- 1 postgres dba 262144 Dec 31 09:55 000A
> -rw------- 1 postgres dba 262144 Jan 4 21:17 000B
> -rw------- 1 postgres dba 262144 Jan 6 10:50 000C
> -rw------- 1 postgres dba 262144 Jan 7 18:20 000D
> -rw------- 1 postgres dba 262144 Jan 13 13:55 000E
> -rw------- 1 postgres dba 262144 Jan 15 11:55 000F
> -rw------- 1 postgres dba 262144 Jan 22 07:50 0010
> -rw------- 1 postgres dba 262144 Jan 26 16:35 0011
> -rw------- 1 postgres dba 262144 Jan 29 10:16 0012
> -rw------- 1 postgres dba 262144 Feb 3 13:17 0013
> -rw------- 1 postgres dba 262144 Feb 3 16:13 0014
> -rw------- 1 postgres dba 262144 Feb 4 08:24 0015
> -rw------- 1 postgres dba 262144 Feb 5 13:20 0016
> -rw------- 1 postgres dba 262144 Feb 8 11:26 0017
> -rw------- 1 postgres dba 262144 Feb 8 11:46 0018
> -rw------- 1 postgres dba 262144 Feb 8 12:25 0019
> -rw------- 1 postgres dba 262144 Feb 8 13:19 001A
> -rw------- 1 postgres dba 262144 Feb 8 14:23 001B
> -rw------- 1 postgres dba 262144 Feb 8 15:32 001C
> -rw------- 1 postgres dba 262144 Feb 8 17:01 001D
> -rw------- 1 postgres dba 262144 Feb 8 19:19 001E
> -rw------- 1 postgres dba 262144 Feb 8 22:11 001F
> -rw------- 1 postgres dba 262144 Feb 9 01:44 0020
> -rw------- 1 postgres dba 262144 Feb 9 05:57 0021
> -rw------- 1 postgres dba 262144 Feb 9 10:45 0022
> -rw------- 1 postgres dba 98304 Feb 10 13:35 0023
>
> the members directory has 15723 files:
> ls -l|wc -l
> 15723

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt