DeskNow Knowledge Base
Home Installation and initial setup Performance tuning for PostgreSQL database (Linux)

Performance tuning for PostgreSQL database (Linux)

Note: this page is for tuning PostgreSQL on Linux systems. For the default PostgreSQL included in Desknow for Windows , see /kb/idx/0/145/article/

PostgreSQL can be a very fast database, but its default settings are very conservative. Very simple adjustments can improve its performance dramatically.

General considerations

In almost every interactive web application, the bottleneck is the database, not the application. This is the case of DeskNow as well. As with every database, the more RAM you can give to the database, the better.

Very important are also SCSI or SATA NCQ drives, as they free CPU for other tasks while data is being loaded.

Finally, partitioning the database over several disks can improve considerably performance. Even the ability of writing transaction log files to a separate disk improves performance a lot (this last is a very advanced performance improvement, which will not be discussed here).

Simple configuration changes.

Most of the config changes are done in the postgresql.conf file (typically in /var/lib/pgsql/data on Unix systems).

Important: before starting, backup the file postgresql.conf

shared_buffers this is the most important parameter. Multiply this value by 8192 to understand how much memory postgres will take.
Ex 20000=160Mb ram
Keep in mind that DeskNow uses 260 Mb at max.
So 20000 is good for a server with 512Mb RAM.
If you have more memory, increase this number. Make sure to not use too much, otherwise the system will start swapping to disk.

After changing this parameter, probably postgres will not start.
do this:
su - postgres
pg_ctl -D /var/lib/pgsql/data start

If the message says that you need to increase the kernel's SHMMAX value to a certain number, do this command (you must be root):
echo <shared_buffers * 8192> > /proc/sys/kernel/shmmax (this is for Linux)
<shared_buffers * 8192> is also the number reported by pg_ctl in the error message.

After setting the proper number, make sure it is used at the next reboot, by adding/setting the following in /etc/sysctl.conf
kernel.shmmax = <shared_buffers * 8192>

Example: for shared_buffers=20000 it is 167763968 (160 Mb), so you would type:
echo 167763968 > /proc/sys/kernel/shmmax (this is for Linux)

and then add this line to /etc/sysctl.conf
kernel.shmmax = 167763968

Tip: on some Linux flavours /etc/sysctl.conf does not exist. You can create this file. Also you may need to type this command to make it effective: 

sysctl -p
You may also add the same command to your boot sequence. In Suse Linux, for example, you may add it to  

Vacuum regularly

It is very important to give a 'vacuum analyze;' command periodically to the database. This will clean up deleted rows, and improve query statistics.

Tip: it is possible to vacuum the database without entring the PostgreSQL console. Type 'vacuumdb --help' in console for more information.

Email Article Email
Print Article Print

How helpful was this article to you?
Related Articles
article Performance tuning for the embedded PostgreSQL database (Windows)
PostgreSQL can be a very fast database, but its...

  August 11, 2006    Views: 15351   
article Performance tuning for MySQL database
By default MySQL is installed with very...

  April 11, 2006    Views: 13749   
article Performance tuning
This article provides a few tip and strategies...

  February 2, 2007    Views: 11890   

Powered by Lore :: (c)2003 Pineapple Technologies.