Re: MultiXact member wraparound protections are disabled

We are also seeing this in our log file

Oct 12 19:08:14 site-db01a postgres[6117]: [7589-1] app=,user=,db=,ip=LOG:
MultiXact member wraparound protections are disabled because oldest
checkpointed MultiXact 1 does not exist on disk

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

> 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
>>>
>>
>>
>