X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 889FDD6E77 for ; Thu, 25 Aug 2005 16:28:08 -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 74262-02 for ; Thu, 25 Aug 2005 19:28:04 +0000 (GMT) Received: from mail.tecarta.com (66.238.115.135.ptr.us.xo.net [66.238.115.135]) by svr1.postgresql.org (Postfix) with ESMTP id F0A02D6E67 for ; Thu, 25 Aug 2005 16:28:03 -0300 (ADT) Received: from mail pickup service by mail.tecarta.com with Microsoft SMTPSVC; Thu, 25 Aug 2005 12:31:54 -0700 Received: from mail.tecarta.com ([192.168.160.2]) by mail.tecarta.com with Microsoft SMTPSVC(6.0.3790.0); Thu, 25 Aug 2005 12:31:48 -0700 Received: from barracuda.tecarta.com ([192.168.160.200]) by mail.tecarta.com (SAVSMTP 3.1.0.29) with SMTP id M2005082512314710024 for ; Thu, 25 Aug 2005 12:31:47 -0700 X-ASG-Debug-ID: 1124998078-3388-3-0 X-Barracuda-URL: http://192.168.160.200:8000/cgi-bin/mark.cgi Received: from mail1 (mail1.hq.corp [192.168.160.5]) by barracuda.tecarta.com (Spam Firewall) with SMTP id 1BAB120206D1 for ; Thu, 25 Aug 2005 12:27:58 -0700 (PDT) Received: from [10.0.0.250] ([63.193.127.22]) by mail.tecarta.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 25 Aug 2005 12:31:44 -0700 X-ASG-Orig-Subj: Re: [PERFORM] What *_mem to increase when running CLUSTER Subject: Re: What *_mem to increase when running CLUSTER From: Steve Poe To: andrew@pillette.com Cc: pgsql-performance@postgresql.org In-Reply-To: <430E1AF6.9030803@pillette.com> References: <6EE64EF3AB31D5448D0007DD34EEB3417DD1D2@Herge.rcsinc.local> <430E1AF6.9030803@pillette.com> Content-Type: text/plain Date: Thu, 25 Aug 2005 13:56:11 +0000 Message-Id: <1124978171.11102.12.camel@amd64-laptop-spoe> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 25 Aug 2005 19:31:44.0159 (UTC) FILETIME=[A02AD2F0:01C5A9AB] X-Virus-Scanned: by Barracuda Spam Firewall at tecarta.com X-Barracuda-Spam-Score: 2.00 X-Barracuda-Spam-Status: No, SCORE=2.00 using global scores of TAG_LEVEL=4.0 QUARANTINE_LEVEL=1000.0 KILL_LEVEL=7.0 tests=BAYES_80 X-Barracuda-Spam-Report: Code version 3.02, rules version 3.0.3477 Rule breakdown below pts rule name description ---- ---------------------- -------------------------------------------------- 2.00 BAYES_80 BODY: Bayesian spam probability is 80 to 95% [score: 0.9434] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 required=5 tests=[FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/421 X-Sequence-Number: 14168 Andrew, On Thu, 2005-08-25 at 12:24 -0700, Andrew Lazarus wrote: > Should I temporarily increase sort_mem, vacuum_mem, neither, or both > when doing a CLUSTER on a large (100 million row) table where as many as > half of the tuples are deadwood from UPDATEs or DELETEs? I have large > batch (10 million row) inserts, updates, and deletes so I'm not sure > frequent vacuuming would help. You may need to experiment with both. What version of Postgres? What is the size of your database? How many concurrent users? If you're seeing half of the tuples are dead, I look at checking your max_fsm_pages and max_fsm_relations after a full vacuum analyze before doing too much with sort mem. Your mileage may vary. Best of luck. Steve Poe > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings