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 49113D1CA83 for ; Mon, 8 Mar 2004 16:52:03 +0000 (GMT) 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 45655-07 for ; Mon, 8 Mar 2004 12:51:56 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 52869D1B906 for ; Mon, 8 Mar 2004 12:51:56 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i28Gpr1Y099639 for ; Mon, 8 Mar 2004 16:51:53 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i28GfbMK097146 for pgsql-performance@postgresql.org; Mon, 8 Mar 2004 16:41:37 GMT From: William Yu X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Scaling further up Date: Mon, 08 Mar 2004 08:40:28 -0800 Organization: Hub.Org Networking Services Lines: 20 Message-ID: References: <4BAFBB6B9CC46F41B2AD7D9F4BBAF785098045@vt-pe2550-001.vantage.vantage.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en In-Reply-To: <4BAFBB6B9CC46F41B2AD7D9F4BBAF785098045@vt-pe2550-001.vantage.vantage.com> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/102 X-Sequence-Number: 5958 Anjan Dave wrote: > Great response, Thanks. > > Regarding 12GB memory and 13G db, and almost no I/O, one thing I don't > understand is that even though the OS caches most of the memory and PG > can use it if it needs it, why would the system swap (not much, only > during peak times)? The SHMMAX is set to 512MB, shared_buffers is 150MB, > effective cache size is 2GB, sort mem is 2MB, rest is default values. It > also happens that a large query (reporting type) can hold up the other > queries, and the load averages shoot up during peak times. In regards to your system going to swap, the only item I see is sort_mem at 2MB. How many simultaneous transactions do you get? If you get hundreds or thousands like your first message stated, every select sort would take up 2MB of memory regardless of whether it needed it or not. That could cause your swap activity during peak traffic. The only other item to bump up is the effective cache size -- I'd set it to 12GB.