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 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to set shared buffer 5G, the maximum possible value is 4G. When I decrease the configuration parameters and start the instance successfully: some queries fails on "out of memory" error. I verified kernel parameters: they looks same as on Solaris 10 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