To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and
 writes
Date: Wed, 03 Feb 2010 17:35:55 +0100
Lines: 69
Message-ID: <hkc8l8$ut3$1@ger.gmane.org>
References: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US;
	rv:1.9.1.5) Gecko/20100118 Thunderbird/3.0
In-Reply-To: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com>

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.