Re: Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

On Thu, May 24, 2012 at 12:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
>> On Thu, May 24, 2012 at 12:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Can you correlate the performance hit with any specific part of
>>> autovacuum? In particular, I'm wondering if it matters whether vacuum
>>> is cleaning tables or indexes --- it alternates between the two, and the
>>> access patterns are a bit different. You could probably watch what the
>>> autovac process is doing with strace to see what it's accessing.
>
>> Is there something specific I should be looking for in the strace
>> output, or is this just a matter of correlating PID and FD to
>> pg_class.relfilenode ?
>
> Nah, just match up the files it touches with pg_class.relfilenode.

Seems to be slower across the board regardless of what is being
vacuumed (tables or indices).

For example, i have a relatively small table (currently 395 rows,
rarely has any inserts or deletes). vacuuming just the table (not
indices) takes on average 4s with the khugepaged defragmenter on, and
less than 1s with it off. vacuuming just the indices takes on average
2s with the khugepaged defragmenter on, and less than 1s with it off.

The much larger tables (thousands to millions of rows), I see similar
performance (although they take even longer to complete with & without
the khugepaged defragmenter enabled).