Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 08A4D65174A for ; Sat, 12 Jul 2008 10:06:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 70565-07 for ; Sat, 12 Jul 2008 10:05:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id DA9F0650A95 for ; Sat, 12 Jul 2008 10:06:00 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d37so2819147pye.29 for ; Sat, 12 Jul 2008 06:05:59 -0700 (PDT) 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=cxPM6BBiyBGl4CqURgJEHdkSu25O7lLjJEd+iuCPpfg=; b=cjhdMDrn4Sfixt4/rrJR6If0AXxJEShcfn9j2oZMNdzpjGg6zGfeFM04fipVW+N9LX 8/Sdi8QNTCWM2R67k2jIzbjxEomcHDPXSXbZgRj5n/J93j1PFbxFQYT0l7Zqr3U5TwLI n0KgfLePcZUGhY/Kjb0qFn7fFwwvPXzTeEIH0= 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=wRzvxJi4Cdhbdy+fywLUsHq6pKbXU4diql8Dz0N/1F5ha0BM7yeXxhR4JneRsDw4KK 7lF2gSRvW3rrvlC9anaHFWis3Qzx80dHqDrD/z6SHJQx0siOaOLOS1CmD1NzXmim2OGh 5uGt4+ietdsFwtf/LqEZdJ0mVk8hvb3PyyfgE= Received: by 10.114.124.12 with SMTP id w12mr15479158wac.210.1215867959260; Sat, 12 Jul 2008 06:05:59 -0700 (PDT) Received: by 10.114.130.17 with HTTP; Sat, 12 Jul 2008 06:05:59 -0700 (PDT) Message-ID: Date: Sat, 12 Jul 2008 07:05:59 -0600 From: "Scott Marlowe" To: "Jessica Richard" Subject: Re: how to estimate shared_buffers... Cc: pgsql-performance@postgresql.org In-Reply-To: <93721.43246.qm@web56401.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <93721.43246.qm@web56401.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/117 X-Sequence-Number: 30717 On Sat, Jul 12, 2008 at 5:30 AM, Jessica Richard wrote: > On a running production machine, we have 900M configured on a 16G-memory > Linux host. The db size for all dbs combined is about 50G. There are many > transactions going on all the times (deletes, inserts, updates). We do not > have a testing environment that has the same setup and the same amount of > workload. I want to evaluate on the production host if this 900M is enough. > If not, we still have room to go up a little bit to speed up all Postgres > activities. I don't know enough about the SA side. I just would imagine, if > something like "top" command or other tools can measure how much total > memory Postgres is actually using (against the configured 900M shared > buffers), and if Postgres is using almost 900M all the time, I would take > this as an indication that the shared_buffers can go up for another 100M... > > What is the best way to tell how much memory Postgres (all Postgres related > things) is actually using? If you've got a 50G data set, then postgresql is most likely using whatever memory you give it for shared buffers. top should show that easily. I'd say start at 25% ~ 4G (this is a 64 bit machine, right?). That leaves plenty of memory for the OS to cache data, and for postgresql to allocate work_mem type stuff from.