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

Re: Problems with autovacuum and vacuum

From: JotaComm <jota(dot)comm(at)gmail(dot)com>
To: Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Problems with autovacuum and vacuum
Date: 2011-01-03 12:54:31
Message-ID: AANLkTikrm1QubXKjkWWcpe-vL6Bt4n89QFE9877L9j46@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello, Filip

2011/1/1 Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>

>
> 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?
>

About 15 millions rows inserted by day.

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

João Paulo

-- 
JotaComm
http://jotacomm.wordpress.com

In response to

pgsql-hackers by date

Next:From: Christian UllrichDate: 2011-01-03 13:11:37
Subject: SSPI client authentication in non-Windows builds
Previous:From: Dmitry KoterovDate: 2011-01-03 12:11:33
Subject: Re: pg_dump --split patch

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