Re: Multixacts wraparound monitoring

On 03/24/2016 12:54 AM, Pavlov, Vladimir wrote:
> Hello,
>
> How can we determine when an error of approximation multixacts wraparound?
>
> According to the information from pg_class:
>
> select datname,datminmxid from pg_database;
>
> datname | datminmxid
>
> ----------------+------------
>
> template1 | 347462426
>
> template0 | 347462426
>
> postgres | 347462426
>
> zabbix | 467261307
>
> db_3 | 291141939
>
> db_1 | 388282963
>
> db | 388282963
>
> But when the vacuum/autovacuum starts up, an error occurs:
>
> WARNING: oldest multixact is far in the past
>
> HINT: Close open transactions with multixacts soon to avoid wraparound
> problems.

The above would seem to be the key. Take a look at what is in:

select * from pg_stat_activity;

You are looking for long running queries and/or 'idle in transaction'
queries'.

For more information see:

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

>
> If I understand correctly, approaching Multixact member wraparound.
>
> But how to understand when it comes exactly and what to do?
>
> PostgreSQL version – 9.3.10, OS Debian 7.8.
>
> Thank you.
>
> Sorry, if I chose the wrong mailing list.
>
> Kind regards,
>
> *Vladimir Pavlov*
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com