Ian Westmacott <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend