On Jun 14, 2010, at 11:53 AM, Tom Wilcox wrote:
>
>
> max_connections=3
> effective_cache_size=15GB
> maintenance_work_mem=5GB
> shared_buffers=7000MB
> work_mem=5GB
>
maintenance_work_mem doesn't need to be so high, it certainly has no effect on your queries below. It would affect
vacuum,reindex, etc.
With fast disk like this (assuming your 700MB/sec above was not a typo) make sure you tune autovacuum up to be much
moreaggressive than the default (increase the allowable cost per sleep by at least 10x).
A big work_mem like above is OK if you know that no more than a couple sessions will be active at once. Worst case, a
singleconnection ... probably ... won't use more than 2x that ammount.
> For now, I will go with the config using 7000MB shared_buffers. Any
> suggestions on how I can further optimise this config for a single
> session, 64-bit install utilising ALL of 96GB RAM. I will spend the next
> week making the case for a native install of Linux, but first we need to
> be 100% sure that is the only way to get the most out of Postgres on
> this machine.
>
Getting the most from the RAM does *_NOT_* mean making Postgres use all the RAM. Postgres relies on the OS file cache
heavily. If there is a lot of free RAM for the OS to use to cache files, it will help the performance. Both Windows
andLinux aggressively cache file pages and do a good job at it.
