Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8B4C6650EA9 for ; Tue, 1 Jul 2008 22:24:55 -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 35182-04 for ; Tue, 1 Jul 2008 22:24:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.157]) by postgresql.org (Postfix) with ESMTP id 61E10650EA7 for ; Tue, 1 Jul 2008 22:24:51 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so70901fgg.23 for ; Tue, 01 Jul 2008 18:24:49 -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:references; bh=G+61YlOH9LSiTivcMlQbUefGVBPXrnD2LqKx3O745HU=; b=JNISBraQD12+d/uilQQsywwB5eaSIPcom06quPKnB4y1BPdzAMHt8pY+5BwSYmRlY1 sbLuulpIG/uqP1u/wXbrChMKc0ebSez7MtYW4qECUHrZv7uXDoJigO+G+yD6nICDRo0y zIAqoXfJXhEjVG8FyhinIoa59ckAHzM2e8MMU= 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:references; b=YgNpNed0UE8iwXM4v5fa6AABRkdveOvg0nV8yItj3hywPVzudgAl1QnC02+p5VfT5e SnT6JTC6PM/uPH4x4MaAgMa/tsmkHHmwRzZCmTL95qUH9E9gmREhmD4NUgfqdh+LbDrX 5arrul+Yidpj2Suv/yfQOd6EaS2YA0Pl2WypU= Received: by 10.86.80.5 with SMTP id d5mr8379758fgb.26.1214961889523; Tue, 01 Jul 2008 18:24:49 -0700 (PDT) Received: by 10.86.71.17 with HTTP; Tue, 1 Jul 2008 18:24:49 -0700 (PDT) Message-ID: <3a0028490807011824h3da9d7begeb8a8d31f3fe7366@mail.gmail.com> Date: Tue, 1 Jul 2008 22:24:49 -0300 From: "Rafael Domiciano" To: "Kevin Neufeld" Subject: Re: Configuring Shared Buffers Cc: pgsql-admin@postgresql.org In-Reply-To: <486999BF.9030306@refractions.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_65_8038480.1214961889513" References: <3a0028490806301052y193a4aeayfcc6deafdeb1cdb2@mail.gmail.com> <20080630182830.GA5358@easy2.in-chemnitz.de> <3a0028490806301201m5e4c48d4qc51a880b8f80ecf5@mail.gmail.com> <20080701010927.GH18252@alvh.no-ip.org> <486999BF.9030306@refractions.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.08 tagged_above=0 required=5 tests=AWL=0.079, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/19 X-Sequence-Number: 29629 ------=_Part_65_8038480.1214961889513 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Yes, I was talking about 100 max connections... I am doing some benchmarks tests with diferent configurations... I'll try what you said Kevin. Thank you All 2008/6/30 Kevin Neufeld : > I agree. I have a similar system that I use for development purposes and > have the shared_buffers sitting comfortable around 1GB. On production > systems with 16GB of RAM, I've seen this as high as 12GB. There is talk > nowadays, however, that this setting could drop back down to defaults on > modern installations and let the OS handle cached memory as it sees fit. > In any case, the biggest performance gain I see for you would be setting > work_mem appropriately. This is the memory postgres is permitted to use = for > sorts, merges, hash joins, etc. before being forced to disk. It default= s > to 1MB. In my opinion, this is far too low. This is the memory allocate= d > to each sort/hash/etc operation. So for a complicated query, postgres co= uld > use several allocations. Even though, I think you could raise this > considerably. If your system is a dedicated postgres box, I would take t= he > total memory, subtract that needed for the OS, subtract what you decided = to > use for shared_buffers, and divide the rest by your 100 connections. So, > for you, I see this around 30MB. On my development box with only a few > connections, I have this around 500MB and sometime spike it to 1.2GB on t= he > fly before a long running query. > > Cheers, > Kevin > > Alvaro Herrera wrote: > >> Rafael Domiciano escribi=F3: >> >> >>> The Postgres version is 8.3.3 and I am using Fedora Core 8. >>> I have in the actual server around 70 connections the same time. I am >>> assigning for this 100. >>> >>> >> >> 100 MB? That's not very much either. You can probably get a hefty >> performance boost by upping it a lot more (depending on whether the >> machine is doing something else, or Postgres is the only service running >> on it.) >> >> >> >> > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > ------=_Part_65_8038480.1214961889513 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Yes, I was talking about 100 max connections...
I am doing some benchmar= ks tests with diferent configurations...
I'll try what you said Kevi= n.

Thank you All

2008/6/30 Kevin N= eufeld <kneufeld@refractions= .net>:
I agree.  I = have a similar system that I use for development purposes and have the shar= ed_buffers sitting comfortable around 1GB.  On production systems with= 16GB of RAM, I've seen this as high as 12GB.  There is talk nowad= ays, however, that this setting could drop back down to defaults on modern = installations and let the OS handle cached memory as it sees fit.
In any case, the biggest performance gain I see for you would be setting wo= rk_mem appropriately.  This is the memory postgres is permitted to use= for sorts, merges, hash joins, etc.  before being forced to disk. &nb= sp;It defaults to 1MB.  In my opinion, this is far too low.  This= is the memory allocated to each sort/hash/etc operation.  So for a co= mplicated query, postgres could use several allocations.  Even though,= I think you could raise this considerably.  If your system is a dedic= ated postgres box, I would take the total memory, subtract that needed for = the OS, subtract what you decided to use for shared_buffers, and divide the= rest by your 100 connections.  So, for you, I see this around 30MB. &= nbsp;On my development box with only a few connections, I have this around = 500MB and sometime spike it to 1.2GB on the fly before a long running query= .

Cheers,
Kevin


Alvaro Herrera wrote:
Rafael Domiciano escribi=F3:
 
The Postgres version is 8.3.3 and I am using Fedora Core 8.
I have in the actual server around 70 connections the same time. I am
assigning for this 100.
   

100 MB?  That's not very much either.  You can probably get a= hefty
performance boost by upping it a lot more (depending on whether the
machine is doing something else, or Postgres is the only service running on it.)


 

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

------=_Part_65_8038480.1214961889513--