On Thursday 26 November 2009 17:20:35 Richard Neill 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 > but I'm not sure this is doing exactly what I think it is. In > particular, the system-wide I/O (and CPU) limit of autovacuum is > negligible, but it's possible that queries may be waiting on locks. > > In particular, I want to make sure that the autovacuum daemon never > holds any lock for more than about 50ms at a time. (or will release it > immediately if something else wants it) > > Or am I barking up the wrong tree entirely? I would suggest enabling log_log_wait and setting deadlock_timeout to a low value - should give you more information. Andres -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
