Re: vacuum won't even start

[Jean-Christophe Praud <jc@xxxxxxxxx>]

Tom Lane a écrit :

Jean-Christophe Praud <jc@xxxxxxxxx> writes:
  

I've a problem on a heavy loaded database: vacuums don't work since 
about a week. All I got is:
    

mybase=# vacuum verbose analyze public.mytable;
INFO:  vacuuming "public.mytable"
(I stop it after hours)
    

Looking with top and iotop, I see the process takes some cpu and disk io 
time during several minutes, then it seems to fall asleep.
The process isn't locked according to pg_stat_activity.
    

When vacuum wants to clean up a particular table page, it will wait
until no other process is examining that page; and this wait is not
visible in pg_locks.  Perhaps you have got some queries referencing
those tables that have stopped midway and are just sitting?

Although pg_locks won't immediately show the wait, it could be useful
to help identify the culprit --- look for other processes holding
any type of lock on the table the vacuum is stuck on, and then go to
pg_stat_activity to see how old their current query is.

			regards, tom lane
  

Indeed, the tables I tried to vacuum have locks on them.  AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal.

How can I get rid of these blocking queries without restarting the server ? They are not listed as "waiting" in pg_stat_activity.

These queries are MOVE FORWARD on cursors, the underlying query is a rather complex one (unions, joins, functions calls)

Regards,

-- 
JC
Ph'nglui  mglw'nafh  Cthulhu  n'gah  Bill  R'lyeh  Wgah'nagl fhtagn!