Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 72E696325D5 for ; Thu, 4 Feb 2010 05:03:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 06369-02 for ; Thu, 4 Feb 2010 09:03:20 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f188.google.com (mail-qy0-f188.google.com [209.85.221.188]) by mail.postgresql.org (Postfix) with ESMTP id 9B0AF6322C6 for ; Thu, 4 Feb 2010 05:03:20 -0400 (AST) Received: by qyk26 with SMTP id 26so1060440qyk.5 for ; Thu, 04 Feb 2010 01:03:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=BodtYFnG3Lc4+3rWjV9TJJxu6+TC7h0cIMAd2KZXnYk=; b=WWzC/HenG9Li9uEvBtINS+j4eZFPzwrWAfkiwtr39dRsJBoi9b4cxoQYDmzu3gQ7ux rw0jOsJZvpXW3F1JbP6J8D15KIvXmXDo6nyVPzCfObqSxrPBkMSCgEoVrbjxxqsKoY1x tnoSUg3g0KmYbFHYgCq4bgLEZ0GltwEHxM588= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=ouBUBxuCUNUYHK7MfLQj0ahzZaQ65o3DjEJNZAccNfVhoBw//oazpsnXooRC6adqrI 1yVBdWWOojIj9babUUeUnuUeCHXxlQrBJVePQf7+aYVpP2CQnrntUk/S1NKAKWu8tk+Y xCbEuah40aySuNzhKdb6bpfeLFgmR8o+Tkfjo= MIME-Version: 1.0 Received: by 10.229.55.69 with SMTP id t5mr447125qcg.34.1265274199134; Thu, 04 Feb 2010 01:03:19 -0800 (PST) In-Reply-To: References: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com> From: Amitabh Kant Date: Thu, 4 Feb 2010 14:32:59 +0530 Message-ID: <84b68b3d1002040102o41b5c121k168accda7e31072b@mail.gmail.com> Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes To: Ivan Voras Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0015175dd8b82ed314047ec29bfe X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.184 tagged_above=-10 required=5 tests=BAYES_40=-0.185, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/80 X-Sequence-Number: 37501 --0015175dd8b82ed314047ec29bfe Content-Type: text/plain; charset=ISO-8859-1 On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras wrote: > 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. > > Can't do anything about this server now, but would surely keep in mind before upgrading other servers. Would you recommend the same speed drives(15K SAS) for RAID 1, or would a slower drive also work here (10K SAS or even SATA II)? > > 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. > > Yes, its running amd64 arch. > 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. > Thanks Ivan. That's a great explanation of the variables involved. With regards Amitabh Kant --0015175dd8b82ed314047ec29bfe Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <ivoras@freebsd.org<= /a>> wrote:
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 - =A0RAID 10 with BBU, Dual Xeon =A0E5345 =A0= @
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 sy= stem (or more accurately: adding more memory will help reads but nothing he= lps writes except more drivers or faster (SSD) drives). If you can, add ano= ther 2 drives in RAID 1 and move+symlink the pg_xlog directory to the new a= rray.


Can't do anything about this server now= , but would surely keep in mind before upgrading other servers. Would you r= ecommend the same speed drives(15K SAS) for RAID 1, or would a slower drive= also work here (10K SAS or even SATA II)?

=A0

maintenance_work_mem =3D 960MB # pg_generate_conf wizard 2010-02-03
checkpoint_completion_target =3D 0.9 # pg_generate_conf wizard 2010-02-03 effective_cache_size =3D 11GB # pg_generate_conf wizard 2010-02-03
work_mem =3D 160MB # pg_generate_conf wizard 2010-02-03
wal_buffers =3D 8MB # pg_generate_conf wizard 2010-02-03
checkpoint_segments =3D 16 # pg_generate_conf wizard 2010-02-03
shared_buffers =3D 3840MB # pg_generate_conf wizard 2010-02-03
max_connections =3D 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.

Yes, its running amd64 arch.
=A0
In /boot/loader.conf you will probably need to increase the number of sysv = ipc semaphores:

kern.ipc.semmni=3D512
kern.ipc.semmns=3D1024

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=3D4089446400
This is the maximum shared memory segment size, in bytes.

kern.ipc.shmall=3D1050000
This is the maximum amount of memory allowed to be used as sysv shared memo= ry, 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 t= he directory list cache:

kern.maxfiles=3D16384
vfs.ufs.dirhash_maxmem=3D4194304

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

vfs.read_max=3D32

Be sure that soft-updates is enabled on the file system you are using for d= ata. Ignore all Linux-centric discussions about problems with journaling an= d 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.

Thanks Ivan. That's a great explanation of the variables involved.
<= br>
With regards

Amitabh Kant

--0015175dd8b82ed314047ec29bfe--