postgresql shared memory settings
Hi all!
We have at present the following parameters related to shared memory:
postgres
shared_buffers = 7GB
max_connections = 1 500
max_locks_per_transaction = 1 024
max_prepared_transactions = 0 (not set)
system
SHMALL = 2 097 152
SHMMAX = 17 670 512 640
SHMMNI = 4096
The amount of RAM is 24 693 176k
We need to increase max_connections to 3 000. When we tried to do this, we got an error
[1-1] FATAL: could not create shared memory segment: No space left on device
[2-1] DETAIL: Failed system call was shmget(key=5432001, size=8964661248, 03600)
[3-1] HINT: This error does *not* mean that you have run out of disk space.
It occurs either if all available shared memory IDs have been taken, in which
case you need to raise the SHMMNI parameter in your kernel, or because the
system's overall limit for shared memory has been reached. If you cannot
increase the shared memory limit, reduce PostgreSQL's shared memory request
(currently 8964661248 bytes), perhaps by reducing shared_buffers or
max_connections.
The PostgreSQL documentation contains more information about shared memory
configuration.
The hint proposes to increase SHMMNI kernel parameter, but I'm not sure about how much to add :) Also, I believe that all these parameters correlate somehow, so do we need to change any other parameters accordingly?
Thanks in advance,
Alexander
Answer
Increasing SHMMNI will not help, it's the second part of the hint that matters here.
Get your system's page size with the shell command getconf PAGE_SIZE.
Usually it's 4096. Multiply that by SHMALL.
In your case that should be 2097152 * 4096 = 8589934592, which is exactly 8Gb.
That's your current maximum shared memory, independently of SHMMNI.
PostgreSQL error message indicates that it needs a bit more than that.
Conclusion: increase SHMALL.