pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Normal VACUUM locks indexes?
2+ messages / 2 participants
[nested] [flat]

* Normal VACUUM locks indexes?
@ 2005-08-18 13:08 Aldor <an@mediaroot.de>
  2005-08-19 14:59 ` Re: Normal VACUUM locks indexes? Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Aldor @ 2005-08-18 13:08 UTC (permalink / raw)
  To: pgsql-admin-owner@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Normal VACUUM locks indexes?
  2005-08-18 13:08 Normal VACUUM locks indexes? Aldor <an@mediaroot.de>
@ 2005-08-19 14:59 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Alvaro Herrera @ 2005-08-19 14:59 UTC (permalink / raw)
  To: Aldor <an@mediaroot.de>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-08-19 14:59 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-18 13:08 Normal VACUUM locks indexes? Aldor <an@mediaroot.de>
2005-08-19 14:59 ` Alvaro Herrera <alvherre@alvh.no-ip.org>

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