On Thu, 2010-06-03 at 13:42 -0400, Tom Lane wrote:
> Claudio Freire <claudio@livra.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?
> > That's about as much information I can give. We've worked around the
> > issue successfully and it hasn't happened since. Even if it is not a
> > proper deadlock, the performance drop is unacceptable. I've done massive
> > updates before, and that performance drop was not expected (more than
> > one day for updating 30k rows on a table with a couple indices).
>
> I'm afraid we're not going to be able to do much with this report
> if you can't provide a reproduction scenario.
Hold your horses there... I may be able to give you a stripped dump of
the tables involved in the query.
They have sensitive data, but I guess I could randomize the contents and
keep only the relevant distributions.