Ian Westmacott <ianw@intellivid.com> writes:
> On Wed, 2005-07-13 at 11:55, Simon Riggs wrote:
>> On Tue, 2005-07-12 at 13:50 -0400, Ian Westmacott wrote:
>>> It appears not to matter whether it is one of the tables
>>> being written to that is ANALYZEd. I can ANALYZE an old,
>>> quiescent table, or a system table and see this effect.
>>
>> Can you confirm that this effect is still seen even when the ANALYZE
>> doesn't touch *any* of the tables being accessed?
> Yes.
This really isn't making any sense at all. I took another look through
the vacuum_delay_point() calls, and I can see a couple that are
questionably placed:
* the one in count_nondeletable_pages() is done while we are holding
exclusive lock on the table; we might be better off not to delay there,
so as not to block non-VACUUM processes longer than we have to.
* the ones in hashbulkdelete and rtbulkdelete are done while holding
various forms of exclusive locks on the index (this was formerly true
of gistbulkdelete as well). Again it might be better not to delay.
However, these certainly do not explain Ian's problem, because (a) these
only apply to VACUUM, not ANALYZE; (b) they would only lock the table
being VACUUMed, not other ones; (c) if these locks were to block the
reader or writer thread, it'd manifest as blocking on a semaphore, not
as a surge in LWLock thrashing.
>> Is that Xeon MP then?
> Yes.
The LWLock activity is certainly suggestive of prior reports of
excessive buffer manager lock contention, but it makes *no* sense that
that would be higher with vacuum cost delay than without. I'd have
expected the other way around.
I'd really like to see a test case for this...
regards, tom lane