HOWTO : Performance tuning for PostgreSQL on Ubuntu/Debian

Step 1 :

Edit postgresql.conf.

sudo nano /etc/postgresql/8.3/main/postgresql.conf

Step 2 :

The performance tuning setting is as the following :

(1) shared_buffers

Recommended : 0.25 * Available Memory

(2) work_mem

Recommended : Available Memory / max_connections
(If your queries tend to be more complicated, then divide that by 2. If you typically run very close to max_connections connections, then consider dividing by 2 again. If that gives you a number that isn’t at least 16MB, buy more memory.)

(3) maintenance_work_mem

Recommended : Available Memory / 8

(4) wal_buffers

Recommended : 8MB

(5) checkpoint_segments

Recommended : 16 to 128

(6) effective_cache_size

Recommended : Available Memory * 0.75

(7) cpu_tuple_cost

Recommended : 0.0030

(8) cpu_index_tuple_cost

Recommended : 0.0010

(9) cpu_operator_cost

Recommended : 0.0005

(10) fsync

Recommended : off

Warning : If “fsync” is set to “off”, you may encounter data loss when the power failure unless you have a battery backup unit at your hardware RAID card.

(11) max_connection

Recommended : 140% (100 clients average means 140 max connections)

(12) checkpoint_timeout

Recommended : 1h

Step 3 :

Restart PostgreSQL server.

sudo /etc/init.d/postgresql-8.3 restart

Step 4 :

If it produces error message and cannot restart, change the setting for “kernel.shmmax” on sysctl.conf as suggested.

Edit the sysctl.conf as suggested.

sudo nano /etc/sysctl.conf

Reference #1 :

The following is the my setting of a 8GB RAM server which is running PostgreSQL.

/etc/postgresql/8.3/main/postgresql.conf

max_connections = 140
shared_buffers = 2GB
temp_buffers = 8MB
work_mem = 16MB
maintenance_work_mem = 1GB
wal_buffers = 8MB
checkpoint_segments = 128
effective_cache_size = 6GB
cpu_tuple_cost = 0.0030
cpu_index_tuple_cost = 0.0010
cpu_operator_cost = 0.0005
fsync = off
checkpoint_timeout = 1h

Reference #2 :

The following is my setting of sysctl.conf on the same server.

/etc/sysctl.conf

kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmax = 2209914880
kernel.shmmni = 4096
fs.file-max = 262140
vm.vfs_cache_pressure = 50
vm.min_free_kbytes = 65536

net.core.rmem_default = 33554432
net.core.rmem_max = 33554432
net.core.wmem_default = 33554432
net.core.wmem_max = 33554432
net.ipv4.tcp_rmem = 10240 87380 33554432
net.ipv4.tcp_wmem = 10240 87380 33554432
net.ipv4.tcp_no_metrics_save = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_sack = 1
net.core.netdev_max_backlog = 5000
net.ipv4.tcp_mem = 786432 1048576 26777216
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_max_tw_buckets = 360000

Step 5 :

Add the following parameters to the kernel tag of Grub.

reservation,nodiratime,noatime

Step 6 :

sudo mount -a

If no error message produced, issue the following command to make it work.

sudo mount -o remount /

Green Computing with Proxmox VE 1.2 and 3ware

Proxmox VE 1.2 is running Kernel-based Virtual Machine (KVM-85) and OpenVZ on Debian 5.0. It is a 64-bit system and works with virtualization capable CPU. It equipped with a web-based control panel and allows you to create, delete and modify the virtual guests from it. You can also backup the virtual guests with cron job. It is a very good and easy way to implement the virtualization environment. However, Proxmox VE does not support fakeRAID and software RAID. A hardware RAID card is required.

It will halt at 99% during installation and you should be patient and the process will be completed within minutes.

You can access each guest via VNC which is running under Java. It is quite responsive under my Gigabit home network. You cannot use keyboard and mouse unless you pointed your mouse cursor on the guest window. However, the mouse wheel does not work in this release. According to the reply from forum that this feature may be supported in the future. In addition, since the VNC viewer is running under Java, the guest does not support any Java applet from execution, such as http://www.ppshk.com for example.

It provides some appliances for OpenVZ, such as mail gateway, Debian, CentOS, Ubuntu, Drual, WordPress, SugarCMS and etc. You can implement the appliances with a few clicks. You can create your appliance template very easily (you can refer to her wiki website). Furthermore, You can install other Linux distributions or Windows systems via iso or CD-ROM for the KVM but not the OpenVZ.

Since my motherboard equipped 2 PCI-Express x1, I bought a 3Ware 9650SE-2LP which supports 2 SATA II ports for RAID 0, 1 and JBOD. It is quite expensive ($1,800-HK) and without battery backup. The configuration of RAID 1 is very easy via its BIOS. You can also configure the RAID card by browser through its Disk Manager software but it is not yet tested by me.

In conclusion, Proxmox VE is ideal for server virtualization. It is very easy to create, delete and modify any virtual guest server with a few clicks. Let’s go Green Computing!