Re: Why memory is not used ? Why vacuum so slow ?

Hi Tom,

Le Vendredi 2 Janvier 2004 15:42, Tom Lane a écrit :
> =?iso-8859-15?q?Herv=E9=20Piedvache?= <herve(at)elma(dot)fr> writes:
> > Second point ... after importing my dump ... I make a vacuum full analyze
> > of my base (in same time because of my caculation of the day before for
> > my aggregats and stats tables about 200 000 row deleted and/or inserted
> > for more than 20 tables (each)) ... but It takes about 5 hours ...
>
> Don't do vacuum full. You should not need it in ordinary circumstances,
> if you are doing plain vacuums on a reasonable schedule and you have the
> FSM parameters set high enough. (You do not BTW ... with 175000 pages in
> this table alone, 10000 FSM pages for the whole database is surely too
> low.)

Ok for this ... I have now configured the FSM pages to 300 000 ... then when I
have started the database I get a message about my SHMMAX too low ... it was
set to :
more /proc/sys/kernel/shmmax
262111232

Then I put 300000000 ... PostgreSQL accepted to start ... What can be maximum
value for this ? To be usufull to the entire configuration ... ?

Like this during during the vacuum full this is my used memory ...
total used free shared buffers cached
Mem: 2069608 2059052 10556 0 8648 1950672
-/+ buffers/cache: 99732 1969876
Swap: 2097136 16080 2081056

Seems that's I'm really using 5% of my memory ??? no ? or I missed something ?

Now difficult to test again ... I will have to wait tomorrow morning to see
the result ... because I have already vacuumed the base to day ...

But I have done again a full vacuum to see if I have quick visible
difference ... and I have also saw that the full vacuum for pg_atribute seems
to be so slow ... more than 1 min for 7256 tupples ? Is this is normal ?

INFO: --Relation pg_catalog.pg_attribute--
INFO: Pages 119: Changed 0, reaped 1, Empty 0, New 0; Tup 7256: Vac 0, Keep/
VTL 0/0, UnUsed 3, MinLen 128, MaxLen 128; Re-using: Free/Avail. Space
14664/504; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.08 sec.
INFO: Index pg_attribute_relid_attnam_index: Pages 21082; Tuples 7256:
Deleted 0.
CPU 0.83s/0.13u sec elapsed 59.32 sec.
INFO: Index pg_attribute_relid_attnum_index: Pages 5147; Tuples 7256: Deleted
0.
CPU 0.26s/0.03u sec elapsed 8.79 sec.
INFO: Rel pg_attribute: Pages: 119 --> 119; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_attribute

Thanks for your help ...

Regards,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902