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