Re: requested shared memory size overflows size_t

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 more aggressive 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 single connection ... 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 and Linux aggressively cache file pages and do a good job at it.