Fixing memory issues

Some of the most important issues are related to the kernel and memory. Up to version 9.2, PostgreSQL was using the classical system V shared memory to cache data, store locks, and so on. Since PostgreSQL 9.3, things have changed, solving most issues people had been facing during installation.

However, in PostgreSQL 9.2 or before, you might have faced the following error message:

If you are facing a message like this, it means that the kernel does not provide you with enough shared memory to satisfy your needs. Where does this need for shared memory come from? Back in the old days, PostgreSQL stored a lot of stuff, such as the I/O cache (shared_buffers, locks, autovacuum-related information and a lot more), in the shared memory. Traditionally, most Linux distributions have had a tight grip on the memory, and they don't issue large shared memory segments; for example, Red Hat has long limited the maximum amount of shared memory available to applications to 32 MB. For most applications, this is not enough to run PostgreSQL in a useful way—especially not if performance does matter (and it usually does).

To fix this problem, you have to adjust kernel parameters. Managing Kernel Resources of the PostgreSQL Administrator's Guide will tell you exactly why we have to adjust kernel parameters.

For more information, check out the PostgreSQL documentation at http://www.postgresql.org/docs/9.4/static/kernel-resources.htm.

This chapter describes all the kernel parameters that are relevant to PostgreSQL. Note that every operating system needs slightly different values here (for open files, semaphores, and so on).

Since not all operating systems can be covered in this little book, only Linux and Mac OS X will be discussed here in detail.

Adjusting kernel parameters for Linux

In this section, parameters relevant to Linux will be covered. If shmget (previously mentioned) fails, two parameters must be changed:

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

In this example, shmmax and shmall have been adjusted to 16 GB. Note that shmmax is in bytes while shmall is in 4k blocks. The kernel will now provide you with a great deal of shared memory.

Also, there is more; to handle concurrency, PostgreSQL needs something called semaphores. These semaphores are also provided by the operating system. The following kernel variables are available:

Don't change these variables unless you really have to. Changes can be made with sysctl, as was shown for the shared memory.

Adjusting kernel parameters for Mac OS X

If you happen to run Mac OS X and plan to run a large system, there are also some kernel parameters that need changes. Again, /etc/sysctl.conf has to be changed. Here is an example:

kern.sysv.shmmax=4194304
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=1024

Mac OS X is somewhat nasty to configure. The reason is that you have to set all five parameters to make this work. Otherwise, your changes will be silently ignored, and this can be really painful.

In addition to that, it has to be assured that SHMMAX is an exact multiple of 4096. If it is not, trouble is near.

If you want to change these parameters on the fly, recent versions of OS X provide a systcl command just like Linux. Here is how it works:

sysctl -w kern.sysv.shmmax
sysctl -w kern.sysv.shmmin
sysctl -w kern.sysv.shmmni
sysctl -w kern.sysv.shmseg
sysctl -w kern.sysv.shmall

Fixing other kernel-related limitations

If you are planning to run a large-scale system, it can also be beneficial to raise the maximum number of open files allowed. To do that, /etc/security/limits.conf can be adapted, as shown in the next example:

postgres hard nofile 1024
postgres soft nofile 1024

This example says that the postgres user can have up to 1,024 open files per session.

Note that this is only important for large systems; open files won't hurt an average setup.