Re: MultiXact member wraparound protections are disabled

Got the output of pg_control

postgres(at)site-db01a:~/cmates/data/global $
/usr/pgsql-9.4/bin/pg_controldata /var/lib/pgsql/cmates/data
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6228991221455883206
Database cluster state: in production
pg_control last modified: Wed 12 Oct 2016 07:08:14 PM PDT
Latest checkpoint location: 62E1/890DA8D8
Prior checkpoint location: 62E0/550B2178
Latest checkpoint's REDO location: 62E1/4F054A08
Latest checkpoint's REDO WAL file: 00000001000062E10000004F
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 1/1834454859
Latest checkpoint's NextOID: 19540816
Latest checkpoint's NextMultiXactId: 784527
Latest checkpoint's NextMultiOffset: 1445313
Latest checkpoint's oldestXID: 226141373
Latest checkpoint's oldestXID's DB: 16457
Latest checkpoint's oldestActiveXID: 1834454859
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 16457
Time of latest checkpoint: Wed 12 Oct 2016 07:06:45 PM PDT
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: 1500
Current max_worker_processes setting: 8
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 1000
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

On Wed, Oct 12, 2016 at 7:10 PM, avi Singh <avisingh19811981(at)gmail(dot)com>
wrote:

> Sharing output
>
> postgres(at)site-db01a:~/cmates/data/pg_multixact/members $ ls
> 0000 0002 0004 0006 0008 000A 000C 000E 0010 0012 0014 0016
> 0018 001A
> 0001 0003 0005 0007 0009 000B 000D 000F 0011 0013 0015 0017
> 0019 001B
>
> postgres(at)site-db01a:~/cmates/data/pg_multixact/offsets $ ls
> 0001 0002 0003 0004 0005 0006 0007 0008 0009 000A 000B
>
>
> postgres(at)site-db01a:/tmp $ /usr/pgsql-9.4/bin/pg_controldata -D
> /var/lib/pgsql/cmates/data
> pg_controldata: could not open file "-D/global/pg_control" for reading: No
> such file or directory
>
> pg_controldata is not working in here even though the file is there inside
> global but it is not reading from it
>
> postgres(at)site-db01a:~/cmates/data/global $ ls -la pg_control
> -rw-------. 1 postgres postgres 8192 Oct 12 18:55 pg_control
>
>
> On Wed, Oct 12, 2016 at 4:58 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
> wrote:
>
>> AnandKumar, Karthik wrote:
>> > Hi,
>> >
>> > We run postgres 9.4.5.
>> >
>> > Starting this morning, we started seeing messages like the below:
>> > Oct 12 14:07:15 site-db01a postgres[11253]: [106430-1]
>> app=,user=,db=,ip=LOG: MultiXact member wraparound protections are
>> disabled because oldest checkpointed MultiXact 1 does not exist on disk
>> > Oct 12 14:09:26 site-db01a postgres[11253]: [106526-1]
>> app=,user=,db=,ip=LOG: MultiXact member wraparound protections are
>> disabled because oldest checkpointed MultiXact 1 does not exist on disk
>> > Oct 12 14:14:18 site-db01a postgres[11253]: [106608-1]
>> app=,user=,db=,ip=LOG: MultiXact member wraparound protections are
>> disabled because oldest checkpointed MultiXact 1 does not exist on disk
>> >
>> > Our autovacuum_freeze_max_age = 1750000000.
>> >
>> > site=# SELECT datname, age(datfrozenxid) FROM pg_database;
>> > datname | age
>> > -----------+------------
>> > site | 1645328344
>> > template0 | 1274558807
>> > bench | 1274558807
>> > postgres | 1324283514
>> > template1 | 1274558807
>> >
>> > So we’re about 100 mil transactions away before we start vacuuming to
>> prevent wraparound.
>> >
>> > We’re running precautionary vacuums on our largest offenders to try and
>> drop our transaction ids
>> >
>> > What I’d request some clarity on is the message above. What does it
>> mean that "oldest checkpointed MultiXact does not exist on disk”? Would we
>> lose data if we did have to wrap around?
>> >
>> > Is this telling us we’re not vacuuming effectively enough?
>>
>> Ugh. Can you share the output of pg_controldata and the list of files
>> in pg_multixact/members and pg_multixact/offset?
>>
>> The problem here is that multixact vacuuming is separate from xid
>> vacuuming, so you need to be looking at datminmulti rather than
>> datfrozenxid. It may be that multixact wrap around has already
>> occurred.
>>
>> --
>> Álvaro Herrera https://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>