root@site-db01a:/var/lib/pgsql/cmates/data # ls pg_multixact/members 0000 0001 0002 0003 0004 0005 0006 0007 0008 0009 000A 000B 000C 000D 000E 000F 0010 0011 0012 0013 0014 0015 0016 0017 0018 0019 001A 001B root@site-db01a:/var/lib/pgsql/cmates/data # ls pg_multixact/offsets 0001 0002 0003 0004 0005 0006 0007 0008 0009 000A 000B postgres@site-db01a:~ $ /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 05:22:45 PM PDT Latest checkpoint location: 62D0/BDE939F8 Prior checkpoint location: 62CF/F039BFD0 Latest checkpoint's REDO location: 62D0/8A060220 Latest checkpoint's REDO WAL file: 00000001000062D00000008A Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 1/1834305762 Latest checkpoint's NextOID: 19540327 Latest checkpoint's NextMultiXactId: 784503 Latest checkpoint's NextMultiOffset: 1445264 Latest checkpoint's oldestXID: 226141373 Latest checkpoint's oldestXID's DB: 16457 Latest checkpoint's oldestActiveXID: 1834302410 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 16457 Time of latest checkpoint: Wed 12 Oct 2016 05:22:05 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 10/13/16, 5:28 AM, "Alvaro Herrera" <alvherre@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
