Skip site navigation (1) Skip section navigation (2)

Analyse without locking?

From: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Analyse without locking?
Date: 2009-11-26 16:20:35
Message-ID: 4B0EAAD3.3080505@cam.ac.uk (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-performance by date

Next:From: Grzegorz JaśkiewiczDate: 2009-11-26 16:26:30
Subject: Re: Analyse without locking?
Previous:From: Richard NeillDate: 2009-11-26 16:04:32
Subject: Re: Query times change by orders of magnitude as DB ages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group