Обсуждение: Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions

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

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

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

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

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

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