Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
So this is what i did but my problem is still not going away. i shutdown the database and started it in single user mode and issued command vacuum full The command completed but the issue still exists The thing i noticed is that whenever i start the database autovaccum automatically starts on one table all the time like this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) This same auto vacuum is running since the problem started. i tried to cancel it using pg_cancel_backend but it starts again. i did a vacuum full public.hotel_site_market and the statement completes but again it starts running. i checked the stats using this caesius=# select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc; WARNING: database caesius must be vacuumed within 1648680 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in caesius. relname |age + hotel_site_market | 2145834967 cc_table_data |198017413 Even after running the full vacuum the stats are not changing and this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) keeps coming back i m getting this message as well WARNING: database prod01 must be vacuumed within 1648687 transactions Pls let me know what i should do on this Thanks avi On Mon, Jul 7, 2014 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: On 7/7/2014 2:14 PM, Prabhjot Sheena wrote: i will run full vacuum than and see how it goes. do make sure there aren't any OLD pending transactions hanging around. Not only regular transactions, but prepared transactions: select * from pg_prepared_xacts; 8.3 was the last release in which max_prepared_transactions was nonzero by default, thereby allowing people to shoot themselves in the foot this way without having taken off the safety first :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
First question - are you sure you ran vacuum in the correct database? I.e. in caesius? Second - is there any long running transaction? select min(xact_start) from pg_stat_activity where xact_start is not null; should tell you. depesz On Tue, Jul 8, 2014 at 12:44 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: So this is what i did but my problem is still not going away. i shutdown the database and started it in single user mode and issued command vacuum full The command completed but the issue still exists The thing i noticed is that whenever i start the database autovaccum automatically starts on one table all the time like this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) This same auto vacuum is running since the problem started. i tried to cancel it using pg_cancel_backend but it starts again. i did a vacuum full public.hotel_site_market and the statement completes but again it starts running. i checked the stats using this caesius=# select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc; WARNING: database caesius must be vacuumed within 1648680 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in caesius. relname |age + hotel_site_market | 2145834967 cc_table_data |198017413 Even after running the full vacuum the stats are not changing and this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) keeps coming back i m getting this message as well WARNING: database prod01 must be vacuumed within 1648687 transactions Pls let me know what i should do on this Thanks avi On Mon, Jul 7, 2014 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: On 7/7/2014 2:14 PM, Prabhjot Sheena wrote: i will run full vacuum than and see how it goes. do make sure there aren't any OLD pending transactions hanging around. Not only regular transactions, but prepared transactions: select * from pg_prepared_xacts; 8.3 was the last release in which max_prepared_transactions was nonzero by default, thereby allowing people to shoot themselves in the foot this way without having taken off the safety first :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
Yes i did ran it in caesius database and not prod01 db that was a typo there is no long running transactions. i just ran this command select min(xact_start) from pg_stat_activity where xact_start is not null; to make sure Thanks On Tue, Jul 8, 2014 at 4:43 AM, hubert depesz lubaczewski dep...@gmail.com wrote: First question - are you sure you ran vacuum in the correct database? I.e. in caesius? Second - is there any long running transaction? select min(xact_start) from pg_stat_activity where xact_start is not null; should tell you. depesz On Tue, Jul 8, 2014 at 12:44 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: So this is what i did but my problem is still not going away. i shutdown the database and started it in single user mode and issued command vacuum full The command completed but the issue still exists The thing i noticed is that whenever i start the database autovaccum automatically starts on one table all the time like this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) This same auto vacuum is running since the problem started. i tried to cancel it using pg_cancel_backend but it starts again. i did a vacuum full public.hotel_site_market and the statement completes but again it starts running. i checked the stats using this caesius=# select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc; WARNING: database caesius must be vacuumed within 1648680 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in caesius. relname |age + hotel_site_market | 2145834967 cc_table_data |198017413 Even after running the full vacuum the stats are not changing and this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) keeps coming back i m getting this message as well WARNING: database prod01 must be vacuumed within 1648687 transactions Pls let me know what i should do on this Thanks avi On Mon, Jul 7, 2014 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: On 7/7/2014 2:14 PM, Prabhjot Sheena wrote: i will run full vacuum than and see how it goes. do make sure there aren't any OLD pending transactions hanging around. Not only regular transactions, but prepared transactions: select * from pg_prepared_xacts; 8.3 was the last release in which max_prepared_transactions was nonzero by default, thereby allowing people to shoot themselves in the foot this way without having taken off the safety first :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
OK. Please run what Tom suggested ( select * from pg_prepared_xacts; ), and show us output. Also, please run: vacuum verbose analyze hotel_site_market; and also show us output. depesz On Tue, Jul 8, 2014 at 2:39 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: Yes i did ran it in caesius database and not prod01 db that was a typo there is no long running transactions. i just ran this command select min(xact_start) from pg_stat_activity where xact_start is not null; to make sure Thanks On Tue, Jul 8, 2014 at 4:43 AM, hubert depesz lubaczewski dep...@gmail.com wrote: First question - are you sure you ran vacuum in the correct database? I.e. in caesius? Second - is there any long running transaction? select min(xact_start) from pg_stat_activity where xact_start is not null; should tell you. depesz On Tue, Jul 8, 2014 at 12:44 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: So this is what i did but my problem is still not going away. i shutdown the database and started it in single user mode and issued command vacuum full The command completed but the issue still exists The thing i noticed is that whenever i start the database autovaccum automatically starts on one table all the time like this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) This same auto vacuum is running since the problem started. i tried to cancel it using pg_cancel_backend but it starts again. i did a vacuum full public.hotel_site_market and the statement completes but again it starts running. i checked the stats using this caesius=# select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc; WARNING: database caesius must be vacuumed within 1648680 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in caesius. relname |age + hotel_site_market | 2145834967 cc_table_data |198017413 Even after running the full vacuum the stats are not changing and this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) keeps coming back i m getting this message as well WARNING: database prod01 must be vacuumed within 1648687 transactions Pls let me know what i should do on this Thanks avi On Mon, Jul 7, 2014 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: On 7/7/2014 2:14 PM, Prabhjot Sheena wrote: i will run full vacuum than and see how it goes. do make sure there aren't any OLD pending transactions hanging around. Not only regular transactions, but prepared transactions: select * from pg_prepared_xacts; 8.3 was the last release in which max_prepared_transactions was nonzero by default, thereby allowing people to shoot themselves in the foot this way without having taken off the safety first :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
It does say a FULL vacuum, and that you are not doing. div Original message /divdivFrom: Mike Christensen m...@kitchenpc.com /divdivDate:07/07/2014 3:17 PM (GMT-06:00) /divdivTo: Prabhjot Sheena prabhjot.she...@rivalwatch.com /divdivCc: pgsql-ad...@postgresql.org,Forums postgresql pgsql-general@postgresql.org /divdivSubject: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions /divdiv /divSounds like you just have to wait until it finishes.. On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: Hello We are using postgresql 8.3 database for last 5 yrs for this production database and its running fine. This is our critical database which runs 24*7. This weekend we started getting these messages HINT: To avoid a database shutdown, execute a full-database VACUUM. WARNING: database must be vacuumed within 8439472 transactions i am currently running this command vacuumdb --analyze db while this command is running i m still getting these messages WARNING: database must be vacuumed within 2645303 transactions. The value of number of transactions is going down every minute Can anyone tell me what is the best way to sort up this issue. Thanks Avi
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
Maybe you can priorize your worker with a ionice? - Mail original - De: Mike Christensen m...@kitchenpc.com À: Prabhjot Sheena prabhjot.she...@rivalwatch.com Cc: pgsql-ad...@postgresql.org, Forums postgresql pgsql-general@postgresql.org Envoyé: Lundi 7 Juillet 2014 16:15:18 Objet: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions Sounds like you just have to wait until it finishes.. On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: Hello We are using postgresql 8.3 database for last 5 yrs for this production database and its running fine. This is our critical database which runs 24*7. This weekend we started getting these messages HINT: To avoid a database shutdown, execute a full-database VACUUM. WARNING: database must be vacuumed within 8439472 transactions i am currently running this command vacuumdb --analyze db while this command is running i m still getting these messages WARNING: database must be vacuumed within 2645303 transactions. The value of number of transactions is going down every minute Can anyone tell me what is the best way to sort up this issue. Thanks Avi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
On Mon, 7 Jul 2014 15:22:44 -0500 (CDT) Scott Whitney sc...@journyx.com wrote: It does say a FULL vacuum, and that you are not doing. You're confusing terminology. VACUUM FULL is not the same as a full database vacuum. The latter is simply expressing that vacuuming individual tables won't fix the problem. A VACUUM FULL is not required. You really just need to wait it out. Although at the rate the number seems to be dropping, you may be in for trouble. div Original message /divdivFrom: Mike Christensen m...@kitchenpc.com /divdivDate:07/07/2014 3:17 PM (GMT-06:00) /divdivTo: Prabhjot Sheena prabhjot.she...@rivalwatch.com /divdivCc: pgsql-ad...@postgresql.org,Forums postgresql pgsql-general@postgresql.org /divdivSubject: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions /divdiv /divSounds like you just have to wait until it finishes.. On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: Hello We are using postgresql 8.3 database for last 5 yrs for this production database and its running fine. This is our critical database which runs 24*7. This weekend we started getting these messages HINT: To avoid a database shutdown, execute a full-database VACUUM. WARNING: database must be vacuumed within 8439472 transactions i am currently running this command vacuumdb --analyze db while this command is running i m still getting these messages WARNING: database must be vacuumed within 2645303 transactions. The value of number of transactions is going down every minute Can anyone tell me what is the best way to sort up this issue. Thanks Avi -- Bill Moran wmo...@potentialtech.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: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
That actually sounds terrifying. I'd throw up a caching layer ASAP to try to decrease the speed those transactions are happening. Frank On Mon, Jul 7, 2014 at 2:25 PM, Nicolas Zin nicolas@savoirfairelinux.com wrote: Maybe you can priorize your worker with a ionice? - Mail original - De: Mike Christensen m...@kitchenpc.com À: Prabhjot Sheena prabhjot.she...@rivalwatch.com Cc: pgsql-ad...@postgresql.org, Forums postgresql pgsql-general@postgresql.org Envoyé: Lundi 7 Juillet 2014 16:15:18 Objet: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions Sounds like you just have to wait until it finishes.. On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena prabhjot.she...@rivalwatch.com wrote: Hello We are using postgresql 8.3 database for last 5 yrs for this production database and its running fine. This is our critical database which runs 24*7. This weekend we started getting these messages HINT: To avoid a database shutdown, execute a full-database VACUUM. WARNING: database must be vacuumed within 8439472 transactions i am currently running this command vacuumdb --analyze db while this command is running i m still getting these messages WARNING: database must be vacuumed within 2645303 transactions. The value of number of transactions is going down every minute Can anyone tell me what is the best way to sort up this issue. Thanks Avi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
Yes. That's what I was referring to. Back in my 8.3 days, I was required to do a FULL vacuum every week. I discussed it quite a bit on this list, and there were many people who said it should not have been required, but it was, and it resolved my particular issues (clogs not getting removed until full vac). I would recommend it. div Original message /divdivFrom: Prabhjot Sheena prabhjot.she...@rivalwatch.com /divdivDate:07/07/2014 3:46 PM (GMT-06:00) /divdivTo: Alvaro Herrera alvhe...@2ndquadrant.com /divdivCc: pgsql-ad...@postgresql.org,Forums postgresql pgsql-general@postgresql.org /divdivSubject: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions /divdiv /divWhile the vacuumdb --analyze command is running i m getting these messages for these tables which might require full vacuum. WARNING: relation public.result contains more than max_fsm_pages pages with useful free space HINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter max_fsm_pages. WARNING: relation public.run contains more than max_fsm_pages pages with useful free space HINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter max_fsm_pages. On Mon, Jul 7, 2014 at 1:31 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Prabhjot Sheena wrote: Hello We are using postgresql 8.3 database for last 5 yrs for this production database and its running fine. This is our critical database which runs 24*7. This weekend we started getting these messages HINT: To avoid a database shutdown, execute a full-database VACUUM. WARNING: database must be vacuumed within 8439472 transactions Did you omit the database name here, or is it really an empty string? Make sure you vacuum exactly the database mentioned there. Autovacuum should be doing it, though, but perhaps it's dying for some reason and it can't vacuum one table in particular. You should check your log for errors. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
i will run full vacuum than and see how it goes. Thanks avi On Mon, Jul 7, 2014 at 2:05 PM, Scott Whitney sc...@journyx.com wrote: Yes. That's what I was referring to. Back in my 8.3 days, I was required to do a FULL vacuum every week. I discussed it quite a bit on this list, and there were many people who said it should not have been required, but it was, and it resolved my particular issues (clogs not getting removed until full vac). I would recommend it. Original message From: Prabhjot Sheena Date:07/07/2014 3:46 PM (GMT-06:00) To: Alvaro Herrera Cc: pgsql-ad...@postgresql.org,Forums postgresql Subject: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions While the vacuumdb --analyze command is running i m getting these messages for these tables which might require full vacuum. WARNING: relation public.result contains more than max_fsm_pages pages with useful free space HINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter max_fsm_pages. WARNING: relation public.run contains more than max_fsm_pages pages with useful free space HINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter max_fsm_pages. On Mon, Jul 7, 2014 at 1:31 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Prabhjot Sheena wrote: Hello We are using postgresql 8.3 database for last 5 yrs for this production database and its running fine. This is our critical database which runs 24*7. This weekend we started getting these messages HINT: To avoid a database shutdown, execute a full-database VACUUM. WARNING: database must be vacuumed within 8439472 transactions Did you omit the database name here, or is it really an empty string? Make sure you vacuum exactly the database mentioned there. Autovacuum should be doing it, though, but perhaps it's dying for some reason and it can't vacuum one table in particular. You should check your log for errors. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
Prabhjot Sheena prabhjot.she...@rivalwatch.com writes: i will run full vacuum than and see how it goes. I think that is seriously bad advice. It will take longer and not do anything more to resolve your immediate problem --- which, it appears, you don't have a whole lot of time to resolve if you want to avoid a forced shutdown. It would likely be worth your time to figure out which table(s) in which database(s) are actually causing this issue, and vacuum those first, instead of blindly vacuuming everything. This will tell you which database(s) are most problematic: select datname, age(datfrozenxid) from pg_database order by 2 desc; and then within those database(s) you can similarly do select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc; to find the most problematic table(s). BTW, did you perhaps turn autovacuum off, or cripple its performance through ill-chosen throttling settings? It really should've kept you out of this problem. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
On 7/7/2014 2:14 PM, Prabhjot Sheena wrote: i will run full vacuum than and see how it goes. do make sure there aren't any OLD pending transactions hanging around. if you have any stalled client connections that have left a transaction open for weeks/months, vacuum can't free any tuples newer than the oldest transaction. select * from pg_stat_activity where xact_start now()-interval '1 hour'; will list all connections with transactions over 1 hour old. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
John R Pierce pie...@hogranch.com writes: On 7/7/2014 2:14 PM, Prabhjot Sheena wrote: i will run full vacuum than and see how it goes. do make sure there aren't any OLD pending transactions hanging around. Not only regular transactions, but prepared transactions: select * from pg_prepared_xacts; 8.3 was the last release in which max_prepared_transactions was nonzero by default, thereby allowing people to shoot themselves in the foot this way without having taken off the safety first :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
