Re: requested shared memory size overflows size_t

On 03/04/2014 11:59 AM, Yuri Levinsky wrote:
> Dear Developers,
> Please help with the following problem. I am running PostgreSQL 9.2.3 on SUN Solaris 9. This is 64 bit system with
32Gswap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable
toset shared buffer 5G, the maximum possible value is 4G. When I decrease the configuration parameters and start the
instancesuccessfully: some queries fails on "out of memory" error. I verified kernel parameters: they looks same as on
Solaris10 and big enough. The only one difference is: Solaris 9 PostgreSQL version, in opposite to Solaris 10 and
Linux, was compiled by me with default options.
 

Note that if a query fails with "out of memory", it does *not* mean that 
you should increase shared_buffers. On the contrary: the higher you set 
shared_buffers, the less memory there is left for other things.

> My kernel is:
> set semsys:seminfo_semmap=64
> set semsys:seminfo_semmni=4096
> set semsys:seminfo_semmns=4096
> set semsys:seminfo_semmnu=4096
> set semsys:seminfo_semume=64
> set semsys:seminfo_semmsl=500
> set shmsys:shminfo_shmmax=0xffffffffffff
> set shmsys:shminfo_shmmin=100
> set shmsys:shminfo_shmmni=4096
> set shmsys:shminfo_shmseg=100
>
> Config.
> shared_buffers = 3GB
> temp_buffers = 2GB
> work_mem = 1024MB

temp_buffers = 2GB seems very high. That settings is *per backend*, so 
if you have 10 backends that all use temporary tables, they will consume 
20GB altogether for temp buffers. work_mem works similarly, except that 
a single query can use many times work_mem even in a single backend, so 
you need to be even more conservative with that. 1GB seems very high for 
work_mem. Try resetting these back to the defaults, and see if that 
works for you. Increase them gradually, and only if you have a query 
where the higher value really helps.

- Heikki