Sunday, July 26, 2009

HOWTO : Performance tuning for PostgreSQL on Ubuntu 9.04 Server

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 /


That's all. See you!