When you start a PostgreSQL server, it allocates a fixed-size block of shared memory where all access to the information in the database passes through. In addition, each client that connects to memory uses up its own bit of memory, expanding it as the client uses resources such as sorting space and storing data about pending transactions to commit.
Some settings in the database can be adjusted by the clients after they connect. For example, the work_mem setting, a limiter on how much memory can be used for sorting, can be increased by a client after he connects. These allocations use non-shared memory, and tuning them is covered in the next chapter.
The major component to the shared memory used by the server is a large block allocated for caching blocks, read from and written to the database. This is set by a parameter named shared_buffers. Monitoring and optimizing how this memory is used is the major topic of this chapter. It is one of the most important parameters to get good performance, and one of the hardest to predict an optimum value for.
All of the shared memory settings and the starting client settings for the database are stored in the postgresql.conf file. In PostgreSQL 8.2, a change has greatly simplified memory settings. In earlier versions, you had to know the unit for each memory related setting; some were in units of 1 KB, and some 8 KB, which was difficult to keep track of.
Nowadays, you can still set values like that, but the preferred practice is to use a memory size, instead. For example, if you wanted to set the wal_buffers value that controls how much memory to use for buffering write-ahead log data, you can do that now with a line like the following in the postgresql.conf:
wal_buffers = 64 KB
If you use the SHOW command to display the value for this setting, it will write it similarly (although, it's possible that the value will get re-scaled to display better). However, the database still converts this value into its own internal units, which for this parameter happens to be 8 K blocks. It's helpful to be aware of this because you'll need to know the actual values to understand some of the internal information covered in this chapter.
The pg_settings view in the database can be used to see how that conversion happens. It's also helpful to know that current_setting() function can be used to get the same basic information as SHOW, but in a way you can use in queries. You can combine these two to help see the relationship between the internal way in which the server stores parameters and what they actually represent:
$ psql postgres=# show wal_buffers; wal_buffers ------------- 64kB postgres=# SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='wal_buffers'; name | setting | unit | current_setting -------------+---------+------+----------------- wal_buffers | 8 | 8kB | 64kB
When you use the initdb command to create a new PostgreSQL cluster, the server detects how large a shared memory block it can allocate by starting at a moderate value and decreasing it until the allocation is successful. This is necessary because on many platforms, including some very popular UNIX ones, the default values for allocation of shared memory is very low. 32 MB or less is quite common, even on recent software like the constantly updated Linux kernels, and really small values are possible on older systems.
Note
The default memory sizes in the postgresql.conf are not optimized for performance or for any idea of a typical configuration. They are optimized solely so that the server can start on a system with low settings for the amount of shared memory it can allocate, because that situation is so common.
There are a variety of common errors you can get when a PostgreSQL server fails to start, documented at http://www.postgresql.org/docs/current/static/server-start.html, and the one related to shared memory parameters being too low looks like this:
FATAL: could not create shared memory segment: Invalid argument
DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600)
The kernel resources documentation page at http://www.postgresql.org/docs/current/static/kernel-resources.html goes over how to increase this parameter in detail for most platforms. If you have a system that supports the getconf command, which many UNIX-like systems do, the following program will produce reasonable starting values by asking getconf for information about how much memory is in your system:
#!/bin/bash # simple shmsetup script page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo kernel.shmmax = $shmmax echo kernel.shmall = $shmall
This sets the maximum shared block size to one half of total memory and outputs values suitable for a Linux /etc/sysctl.conf file. The following is an example from a system with 2 GB of physical RAM, run as root:
# ./shmsetup >> /etc/sysctl.conf # sysctl -p kernel.shmmax = 1055092736 kernel.shmall = 257591
Here shmmax is the maximum size (in bytes) for a single shared memory segment, set to 1 GB. And shmall is the total amount of shared memory (in pages) that all processes on the server can use. The number of bytes in a page depends on the operating system; 4096 is a common value. A more robust version of this program is included as one of this book's code samples, which handles this page math for you.
While not a memory allocation figure, another occasional sysctl tweaking requirement for PostgreSQL is to increase the number of available system semaphores, an object used for process communication. The defaults on a recent Linux system look like this:
$ ipcs -l ... ------ Semaphore Limits -------- max number of arrays = 128 max semaphores per array = 250 max semaphores system wide = 32000 max ops per semop call = 32 semaphore max value = 32767 ...
One of the parts trimmed from the ipcs output also shows the shared memory limits, so you can also double-check them with it. Setting higher semaphore limits is done with a kernel parameter that combines the main four limits here into one line. The last shown semaphore maps into that format like this:
$ sysctl kernel.sem kernel.sem = 250 32000 32 128
All four of the values here might need to be increased on systems with a large number of processes, setting the same way as the increased shared memory sizes.
It's possible to predict how much memory the PostgreSQL server is expected to allocate given the server parameters. A table labeled "PostgreSQL shared memory usage" near the bottom of http://www.postgresql.org/docs/current/static/kernel-resources.html gives a rough estimate (last updated as of Version 8.3) of how much shared memory is allocated by the server. It looks like this:
The first two lines are very similar because they're just accounting for the fact that the autovacuum workers each take up their own connection. You can just add max_connections + autovacuum_max_workers and use that as your true connection count to simplify this estimate a bit.
Very little of this is likely to matter to you because all the other sizes are dwarfed by shared_buffers unless your client count is extremely high. Assuming a PostgreSQL 9.0 server, these are the default values for the settings involved here:
There's no default value you can assume for shared_buffers, because that value is detected at server cluster creation time. The default setting of 0 for max_prepared_transactions makes the prepared transaction space allocation go away altogether.
To further simplify the memory estimate computation, you can just add in the autovacuum workers as clients (103 instead of 100). The main elements of the allocation estimate table then become:
That's about 2.8 MB plus whatever the shared_buffers amount comes out as. As a typical value for shared_buffers starts at 24 MB or more, it is obviously the dominant thing to be concerned about when estimating shared memory consumption.
Usage
Approximate shared memory bytes
Connections (1800 + 270 * max_locks_per_transaction) * max_connections
Autovacuum workers (1800 + 270 * max_locks_per_transaction) * autovacuum_max_workers
Prepared transactions (770 + 270 * max_locks_per_transaction) * max_prepared_transactions
Shared disk buffers (block_size + 208) * shared_buffers
WAL buffers (wal_block_size + 8) * wal_buffers
Fixed space requirements 770 kB
Parameter
Default value
max_locks_per_transaction 64
max_connections 100
autovacuum_max_workers 3
max_prepared_transactions 0
block_size 8192
wal_block_size 8192
wal_buffers 8
Usage
Approximate shared memory bytes
Connections+AV workers 1.9MB
Shared disk buffers 8400 * shared_buffers
WAL buffers 64 kB
Fixed space requirements 770 kB