pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hervé Piedvache <herve@elma.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why memory is not used ? Why vacuum so slow ?
Date: Fri, 2 Jan 2004 16:18:28 +0100
Message-ID: <200401021618.28300.herve@elma.fr> (raw)
In-Reply-To: <5630.1073054548@sss.pgh.pa.us>
References: <200401021042.57714.herve@elma.fr>
	<5630.1073054548@sss.pgh.pa.us>

Hi Tom,

Le Vendredi 2 Janvier 2004 15:42, Tom Lane a écrit :
> =?iso-8859-15?q?Herv=E9=20Piedvache?= <herve@elma.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




view thread (4+ messages)  latest in thread

Message-ID: <200401021618.28300.herve@elma.fr>
Permalink:  ../200401021618.28300.herve@elma.fr/
Also on:    postgresql.org/message-id/200401021618.28300.herve@elma.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: herve@elma.fr, tgl@sss.pgh.pa.us
  Subject: Re: Why memory is not used ? Why vacuum so slow ?
  In-Reply-To: <200401021618.28300.herve@elma.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox