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.klaver@aklaver.com