Thread: selects during vacuum
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
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
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
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?
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?
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
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)
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.
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
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
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