Luki Rustianto wrote:
> Ok I see. So what's the best way to find optimum value for
> various memory-related setting of postgresql ?
How much memory is there in the machine?
Are there other programs running or is the machine dedicated to the database?
Are the queries you run complicated (order / hash large amounts of data) or simple?
Maybe a rough guideline would be:
Set work_mem to the amount of memory a query will regularly need for sorting and such,
set max_connections to the maximum number of connections you need.
Then figure out how much memory you want to dedicate to the database,
subtract work_mem * max_connections from that value and set
shared_buffers to the result value.
Make sure you adjust the kernel parameters accordingly, see
http://www.postgresql.org/docs/current/static/kernel-resources.html
Tom Lane suggested in
http://archives.postgresql.org/pgsql-general/2004-02/msg00471.php
that it might be better to set shared_buffers "relatively
small" and let the filesystem cache do the buffering, so that's
another way you can go. His advice is usually good.
Yours,
Laurenz Albe
