The Postgres server (named postmaster) works with shared memory. The total amount of available shared memory is limited by the OS (variable SHMMAX). The default limit is very low on Linux (e.g. 33Mb). If your Postgres installation accepts for instance 10 connections, each connection will have less than 3Mb to do its Job. These kind of values are relics of very old hardware. This limit must be pushed ahead to give posmaster room enough to work.

• Increase SHMMAX (increased to 500Mb in this example) No reboot needed :
  [root@saada init.d]# more /proc/sys/kernel/shmmax
33554432
[root@saada init.d]# sysctl -w kernel.shmmax=500000000
kernel.shmmax = 500000000
[root@saada init.d]# sysctl -p /etc/sysctl.conf
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
[root@saada init.d]# more /proc/sys/kernel/shmmax
500000000

• • SHMMAX is limited to 4Gb on 32bits systems
  • You can see the current use of the shared memory wuth the command ipcs

• Configure the postmaster : The amount of shared memory available per connection obeys the following rule : max_connections*shared_buffers < kernel.shmmax Don’t forget that other processes also need shared memory (e.g. XOrg)
  • Edit PG_HOME/postgresql.conf and set both max_connections and shared_buffers as higher as possible but conforming with the precedent rule.
  • Restart the service

 Some tips :

• Do not hesitate to take the highest SHMAMX possible, especially on a server machine.
• Remember that queries will be faster with an high shared_buffers value. You will need less connections in parallel therefore.
• If your service runs a few hits, take a lower max_connections and an higher shared_buffers
• If your service runs a lot of simple queries (e.g SaadaDB browsing), take a higher max_connections and an lower shared_buffers
• If your service runs a few heavy queries, take a low ermax_connections and an higher shared_buffers.
• If your service runs a lot of heavy queries, change your hardware.