Skip site navigation (1) Skip section navigation (2)

Re: Configuring Shared Buffers

From: Kevin Neufeld <kneufeld(at)refractions(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Configuring Shared Buffers
Date: 2008-07-01 02:43:11
Message-ID: 486999BF.9030306@refractions.net (view raw or flat)
Thread:
Lists: pgsql-admin
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 
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 complicated 
query, postgres could use several allocations.  Even though, I think you 
could raise this considerably.  If your system is a dedicated 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.  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ó:
>   
>> 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.)
>
>
>   

In response to

Responses

pgsql-admin by date

Next:From: ria ulfaDate: 2008-07-01 07:28:29
Subject: tanya ttg postgre di linux
Previous:From: Alvaro HerreraDate: 2008-07-01 02:38:46
Subject: Re: Configuring Shared Buffers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group