Claudio Freire <claudio(at)livra(dot)com> wrote:
> On Thu, 2010-06-03 at 13:42 -0400, Tom Lane wrote:
>> Claudio Freire <claudio(at)livra(dot)com> writes:
>> > What I did do is analyze server load during the events, and as
>> > I suspected, disk activity during the "deadlocks" seems to
>> > suggest a vacuuming taking place. Although there was no
>> > autovacuum entry in pg_stat_activity every time I checked, disk
>> > activity precisely matches the case when autovacuum decides to
>> > vacuum a big table.
>>
>> [ shrug... ] If autovacuum isn't shown in pg_stat_activity then
>> it's pretty hard to credit that there was an autovacuum going
>> on. Moreover, if there *was* an undetected deadlock that
>> autovacuum was somehow involved in, then the autovacuum would be
>> blocked too so it's hardly possible that you'd miss seeing it in
>> pg_stat_activity.
>
> I know. However, I seem to recall that HOT did a sort-of vacuuming
> of full pages, hoping to make space and not resort to regular
> updates. Now that wouldn't show up in pg_stat_activity, would it?
It would show up as part of whatever DML statement initiated the
cleanup. It also wouldn't generally look a whole lot like a
table-level vacuum. Other prime suspects would be checkpoint
activity (you might want to turn on checkpoint logging and see if
those match up to the events) and hint-bit rewrites (which can cause
disk writes the first time tuples are read after they are
committed). Based on what I've heard so far, I wouldn't entirely
rule out some sort of bloat being a factor, either.
-Kevin