Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 900742E3ED2 for ; Thu, 24 Jan 2008 14:43:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 06559-02 for ; Thu, 24 Jan 2008 14:43:07 -0400 (AST) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 116922E3EDC for ; Thu, 24 Jan 2008 14:01:34 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id f5so42174nfh.23 for ; Thu, 24 Jan 2008 10:01:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=eqXCG3z9kOgdbbbiPKVAVRK8bH82aWBisTFuJB7YYd8=; b=bLrI3A/JD1pGjk8/uaEAt3UzO5VBcDhF4VpB7vxcz0ctmmLMGI0PIRpMr6B6SaSH+MpdJboIAkx0SaN530MVPtACp8TdIMBdgwHZu0/gDZLKRjLh4BVbADKijZK7FiqAhHFNma/8Y4KFQTsLTgznVVKygZ6mlZj+VWcDnITaxdY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Ao7WB4gCaom3+ri+YkA4VVbgIvDf8M0/ZZV+P7syZp/0IMrMKofiEWcj/DgZh4g2+qiel5FC7/a9dcj/PRo5U1NuhlqiOZiCI7uc/aQx/fXWGaV3/MEQa5H6XHzK6HKIp/YC2Xf+ZqPl7kWzjRtpZCu9iL2j9Jh4a5HWHYU3t+w= Received: by 10.78.147.6 with SMTP id u6mr1360063hud.59.1201197693311; Thu, 24 Jan 2008 10:01:33 -0800 (PST) Received: by 10.78.18.2 with HTTP; Thu, 24 Jan 2008 10:01:33 -0800 (PST) Message-ID: Date: Thu, 24 Jan 2008 13:01:33 -0500 From: "Merlin Moncure" To: "Joshua Fielek" Subject: Re: Making the most of memory? Cc: pgsql-performance@postgresql.org In-Reply-To: <4796BE84.9030803@centriccrm.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4796BE84.9030803@centriccrm.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/219 X-Sequence-Number: 28458 On Jan 22, 2008 11:11 PM, Joshua Fielek wrote: > The database itself is not that large -- a db_dump of the sql file as > text is only about 110MB. I haven't checked the exact size of the actual > data base, but the entire data directory is smaller than the available > memory at about 385MB including logs and config files. This is a single > database with a relatively small number of client connections (50 or so) > making a fair number of smaller queries. This is not a massive data > effort by any means at this time, but it will be growing. > > We have available currently ~4GB (8GB total) for Postgres. We will be > moving to a server that will have about 24GB (32GB total) available for > the database, with the current server becoming a hot backup, probably > with slony or something similar to keep the databases in sync. The database is cached in RAM. As soon as the database files are read for the first time, they will stay cached in the o/s basically forever (in either o/s file cache or postgresql buffer cache) as long as there are no other demands on memory...not likely in your case. This also means extra ram is not likely to help performance much if at all. I'll give you a little hint about postgresql.conf...tuning shared buffers rarely has a huge impact on performance...the o/s will possible issues you might be having: *) sync issues: asking drives to sync more often they can handle. possible solutions...faster/more drives or ask database to sync less (fsync off, or better transaction management) *) cpu bound issues: poorly designed queries, or poorly designed tables, bad/no indexes, etc *) unrealistic expectations of database performance *) not maintaining database properly, vacuum, etc *) mvcc issues maybe post your transaction load, and/or some slow queries you are dealing with. merlin