Thread: selects during vacuum

selects during vacuum

From
Joe Maldonado
Date:
Hello,
    Vacuum analyze is taking a really long time on a fairly small table and
during the time the vacuum is running all "select * from <the table>;"
seems to hang untill the vacuum is done.  Any help on this matter would
be greatly appreciated...

-Joe


Re: selects during vacuum

From
"Nigel J. Andrews"
Date:
On 15 Jul 2003, Joe Maldonado wrote:

> Hello,
>     Vacuum analyze is taking a really long time on a fairly small table and
> during the time the vacuum is running all "select * from <the table>;"
> seems to hang untill the vacuum is done.  Any help on this matter would
> be greatly appreciated...
>

You probably need to question why the vacuum analyse is taking a long time on a
small table. Not because it isn't annoying for your selects to appear to hang
but because it shouldn't take a long time to analyze a small table so there's
probably something more significant wrong than slow/blocked queries.


--
Nigel J. Andrews


Re: selects during vacuum

From
Andrew Sullivan
Date:
On Tue, Jul 15, 2003 at 04:09:28PM -0400, Joe Maldonado wrote:
> Hello,
>     Vacuum analyze is taking a really long time on a fairly small table and
> during the time the vacuum is running all "select * from <the table>;"
> seems to hang untill the vacuum is done.  Any help on this matter would
> be greatly appreciated...

If you're running Postgres < 7.2, then vacuum blocks.  And it's
probably waiting for a different transaction to finish before it
proceeds, which means everything is blocked.  Alternatively, if
you've actually run VACUUM FULL, you get the same behaviour.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: selects during vacuum

From
"scott.marlowe"
Date:
On 15 Jul 2003, Joe Maldonado wrote:

> Hello,
>     Vacuum analyze is taking a really long time on a fairly small table and
> during the time the vacuum is running all "select * from <the table>;"
> seems to hang untill the vacuum is done.  Any help on this matter would
> be greatly appreciated...

What version of postgresql are you running?


Re: selects during vacuum

From
Joe Maldonado
Date:
Sorry forgot to mention we are running postgres 7.2.3.

-Joe
On Tue, 2003-07-15 at 16:15, scott.marlowe wrote:
> On 15 Jul 2003, Joe Maldonado wrote:
>
> > Hello,
> >     Vacuum analyze is taking a really long time on a fairly small table and
> > during the time the vacuum is running all "select * from <the table>;"
> > seems to hang untill the vacuum is done.  Any help on this matter would
> > be greatly appreciated...
>
> What version of postgresql are you running?


Re: selects during vacuum

From
Richard Huxton
Date:
On Tuesday 15 Jul 2003 9:09 pm, Joe Maldonado wrote:
> Hello,
>     Vacuum analyze is taking a really long time on a fairly small table and
> during the time the vacuum is running all "select * from <the table>;"
> seems to hang untill the vacuum is done.  Any help on this matter would
> be greatly appreciated...

Can you post the output of a "verbose" vacuum analyse? That will tell us what
it's up to.

--
  Richard Huxton

Re: selects during vacuum

From
Alvaro Herrera
Date:
On Tue, Jul 15, 2003 at 04:09:28PM -0400, Joe Maldonado wrote:

>     Vacuum analyze is taking a really long time on a fairly small table and
> during the time the vacuum is running all "select * from <the table>;"
> seems to hang untill the vacuum is done.  Any help on this matter would
> be greatly appreciated...

Try to reindex the table.  That should speed up the vacuuming.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

Re: selects during vacuum

From
Joe Maldonado
Date:
Here it is...
things to note:
    This table contains 1 record allways.
    The record is updated once per second.
    Every 167 seconds a vacuum analyze is run on the table
    After some time it hangs in the analyze and blocks all
     access to that table including selects.


# vacuum analyze verbose <table_name>
# ;
NOTICE:  --Relation <table_name>--
NOTICE:  Pages 513: Changed 1, Empty 0; Tup 80: Vac 0, Keep 79, UnUsed 4540.
        Total CPU 0.02s/0.00u sec elapsed 0.02 sec.
NOTICE:  --Relation pg_toast_18119--
NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing <table_name>

We dropped the table and recreated it and things have seemed to be
working right for now though I have an accelerated simulation of the
conditions running on another db to see if we can reproduce...

-Joe

On Tue, 2003-07-15 at 16:35, Richard Huxton wrote:
> On Tuesday 15 Jul 2003 9:09 pm, Joe Maldonado wrote:
> > Hello,
> >     Vacuum analyze is taking a really long time on a fairly small table and
> > during the time the vacuum is running all "select * from <the table>;"
> > seems to hang untill the vacuum is done.  Any help on this matter would
> > be greatly appreciated...
>
> Can you post the output of a "verbose" vacuum analyse? That will tell us what
> it's up to.


Re: selects during vacuum

From
Richard Huxton
Date:
On Wednesday 16 Jul 2003 8:04 pm, Joe Maldonado wrote:
> Here it is...
> things to note:
>     This table contains 1 record allways.
>     The record is updated once per second.
>     Every 167 seconds a vacuum analyze is run on the table
>     After some time it hangs in the analyze and blocks all
>      access to that table including selects.
>
>
> # vacuum analyze verbose <table_name>
> # ;
> NOTICE:  --Relation <table_name>--
> NOTICE:  Pages 513: Changed 1, Empty 0; Tup 80: Vac 0, Keep 79, UnUsed
> 4540. Total CPU 0.02s/0.00u sec elapsed 0.02 sec.
> NOTICE:  --Relation pg_toast_18119--
> NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
>         Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
> NOTICE:  Analyzing <table_name>

OK - this is telling us there are 513 disk pages in use, and 80 tuples (rows)
in use with 4540 not in use (old/deleted versions of rows). There's also an
empty toasted table associated (presumably you have a text field?)

Are you sure there's only one row in the table? Could it be you have 79 old,
long-running transactions seeing older versions of the data?

> We dropped the table and recreated it and things have seemed to be
> working right for now though I have an accelerated simulation of the
> conditions running on another db to see if we can reproduce...

Someone else suggested reindexing (which frankly is what I suspected) but I
don't see an index mentioned above. Must admit I'm puzzled - does the fact
you had 80 tuples above make any sense to you?

--
  Richard Huxton

Re: selects during vacuum

From
Joe Maldonado
Date:
On Thu, 2003-07-17 at 03:11, Richard Huxton wrote:
> On Wednesday 16 Jul 2003 8:04 pm, Joe Maldonado wrote:
> > Here it is...
> > things to note:
> >     This table contains 1 record allways.
> >     The record is updated once per second.
> >     Every 167 seconds a vacuum analyze is run on the table
> >     After some time it hangs in the analyze and blocks all
> >      access to that table including selects.
> >
> >
> > # vacuum analyze verbose <table_name>
> > # ;
> > NOTICE:  --Relation <table_name>--
> > NOTICE:  Pages 513: Changed 1, Empty 0; Tup 80: Vac 0, Keep 79, UnUsed
> > 4540. Total CPU 0.02s/0.00u sec elapsed 0.02 sec.
> > NOTICE:  --Relation pg_toast_18119--
> > NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
> >         Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
> > NOTICE:  Analyzing <table_name>
>
> OK - this is telling us there are 513 disk pages in use, and 80 tuples (rows)
> in use with 4540 not in use (old/deleted versions of rows). There's also an
> empty toasted table associated (presumably you have a text field?)

the schema has 4 ints, 1 text ( large blob of xml ), 1 boolean
>
> Are you sure there's only one row in the table? Could it be you have 79 old,
> long-running transactions seeing older versions of the data?

select count(*) from this table returns 1. also this record is only
updated no inserts are done unless the table is empty.

>
> > We dropped the table and recreated it and things have seemed to be
> > working right for now though I have an accelerated simulation of the
> > conditions running on another db to see if we can reproduce...
>
> Someone else suggested reindexing (which frankly is what I suspected) but I
> don't see an index mentioned above. Must admit I'm puzzled - does the fact
> you had 80 tuples above make any sense to you?

There are no indexes because this table consited on only 1 tuple and is
not joined with any other tables during the queries.  Is there anything
to be gained from indexing this table?
as far as the 80 tuples, that does not make sense to me since the table
is vacuumed regularly and we do not insert into it...though I can go
back and double check...

-Joe


Re: selects during vacuum

From
Tom Lane
Date:
Joe Maldonado <jmaldonado@webehosting.biz> writes:
>>> This table contains 1 record allways.
>>> The record is updated once per second.
>>> Every 167 seconds a vacuum analyze is run on the table
>>> After some time it hangs in the analyze and blocks all
>>> access to that table including selects.
>>
>> Someone else suggested reindexing (which frankly is what I suspected) but I
>> don't see an index mentioned above. Must admit I'm puzzled - does the fact
>> you had 80 tuples above make any sense to you?

> There are no indexes because this table consited on only 1 tuple and is
> not joined with any other tables during the queries.  Is there anything
> to be gained from indexing this table?

Probably not.

> as far as the 80 tuples, that does not make sense to me since the table
> is vacuumed regularly and we do not insert into it...though I can go
> back and double check...

Given that you update once per second, that just says that your oldest
open transaction was eighty seconds old when VACUUM ran.  VACUUM won't
reclaim tuples that *might* be visible to some other open transaction.
But its method of detecting this is not exact enough to determine
exactly which tuples are visible to exactly which transactions.  Most
likely, only a few of the last eighty updates are actually still visible
to any live transaction, but VACUUM is not going to realize that.  It
just keeps everything newer than the oldest open transaction.

What I don't understand is how the ANALYZE step could hang up.  It
acquires ACCESS SHARE lock on the target table, and I think in the
version you are running it temporarily acquires EXCLUSIVE (*not* ACCESS
EXCLUSIVE) lock on pg_statistic.  Neither of those could create any
deadlock unless you have other transactions doing strange things, like
trying to acquire ACCESS EXCLUSIVE lock on the target table, or perhaps
acquiring locks on pg_statistic.

            regards, tom lane