8. Performance Tuning of PostgreSQL server

Generally database server is standalone box connected to network. Since database server is the only unix process which runs on the CPU, you can do several optimizations to speed up the server.

8.1 OS Tuning for Database server

To get more bang for a given CPU processing power, do the following:-

• Recompile linux kernel to make it small and lean. Remove items which are not used. See kernel howto at http://www.linuxdoc.org/HOWTO/Kernel-HOWTO.html

• Turn off unneccessary unix processes - on linux/unix systems run chkconfig

  ---

  bash$ su - root
  bash# man chkconfig
  bash# chkconfig --help
  bash# chkconfig --list | grep on | less
  From the above list, turn off the processes you do not want to start automatically -
  bash# chkconfig --level 0123456 <service name> off
  Next time when the machine is booted these services will not be started.
  Now, shutdown the services manually which you just turning off.
  bash# cd /etc/rc.d/init.d
  bash# ./<service name> stop
  

  ---

• Do not run any other application processes which are unnecessary.

• Do not leave X-Window running unattended. Because X-window processes consume memory, CPU load and can be a serious security hole from outside attacks. The X-window managers generally used are KDE, GNOME, CDE, XDM and others. You must exit the X-window immediately after using and most of the time you should see command line console login prompt on the database server machine.

8.2 Tuning Database server process

General tuning tips:

• Indices can speed up queries. The explain command allows you to see how PostgreSQL is interpreting your query, and which indices are being used.
• Use the cluster command to group data in base tables to match an index. See the man cluster(1) manual page for more details.
• If you are doing a lot of inserts, consider doing them in a large batch using the copy command. This is much faster than individual inserts.
• Statements not in a begin work/commit transaction block are considered to be in their own transaction. Consider performing several statements in a single transaction block. This reduces the transaction overhead. Also consider dropping and recreating indices when making large data changes.
• It is suggested that you purchase the "Performance Tuning guide" and tuning support from PostgreSQL Corp..

Specialized tuning tips:

• Internal tuning of PostgreSQL is a complex topic. You need a sound knowledge of source code and internals of postgresql. It is strongly recommended that only professionals attempt specialized tuning tips given below:
• You can disable fsync() by starting the postmaster with a -o -F option. This will prevent fsync() from flushing to disk after every transaction. But there is risk of losing data due to power/media failure. You can reduce the risk of losing data due to power failure by having the APC UPS (Uninterrupted Power Supply) and media failures by disk RAID systems ( Antares-Sparc-Raid system, Software-Raid system, Old-Software-Raid system, Root-Raid system, Boot-Root-Raid system) to gaurd against media failures.
• Use the postmaster -B option to increase the number of shared memory buffers used by the back-end processes. If you make this parameter too high, the postmaster may not start up because you've exceeded your kernel's limit on shared memory space. Each buffer is 8K and the default is 64 buffers.
• Use the back-end -S option to increase the maximum amount of memory used by each backend process for temporary sorts. The -S value is measured in kilobytes, and the default is 512 (i.e., 512K). It is unwise to make this value too large, or you may run out of memory when query invokes several concurrent sorts.