Database-SQL-RDBMS HOW-TO document for Linux (PostgreSQL Object Relational Database System): Performance Tuning of PostgreSQL serverNextPreviousContents
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.
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.
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.
