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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks for your reply.
Yes, the first thing I looked at the statistics from pg_stat_activity.
But I have a transaction is not more than 60 seconds and the condition 'idle in transaction' lasts only a few seconds.

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, March 24, 2016 4:36 PM
To: Pavlov Vladimir; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavlov, Vladimir wrote:

Thanks for your reply.
Yes, the first thing I looked at the statistics from pg_stat_activity.
But I have a transaction is not more than 60 seconds and the condition 'idle in transaction' lasts only a few seconds.

Maybe you have a prepared transaction? See
select * from pg_prepared_xacts;

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Thursday, March 24, 2016 9:03 PM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

Thanks for your reply.
Yes, the first thing I looked at the statistics from pg_stat_activity.
But I have a transaction is not more than 60 seconds and the condition 'idle in transaction' lasts only a few seconds.

Maybe you have a prepared transaction? See select * from pg_prepared_xacts;

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavlov, Vladimir wrote:

There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi, thank you very much for your help.
Pg_control out in the attachment.

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Friday, March 25, 2016 12:25 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hello,
There is no news?
Now I have to do VACUUM every night, so that the server worked.
Maybe run VACUUM FREEZE?

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Pavlov Vladimir
Sent: Friday, March 25, 2016 9:55 AM
To: 'Alvaro Herrera'
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Multixacts wraparound monitoring

Hi, thank you very much for your help.
Pg_control out in the attachment.

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Friday, March 25, 2016 12:25 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/30/2016 06:24 AM, Pavlov, Vladimir wrote:

Hello,
There is no news?
Now I have to do VACUUM every night, so that the server worked.

So has the WARNING gone away?:

WARNING: oldest multixact is far in the past
HINT: Close open transactions with multixacts soon to avoid wraparound
problems.

Or to put it another way, define worked.

Maybe run VACUUM FREEZE?

Kind regards,

Vladimir Pavlov

-----Original Message-----
From: Pavlov Vladimir
Sent: Friday, March 25, 2016 9:55 AM
To: 'Alvaro Herrera'
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Multixacts wraparound monitoring

Hi, thank you very much for your help.
Pg_control out in the attachment.

Kind regards,

Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Friday, March 25, 2016 12:25 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes, VACUUM helps to solve the problem and the WARNING gone away.
But, the problem is that the VACUUM for the entire database (2.4T) takes over 7 hours, and it has to run every 15-20 hours (about 300 millions transactions), otherwise:
ERROR: multixact "members" limit exceeded - and server stops working.
The question is how to start the VACUUM at least once in three days.

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, March 30, 2016 4:52 PM
To: Pavlov Vladimir; 'Alvaro Herrera'
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Multixacts wraparound monitoring

On 03/30/2016 06:24 AM, Pavlov, Vladimir wrote:

Hello,
There is no news?
Now I have to do VACUUM every night, so that the server worked.

So has the WARNING gone away?:

WARNING: oldest multixact is far in the past
HINT: Close open transactions with multixacts soon to avoid wraparound problems.

Or to put it another way, define worked.

Maybe run VACUUM FREEZE?

Kind regards,

Vladimir Pavlov

-----Original Message-----
From: Pavlov Vladimir
Sent: Friday, March 25, 2016 9:55 AM
To: 'Alvaro Herrera'
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Multixacts wraparound monitoring

Hi, thank you very much for your help.
Pg_control out in the attachment.

Kind regards,

Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Friday, March 25, 2016 12:25 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/30/2016 08:03 AM, Pavlov, Vladimir wrote:

Yes, VACUUM helps to solve the problem and the WARNING gone away.

Okay, so now we are on a different problem.

But, the problem is that the VACUUM for the entire database (2.4T) takes over 7 hours, and it has to run every 15-20 hours (about 300 millions transactions), otherwise:
ERROR: multixact "members" limit exceeded - and server stops working.
The question is how to start the VACUUM at least once in three days.

That is the purpose of autovacuum:

http://www.postgresql.org/docs/9.5/interactive/routine-vacuuming.html#AUTOVACUUM

http://www.postgresql.org/docs/9.5/interactive/runtime-config-autovacuum.html

Which also has a per table feature:

http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

So how is your autovacuum set up?

Do really need to vacuum the whole database or selected heavily updated
table?

Kind regards,

Vladimir Pavlov

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, March 30, 2016 4:52 PM
To: Pavlov Vladimir; 'Alvaro Herrera'
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Multixacts wraparound monitoring

On 03/30/2016 06:24 AM, Pavlov, Vladimir wrote:

Hello,
There is no news?
Now I have to do VACUUM every night, so that the server worked.

So has the WARNING gone away?:

WARNING: oldest multixact is far in the past
HINT: Close open transactions with multixacts soon to avoid wraparound problems.

Or to put it another way, define worked.

Maybe run VACUUM FREEZE?

Kind regards,

Vladimir Pavlov

-----Original Message-----
From: Pavlov Vladimir
Sent: Friday, March 25, 2016 9:55 AM
To: 'Alvaro Herrera'
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Multixacts wraparound monitoring

Hi, thank you very much for your help.
Pg_control out in the attachment.

Kind regards,

Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Friday, March 25, 2016 12:25 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavlov, Vladimir wrote:

Yes, VACUUM helps to solve the problem and the WARNING gone away.
But, the problem is that the VACUUM for the entire database (2.4T) takes over 7 hours, and it has to run every 15-20 hours (about 300 millions transactions), otherwise:
ERROR: multixact "members" limit exceeded - and server stops working.
The question is how to start the VACUUM at least once in three days.

You should have *started* the thread with this information.

My bet is that your multixacts are overly large and that's causing
excessive vacuuming work; this is likely due to bug #8470 (which is
fixed in 9.5 and master but not 9.3 and 9.4) and my bet is that you
would very much benefit from the patch I posted in
/messages/by-id/20150620043650.GX133018@postgresql.org
I didn't actually verify this; you could with some arithmetic on the
deltas in multixact counters in pg_controldata output that you could
take periodically.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,
If I get you right:
Latest checkpoint's NextMultiXactId: 2075246000
Latest checkpoint's oldestMultiXid: 2019511697
Number of members files: 10820
Size pg_multixact/members/ (bytes) (2.7Gb): 2887696384
Pages in file: 32
Members on page: 2045
Number of members (32*2045*10820): 708060800
Members per multixact (2075246000 - 2019511697)/708060800: 12,70421916
Multixact size (bytes) (2887696384/708060800): 4,078316981 - It's a lot?

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Thursday, March 31, 2016 12:17 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

Yes, VACUUM helps to solve the problem and the WARNING gone away.
But, the problem is that the VACUUM for the entire database (2.4T) takes over 7 hours, and it has to run every 15-20 hours (about 300 millions transactions), otherwise:
ERROR: multixact "members" limit exceeded - and server stops working.
The question is how to start the VACUUM at least once in three days.

You should have *started* the thread with this information.

My bet is that your multixacts are overly large and that's causing excessive vacuuming work; this is likely due to bug #8470 (which is fixed in 9.5 and master but not 9.3 and 9.4) and my bet is that you would very much benefit from the patch I posted in /messages/by-id/20150620043650.GX133018@postgresql.org
I didn't actually verify this; you could with some arithmetic on the deltas in multixact counters in pg_controldata output that you could take periodically.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavlov, Vladimir wrote:

Hello,
If I get you right:
Latest checkpoint's NextMultiXactId: 2075246000
Latest checkpoint's oldestMultiXid: 2019511697
Number of members files: 10820
Size pg_multixact/members/ (bytes) (2.7Gb): 2887696384
Pages in file: 32
Members on page: 2045
Number of members (32*2045*10820): 708060800
Members per multixact (2075246000 - 2019511697)/708060800: 12,70421916
Multixact size (bytes) (2887696384/708060800): 4,078316981 - It's a lot?

Yeah, 12.7 members per multixact on average is a lot, unless you have 12
processes concurrently locking the same tuples, all the time (although
that is possible). My guess is that this is related to subtransactions
(either explicit SAVEPOINTs in your SQL code, or EXCEPTION blocks in
plpgsql functions).

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I understand correctly, that number of members cannot be more than 2^32 (also uses a 32-bit counter)?
I had 69640 files in main/pg_multixact/members/, 69640*32*2045 = 4557241600 members, this is normal?

Kind regards,
 
Vladimir Pavlov

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: Thursday, March 31, 2016 4:17 PM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:

Hello,
If I get you right:
Latest checkpoint's NextMultiXactId: 2075246000
Latest checkpoint's oldestMultiXid: 2019511697
Number of members files: 10820
Size pg_multixact/members/ (bytes) (2.7Gb): 2887696384
Pages in file: 32
Members on page: 2045
Number of members (32*2045*10820): 708060800
Members per multixact (2075246000 - 2019511697)/708060800: 12,70421916
Multixact size (bytes) (2887696384/708060800): 4,078316981 - It's a lot?

Yeah, 12.7 members per multixact on average is a lot, unless you have 12 processes concurrently locking the same tuples, all the time (although
that is possible). My guess is that this is related to subtransactions
(either explicit SAVEPOINTs in your SQL code, or EXCEPTION blocks in plpgsql functions).

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Apr 1, 2016 at 4:31 AM, Pavlov, Vladimir
<Vladimir.Pavlov@tns-global.ru> wrote:

I understand correctly, that number of members cannot be more than 2^32 (also uses a 32-bit counter)?

Correct.

I had 69640 files in main/pg_multixact/members/, 69640*32*2045 = 4557241600 members, this is normal?

Where did you get 2045 from? I thought it was like this:

number of members = number of member segment files * 1636 * 32
number of multixacts = number of offsets segment files * 2048 * 32

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general