From: | Jean-Christophe Praud <jc(at)steek(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: vacuum won't even start |
Date: | 2009-09-09 16:43:51 |
Message-ID: | 4AA7DB47.7080702@steek.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane a écrit :
> Jean-Christophe Praud <jc(at)steek(dot)com> 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!
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Mlodgenski | 2009-09-09 16:50:01 | Re: Swapped download links for 8.4.1 zip binaries |
Previous Message | Jean-Christophe Praud | 2009-09-09 16:27:15 | Re: vacuum won't even start |