Analyse without locking? - Mailing list pgsql-performance

From Richard Neill
Subject Analyse without locking?
Date
Msg-id 4B0EAAD3.3080505@cam.ac.uk
Whole thread Raw
Responses Re: Analyse without locking?  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Re: Analyse without locking?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Analyse without locking?  (Andres Freund <andres@anarazel.de>)
Re: Analyse without locking?  (Greg Smith <greg@2ndquadrant.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Richard Neill
Date:
Subject: Re: Query times change by orders of magnitude as DB ages
Next
From: Grzegorz Jaśkiewicz
Date:
Subject: Re: Analyse without locking?