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 1C7D5D1D69B for ; Fri, 12 Mar 2004 23:02:06 +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 98728-04 for ; Fri, 12 Mar 2004 19:02:06 -0400 (AST) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 197CED1C9DB for ; Fri, 12 Mar 2004 19:02:02 -0400 (AST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id i2CMZbcM026483; Fri, 12 Mar 2004 17:35:39 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id i2CN1vl04639; Fri, 12 Mar 2004 18:01:57 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id FVMAXMKM; Fri, 12 Mar 2004 18:01:54 -0500 Subject: Re: Scaling further up From: Robert Treat To: William Yu Cc: pgsql-performance@postgresql.org In-Reply-To: References: <4BAFBB6B9CC46F41B2AD7D9F4BBAF785098045@vt-pe2550-001.vantage.vantage.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 12 Mar 2004 18:01:57 -0500 Message-Id: <1079132517.27322.22.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/165 X-Sequence-Number: 6021 On Mon, 2004-03-08 at 11:40, William Yu wrote: > 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. > Was surprised that no one corrected this bit of erroneous info (or at least I didn't see it) so thought I would for completeness. a basic explanation is that sort_mem controls how much memory a given query is allowed to use before spilling to disk, but it will not grab that much memory if it doesn't need it. See the docs for a more detailed explanation: http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-RESOURCE Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL