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