POSTGRESQL is an object-relational database developed on the Internet by a group of developers spread across the globe. It is an open-source alternative to commercial databases like Oracle and Informix.

POSTGRESQL was originally developed at the University of California at Berkeley. In 1996,a group began development of the database on the Internet. They use email to share ideas and file servers to share code. POSTGRESQL is now comparable to commercial databases in terms of features,performance,and reliability. It has transactions,views,stored procedures,and referential integrity constraints. It supports a large number of programming interfaces,including ODBC,Java (JDBC),TCL/TK,PHP,Perl,and Python. POSTGRESQL continues to improve at a tremendous pace thanks to a talented pool of Internet developers.

Performance Concepts

There are two aspects of database performance tuning. One is improving the database's use of the CPU,memory,and disk drives in the computer. The second is optimizing the queries sent to the database. This article talks about the hardware aspects of performance tuning. The optimization of queries is done using SQL commands likeCREATE INDEX,VACUUM,VACUUM FULL,ANALYZE, CLUSTER,and EXPLAIN. These are discussed in my book,PostgreSQL: Introduction and Concepts at http://momjian.us/main/writings/pgsql/aw_pgsql_book/.

To understand hardware performance issues,it is important to understand what is happening inside the computer. For simplicity,a computer can be thought of as a central processing unit (CPU) surrounded by storage. On the same chip with the CPU are several CPUregisters which store intermediate results and various pointers and counters. Surrounding this is the CPU cache which holds the most recently accessed information. Beyond the CPU cache is a large amount of random-access main memory (RAM) which holds executing programs and data. Beyond this main memory are disk drives,which store even larger amounts of information. Disk drives are the only permanent storage area,so anything to be kept when the computer is turned off must be placed there. In summary,here are the storage areas surrounding the CPU:

You can see that storage areas increase in size as they get farther from the CPU. Ideally,a huge amount of permanent memory could be placed right next to the CPU,but this would be too slow and expensive. In practice,the most frequently used information is stored next to the CPU,and less frequently accessed information is stored farther away and brought to the CPU as needed.

Keeping Information Near the CPU

Moving information between various storage areas happens automatically. Compilers determine which information should be stored in registers. CPU chip logic keeps recently used information in the CPUcache. The operating system controls which information is stored in RAM and shuttles it back and forth from the disk drive.

CPU registers and the CPU cache cannot be effectively tuned by the database administrator. Effective database tuning involves increasing the amount of useful information in RAM, thus preventing disk access where possible.

You might think this is easy to do,but it is not. A computer's RAMcontains many things:

• executing programs

• program data and stack

• POSTGRESQL shared buffer cache

• kernel disk buffer cache

• kernel

Proper tuning involves keeping as much database information in RAMas possible while not adversely affecting other areas of the operating system.

POSTGRESQL Shared Buffer Cache

POSTGRESQL does not directly change information on disk. Instead,it requests data be read into the POSTGRESQL shared buffer cache. POSTGRESQL backends then read/write these blocks,and finally flush them back to disk.

Backends that need to access tables first look for needed blocks in this cache. If they are already there,they can continue processing right away. If not,an operating system request is made to load the blocks. The blocks are loaded either from the kernel disk buffer cache,or from disk. These can be expensive operations.

The default POSTGRESQL configuration allocates 1000 shared buffers. Each buffer is 8 kilobytes. Increasing the number of buffers makes it more likely backends will find the information they need in the cache,thus avoiding an expensive operating system request. The change can be made with a postmaster command-line flag or by changing the value of shared_buffers in postgresql.conf.

How Big Is Too Big?

You may think,``I will just give all my RAM to the POSTGRESQL shared buffer cache.'' However,if you do that,there will be no room for the kernel or for any programs to run. The proper size for the POSTGRESQL shared buffer cache is the largest useful size that does not adversely affect other activity.

To understand adverse activity,you need to understand how UNIXoperating systems manage memory. If there is enough memory to hold all programs and data,little memory management is required. However,if everything doesn't fit in RAM,the kernel starts forcing memory pages to a disk area called swap. It moves pages that have not been used recently. This operation is called a swap pageout. Pageouts are not a problem because they happen during periods of inactivity. What is bad is when these pages have to be brought back in from swap,meaning an old page that was moved out to swap has to be moved back into RAM. This is called a swap pagein. This is bad because while the page is moved from swap,the program is suspended until the pagein completes.

Pagein activity is shown by system analysis tools like vmstatand sar and indicates there is not enough memory available to function efficiently. Do not confuse swap pageins with ordinary pageins,which may include pages read from the filesystem as part of normal system operation. If you can't find swap pageins,many pageouts is a good indicator you are are also doing swap pageins.

Effects of Cache Size

You may wonder why cache size is so important. First,imagine the POSTGRESQL shared buffer cache is large enough to hold an entire table. Repeated sequential scans of the table will require no disk access because all the data is already in the cache. Now imagine the cache is one block smaller than the table. A sequential scan of the table will load all table blocks into the cache until the last one. When that block is needed,the oldest block is removed,which in this case is the first block of the table. When another sequential scan happens,the first block is no longer in the cache,and to load it in,which in this case is now the second block in the table. This pushing out of the next needed block continues to the end of the table. This is an extreme example,but you can see that a decrease of one block can change the efficiency of the cache from 100% to 0%. It shows that finding the right cache size can dramatically affect performance.

Proper Sizing of Shared Buffer Cache

Ideally,the POSTGRESQL shared buffer cache will be:

• Large enough to hold most commonly-accessed tables

• Small enough to avoid swap pagein activity

Keep in mind that the postmaster allocates all shared memory when it starts. This area stays the same size even if no one is accessing the database. Some operating systems pageout unreferenced shared memory,while others lock shared memory into RAM. Locked shared memory is preferred. The POSTGRESQL administrators guide has information about kernel configuration for various operating systems, http://developer.postgresql.org/docs/postgres/kernel-resources.html

Sort Memory Batch Size

Another tuning parameter is the amount of memory used for sort batches. When sorting large tables or results,POSTGRESQL will sort them in parts,placing intermediate results in temporary files. These files are then merged and resorted until all rows are sorted. Increasing the batch size creates fewer temporary files and often allows faster sorting. However,if the sort batches are too large,they cause pageins because parts of the sort batch get paged out to swap during sorting. In these cases,it is much faster to use smaller sort batches and more temporary files,so again,swap pageins determine when too much memory has been allocated. Keep in mind this parameter is used for every backend performing a sort,either for ORDER BY,CREATE INDEX,or for a merge join. Several simultaneous sorts will use several times this amount of memory.

This value can be changed using a postmaster command-line flag or by changing the value of sort_mem in postgresql.conf.

Cache Size and Sort Size

Both cache size and sort size affect memory usage,so you cannot maximize one without affecting the other. Keep in mind that cache size is allocated on postmaster startup,while sort size varies depending on the number of sorts being performed. Generally,cache size is more significant than sort size. However,certain queries that use ORDER BY,or merge joins may see speedups with larger sort batch sizes.

Also,many operating systems limit how much shared memory can be allocated. Increasing this limit requires operating system-specific knowledge to either recompile or reconfigure the kernel. More information can be found in the POSTGRESQL administrators guide, http://developer.postgresql.org/docs/postgres/kernel-resources.html.

As a start for tuning,use 15% of RAM for cache size,and 2-4% for sort size if you have just a few big sessions,and much smaller if you have lots of small sessions. You can try increasing it to see if performance improves and if no swapping occurs. If the shared buffers are oversized,you waste overhead maintaining too many buffers,and it takes RAM that could be used by other processes and as additional kernel disk buffer cache.

A valuable server parameter is effective_cache_size. This parameter is used by the optimizer to estimate the size of the kernel's disk buffer cache. In kernels with a unified buffer cache,this value should be set to the average amount of unused RAM in the kernel because such kernels use unused RAM to cache recently accessed disk pages. On kernels with a fixed-sized disk buffer cache,this should be set to the size of your kernel buffer cache,typically 10% of RAM.