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