Обсуждение: WARNING: database must be vacuumed within 8439472 transactions
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 messagesHINT: To avoid a database shutdown, execute a full-database VACUUM.
WARNING: database must be vacuumed within 8439472 transactions
vacuumdb --analyze db
WARNING: database must be vacuumed within 2645303 transactions.
The value of number of transactions is going down every minute
Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
От
Mike Christensen
Дата:
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:
AviThanksCan anyone tell me what is the best way to sort up this issue.while this command is running i m still getting these messagesi am currently running this commandHelloWe 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
vacuumdb --analyze db
WARNING: database must be vacuumed within 2645303 transactions.
The value of number of transactions is going down every minute
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 criticaldatabase 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
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
Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions
От
Alvaro Herrera
Дата:
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
От
Prabhjot Sheena
Дата:
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".
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:Did you omit the database name here, or is it really an empty string?
> 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
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
От
Alvaro Herrera
Дата:
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
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.
while this command is running i m still getting these messagesThis is our critical database which runs 24*7. This weekend we started getting these messagesi am currently running this command
HINT: To avoid a database shutdown, execute a full-database VACUUM.
WARNING: database must be vacuumed within 8439472 transactions
vacuumdb --analyze db
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
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres