obsessive-compulsive vacuum behavior

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: obsessive-compulsive vacuum behavior
Date: 2010-03-08 06:58:19
Message-ID: 5AF9F3CF-2B9D-4ACC-98DE-AB8AA797F9AB@silentmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've got an 8.4.2 database where it appears that vacuum keeps redoing the same table and indexes, never thinking it's finished:

auditor=# VACUUM analyze VERBOSE repair_queue ;
INFO: vacuuming "public.repair_queue"
INFO: scanned index "repair_queue_pkey" to remove 2795932 row versions
DETAIL: CPU 14.98s/15.29u sec elapsed 312.50 sec.
INFO: scanned index "repair_queue_auditor" to remove 2795932 row versions
DETAIL: CPU 0.74s/0.50u sec elapsed 10.49 sec.
INFO: scanned index "repair_queue_sort" to remove 2795932 row versions
DETAIL: CPU 2.99s/1.58u sec elapsed 45.14 sec.
INFO: scanned index "repair_queue_sort3" to remove 2795932 row versions
DETAIL: CPU 0.89s/0.48u sec elapsed 10.99 sec.
INFO: "repair_queue": removed 2795932 row versions in 43199 pages
DETAIL: CPU 1.04s/0.39u sec elapsed 17.93 sec.
INFO: scanned index "repair_queue_pkey" to remove 2795938 row versions
DETAIL: CPU 14.71s/15.06u sec elapsed 362.37 sec.
INFO: scanned index "repair_queue_auditor" to remove 2795938 row versions
DETAIL: CPU 0.62s/0.45u sec elapsed 14.36 sec.
INFO: scanned index "repair_queue_sort" to remove 2795938 row versions
DETAIL: CPU 2.97s/1.65u sec elapsed 56.94 sec.
INFO: scanned index "repair_queue_sort3" to remove 2795938 row versions
DETAIL: CPU 0.82s/0.44u sec elapsed 10.54 sec.
INFO: "repair_queue": removed 2795938 row versions in 41055 pages
DETAIL: CPU 0.75s/0.34u sec elapsed 7.59 sec.
INFO: scanned index "repair_queue_pkey" to remove 2795959 row versions
DETAIL: CPU 14.20s/14.56u sec elapsed 539.17 sec.
INFO: scanned index "repair_queue_auditor" to remove 2795959 row versions
DETAIL: CPU 0.75s/0.48u sec elapsed 13.76 sec.
INFO: scanned index "repair_queue_sort" to remove 2795959 row versions
DETAIL: CPU 3.07s/1.65u sec elapsed 44.29 sec.
INFO: scanned index "repair_queue_sort3" to remove 2795959 row versions
DETAIL: CPU 0.78s/0.44u sec elapsed 12.52 sec.
INFO: "repair_queue": removed 2795959 row versions in 41004 pages
DETAIL: CPU 0.88s/0.42u sec elapsed 12.49 sec.

...and so on. It's been running for an hour or so now, when it appears it shouldn't take 10 minutes. This seems pretty weird to me.... has anybody else seen this behavior? I'm not even sure what details I could report which would help figure out what's going on.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2010-03-08 07:16:39 Re: Avoiding duplicates (or at least marking them as such) in a "cumulative" transaction table.
Previous Message Allan Kamau 2010-03-08 06:31:19 Re: Avoiding duplicates (or at least marking them as such) in a "cumulative" transaction table.