Re: autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

On Tue, Nov 22, 2011 at 11:00 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
> On Tue, Nov 22, 2011 at 7:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
>>> I suspect you're right.  I just ran strace against that PID again, and
>>> now all the lseek & read FD's are referrring to a different number
>>> (115), so that means its moved onto something new since I looked a few
>>> hours ago?
>>
>>> Anyway, I think this is what you were referring to:
>>> /proc/30188/fd/115 ->   /var/lib/pgsql/data/base/64793/72633.10
>>
>>> How do I correlate that file to an actual database object?
>>
>> 64793 is the pg_database.oid of the database, and 72633 is the
>> pg_class.relfilenode value of the table/index.
>
> Its definitely an index.    Thanks for your help, I just need to be
> patient now that I understand how to better monitor this.
>

Well, it sounds like you have things set up for both a cost limit and
a cost delay, which means if you manually vacuumed the thing, it would
probably go quicker, at the cost of more i/o, but given the cpu
overhead, probably a trade worth making. Personally I'd throw out
those vacuum cost settings entirely as they cause more trouble than
they're worth (IMNSHO), and you'll likely see this again in the
future.

Robert Treat
conjecture: xzilla.net
consulting: omniti.com