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

Re: Update blocking a select count(*)?

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Benedict Holland" <benedict(dot)m(dot)holland(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Update blocking a select count(*)?
Date: 2012-06-15 19:03:35
Message-ID: 4FDB40B702000025000484EE@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> wrote:
 
> 10:25:08.329-04    vacuum (analyze, verbose, full)
> 2096    rmv    33528    postgres        8/151   
> AccessExclusiveLock
> Yes    2012-06-15 10:25:08.329-04    vacuum (analyze, verbose,
> full)
> 2096    rmv    50267    postgres        8/151   
> AccessExclusiveLock
> Yes    2012-06-15 10:25:08.329-04    vacuum (analyze, verbose,
> full)
 
You have three VACUUM FULL commands running?  VACUUM FULL is very
aggressive maintenance, which is only needed for cases of extreme
bloat.  It does lock the table against any concurrent access, since
it is completely rewriting it.
 
Now, if you are running UPDATE statements which affect all rows in a
table, you will *get* extreme bloat.  You either need to do such
updates as a series of smaller updates with VACUUM commands in
between, or schedule your aggressive maintenance for a time when it
can have exclusive access to the tables with minimal impact.
 
Reporting the other issues without mentioning the VACUUM FULL
processes is a little bit like calling from the Titanic to mention
that the ship isn't going as fast as it should and neglecting to
mention the iceberg.  :-)
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Benedict HollandDate: 2012-06-15 19:04:32
Subject: Re: Update blocking a select count(*)?
Previous:From: Kevin GrittnerDate: 2012-06-15 18:54:04
Subject: Re: Update blocking a select count(*)?

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