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

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