From Luc.Jouneau@diamant.jouy.inra.fr Thu Sep 4 14:26:58 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id EEF19D1BA99 for ; Thu, 4 Sep 2003 14:26:58 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 39231-07 for ; Thu, 4 Sep 2003 11:26:52 -0300 (ADT) Received: from jais.jouy.inra.fr (jais.jouy.inra.fr [138.102.1.17]) by svr1.postgresql.org (Postfix) with ESMTP id 661D9D1BAA1 for ; Thu, 4 Sep 2003 11:26:47 -0300 (ADT) Received: from diamant.jouy.inra.fr (localhost [127.0.0.1]) by jais.jouy.inra.fr (8.10.1/8.10.1) with ESMTP id h84EQnV05864 for ; Thu, 4 Sep 2003 16:26:50 +0200 (MET DST) Received: from pc502jlo (pc502ljo [138.102.3.71]) by diamant.jouy.inra.fr (8.10.1/8.10.1) with SMTP id h84EQna15160 for ; Thu, 4 Sep 2003 16:26:49 +0200 (MET DST) Message-ID: <007c01c372f0$95b80b10$4703668a@pc502jlo> From: "Jouneau Luc" To: Subject: max_fsm_pages Date: Thu, 4 Sep 2003 16:26:46 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0079_01C37301.55CC4DC0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_50_60, RCVD_IN_OSIRUSOFT_COM X-Spam-Level: X-Archive-Number: 200309/74 X-Sequence-Number: 10162 This is a multi-part message in MIME format. ------=_NextPart_000_0079_01C37301.55CC4DC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi everybody, I'm trying to understand how vacuumdb and free space map work together for = retrieving free spaces after delete or update statements. max_fsm_pages and max_fsm_relations are set to default values (respectively= 10000 and 100). I do my tests on a table containing at the beginning 1931968 tuples stocked= in 11944 pages : INFO: Pages 11944: Changed 11944, Empty 0; Tup 1931968: Vac 0, Keep 0, UnU= sed 0 I delete 1931936 tuples (letting 32 tuples left). I thought the number of pages affected by this delete will overflow fsm sto= rage capacity, but when I run a vacuumdb verbose analyze on this table, I have the following log information : INFO: Removed 1397914 tuples in 8641 pages. CPU 0.76s/0.89u sec elapsed 25.72 sec. INFO: Removed 534022 tuples in 3303 pages. CPU 0.37s/0.29u sec elapsed 7.62 sec. INFO: Pages 11944: Changed 11944, Empty 0; Tup 32: Vac 1931936, Keep 0, Un= Used 0. Total CPU 1.57s/1.90u sec elapsed 42.01 sec. It seems that FSM traced all of my deletes since it mentions removes in 864= 1+3303=3D11944 pages (>10000 which was set for max_fsm_page). Can someone explain me, where I'm wrong ? My first aim was to know if max_fsm_pages was set for each table or for a w= hole database. Per table I suppose, but Is it a good supposition ? Where is stored (on disk) this fsm ? Thanks for your answers. Luc ------=_NextPart_000_0079_01C37301.55CC4DC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi everybody,
 
I'm trying to understand how vacuumdb and = free=20 space map work together for retrieving free spaces after delete or update= =20 statements.
max_fsm_pages and max_fsm_relations= =20 are set to default values (respectively 10000 and 100).
I do my tests on a table containing at the= =20 beginning 1931968 tuples stocked in 11944 pages :

INFO:  Pages 11944: Changed 11944, Empty= 0; Tup=20 1931968: Vac 0, Keep 0, UnUsed 0

I delete 1931936 tuples (letting 32 tuples= =20 left).
I thought the number of pages affecte= d by this=20 delete will overflow fsm storage capacity, but when I run a vacuumdb verbos= e=20 analyze on this table,
I have the following log information :

INFO:  Removed 1397914 tuples in 8641=20 pages.

        CPU=20 0.76s/0.89u sec elapsed 25.72 sec.

INFO:  Removed 534022 tuples in 3303=20 pages.

        CPU=20 0.37s/0.29u sec elapsed 7.62 sec.

INFO:  Pages 11944: Changed 11944, Empty= 0; Tup=20 32: Vac 1931936, Keep 0, UnUsed 0.

       =20 Total CPU 1.57s/1.90u sec elapsed 42.01 sec.

It seems that FSM traced all of my deletes= since it=20 mentions removes in 8641+3303=3D11944 pages (>10000 which was set for=20 max_fsm_page).
 
Can someone explain me, where I'm wrong=20 ?
 
My first aim was to know if max_fsm_pages was set for each table or for a whole= =20 database. Per table I suppose, but Is it a good supposition ?
Where is stored (on disk) this fsm ?
 
Thanks for your answers.
 
Luc
------=_NextPart_000_0079_01C37301.55CC4DC0-- From tgl@sss.pgh.pa.us Thu Sep 4 23:24:17 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id D60E6D1B8C6 for ; Thu, 4 Sep 2003 23:24:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 60788-05 for ; Thu, 4 Sep 2003 20:24:09 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id CFCECD1B8E9 for ; Thu, 4 Sep 2003 20:24:04 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h84NO5jX012062; Thu, 4 Sep 2003 19:24:05 -0400 (EDT) To: "Jouneau Luc" Cc: pgsql-admin@postgresql.org Subject: Re: max_fsm_pages In-reply-to: <007c01c372f0$95b80b10$4703668a@pc502jlo> References: <007c01c372f0$95b80b10$4703668a@pc502jlo> Comments: In-reply-to "Jouneau Luc" message dated "Thu, 04 Sep 2003 16:26:46 +0200" Date: Thu, 04 Sep 2003 19:24:04 -0400 Message-ID: <12061.1062717844@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/84 X-Sequence-Number: 10172 "Jouneau Luc" writes: > INFO: Pages 11944: Changed 11944, Empty 0; Tup 32: Vac 1931936, Keep 0, Un= > Used 0. > Total CPU 1.57s/1.90u sec elapsed 42.01 sec. > It seems that FSM traced all of my deletes since it mentions removes in 864= > 1+3303=3D11944 pages (>10000 which was set for max_fsm_page). The numbers output by VACUUM don't tell you anything about how many of those pages will actually be remembered in the FSM. In practice, max_fsm_relations=100 is too small; we've bumped the default to 1000 for 7.4. > My first aim was to know if max_fsm_pages was set for each table or for a w= > hole database. Whole database. That default setting is very small, too (enough for a database of maybe a couple hundred meg, at most). regards, tom lane From gustavo-bartz@dextra.com.br Tue Jun 14 18:36:37 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0C14852874 for ; Tue, 14 Jun 2005 15:36:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31262-05 for ; Tue, 14 Jun 2005 18:36:27 +0000 (GMT) Received: from hm03.locaweb.com.br (hm03.locaweb.com.br [200.234.203.3]) by svr1.postgresql.org (Postfix) with SMTP id 2A06352871 for ; Tue, 14 Jun 2005 15:36:25 -0300 (ADT) Received: (qmail 6792 invoked by uid 0); 14 Jun 2005 18:36:23 -0000 Received: from unknown (10.1.10.28) by hm03.locaweb.com.br with QMTP; 14 Jun 2005 18:36:23 -0000 Received: (qmail 26215 invoked from network); 14 Jun 2005 18:36:27 -0000 Received: from unknown (HELO ?172.16.129.133?) (gustavo@dextra.com.br@200.171.95.238) by hm28.locaweb.com.br with SMTP; 14 Jun 2005 18:36:27 -0000 Message-ID: <42AF23AA.8010206@dextra.com.br> Date: Tue, 14 Jun 2005 15:36:26 -0300 From: Dextra - Gustavo Bartz Guedes User-Agent: Debian Thunderbird 1.0.2 (X11/20050331) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: max_fsm_pages Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200506/92 X-Sequence-Number: 17847 Is the parameter max_fsm_pages cluster-wide or per-database? Thanks. From pgman@candle.pha.pa.us Tue Jun 14 18:45:22 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1DD6A52874 for ; Tue, 14 Jun 2005 15:45:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31901-04 for ; Tue, 14 Jun 2005 18:45:12 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126]) by svr1.postgresql.org (Postfix) with ESMTP id 786F9528C5 for ; Tue, 14 Jun 2005 15:45:08 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id j5EIj7t18996; Tue, 14 Jun 2005 14:45:07 -0400 (EDT) From: Bruce Momjian Message-Id: <200506141845.j5EIj7t18996@candle.pha.pa.us> Subject: Re: max_fsm_pages In-Reply-To: <42AF23AA.8010206@dextra.com.br> To: Dextra - Gustavo Bartz Guedes Date: Tue, 14 Jun 2005 14:45:07 -0400 (EDT) Cc: pgsql-admin@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/93 X-Sequence-Number: 17848 Dextra - Gustavo Bartz Guedes wrote: > Is the parameter max_fsm_pages cluster-wide or per-database? cluster-wide. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 From abumushayeed@yahoo.com Sun Apr 1 13:59:42 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 143E29FB201 for ; Sun, 1 Apr 2007 10:59:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 19240-06 for ; Sun, 1 Apr 2007 10:59:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web57102.mail.re3.yahoo.com (web57102.mail.re3.yahoo.com [216.252.111.115]) by postgresql.org (Postfix) with SMTP id 0F74A9FA170 for ; Sun, 1 Apr 2007 10:59:37 -0300 (ADT) Received: (qmail 89157 invoked by uid 60001); 1 Apr 2007 13:59:36 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=amThOe9CoCEXIfTBHbgrQ9Pz3bf3uDzp9HTRLceA+WFSQvMY1rLRzZnYL7jcUdr6vQ3iYxckp9n4j22+MIfz/qWWh19Taft+hFSredfQYigRmY0PVMKVSHmIJEPiC0zwg3V0wJLPtzasnN9abh0GID4TZeuxTGcsB0D1Fw1vYsg=; X-YMail-OSG: U8ySStIVM1nb69xw9GaTQzr5DQIoW2I27mSbdOb9zpKRGcUumniLaL14UumidYM5Rb53WPfx7PeQLGVxP27DFNBa56mppQSKJCNL Received: from [67.121.145.24] by web57102.mail.re3.yahoo.com via HTTP; Sun, 01 Apr 2007 06:59:36 PDT Date: Sun, 1 Apr 2007 06:59:36 -0700 (PDT) From: Abu Mushayeed Subject: max_fsm_pages To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1043029557-1175435976=:89112" Content-Transfer-Encoding: 8bit Message-ID: <718991.89112.qm@web57102.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=4.379 tagged_above=0 required=5 tests=AWL=1.389, BAYES_20=-0.74, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_30_40=0.374, HTML_MESSAGE=0.001 X-Spam-Level: **** X-Archive-Number: 200704/1 X-Sequence-Number: 25065 --0-1043029557-1175435976=:89112 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit PG DB: 8.1.3 12 DB in the cluster When I do a full vacuum of the database cluster, at the end I am getting the message as follows: psql:dba_task.sql:1: INFO: free space map contains 8033993 pages in 1023 relations DETAIL: A total of 8000000 page slots are in use (including overhead). 8201952 page slots are required to track all free space. Current limits are: 8000000 page slots, 32768 relations, using 49054 KB. psql:dba_task.sql:1: NOTICE: number of page slots needed (8201952) exceeds max_fsm_pages (8000000) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 8201952. My questions are: 1. With out increasing the max_fsm_pages, can I bring down the need for fsm_pages by deleting unnecessary tables? 2. As, the database grows the need for max_fsm_pages grows too, what is the best practice? 3. As a new postgres DBA, how should one manage this parameter? Thanks Abu Mushayeed --------------------------------- It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. --0-1043029557-1175435976=:89112 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
PG DB: 8.1.3
12 DB in the cluster
 
When I do a full vacuum of the database cluster, at the end I am getting the message as follows:
 
psql:dba_task.sql:1: INFO:  free space map contains 8033993 pages in 1023 relations
DETAIL:  A total of 8000000 page slots are in use (including overhead).
8201952 page slots are required to track all free space.
Current limits are:  8000000 page slots, 32768 relations, using 49054 KB.
psql:dba_task.sql:1: NOTICE:  number of page slots needed (8201952) exceeds max_fsm_pages (8000000)
HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a value over 8201952.
 
My questions are:
 
1. With out increasing the max_fsm_pages, can I bring down the need for fsm_pages by deleting unnecessary tables?
2. As, the database grows the need for max_fsm_pages grows too, what is the best practice?
3. As a new postgres DBA, how should one manage this parameter?
 
Thanks
Abu Mushayeed


It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar. --0-1043029557-1175435976=:89112-- From shoaibmir@gmail.com Mon Apr 2 09:55:51 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 9CB669FB416 for ; Mon, 2 Apr 2007 06:55:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 98043-04 for ; Mon, 2 Apr 2007 06:55:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.246]) by postgresql.org (Postfix) with ESMTP id 075D29FB2AB for ; Mon, 2 Apr 2007 06:55:45 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1206272ana for ; Mon, 02 Apr 2007 02:55:45 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=VBjKnZqC2+uh9XCEqjMJFNNLghFsxZ5kFxKwvjg/vrZsDK6onSwUVfSD1wukPfo9Usbt87AbydTvIhyX+v1DbpT43YKHj+GlcOnVfSGhguXfV1/WdQAjwKn1dA/R57wt5Rs7kr+vXvOTz7Wcgayxsn0E2OgXc/h1KwU8JV8V/hY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=f8r7w4gDcHTE9acoyZnZbwRjvqnJAqKx/PQKqZ09VyxGgUltzwVFRRLZe6tneI+2qn4fr/o5l95FfNRWwBodPIEq5DvvjGXCz1xc6kdqL5Gua3BA5ZqBkcuLv765RkLCYudba5QXLANe2TLejFyzhQgl5ZOThF10cfLQ7kkuvpQ= Received: by 10.100.9.19 with SMTP id 19mr3340590ani.1175507744878; Mon, 02 Apr 2007 02:55:44 -0700 (PDT) Received: by 10.100.14.1 with HTTP; Mon, 2 Apr 2007 02:55:44 -0700 (PDT) Message-ID: Date: Mon, 2 Apr 2007 14:55:44 +0500 From: "Shoaib Mir" To: "Abu Mushayeed" Subject: Re: max_fsm_pages Cc: pgsql-admin@postgresql.org In-Reply-To: <718991.89112.qm@web57102.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_83591_10133651.1175507744638" References: <718991.89112.qm@web57102.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/8 X-Sequence-Number: 25072 ------=_Part_83591_10133651.1175507744638 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline First of all run the VACUUM FULL ANALYZE and that should hopefully help you get rid of this problem without changing the max_fsm_pages. Other then this I will recommend you to have the autovacuuming process in place for proper database maintenance. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 4/1/07, Abu Mushayeed wrote: > > PG DB: 8.1.3 > 12 DB in the cluster > > When I do a full vacuum of the database cluster, at the end I am getting > the message as follows: > > psql:dba_task.sql:1: INFO: free space map contains 8033993 pages in 1023 > relations > DETAIL: A total of 8000000 page slots are in use (including overhead). > 8201952 page slots are required to track all free space. > Current limits are: 8000000 page slots, 32768 relations, using 49054 KB. > psql:dba_task.sql:1: NOTICE: number of page slots needed (8201952) > exceeds max_fsm_pages (8000000) > HINT: Consider increasing the configuration parameter "max_fsm_pages" to > a value over 8201952. > > My questions are: > > 1. With out increasing the max_fsm_pages, can I bring down the need for > fsm_pages by deleting unnecessary tables? > 2. As, the database grows the need for max_fsm_pages grows too, what is > the best practice? > 3. As a new postgres DBA, how should one manage this parameter? > > Thanks > Abu Mushayeed > > ------------------------------ > It's here! Your new message! > Get new email alertswith the free Yahoo! > Toolbar. > > ------=_Part_83591_10133651.1175507744638 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline First of all run the VACUUM FULL ANALYZE and that should hopefully help you get rid of this problem without changing the max_fsm_pages. Other then this I will recommend you to have the autovacuuming process in place for proper database maintenance.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 4/1/07, Abu Mushayeed < abumushayeed@yahoo.com> wrote:
PG DB: 8.1.3
12 DB in the cluster
 
When I do a full vacuum of the database cluster, at the end I am getting the message as follows:
 
psql:dba_task.sql:1: INFO:  free space map contains 8033993 pages in 1023 relations
DETAIL:  A total of 8000000 page slots are in use (including overhead).
8201952 page slots are required to track all free space.
Current limits are:  8000000 page slots, 32768 relations, using 49054 KB.
psql:dba_task.sql:1: NOTICE:  number of page slots needed (8201952) exceeds max_fsm_pages (8000000)
HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a value over 8201952.
 
My questions are:
 
1. With out increasing the max_fsm_pages, can I bring down the need for fsm_pages by deleting unnecessary tables?
2. As, the database grows the need for max_fsm_pages grows too, what is the best practice?
3. As a new postgres DBA, how should one manage this parameter?
 
Thanks
Abu Mushayeed


It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.


------=_Part_83591_10133651.1175507744638--