X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 997D83A469A for ; Fri, 29 Oct 2004 15:29:57 +0100 (BST) 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 49078-05 for ; Fri, 29 Oct 2004 14:29:49 +0000 (GMT) Received: from sraigw.sra.co.jp (sraigw.sra.co.jp [202.32.10.2]) by svr1.postgresql.org (Postfix) with ESMTP id 010893A4BD8 for ; Fri, 29 Oct 2004 15:29:51 +0100 (BST) Received: from srascb.sra.co.jp (srascb [133.137.8.65]) by sraigw.sra.co.jp (Postfix) with ESMTP id B83F06278D; Fri, 29 Oct 2004 23:29:50 +0900 (JST) Received: from srascb.sra.co.jp (localhost [127.0.0.1]) by localhost.sra.co.jp (Postfix) with ESMTP id A120710CD06; Fri, 29 Oct 2004 23:29:50 +0900 (JST) Received: from sranhm.sra.co.jp (sranhm [133.137.44.16]) by srascb.sra.co.jp (Postfix) with ESMTP id 8695B10CD04; Fri, 29 Oct 2004 23:29:50 +0900 (JST) Received: from localhost (sraihb-hub.sra.co.jp [133.137.8.6]) by sranhm.sra.co.jp (8.9.3+3.2W/3.7W-srambox) with ESMTP id XAA23107; Fri, 29 Oct 2004 23:29:50 +0900 Date: Fri, 29 Oct 2004 23:31:51 +0900 (JST) Message-Id: <20041029.233151.78703659.t-ishii@sra.co.jp> To: pathat@comcast.net Cc: pgsql-performance@postgresql.org Subject: Re: determining max_fsm_pages From: Tatsuo Ishii In-Reply-To: <41824E05.8000903@comcast.net> References: <41824E05.8000903@comcast.net> X-Mailer: Mew version 2.3 on Emacs 20.7 / Mule 4.1 =?iso-2022-jp?B?KBskQjAqGyhCKQ==?= Mime-Version: 1.0 Content-Type: Text/Plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/469 X-Sequence-Number: 8945 > Pg: 7.4.5 > 8G ram > 200G RAID5 > > I have my fsm set as such: > max_fsm_pages = 300000 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 500 # min 100, ~50 bytes each > > > I just did a vacuum full on one table and saw this result: > INFO: analyzing "cdm.cdm_fed_agg_purch" > INFO: "cdm_fed_agg_purch": 667815 pages, 3000 rows sampled, 52089570 > estimated total rows > > > My question is this: I have about 8 databases running on this server. > When I do a vacuum full on each of these databases, there is a INFO > section that I assume is the total pages used for that database. Should > add ALL these individual pages together and pad the total and use this > as my new max_fsm_pages? Should I do the same thing with max_fsm_relations? I think that's too much and too big FSM affects performance in my opinion. The easiest way to calculate appropreate FSM size is doing vacuumdb -a -v and watching the message. At the very end, you would see something like: INFO: free space map: 13 relations, 1447 pages stored; 1808 total pages needed DETAIL: Allocated FSM size: 100 relations + 1600 pages = 19 kB shared memory. In this case 1808 is the minimum FSM size. Of course this number would change depending on the frequency of VACUUM. Therefore you need some room for the FSM size. -- Tatsuo Ishii