pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWARNING: database must be vacuumed within 8439472 transactions
20+ messages / 12 participants
[nested] [flat]
* WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 19:56 Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
0 siblings, 3 replies; 20+ messages in thread
From: Prabhjot Sheena @ 2014-07-07 19:56 UTC (permalink / raw)
To: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:15 Mike Christensen <mike@kitchenpc.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2 siblings, 1 reply; 20+ messages in thread
From: Mike Christensen @ 2014-07-07 20:15 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
Sounds like you just have to wait until it finishes..
On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena <
prabhjot.sheena@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
>
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:22 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 20+ messages in thread
From: Scott Whitney @ 2014-07-07 20:22 UTC (permalink / raw)
To: Mike Christensen <mike@kitchenpc.com>; Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
It does say a FULL vacuum, and that you are not doing.
<div>-------- Original message --------</div><div>From: Mike Christensen <mike@kitchenpc.com> </div><div>Date:07/07/2014 3:17 PM (GMT-06:00) </div><div>To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> </div><div>Cc: pgsql-admin@postgresql.org,Forums postgresql <pgsql-general@postgresql.org> </div><div>Subject: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions </div><div>
</div>Sounds like you just have to wait until it finishes..
On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena <prabhjot.sheena@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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:25 Nicolas Zin <nicolas.zin@savoirfairelinux.com>
parent: Mike Christensen <mike@kitchenpc.com>
0 siblings, 1 reply; 20+ messages in thread
From: Nicolas Zin @ 2014-07-07 20:25 UTC (permalink / raw)
To: Mike Christensen <mike@kitchenpc.com>; +Cc: pgsql-admin@postgresql.org, Forums postgresql <pgsql-general@postgresql.org>; Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
Maybe you can priorize your worker with a ionice?
----- Mail original -----
De: "Mike Christensen" <mike@kitchenpc.com>
À: "Prabhjot Sheena" <prabhjot.sheena@rivalwatch.com>
Cc: pgsql-admin@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.sheena@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-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:27 Bill Moran <wmoran@potentialtech.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 20+ messages in thread
From: Bill Moran @ 2014-07-07 20:27 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; +Cc: Mike Christensen <mike@kitchenpc.com>; Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
On Mon, 7 Jul 2014 15:22:44 -0500 (CDT) Scott Whitney <scott@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 --------</div><div>From: Mike Christensen <mike@kitchenpc.com> </div><div>Date:07/07/2014 3:17 PM (GMT-06:00) </div><div>To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> </div><div>Cc: pgsql-admin@postgresql.org,Forums postgresql <pgsql-general@postgresql.org> </div><div>Subject: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions </div><div>
> </div>Sounds like you just have to wait until it finishes..
>
>
> On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena <prabhjot.sheena@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 <wmoran@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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:31 Frank Pinto <frank@ayalo.co>
parent: Nicolas Zin <nicolas.zin@savoirfairelinux.com>
0 siblings, 0 replies; 20+ messages in thread
From: Frank Pinto @ 2014-07-07 20:31 UTC (permalink / raw)
To: Nicolas Zin <nicolas.zin@savoirfairelinux.com>; +Cc: Mike Christensen <mike@kitchenpc.com>; pgsql-admin@postgresql.org, Forums postgresql <pgsql-general@postgresql.org>; Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
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.zin@savoirfairelinux.com> wrote:
> Maybe you can priorize your worker with a ionice?
>
> ----- Mail original -----
> De: "Mike Christensen" <mike@kitchenpc.com>
> À: "Prabhjot Sheena" <prabhjot.sheena@rivalwatch.com>
> Cc: pgsql-admin@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.sheena@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
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:31 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2 siblings, 1 reply; 20+ messages in thread
From: Alvaro Herrera @ 2014-07-07 20:31 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
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 Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:44 Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 20+ messages in thread
From: Prabhjot Sheena @ 2014-07-07 20:44 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
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 <alvherre@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 Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 20:51 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
0 siblings, 0 replies; 20+ messages in thread
From: Alvaro Herrera @ 2014-07-07 20:51 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
Prabhjot Sheena wrote:
> 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".
Ouch. I had read you were on 9.3 in your first email, but this message
gives me a trip back in time a decade ago -- you're on 8.3! Clearly you
have had vacuuming issues for a long time.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 21:05 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 20+ messages in thread
From: Scott Whitney @ 2014-07-07 21:05 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
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 --------</div><div>From: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> </div><div>Date:07/07/2014 3:46 PM (GMT-06:00) </div><div>To: Alvaro Herrera <alvherre@2ndquadrant.com> </div><div>Cc: pgsql-admin@postgresql.org,Forums postgresql <pgsql-general@postgresql.org> </div><div>Subject: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions </div><div>
</div>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 <alvherre@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 Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 21:14 Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 2 replies; 20+ messages in thread
From: Prabhjot Sheena @ 2014-07-07 21:14 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
i will run full vacuum than and see how it goes.
Thanks
avi
On Mon, Jul 7, 2014 at 2:05 PM, Scott Whitney <scott@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-admin@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 <alvherre@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 Herrera http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 22:10 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2 siblings, 0 replies; 20+ messages in thread
From: Matheus de Oliveira @ 2014-07-07 22:10 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
On Mon, Jul 7, 2014 at 4:56 PM, Prabhjot Sheena <
prabhjot.sheena@rivalwatch.com> wrote:
> We are using postgresql 8.3 database for last 5 yrs for this
> production database and its running fine.
>
Man. You really should consider upgrading to a more recent version. If you
are running "fine" with 8.3 you'll be running fantastically fine on 9.3...
:-)
Also, 8.3 is EOL now. People shouldn't be using it anymore.
> 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.
If your VACUUM is still running, it may be a simple problem of waiting it
(and hopping for it to finish before you run out of transactions). You
shouldn't be using --analyze there, because it is not necessary for this
case and may slow down the VACUUM.
Another problem you need to consider is that if there is a really old
transaction (about 2bi old) opened that is preventing VACUUM to lower
relfrozenxid on tables. To do this you first need to check for very old
sessions at pg_stat_activity and also (if you use prepared transaction)
query pg_prepared_xacts, if something old is there you must finish it
either with ROLLBACK PREPARED or (less likely) COMMIT PREPARED.
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 22:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
1 sibling, 0 replies; 20+ messages in thread
From: Tom Lane @ 2014-07-07 22:51 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: Scott Whitney <scott@journyx.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
Prabhjot Sheena <prabhjot.sheena@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-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 22:57 John R Pierce <pierce@hogranch.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
1 sibling, 1 reply; 20+ messages in thread
From: John R Pierce @ 2014-07-07 22:57 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-07 23:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: John R Pierce <pierce@hogranch.com>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2014-07-07 23:14 UTC (permalink / raw)
To: John R Pierce <pierce@hogranch.com>; +Cc: pgsql-general@postgresql.org
John R Pierce <pierce@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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-08 10:44 Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 20+ messages in thread
From: Prabhjot Sheena @ 2014-07-08 10:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John R Pierce <pierce@hogranch.com>; pgsql <pgsql-general@postgresql.org>
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 <tgl@sss.pgh.pa.us> wrote:
> John R Pierce <pierce@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
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-08 11:43 hubert depesz lubaczewski <depesz@gmail.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
1 sibling, 1 reply; 20+ messages in thread
From: hubert depesz lubaczewski @ 2014-07-08 11:43 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; John R Pierce <pierce@hogranch.com>; pgsql <pgsql-general@postgresql.org>
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.sheena@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 <tgl@sss.pgh.pa.us> wrote:
>
>> John R Pierce <pierce@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
>>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-08 12:39 Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
parent: hubert depesz lubaczewski <depesz@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Prabhjot Sheena @ 2014-07-08 12:39 UTC (permalink / raw)
To: hubert depesz lubaczewski <depesz@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; John R Pierce <pierce@hogranch.com>; pgsql <pgsql-general@postgresql.org>
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 <depesz@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.sheena@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 <tgl@sss.pgh.pa.us> wrote:
>>
>>> John R Pierce <pierce@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
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-08 13:12 hubert depesz lubaczewski <depesz@gmail.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
0 siblings, 0 replies; 20+ messages in thread
From: hubert depesz lubaczewski @ 2014-07-08 13:12 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; John R Pierce <pierce@hogranch.com>; pgsql <pgsql-general@postgresql.org>
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.sheena@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 <
> depesz@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.sheena@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 <tgl@sss.pgh.pa.us> wrote:
>>>
>>>> John R Pierce <pierce@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
>>>>
>>>
>>>
>>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions
@ 2014-07-08 14:22 Kevin Grittner <kgrittn@ymail.com>
parent: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
1 sibling, 0 replies; 20+ messages in thread
From: Kevin Grittner @ 2014-07-08 14:22 UTC (permalink / raw)
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John R Pierce <pierce@hogranch.com>; pgsql <pgsql-general@postgresql.org>
Prabhjot Sheena <prabhjot.sheena@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
That is to be expected. The VACUUM FULL command is not useful in
this situation. You need to do a normal VACUUM of the full
database, which is something completely different. You might want
to read this blog page for a discussion of this:
http://rhaas.blogspot.com/2014/03/vacuum-full-doesnt-mean-vacuum-but.html
If you can upgrade to a supported version you will find many
improvements to VACUUM in general and autovacuum in particular, but
that's probably something to consider after you dig yourself out of
the hole you're in now due to inadequate vacuuming.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2014-07-08 14:22 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-07 19:56 WARNING: database must be vacuumed within 8439472 transactions Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2014-07-07 20:15 ` Mike Christensen <mike@kitchenpc.com>
2014-07-07 20:25 ` Nicolas Zin <nicolas.zin@savoirfairelinux.com>
2014-07-07 20:31 ` Frank Pinto <frank@ayalo.co>
2014-07-07 20:31 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-07-07 20:44 ` Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2014-07-07 20:51 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-07-07 22:10 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2014-07-07 20:22 Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions Scott Whitney <scott@journyx.com>
2014-07-07 20:27 ` Bill Moran <wmoran@potentialtech.com>
2014-07-07 21:05 Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions Scott Whitney <scott@journyx.com>
2014-07-07 21:14 ` Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2014-07-07 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-07 22:57 ` John R Pierce <pierce@hogranch.com>
2014-07-07 23:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-08 10:44 ` Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2014-07-08 11:43 ` hubert depesz lubaczewski <depesz@gmail.com>
2014-07-08 12:39 ` Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
2014-07-08 13:12 ` hubert depesz lubaczewski <depesz@gmail.com>
2014-07-08 14:22 ` Kevin Grittner <kgrittn@ymail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox