Обсуждение: Re: MultiXactId Error in Autovacuum

If autovacuum doesn't want to finish the vacuuming of that table, why not just kill the autovacuum process on that table (you could use pg_cancel_backend())and run vacuum manually on that table?

Strahinja Kustudić | Lead System Engineer | Nordeus

On Fri, Nov 15, 2013 at 10:33 AM, Karl Hafner <karl@scoreloop.com> wrote:

Sorry, dear mailing list, somehow my email got sent before I finished it :-(

I will go on and repeat. Please bear with me!

I am currently worried about an error that shows up in our log files since a few weeks: 

2013-11-15 07:13:13 UTC [22668]: [2-1] ERROR:  MultiXactId 2683601542 does no longer exist -- apparent wraparound

2013-11-15 07:13:13 UTC [22668]: [3-1] CONTEXT:  automatic vacuum of table "scoreloop.public.gamer_device_sightings"

It is always the same MultiXactId.

It seems that "autovacuum" is active on that table, but it seems to have restarted today in the morning:  

select query_start, query, state, pid from pg_stat_activity where query LIKE 'autovacuum%' ;

# select query_start, query, state, pid from pg_stat_activity where query LIKE 'autovacuum%' ;

         

query_start  |                                      query                                       | state  | pid  

-------------------------------+----------------------------------------------------------------------------------+--------+------

 2013-11-15 07:13:13.281202+00 | autovacuum: VACUUM ANALYZE public.gamer_device_sightings (to prevent wraparound) | active | 4360

There are a few locks on tat table: 

# select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c join pg_locks l on c.oid=l.relation where l.pid='4360' order by l.pid;

  oid  |                 relname                  | pid  |           mode           | granted 

-------+------------------------------------------+------+--------------------------+---------

 19862 | index_gamer_device_sightings_on_gamer_id | 4360 | RowExclusiveLock         | t

 19719 | gamer_device_sightings_pkey                      | 4360 | RowExclusiveLock         | t

 19309 | gamer_device_sightings                               | 4360 | ShareUpdateExclusiveLock | t

The error I described seems to terminate the auto vacuum process: It has never finished on that table. 

It is the only table without a finished vacuum. So I guess the above error is the reason for this.

Is there anything that I can do to get that fixed? Should I worry about the error? (I would not if the vacuum would run through).

Any tips, any help would be much appreciated!

Thank you very much! And sorry once more for my posting mistake

Karl 

On Fri, Nov 15, 2013 at 10:16 AM, Karl Hafner <karl@scoreloop.com> wrote:

Dear mailing-list, 

I am currently worried about an error that shows up in our log files since a few weeks: 

2013-11-15 07:13:13 UTC [22668]: [2-1] ERROR:  MultiXactId 2683601542 does no longer exist -- apparent wraparound

2013-11-15 07:13:13 UTC [22668]: [3-1] CONTEXT:  automatic vacuum of table "scoreloop.public.gamer_device_sightings"

It seems that "autovacuum" is active on that table, but it seems to have restarted today in the morning:  

select query_start, query, state, pid from pg_stat_activity where query LIKE 'autovacuum%' ;

XXX=# select query_start, query, state, pid from pg_stat_activity where query LIKE 'autovacuum%' ;

         

query_start          |                                      query                                       | state  | pid  

-------------------------------+----------------------------------------------------------------------------------+--------+------

 2013-11-15 07:13:13.281202+00 | autovacuum: VACUUM ANALYZE public.gamer_device_sightings (to prevent wraparound) | active | 4360

Do you know if you experienced transaction ID wraparound as the
message suggests?

Are there any other questionable messages in the log?

What do you get for the following queries:

SELECT txid_current();

SELECT datname, datfrozenxid FROM pg_database;

SELECT current_setting('autovacuum_freeze_max_age'),
       current_setting('vacuum_freeze_min_age'),
       current_setting('vacuum_freeze_table_age');

Well, probably you are right, because PostgreSQL would shutdown
before a wraparound happens, and you would have noticed that.

I don't know how it happened, but your database has suffered
corruption.  Any chance you can restore from a backup?
Can you still "SELECT *" from the table that has the problem?