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 >
