Re: [GENERAL] Multixacts wraparound monitoring
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:alvhe...@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 Herrerahttp://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
Re: [GENERAL] Multixacts wraparound monitoring
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:alvhe...@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 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 Herrerahttp://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
Re: [GENERAL] Multixacts wraparound monitoring
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.kla...@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:alvhe...@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.kla...@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
Re: [GENERAL] Multixacts wraparound monitoring
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:alvhe...@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 Herrerahttp://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
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:alvhe...@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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services pg_control.out Description: pg_control.out -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multixacts wraparound monitoring
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:alvhe...@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 Herrerahttp://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
Re: [GENERAL] Multixacts wraparound monitoring
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.kla...@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.kla...@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
[GENERAL] 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
