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

Re: Update blocking a select count(*)?

From: Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Update blocking a select count(*)?
Date: 2012-06-15 19:27:14
Message-ID: CAD+mzozdFBvaNuPpVw3Wk_n-=X1MdQAwMRc83EUoXCU2Ct5WMw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
For kicks I stopped the full vacuum and the status of the remaining
processes has not changed. The select count(*) is still blocked by the
update.

~Ben



On Fri, Jun 15, 2012 at 3:12 PM, Benedict Holland <
benedict(dot)m(dot)holland(at)gmail(dot)com> wrote:

> Yes. I needed to do a full vacuum. Again, the database is very large. I
> batch inserted quite a lot of data and then modified that data. The vacuum
> isn't blocking anything. It was blocking other tables (as expected) but
> continues to run and clean. My tables in general are around 10GB, each
> update seems to nearly double the size of it so I required a full vacuum.
> The blocked statements are the select count(*) and the alter table. Both
> are blocked on the update table command. The alter table command SHOULD be
> blocked and that is fine. The select count(*) should never be blocked as
> that is the whole point of running an MVCC operation at least to my
> understanding. I can even accept the use case that the select should block
> with an Alter Table operation if data is retrieved from the table, but a
> select count(*) only returns the number of rows and should be table space
> independent. I also don't understand why a select count(*) requires an
> AccessShareLock. I don't understand why a select should lock anything at
> all.
>
> ~Ben
>
>
> On Fri, Jun 15, 2012 at 3:03 PM, Kevin Grittner <
> Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
>> 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

pgsql-performance by date

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

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