9.9. Tuning Postgres

9.9.1. Shared buffers

La dimensione degli sharedbuffers influenza notevolmente alcune operazioni di postgres. Si rimanda alla documentazione ufficiale per come configurare opportunamente il sistema:

http://www.postgresql.org/docs/9.1/static/kernel-resources.html

Estratto della documentazione:

The default maximum segment size is 32 MB, which is only adequate for very small PostgreSQL installations.
The default maximum total size is 2097152 pages. A page is almost always 4096 bytes except in unusual kernel
configurations with "huge pages" (use getconf PAGE_SIZE to verify). That makes a default limit of 8 GB,
which is often enough, but not always.

The shared memory size settings can be changed via the sysctl interface. For example, to allow 16 GB:

        $ sysctl -w kernel.shmmax=17179869184
        $ sysctl -w kernel.shmall=4194304

In addition these settings can be preserved between reboots in the file /etc/sysctl.conf.
Doing that is highly recommended.

Ancient distributions might not have the sysctl program, but equivalent changes can be made by manipulating
the /proc file system:

        $ echo 17179869184 >/proc/sys/kernel/shmmax
        $ echo 4194304 >/proc/sys/kernel/shmall

The remaining defaults are quite generously sized, and usually do not require changes.