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

Re: BUG #5946: Long exclusive lock taken by vacuum (not full)

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
Cc: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #5946: Long exclusive lock taken by vacuum (not full)
Date: 2011-03-25 14:16:08
Message-ID: 1301062308-sup-5944@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Excerpts from Maxim Boguk's message of vie mar 25 05:56:41 -0300 2011:

> From documentation I know that vacuum (without full) can truncate empty
> pages from end of a relation if they are free and vacuum successfully grabed
> exclusive lock for short time.
> 
> However, I wasn't ready to learn that 'short exclusive lock' can be 10-20
> minutes in some cases.
> 
> In my case vacuum tried to truncate last 10-15GB from 100Gb relation, and
> each time (3) it was cost 10+ minutes of service downtime (because that
> table was completely locked).
> 
> Is  that correct behaviour? Are here any way to speedup that process or at
> least allow read-only queries during that time?
> 
> PS: no exessive disk IO observed during that 10+ min locks.

I think you may be using a version prior to a fix we did to that code,
to have it avoid sleeping due to vacuum_cost_delay.  This shouldn't
happen in 8.4 because it was fixed prior to that, though.

Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Branch: master Release: REL8_3_0 [21c27af65] 2007-09-10 17:58:45 +0000
Branch: REL8_2_STABLE Release: REL8_2_5 [053731ab0] 2007-09-10 17:58:50 +0000
Branch: REL8_1_STABLE Release: REL8_1_10 [e52f4ec32] 2007-09-10 17:58:56 +0000
Branch: REL8_0_STABLE Release: REL8_0_14 [a44103519] 2007-09-10 17:59:03 +0000

    Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold
    an exclusive lock on the table at this point, which we want to release as soon
    as possible.  This is called in the phase of lazy vacuum where we truncate the
    empty pages at the end of the table.
    
    An alternative solution would be to lower the vacuum delay settings before
    starting the truncating phase, but this doesn't work very well in autovacuum
    due to the autobalancing code (which can cause other processes to change our
    cost delay settings).  This case could be considered in the balancing code, but
    it is simpler this way.


-- 
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to

pgsql-bugs by date

Next:From: Kris JurkaDate: 2011-03-25 15:00:31
Subject: Re: BUG #5948: JDBC wrond insert of timestamp data
Previous:From: Peter EisentrautDate: 2011-03-25 14:00:12
Subject: Re: BUG #5939: About bytea

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