Re: BUG #13970: Vacuum hangs on particular table; cannot be terminated - requires `kill -QUIT pid`

brian(at)pukkasoft(dot)com wrote:

> Hi Pg team - I've been running a 9.4.1 server for the last year+. In the
> past few months I've had a couple of instances of the server locking up.
> I've done more troubleshooting into this last event and uncovered what
> appears to be a bug. My situation is much like these:
>
> http://comments.gmane.org/gmane.comp.db.postgresql.admin/40587
> http://postgresql.nabble.com/VACUUM-hanging-on-PostgreSQL-8-3-1-for-larger-tables-td1898438.html
>
> The former claims lightweight locks had a bug up thru 9.4.5 but I'm running
> the latest 9.4.6 and still experiencing this issue with one table. Here's
> the scenario:
>
> * Either autovacuum OR manual vacuum on a single table hangs
> * There is no cpu or i/o usage; top -p <pid> shows the vacuum process is
> sleeping
> * strace of the process id shows rapidly scrolling screenfuls of `select(0,
> NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)`

This smells like it's looping waiting for a multixact to be fully
written out ... except that the uninterruptibility part of that was fixed in
time for 9.4.0,

Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Branch: master Release: REL9_5_BR [51f9ea25d] 2014-11-14 15:14:01 -0300
Branch: REL9_4_STABLE Release: REL9_4_0 [137e4da6d] 2014-11-14 15:14:02 -0300
Branch: REL9_3_STABLE Release: REL9_3_6 [d45e8dc52] 2014-11-14 15:14:02 -0300
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=137e4da6d

Can you attach to the looping process with gdb when it's doing the
select() dance, and obtain a backtrace? You need debug symbols
installed.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services