Re: DB Tuning Notes for comment...

Tom Lane wrote:
> Scott Shattuck <ss@technicalpursuit.com> writes:
> 
>>Willing to learn here but skipping a vacuum full has caused some issues 
>>for us. Here's some data from a recent 3 day test run that was done with 
>>regular vacuums but not vacuum fulls. When running with vacuum full the 
>>indexes remain in line:
> 
> 
>>nsuite-10=# select relname, relpages, reltuples from pg_class where 
>>relname not like 'pg_%' order by reltuples desc;
>>-[ RECORD 1 ]------------------------------
>>relname   | directory_fullpath_ix
>>relpages  | 96012
>>reltuples | 1.38114e+06
>>-[ RECORD 2 ]------------------------------
>>relname   | directory_pkey
>>relpages  | 16618
>>reltuples | 1.38114e+06
>>-[ RECORD 3 ]------------------------------
>>relname   | directory
>>relpages  | 23924
>>reltuples | 59578
>><snip>
> 
> 
> <<blink>> There's no way that the index and table tuple counts should
> get that far out of line; in the absence of any concurrent updates,
> they should be *equal* (or index < table, if you have a partial index,
> which I assume these are not).  I would credit the recorded index count
> exceeding the recorded table count by the number of tuples inserted/
> updated while a (plain) VACUUM is in process on that table --- but this
> doesn't look like it meets that situation.
> 
> There was a bug a long time ago wherein vacuum would forget to update
> pg_class.reltuples for indexes in some cases, but according to the CVS
> logs that was fixed before 7.2 release.  What version are you running
> exactly?

test=# select version();                           version
------------------------------------------------------------- PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC
2.96
(1 row)

test=#

> 
> In any case, you seem to be dealing with some kind of bug here.  It
> might be helpful to look at the output of "vacuum verbose directory"
> if you still have it available.
> 


NOTICE:  --Relation directory--
NOTICE:  Index directory_pkey: Pages 15628; Tuples 4988848: Deleted 35407.        CPU 0.73s/3.00u sec elapsed 40.53
sec.

NOTICE:  Index directory_fullpath_ix: Pages 80808; Tuples 4989317: 
Deleted 35407.        CPU 4.84s/3.91u sec elapsed 275.66 sec.
NOTICE:  Removed 35407 tuples in 786 pages.        CPU 0.13s/0.11u sec elapsed 1.80 sec.
NOTICE:  Pages 80156: Changed 18, Empty 0; Tup 4988787: Vac 35407, Keep 
4977704, UnUsed 348422.        Total CPU 7.85s/7.58u sec elapsed 343.84 sec.


>             regards, tom lane
> 

Thanks for any insight you can offer here.


ss