Re: Optimizing Postgresql server and FreeBSD for heavy read and writes

On 02/03/10 16:10, Amitabh Kant wrote:

Hello

I have a server dedicated for Postgres with the following specs:

RAM 16GB, 146GB SAS (15K) x 4 -  RAID 10 with BBU, Dual Xeon  E5345  @
2.33GHz
OS: FreeBSD 8.0

If you really do have "heavy read and write" load on the server, nothing will save you from the bottleneck of having only 4 drives in the system (or more accurately: adding more memory will help reads but nothing helps writes except more drivers or faster (SSD) drives). If you can, add another 2 drives in RAID 1 and move+symlink the pg_xlog directory to the new array.

maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
work_mem = 160MB # pg_generate_conf wizard 2010-02-03
wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
max_connections = 100 # pg_generate_conf wizard 2010-02-03

I would appreciate if somebody could point out the sysctl/loader.conf
settings that I need to have in FreeBSD.

Firstly, you need to run a 64-bit version ("amd64") of FreeBSD.

In /boot/loader.conf you will probably need to increase the number of sysv ipc semaphores:

kern.ipc.semmni=512
kern.ipc.semmns=1024

This depends mostly on the number of connections allowed to the server. The example values I gave above are more than enough but since this is a boot-only tunable it is expensive to modify later.

In /etc/sysctl.conf you will need to increase the shared memory sizes, e.g. for a 3900 MB shared_buffer:

kern.ipc.shmmax=4089446400
This is the maximum shared memory segment size, in bytes.

kern.ipc.shmall=1050000
This is the maximum amount of memory allowed to be used as sysv shared memory, in 4 kB pages.

If the database contains many objects (tables, indexes, etc.) you may need to increase the maximum number of open files and the amount of memory for the directory list cache:

kern.maxfiles=16384
vfs.ufs.dirhash_maxmem=4194304

If you estimate you will have large sequential reads on the database, you should increase read-ahead count:

vfs.read_max=32

Be sure that soft-updates is enabled on the file system you are using for data. Ignore all Linux-centric discussions about problems with journaling and write barriers :)

All settings in /etc/sysctl.conf can be changed at runtime (individually or by invoking "/etc/rc.d/sysctl restart"), settings in loader.conf are boot-time only.