Thread: Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions

Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions

From

Scott Whitney

Date:

07 July 2014, 21:06:05

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

Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions

From

Prabhjot Sheena

Date:

07 July 2014, 21:14:56

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

Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions

From

Tom Lane

Date:

07 July 2014, 22:51:28

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