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!