From: Ivan Voras <ivoras@freebsd.org>
To: pgsql-performance@postgresql.org
Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
Date: Wed, 03 Feb 2010 17:35:55 +0100
Message-ID: <hkc8l8$ut3$1@ger.gmane.org> (raw)
In-Reply-To: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com>
References: <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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ivoras@freebsd.org
Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
In-Reply-To: <hkc8l8$ut3$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox