pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Aldor <an@mediaroot.de>
Cc: pgsql-admin@postgresql.org
Subject: Re: Normal VACUUM locks indexes?
Date: Fri, 19 Aug 2005 10:59:10 -0400
Message-ID: <20050819145910.GE12685@surnet.cl> (raw)
In-Reply-To: <4304883E.5010704@mediaroot.de>
References: <4304883E.5010704@mediaroot.de>

On Thu, Aug 18, 2005 at 02:08:14PM +0100, Aldor wrote:

> When running VACUUM it was vacuuming first the indexes, after that it 
> was vacuuming the table data. While it was vacuuming the indexes I tried 
> to make some queries into the table - I didn't get any lock error but 
> saw that that the process which was responsible for my query was set to 
> the state WAIT. After that I made a second query, again... WAIT. After 
> VACUUM finished the indexes and started processing the table data the 
> WAIT processes were processed and the query result appeared. Any new 
> query was also not set to WAIT, it was processed immediately.

Set to state WAIT where?  If you saw that in "top" or "ps" output, then
it wasn't set by Postgres itself -- the operating system did that, and
what it was waiting for was disk I/O.

If this is the case, you should consider setting the cost-based vacuum
nap options appropiately, so that the vacuum process does not impact on
I/O too much while running.  Read the section of the fine manual about
"Cost-based vacuum delay"; I think it's in the "runtime configuration"
section.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are."  -- Charles J. Sykes' advice to teenagers



view thread (2+ messages)

Message-ID: <20050819145910.GE12685@surnet.cl>
Permalink:  ../20050819145910.GE12685@surnet.cl/
Also on:    postgresql.org/message-id/20050819145910.GE12685@surnet.cl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: alvherre@alvh.no-ip.org, an@mediaroot.de
  Subject: Re: Normal VACUUM locks indexes?
  In-Reply-To: <20050819145910.GE12685@surnet.cl>
  References: <4304883E.5010704@mediaroot.de> <20050819145910.GE12685@surnet.cl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox