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 <avisingh19811...@gmail.com> wrote: > Got the output of pg_control > > postgres@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 <avisingh19811...@gmail.com> > wrote: > >> Sharing output >> >> postgres@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@site-db01a:~/cmates/data/pg_multixact/offsets $ ls >> 0001 0002 0003 0004 0005 0006 0007 0008 0009 000A 000B >> >> >> postgres@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@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 <alvhe...@2ndquadrant.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@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> >
