Re: MultiXact member wraparound protections are disabled

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
>