Обсуждение: Multixacts wraparound monitoring
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.
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
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
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
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
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
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
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
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
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
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
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 https://www.postgresql.org/message-id/20150620043650.GX133018%40postgresql.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
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 patchI posted in https://www.postgresql.org/message-id/20150620043650.GX133018%40postgresql.org I didn't actually verify this; you could with some arithmetic on the deltas in multixact counters in pg_controldata outputthat you could take periodically. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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
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
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