From: Aldor <an@mediaroot.de>
To: pgsql-admin-owner@postgresql.org
Subject: Normal VACUUM locks indexes?
Date: Thu, 18 Aug 2005 14:08:14 +0100
Message-ID: <4304883E.5010704@mediaroot.de> (raw)
Hi,
when vacuuming a big table with some indexes I experienced a locking of
VACUUM.
As I understood the theory the normal VACUUM should never lock something
or influence queries made to the table.
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.
The idea of normal VACUUM is to run it while the system needs to process
user queries in the same time.
I saw this behevior when setting the vacuum-options in postgresql.conf
to no delay so that I can run VACUUM with nearly full power.
I'm not sure if I would experience the same problem when I would set up
a delay and setup the cost rules in postgresql.conf but I think when
normal VACUUM starts vacuuming the indexes of the table it locks queries
using them. On bigger tables this can mean that your queries are
sleeping for 10-20 Minutes until the indexes have been vacuumed and this
is the reason why I think that this behavior is not compatible with the
"theory" of normal VACUUM;-)
1) Question: Is this a normal behavior of Postgres to lock the indexes
while vacuuming them?
2a) Question: If yes: Is there anything which can be done to prevent
Postgres doing this or maybe tell VACUUM to skip the indexes and only
vacuum the table data?
2b) Question: If no: Which parameters are important to find out why this
happens only in my case or what kind of other information is needed to
find out why this happen?
Aldor
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: an@mediaroot.de, pgsql-admin-owner@postgresql.org
Subject: Re: Normal VACUUM locks indexes?
In-Reply-To: <4304883E.5010704@mediaroot.de>
References: <4304883E.5010704@mediaroot.de>
* 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