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

On 2003-06-10 08:46:21 -0700, Josh Berkus wrote:
> Rune,
>
> > > shared_buffers = 117248 (shmmax / 2 / 1024 / 8 ) This I got from this
> > > forum.
> > > Does this sound right or am I totally out of bounds here? I have, as said
>
> Out of bounds, through no fault of your own .... I'm still working on
> documentation for this. However, let me qoute the upcoming supplimentary
> docs:
>
> 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 found useful values in the
> range of 1000-6000, depending on available RAM, database size, and number of
> concurrent queries. No one has yet reported positive results for any number
> over 6000.
>

We run a dual P3 1GHz server, running Debian Linux (stable), kernel 2.4.20,
with a 5-disk (10K rpm) RAID 5 array (ICP Vortex controller) and 4GB RAM, most
of which is used for filesystem cache. This server runs Postgresql 7.3.2
exclusively, with a database of roughly 7GB. This database is used for a very
busy community website, running an enormous amount of small and simple
select/update/insert queries and a number of complex select queries, to search
through all kinds of data.

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.

(yay, 3 lines in a row starting with the word 'shared_buffers'! ;))

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%.

I don't have any hard performance statistics, we just threw the site live with
different settings and watched the load on all servers, and the amount of
requests/second our webservers could generate (the bottleneck is the
postgresql server, not the webservers).

I'm really eager for any useful tips regarding the various cost settings. I've
been following this list for months and read through a large portion of the
archives, but noone has been able to do more than handwaving around certain
numbers, which are close to the defaults anyway.

Currently, we have the following settings:
shared_buffers = 32768
max_fsm_relations = 100
max_fsm_pages = 100000
sort_mem = 16384
vacuum_mem = 131072
effective_cache_size = 327680
random_page_cost = 1.5
cpu_tuple_cost = 0.005
#cpu_index_tuple_cost = 0.001 (default)
#cpu_operator_cost = 0.0025 (default)

Halving the cpu_tuple_cost has given a very impressive performance boost
(performance roughly doubled). I'm not sure why, because the plans of the
large queries I was checking haven't changed as far as I can see, but maybe
some smaller queries I didn't bother to check are using a different plan now.
Although I was quite sure those smaller queries were all using the correct
indexes etc before the change anyway.

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?

Regards,

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/