Thread: What's canceling autovacuum tasks?
Hi there. During the weekend I've worked for hours on recovering table bloat. Now I was hoping that after the tables are properly trimmed,then after the next delete operation which created dead tuples, autovacuum will go into effect and do its job properly,and prevent the situation from recurring. Indeed autovacuum started working on some of the tables. At least one of these tables was one that I have trimmed up usingCLUSTER. So I was watching that autovacuum process carefully. And then suddenly it was gone, after working for 20-oddhours. And I had even more dead tuples in pg_stat_user_tables for that table than before. Looking at the log, I get this: 2011-02-06 15:21:42 IST ERROR: canceling autovacuum task 2011-02-06 15:21:42 IST CONTEXT: automatic vacuum of table "reports.alerts.smsq__archive" Why? Postgres version is 8.3.11 Thank you, Herouth
Herouth Maoz <herouth@unicell.co.il> writes:
> Indeed autovacuum started working on some of the tables. At least one of these tables was one that I have trimmed up
usingCLUSTER. So I was watching that autovacuum process carefully. And then suddenly it was gone, after working for
20-oddhours. And I had even more dead tuples in pg_stat_user_tables for that table than before.
> Looking at the log, I get this:
> 2011-02-06 15:21:42 IST ERROR: canceling autovacuum task
> 2011-02-06 15:21:42 IST CONTEXT: automatic vacuum of table "reports.alerts.smsq__archive"
> Why?
Most likely, some other session requested an exclusive lock on the
table. Autovacuum will quit to avoid blocking the other query.
regards, tom lane
on 06/02/11 18:16, quoting Tom Lane: > > Most likely, some other session requested an exclusive lock on the > table. Autovacuum will quit to avoid blocking the other query. > That's strange. During the day, only selects are running on that database, or at worst, temporary tables are being created and updated. And that particular table gets updated only on weekends (it's one of my archive tables). Besides, I assume that a simple update/insert/delete is not supposed to request an exclusive lock, or autovacuum would not work at all in an average database. Even backups don't run during the day, and I think backups also don't create an exclusive lock or I'd never see a vacuum process run more than a day. This is really inexplicable. Herouth
On 6 Feb 2011, at 18:52, Herouth Maoz wrote: > on 06/02/11 18:16, quoting Tom Lane: >> >> Most likely, some other session requested an exclusive lock on the >> table. Autovacuum will quit to avoid blocking the other query. >> > That's strange. During the day, only selects are running on that database, or at worst, temporary tables are being createdand updated. And that particular table gets updated only on weekends (it's one of my archive tables). Besides, I assumethat a simple update/insert/delete is not supposed to request an exclusive lock, or autovacuum would not work at allin an average database. Even backups don't run during the day, and I think backups also don't create an exclusive lockor I'd never see a vacuum process run more than a day. > > This is really inexplicable. You could try turning on statement-level logging. On a busy database the logs would probably grow huge, but you should beable to see what statements coincide with autovacuum aborting. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d4f250611735773614733!