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

Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Vincent van Leeuwen <pgsql(dot)spam(at)vinz(dot)nl>,pgsql-performance(at)postgresql(dot)org
Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning
Date: 2003-06-10 18:28:00
Message-ID: 200306101128.00609.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Vincent,

> This server isn't running postgres that long, and we're still trying to 
figure
> out the best configuration parameters for the highest possible performance.
> Shared_buffers was one of the first things we looked at. We've tested with
> shared_buffers at 1024, 8192, 32768 and 131072. So far, performance with
> shared_buffers set at 32768 was the best we could attain. 8192 and 131072 
came
> out roughly equal. 1024 was miserable.

Cool!   This is the first report we've had of a successful higher setting for 
shared_buffers.  I'll need to revise the text.  What do people think of:

SHARED_BUFFERS
Sets the size of Postgres' memory buffer where queries are held before being
fed into the Kernel buffer of the host system.  It's very important to
remember that this is only a holding area, and not the total memory available
for the server.  As such, resist the urge to set this number to a large
portion of your RAM, as this will actually degrade performance on many OSes.
Members of the pgsql-performance mailing list have mostly found useful values
in the range of 1000-6000, depending on available RAM, database size, and
number of concurrent queries.
This can go up slightly for servers with a great deal of RAM; the useful
maximum on Linux seems to be 6% to 10% of available RAM, with performance
degrading at higher settings.  Information on other OSes is not yet posted.
On multi-purpose servers, of course, the setting should be lowered. 

> Also, there was a very strong relation between the shared_buffers setting 
and
> the amount of cpu time spent in kernelland. Currently, the server spends
> roughly 20% of it's time in kernelspace (according to vmstat). When
> shared_buffers was 8192, this went up to about 30%.

This makes perfect sense ... less shared_buffers = more kernel_buffers, and 
vice-versa.

> Currently, we have the following settings:
> shared_buffers = 32768
> max_fsm_relations = 100

You might wanna increase this; current recommended is 300 just to make sure 
that you have one for every table.

> Just to be absolutely sure: all *_cost parameters only influence the chosen
> plan, right? There is absolutely nothing else influenced which doesn't show 
up
> in an EXPLAIN ANALYZE, right?

Yes, AFAIK.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2003-06-10 19:05:11
Subject: Re: Re-ordering .CONF params ... questions for this list
Previous:From: Vincent van LeeuwenDate: 2003-06-10 18:12:48
Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning

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