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.