X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2E4B2D1D25B for ; Fri, 16 Apr 2004 17:05:33 -0300 (ADT) 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 63140-04 for ; Fri, 16 Apr 2004 17:05:27 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 83612D1C9C8 for ; Fri, 16 Apr 2004 17:05:28 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3GK5SlO002409; Fri, 16 Apr 2004 16:05:28 -0400 (EDT) To: josh@agliodbs.com Cc: Ron St-Pierre , pgsql-performance@postgresql.org Subject: Re: Index Problem? In-reply-to: <200404161210.47995.josh@agliodbs.com> References: <40800CC2.3050707@syscor.com> <200404161001.52989.josh@agliodbs.com> <40801E1B.4020401@syscor.com> <200404161210.47995.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Fri, 16 Apr 2004 12:10:47 -0700" Date: Fri, 16 Apr 2004 16:05:27 -0400 Message-ID: <2408.1082145927@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/224 X-Sequence-Number: 6524 Josh Berkus writes: > A better way to set this would be to run VACUUM VERBOSE ANALYZE right after > doing one of your update batches, and see how many dead pages are being > reclaimed, and then set max_fsm_pages to that # + 50% (or more). Actually, since he's running 7.4, there's an even better way. Do a "VACUUM VERBOSE" (full-database vacuum --- doesn't matter whether you ANALYZE or not). At the end of the very voluminous output, you'll see something like INFO: free space map: 240 relations, 490 pages stored; 4080 total pages needed DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory. Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to exactly cover the present freespace needs of my system. I concur with the suggestion to bump that up a good deal, of course, but that gives you a real number to start from. The DETAIL part of the message shows my current settings (which are the defaults) and what the FSM is costing me in shared memory space. If you have multiple active databases, the best approach to getting these numbers is to VACUUM in each one, adding VERBOSE when you do the last one. The FSM report is cluster-wide but you want to be sure the underlying info is up to date for all databases. >> Also the docs on Varlena show the max_fsm_pages >> default to be 10,000 but my default was 20,000, looks like that needs >> updating. > I don't think the default has been changed. Anyone? Yes, I kicked it up for 7.4 because FSM covers indexes too now. Both the 7.3 and 7.4 defaults are pretty arbitrary of course... regards, tom lane