X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E41BCD1D988 for ; Fri, 2 Jan 2004 16:52:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 49201-09 for ; Fri, 2 Jan 2004 12:52:03 -0400 (AST) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 0BB79D1D958 for ; Fri, 2 Jan 2004 12:51:54 -0400 (AST) Received: from mailer.elma.loc (unknown [213.41.14.138]) by www.postgresql.com (Postfix) with ESMTP id 3E3B9CF8A0D for ; Fri, 2 Jan 2004 12:47:58 -0400 (AST) Received: from mailer.elma.loc (localhost [127.0.0.1]) by localhost (Postfix) with ESMTP id 10D56EC018; Fri, 2 Jan 2004 16:18:47 +0100 (CET) Received: from zoot.elma.fr (herve.elma.fr [10.0.1.2]) by mailer.elma.loc (Postfix) with ESMTP id DBA3AEC00D; Fri, 2 Jan 2004 16:18:46 +0100 (CET) From: =?iso-8859-15?q?Herv=E9=20Piedvache?= Organization: Elma =?iso-8859-15?q?Ing=E9nierie?= Informatique To: Tom Lane Subject: Re: Why memory is not used ? Why vacuum so slow ? Date: Fri, 2 Jan 2004 16:18:28 +0100 User-Agent: KMail/1.5.4 Cc: pgsql-performance@postgresql.org References: <200401021042.57714.herve@elma.fr> <5630.1073054548@sss.pgh.pa.us> In-Reply-To: <5630.1073054548@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 8bit Content-Disposition: inline Message-Id: <200401021618.28300.herve@elma.fr> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/8 X-Sequence-Number: 5183 Hi Tom, Le Vendredi 2 Janvier 2004 15:42, Tom Lane a écrit : > =?iso-8859-15?q?Herv=E9=20Piedvache?= 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