Thread: vacuum process is hanging

Greetings,
I've got an 8.1.10 instance running on Linux-i686.  The system hosts 5
databases, all of which get vacuumed via a cronjob 3 times a day.  All
of a sudden, the vacuum job for 1 of the databases is hanging
indefinitely.  It normally finishes in under 5 minutes.  There are no
errors in the output, it just stops producing any output.  The command
that I'm running is "vacuumdb -v -z -f -d inventory".  I also tried
removing the -z and -f options to see if that would at least get it to
complete, but that had no impact.  Here's the tail end of the output,
leading up to the hang:

INFO:  vacuuming "public.cuhc"
INFO:  index "cuhc_system_key" now contains 0 row versions in 4 pages
DETAIL:  1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "cuhc_system_index" now contains 0 row versions in 4 pages
DETAIL:  1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "cuhc_syststamp_index" now contains 0 row versions in 4 pages
DETAIL:  1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "cuhc_syststamprebooted_index" now contains 0 row
versions in 4 pages
DETAIL:  1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "cuhc": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_85894"
INFO:  index "pg_toast_85894_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_85894": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.


Anyone have any suggestions on what to try to debug this?

thanks!

Lonni J Friedman <netllama@gmail.com> writes:
> I've got an 8.1.10 instance running on Linux-i686.  The system hosts 5
> databases, all of which get vacuumed via a cronjob 3 times a day.  All
> of a sudden, the vacuum job for 1 of the databases is hanging
> indefinitely.

Is it actually blocked, or just busy?  (strace'ing the vacuum process
would be one pretty definitive way of telling.)

8.1.10 is mighty old, so frankly I'd suggest an update to 8.1.recent
before you expend a whole lot of effort tracing the problem.

            regards, tom lane

On Wed, Jun 9, 2010 at 12:13 PM, Lonni J Friedman <netllama@gmail.com> wrote:
> hat I'm running is "vacuumdb -v -z -f -d inventory".  I also tried
> removing the -z and -f options to see if that would at least get it to
> complete, but that had no impact.  Here's the tail end of the output,
> leading up to the hang:

Check the pg_stat_activity view for any processes waiting on locks, or
in "idle in transaction" state for a long time.

On Wed, Jun 9, 2010 at 10:13 AM, Lonni J Friedman <netllama@gmail.com> wrote:
> Greetings,
> I've got an 8.1.10 instance running on Linux-i686.  The system hosts 5
> databases, all of which get vacuumed via a cronjob 3 times a day.  All
> of a sudden, the vacuum job for 1 of the databases is hanging
> indefinitely.  It normally finishes in under 5 minutes.  There are no
> errors in the output, it just stops producing any output.  The command
> that I'm running is "vacuumdb -v -z -f -d inventory".  I also tried
> removing the -z and -f options to see if that would at least get it to
> complete, but that had no impact.  Here's the tail end of the output,
> leading up to the hang:

Well, -f isn't highly recommended anyway, and if you have to do it you
probably need to schedule a reindex to run after it.

Anyway, what does top say about the pg process running the vacuum?