Skip site navigation (1) Skip section navigation (2)

Re: Problems with autovacuum and vacuum

From: Filip RembiaƂkowski <plk(dot)zuber(at)gmail(dot)com>
To: JotaComm <jota(dot)comm(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Problems with autovacuum and vacuum
Date: 2011-01-01 19:22:31
Message-ID: AANLkTinr4NmAJAR+rLiPwhGXRNEgeLFHwaH_fzQ0jBd+@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2010/12/30 JotaComm <jota(dot)comm(at)gmail(dot)com>

> Hello,
>
> Last week I had a serious problem with my PostgreSQL database. My
> autovacuum is OFF, but in September it started to prevent the transaction
> wraparoud; however last week the following message appeared continuously in
> my log:
>
> WARNING: database "production" must be vacuumed within 4827083 transactions
>
> HINT: To avoid a database shutdown, execute a full-database VACUUM in
> "production".
>
> This message appeared for five to six hours; after that, the message
> disappeared from log. Any idea about what could have happened?
>
>

probably another "wraparaund-forced" autovacuum worker did the job, so the
warnings disappeared



> Every day the vacuum is executed on some tables; and on Sundays it's
> executed on all tables. But as the autovacuum is running since September,
> and it runs for a long time, the vacuum was blocked because autovacuum had
> been running on the same table. How should I procede in this case?
>


hmm. single vacuum process runs for more than 3 months on a table with
1000000000 rows?
this is ... less than 128 rows/second, not good.

I would rather terminate this old process, and start a VACUUM VERBOSE when
the database is less loaded.


How many INS/UPD/DEL you have on this table?



PS. When you fix this, enable autovacuum, to avoid more problems...

In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2011-01-01 19:29:45
Subject: Re: Sync Rep Design
Previous:From: Simon RiggsDate: 2011-01-01 19:03:30
Subject: Re: Sync Rep Design

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group