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 B6C9AD1B8C0 for ; Fri, 23 Apr 2004 15:56:10 -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 27570-10 for ; Fri, 23 Apr 2004 15:56:09 -0300 (ADT) Received: from silmaril.syscor.priv (h24-108-208-253.sbm.shawcable.net [24.108.208.253]) by svr1.postgresql.org (Postfix) with ESMTP id 189DCD1C50D for ; Fri, 23 Apr 2004 15:56:06 -0300 (ADT) Received: from syscor.com (strider.syscor.priv [192.168.1.3]) by silmaril.syscor.priv (8.12.8/8.12.8) with ESMTP id i3NIu8XR014585; Fri, 23 Apr 2004 11:56:09 -0700 Message-ID: <40896713.2010703@syscor.com> Date: Fri, 23 Apr 2004 11:57:23 -0700 From: Ron St-Pierre User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: revoohc@sermonaudio.com, pgsql-performance@postgresql.org Subject: Re: Help with performance problems References: <200404230931.17850.revoohc@sermonaudio.com> <4089561B.8010001@speedfc.com> <200404231353.17234.revoohc@sermonaudio.com> <200404231115.23198.josh@agliodbs.com> In-Reply-To: <200404231115.23198.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/414 X-Sequence-Number: 6714 Josh Berkus wrote: >Chris, > > > >>Sorry for the confusion here. I can't run any sort of vacuum durin the day >>due to performance hits. However, I have run vacuums at night. Several >>nights a week I run a vacuumdb -f -z on all of the clusters. I can take >>serveral hours to complete, but it does complete. >> >> > >Well, here's your first problem: since your FSM pages is low, and you're only >vacuuming once a day, you've got to have some serious table and index bloat. >SO you're going to need to do VACUUM FULL on all of your databases, and then >REINDEX on all of your indexes. > >After that, raise your max_fsm_pages to something useful, like 1,000,000. Of >course, data on your real rate of updates would help more. > >If you're getting severe disk choke when you vacuum, you probably are I/O >bound. You may want to try something which allows you to vacuum one table >at a time, either pg_autovacuum or a custom script. > > > Tom and Josh recently gave me some help about setting the fsm settings which was quite useful. The full message is at http://archives.postgresql.org/pgsql-performance/2004-04/msg00229.php and the 'most interesting' posrtion was: 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. Good luck Ron