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

Re: VACUUM ANALYZE block the whole database

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Majid Azimi <majid(dot)merkava(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: VACUUM ANALYZE block the whole database
Date: 2012-08-02 16:44:32
Message-ID: CAOR=d=1riAK2AnkMEvXZGR6EWaRn7+TrsVpK=8qYJ3MJSCNgDw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Sun, Jul 29, 2012 at 11:42 PM, Majid Azimi <majid(dot)merkava(at)gmail(dot)com> wrote:

> because the database is growing too much(beyond tra byte in less than 6
> months) we need to delete the oldest data. in each delete we remove about 80
> million records from vbv(there is a trigger to delete the records from
> vbv_denorm and summary). after delete autovacuum starts on vbv. at the same
> time because summary is small (at maximum 2 million records) I ran VACUUM
> ANALYZE on it(it is not VACUUM FULL). but this cause the database to
> completely block. the process inserting to vbv blocks. the process sending
> summary records blocks. Why this is happening? autovacuum and VACUUM ANALYZE
> should not lock tables.

Vacuum by itself does not block read write to tables, however DDL can
block behind it, and then read and writing to tables can block behind
that DDL.  When this is happening, do the queries on this page:

http://wiki.postgresql.org/wiki/Lock_Monitoring

tell you anything useful?

In response to

pgsql-admin by date

Next:From: Laszlo NagyDate: 2012-08-03 08:31:43
Subject: Messed up time zones
Previous:From: Kevin GrittnerDate: 2012-08-02 16:08:56
Subject: Re: VACUUM ANALYZE block the whole database

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