Re: DB Tuning Notes for comment...

Tom Lane wrote:
> Scott Shattuck <ss(at)technicalpursuit(dot)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