Thread: Warning: database postgres must be vacuumed within 1000000 transactions

I got a big problem:
Warning: database 'postgres' must be vacuumed within 1000000 transactions
HINT: To avoid database shutdown, execute a full database vacuum  in
'postgres'

Warning: database 'postgres' must be vacuumed within 999999 transactions
HINT: To avoid database shutdown, execute a full database vacuum  in
'postgres'

Warning: database 'postgres' must be vacuumed within 999998 transactions
HINT: To avoid database shutdown, execute a full database vacuum  in
'postgres'

Warning: database 'postgres' must be vacuumed within 999997 transactions
HINT: To avoid database shutdown, execute a full database vacuum  in
'postgres'

backend> vacuum


Warning: database 'postgres' must be vacuumed within 999996 transactions
HINT: To avoid database shutdown, execute a full database vacuum  in
'postgres'

Warning: database 'postgres' must be vacuumed within 999995 transactions
HINT: To avoid database shutdown, execute a full database vacuum  in
'postgres'

its so slow!, i dont know what else to do, it will take 20 days to do it
all, and i cant wait,
help me please!

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Warning-database-postgres-must-be-vacuumed-within-1000000-transactions-tp3326896p3326896.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tuesday 04 January 2011 4:06:48 am asti1987 wrote:
> I got a big problem:
> Warning: database 'postgres' must be vacuumed within 1000000 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999999 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999998 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999997 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> backend> vacuum
>
>
> Warning: database 'postgres' must be vacuumed within 999996 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999995 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> its so slow!, i dont know what else to do, it will take 20 days to do it
> all, and i cant wait,
> help me please!
>

What is slow, the vacuum? Are you doing the vacuum on the 'postgres' database?



--
Adrian Klaver
adrian.klaver@gmail.com

>
> I got a big problem:
> Warning: database 'postgres' must be vacuumed within 1000000 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999999 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999998 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999997 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> backend> vacuum
>
>
> Warning: database 'postgres' must be vacuumed within 999996 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> Warning: database 'postgres' must be vacuumed within 999995 transactions
> HINT: To avoid database shutdown, execute a full database vacuum  in
> 'postgres'
>
> its so slow!, i dont know what else to do, it will take 20 days to do it
> all, and i cant wait,
> help me please!

1) What version of PostgreSQL is this?

2) Have you been running autovacuum? If not, have you been running VACUUM
regularly? If both answers are "no" then there may ne a lot of "garbage"
that has to be cleaned up by VACUUM.

3) If the VACUUM is slow (although I'm not sure what exactly you mean by
this), you can try to change several parameters.

   a) maintenance_work_mem - default is 16MB, increasing it may improve
the VACUUM performance significantly

   b) vacuum_cost_delay / vacuum_cost_limit - there are several cost
variavbles related to vacuuming, it's usually enough to modify just
these two so that the VACUUM acts more aggresively (e.g. set the
cost_delay to zero, and it won't sleep at all)

regards
Tomas

8.1.23
Im typing ¨vacuum¨ since yesterday in the backend and stop the vacuuming
each 30 minutes, and I try again typing vacuum, but im by the number
890271/1000000 and its too slow!, i dont know what else to do to make it
faster.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Warning-database-postgres-must-be-vacuumed-within-1000000-transactions-tp3326896p3327374.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

>
> 8.1.23
> Im typing ¨vacuum¨ since yesterday in the backend and stop the vacuuming
> each 30 minutes, and I try again typing vacuum, but im by the number
> 890271/1000000 and its too slow!, i dont know what else to do to make it
> faster.

Why are you stopping it each 30 minutes? And the fact that something runs
for a long time does not mean it's slow ... the vacuum is usually
configured to sleep from to time, not to case problems to the other
processes. Is it causing a lot of I/O? If not, try to tune the parameters
I've already mentioned in the previous e-mail.

Tomas

im not stopping, it stops by it self, what parameters i must to change?, how
i do that?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Warning-database-postgres-must-be-vacuumed-within-1000000-transactions-tp3326896p3327508.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 01/04/2011 09:01 AM, asti1987 wrote:
>
> 8.1.23
> Im typing ¨vacuum¨ since yesterday in the backend and stop the vacuuming
> each 30 minutes, and I try again typing vacuum, but im by the number
> 890271/1000000 and its too slow!, i dont know what else to do to make it
> faster.

Per the error message you need to VACUUM the 'postgres' database. Either
connect to it with psql and run VACUUM or use the command line utility
vacuumdb as follows:

vacuumdb -U postgres postgres

Where -U is the superuser usually postgres.

--
Adrian Klaver
adrian.klaver@gmail.com