Memory and kernel issues
After this brief introduction to installing PostgreSQL, it is time to focus on some of the most common problems.
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:
- FATAL: Could not create shared memory segment
- DETAIL: Failed system call was
shmget(key=5432001, size=1122263040, 03600) - HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's
SHMMAXparameter. You can either reduce the request size or reconfigure the kernel with largerSHMMAX. To reduce the request size (currently 1122263040 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
Tip
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration.
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:
SEMMNI: This is the maximum number of semaphore identifiers. It should be at least ceil((max_connections + autovacuum_max_workers + 4) / 16).SEMMNS: This is the maximum number of system-wide semaphores. It should be at least ceil((max_connections + autovacuum_max_workers + 4) / 16) * 17, and it should have room for other applications in addition to this.SEMMSL: This is the maximum number of semaphores per set. It should be at least 17.SEMMAP: This is the number of entries in the semaphore map.SEMVMX: This is the maximum value of the semaphore. It should be at least 1000.
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.
