10.4 Preparing the Database for the Web

Open source databases play a major role in today's Web environments. Especially for complex Web applications, PostgreSQL is a good choice. To use PostgreSQL efficiently, the database should be configured to satisfy the demands of a modern IT environment. Depending on the kind of application you want to run, the best settings for your database might vary.

In this section a few basic rules will be presented.

10.4.1 Memory

Tuning PostgreSQL's memory management is an important task. The first thing is to find out if the applications you are going to run contain complex queries or a set of simple queries. If you run only a few complex queries, it won't be necessary to change the maximum number of connections allowed, but it will be necessary to increase the memory PostgreSQL is allowed to use for internal sorts. Sort buffers are a tricky thing because if many concurrent sorts are running on the system, a lot of memory could be allocated, and no memory would be left for other operations. On the other hand, small sort buffers will reduce the performance of your system because a lot of I/O power is needed.

In general, the more memory your computer has, the better it is for your database. Caching can make a database perform really fast, so it is recommended to use a lot of cache and many shared buffers. However, make sure that the system does not swap because swapping is much slower than working with temporary sort files. This is an important point, and many people think that the operating system is always faster than the database, which is not true.

To monitor the memory that is currently used by your system, we recommend working with tools like top and free (these tools are available on Linux; if you are using other operating systems, check out the manual).

10.4.2 Configuring the Linux Kernel for PostgreSQL

After PostgreSQL has been configured, it is recommended to change the configuration of the Linux kernel as well. When the database is working under heavy load, some operating system resource limits can easily be exceeded and problems occur because of wrong configuration of the underlying operating system. These problems can easily be avoided by changing the most important settings of the kernel. In this book only the kernel settings of Linux operating systems will be covered because configuring other systems is beyond the scope of this book and how to change parameters can be looked up in the manual of the operating system.

Table 10.1 shows the basic values that can be defined and a reasonable estimation of these parameters.

Table 10.1. Linux Kernel Parameters

Name	Description	Reasonable Values
SHMMAX	Maximum size of shared memory segment (bytes)	250kB + 8.2kB * shared_buffers + 14.2kB * max_connections or infinity
SHMMIN	Minimum size of shared memory segment (bytes)	1
SHMALL	Total amount of shared memory available (bytes or pages)	If bytes, same as SHMMAX; if pages, ceil(SHMMAX/PAGE_SIZE)
SHMSEG	Maximum number of shared memory segments per process	Only 1 segment is needed, but the default is much higher
SHMMNI	Maximum number of shared memory segments systemwide	Like SHMSEG plus room for other applications
SEMMNI	Maximum number of semaphore identifiers (that is, sets)	>= ceil(max_connections / 16)
SEMMNS	Maximum number of semaphores systemwide	ceil(max_connections / 16) * 17 + room for other applications
SEMMSL	Maximum number of semaphores per set	>= 17
SEMMAP	Number of entries in semaphore map
SEMVMX	Maximum value of semaphore	>= 255 (The default is often 32767; don't change unless asked to.)

Table 10.1 has been taken from the developers docs of PostgreSQL.

All parameters of the Linux kernel can be changed at runtime and don't require a reboot.

To change a parameter, a simple echo command can be used:

 [root@notebook root]# echo 1 > /proc/sys/kernel/shmmni 

Simply redirect the output of the echo command to the appropriate location in the proc filesystem and the changes will be made immediately.

10.4.3 General Recommendations

When running PostgreSQL as a Web database, we recommend changing some default settings. Keep in mind that these changes are not required, but daily work with the database has proven that it can be useful to make these changes:

• Run VACUUM periodically Use cron to run VACUUM periodically to free disk space and to rebuild the optimizer statistics.

• Turn sequential scans off If you use PostgreSQL as a Web database, it can be useful to turn off sequential scans. This will in many cases lead to slightly lower performance because index scans are used even if a sequential scan is faster. However, in some case indexes are useful when PostgreSQL decides to perform sequential scans. In this cases a lot of speed is gained and it is less likely that timeouts will occur (at least if enough indexes have been defined).

• Use fsync Fsync helps you to raise the security of your data because buffers are flushed to disk immediately.

• Tuning memory Try to tune PostgreSQL by modifying the memory setting.

Depending on your system, additional changes might be useful. In many situations the settings that have just been described have been useful.