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?
Thanks,
Richard
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
