On Thu, Nov 26, 2009 at 4:20 PM, Richard Neill
<rn214@cam.ac.uk> wrote:
Dear All,
I'm wondering whether Vacuum/analyse (notably by the autovaccuum daemon) is responsible for some deadlocks/dropouts I'm seeing.
One particular table gets hit about 5 times a second (for single row updates and inserts) + associated index changes. This is a very light load for the hardware; we have 7 CPU cores idling, and very little disk activity. The query normally runs in about 20 ms.
However, the query must always respond within 200ms, or userspace gets nasty errors. [we're routing books on a sorter machine, and the book misses its exit opportunity]. Although this is a low load, it's a bit like a heartbeat.
The question is, could the autovacuum daemon (running either in vacuum or in analyse mode) be taking out locks on this table that sometimes cause the query response time to go way up (exceeding 10 seconds)?
I think I've set up autovacuum to do "little and often", using
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 20
those are basically thresholds. So in essence you are forcing your autovacuum to be active pretty often,
And from what I can read here, you are looking for completely opposite behaviour. Unless you think statistical image of your table will be completely invalid, after 20 modifications to it, which I am sure is not true.
--
GJ