Forum OpenACS Q&A: Re: Problem

Just as follow up. Reading the reference given in the docs, I think that the settings that we are recommending are a bad idea. The reference given describes how more is not necessarily better - performance can drop off again if settings are too high.

I reckon that on a server with 1GB of RAM we should need no more than 8192KB for shared_buffers.

The setting recommended for sort_mem is I think way too high. The info below states clearly that this is per sort, so you could very quickly clog up the machine with sorting for a small number of users if set like this.

I think that 2048KB for sort_mem (per sort, per thread) should be adequate unless you are running a server with a very small number of users who run really huge sort intensive queries.

Also, it seems to me that the parameter that we really need to give some guidance on is the effective_cache setting.

Regards
Richard
________________________________________________________

Start at 4MB (512) for a workstation
Medium size data set and 256-512MB available RAM: 16-32MB (2048-4096)
Large dataset and lots of available RAM (1-4GB): 64-256MB (8192-32768)
PLEASE NOTE. PostgreSQL counts a lot on the OS to cache data files and hence does not bother with duplicating its file caching effort. The shared buffers parameter assumes that OS is going to cache a lot of files and hence it is generally very low compared with system RAM. Even for a dataset in excess of 20GB, a setting of 128MB may be too much, if you have only 1GB RAM and an aggressive-at-caching OS like Linux.

There is one way to decide what is best for you. Set a high value of this parameter and run the database for typical usage. Watch usage of shared memory using ipcs or similar tools. A recommended figure would be between 1.2 to 2 times peak shared memory usage.

2.2 Sort memory
This parameter sets maximum limit on memory that a database connection can use to perform sorts. If your queries have order-by or group-by clauses that require sorting large data set, increasing this parameter would help. But beware: this parameter is per sort, per connection. Think twice before setting this parameter too high on any database with many users. A recommended approach is to set this parameter per connection as and when required; that is, low for most simple queries and higher for large, complex queries and data dumps.

2.3 Effective Cache Size
This parameter allows PostgreSQL to make best possible use of RAM available on your server. It tells PostgreSQL the size of OS data cache. So that PostgreSQL can draw different execution plan based on that data.
Say there is 1.5GB RAM in your machine, shared buffers are set to 32MB and effective cache size is set to 800MB. So if a query needs 700MB of data set, PostgreSQL would estimate that all the data required should be available in memory and would opt for more aggressive plan in terms of optimization, involving heavier index usage and merge joins. But if effective cache is set to only 200MB, the query planner is liable to opt for the more I/O efficient sequential scan.

While setting this parameter size, leave room for other applications running on the server machine. The objective is to set this value at the highest amount of RAM which will be available to PostgreSQL all the time.