X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2B0759DC83E for ; Fri, 23 Dec 2005 02:00:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49081-01 for ; Fri, 23 Dec 2005 02:00:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id A65B49DC84E for ; Fri, 23 Dec 2005 02:00:42 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBN60duY016596; Fri, 23 Dec 2005 01:00:39 -0500 (EST) To: "Dan Austin" cc: pgsql-admin@postgresql.org Subject: Re: Pgstat.tmp file activity In-reply-to: References: Comments: In-reply-to "Dan Austin" message dated "Fri, 23 Dec 2005 16:50:32 +1100" Date: Fri, 23 Dec 2005 01:00:39 -0500 Message-ID: <16595.1135317639@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.038 required=5 tests=[AWL=0.038] X-Spam-Score: 0.038 X-Spam-Level: X-Archive-Number: 200512/293 X-Sequence-Number: 20013 "Dan Austin" writes: > One of Sun's kernel engineers has come up with a solution that seems to > have fixed the problem. Instead of increasing work_mem (which we still > might do) we've mounted a swapfs partition over the pg temp directory: > # mount | grep pgsql_tmp > /export/data/pgsql/data/base/64920741/pgsql_tmp on swap [...] > This has actually reduced the disk activity considerably. So far, the > blocking problem seems to have gone. OK, so the stats collector is off the hook: it's not writing anything in that directory. I'd say you have a pretty conventional query-optimization problem here after all --- you want to cut the traffic into those temp files. You ought to be looking at EXPLAIN ANALYZE info for your common queries. regards, tom lane