From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Robert Treat <rob(at)xzilla(dot)net> |
Cc: | Lonni J Friedman <netllama(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: autovacuum stuck on a table for 18+ hours, consuming lots of CPU time |
Date: | 2011-11-23 06:27:35 |
Message-ID: | CAOR=d=1y=O2Y+WNL8dCQZJimtUqNV-FQXv1eMu4OfxqAvkdFHA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Nov 22, 2011 at 10:51 PM, Robert Treat <rob(at)xzilla(dot)net> wrote:
> 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.
I'd keep an eye on iostat -xd 10 output when playing with vacuum settings.
From | Date | Subject | |
---|---|---|---|
Next Message | Капралов Александр | 2011-11-23 10:56:23 | Optimize the query, help me please. |
Previous Message | Robert Treat | 2011-11-23 06:12:53 | Re: wal archiving on a hot-standby server |