I have postgres server that is limited on 100 max_connections, and in some moment when is application is in heavy use that limit is reached, so i try to increase that number and when i try to restart postgres, wont start, and in log i got message that can't start because of some of System V IPC parameters(Ubuntu linux). So question is next, is it better to try to change this parameter or it is better to use some of postgres plugin like pgbouncer(i think i read somewhere that pgbouncer can resolve this problem) or etc... any advice about changing System V IPC parameters or resolving with some plugin.
1 Answer
In general, it's not better to bump max_connection too much. More connections cost more system resources.
Note: Increasing max_connections costs ~400 bytes of shared memory per connection slot, plus lock space (see max_locks_per_transaction).
Please take a look at the relevant wiki article: http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
About the problem of System V IPC, I have no idea. I set max_connections=2000(Ubuntu linux, PostgreSQL 9.3), it can restart.
Update:
This article introduces some about Shared Memory and Semaphores and the methods to set them for some platforms.
http://www.postgresql.org/docs/devel/static/kernel-resources.html#SYSVIPC
2 Comments
Craig Ringer
It's different for older PostgreSQL versions - 9.2 and older used a lot more system V shared memory because they used it for
shared_buffers etc; 9.3 only uses a tiny SYSV segment and then uses posix shmem for the rest.
inferno
Thanks @CraigRinger, I found some articles about
shared memory and semaphores: 1) postgresql.org/docs/devel/static/kernel-resources.html#SYSVIPC 2) postgresql.org/docs/devel/static/… 3) 9.3 reduces System V shared memory requirements, postgresql.org/docs/devel/static/release-9-3.html