From owner-freebsd-database@FreeBSD.ORG Sun Mar 18 11:51:44 2007 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id D907616A401; Sun, 18 Mar 2007 11:51:44 +0000 (UTC) (envelope-from Axel.Rau@Chaos1.DE) Received: from mail1.chaos1.de (Mail1.Chaos1.de [213.160.12.53]) by mx1.freebsd.org (Postfix) with ESMTP id 97BB213C4C9; Sun, 18 Mar 2007 11:51:44 +0000 (UTC) (envelope-from Axel.Rau@Chaos1.DE) Received: from claudius6.in.chaos1.de ([192.168.220.106]) by mail1.chaos1.de with esmtpsa (TLSv1:AES128-SHA:128) (Exim 4.44) id 1HStvL-0007lH-4N; Sun, 18 Mar 2007 12:51:43 +0100 Mime-Version: 1.0 (Apple Message framework v752.2) In-Reply-To: <111EFCD1-0B88-4646-BCF5-B13D1F3BBCB0@khera.org> References: <8CDBEAF9-0945-4CD2-89C4-58AA63850ECC@Chaos1.DE> <111EFCD1-0B88-4646-BCF5-B13D1F3BBCB0@khera.org> Content-Type: text/plain; charset=UTF-8; delsp=yes; format=flowed Message-Id: <7489C7AA-BD33-4144-ABD4-73338CD5C7C7@Chaos1.DE> Content-Transfer-Encoding: quoted-printable From: Axel Rau Date: Sun, 18 Mar 2007 12:51:17 +0100 To: freebsd-database@freebsd.org, FreeBSD AMD list X-Mailer: Apple Mail (2.752.2) Cc: Subject: Re: New Opteron box, dedicated to PostgreSQL X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 18 Mar 2007 11:51:44 -0000 Am 16.03.2007 um 21:04 schrieb Vivek Khera: > > On Mar 16, 2007, at 7:17 AM, Claus Guttesen wrote: > >>> while configuring my 1st PostgreSQL box with dual Opterons (2212) on >>> FreeBSD, I have some questions: >>> >>> 3. What are the recommendations for tuning I/O)? >>> - setting sysctl vfs.read_max to 16 or 32 >>> - rebuilding the relevant filesystem with 32K blocks and 4K =20 >>> frags >>> Are these reliable? >> >> The following were suggetions from Vivek Khera: > > I've recently bumped the shmall and shmmax on my dual opteron with =20 > 16GB of RAM, and increased correspondingly the shared buffers. > > The max shmall you can set on freebsd (at least 6.1) is 2147483647, =20= > so I set shmall to 524288 to correspond. This supports 250000 =20 > shared buffers and 100 max connections. Might support more, but =20 > definitely not 260000. This box will start with 8GB of RAM, should also support 100 =20 connections, so I will try half of your values (pretty your settings =20 posted by Claus Guttesen ). > > I'm also using vfs.read_max=3D32 but I haven't really tested if it =20 > makes a big difference in formal benchmarks. > > The other day I was having some I/O overload, so I tried setting =20 > vfs.hirunningspace to 3K but it didn't solve my immediate problem. =20= > I've left that setting for now. Doesn't seem to really make a big =20 > difference. > > I find that the adaptec 2230SLP RAID controllers are not able to =20 > keep up with my load, but the LSI 320-2X is. I'm currently =20 > investigating external arrays attached via fibre for some boost. This box will have an Areca ARC-1261ML (RAID 1 for OS and WAL, RAID 0 =20= with 7xRAID1 for pg_data). Any hints beside the usual partition alignment and stripe size of =20 128kB ? Do you use ufs2 with softupdates? regards, Axel --------------------------------------------------------------------- Axel Rau, =E2=98=80Frankfurt , Germany +49 69 9514 = 18 0 From owner-freebsd-database@FreeBSD.ORG Sun Mar 18 12:16:37 2007 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 763F916A401; Sun, 18 Mar 2007 12:16:37 +0000 (UTC) (envelope-from Axel.Rau@chaos1.de) Received: from mail1.chaos1.de (Mail1.Chaos1.de [213.160.12.53]) by mx1.freebsd.org (Postfix) with ESMTP id 13FB013C45E; Sun, 18 Mar 2007 12:16:37 +0000 (UTC) (envelope-from Axel.Rau@chaos1.de) Received: from claudius6.in.chaos1.de ([192.168.220.106]) by mail1.chaos1.de with esmtpsa (TLSv1:AES128-SHA:128) (Exim 4.44) id 1HSuJP-0000mb-6I; Sun, 18 Mar 2007 13:16:36 +0100 Mime-Version: 1.0 (Apple Message framework v752.2) In-Reply-To: References: <8CDBEAF9-0945-4CD2-89C4-58AA63850ECC@Chaos1.DE> Message-Id: <9012051A-E60C-4408-B772-B2A733CE23F8@chaos1.de> From: Axel Rau Date: Sun, 18 Mar 2007 13:16:26 +0100 To: freebsd-database@freebsd.org, FreeBSD AMD list X-Mailer: Apple Mail (2.752.2) Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: Subject: Re: New Opteron box, dedicated to PostgreSQL X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 18 Mar 2007 12:16:37 -0000 Am 16.03.2007 um 12:17 schrieb Claus Guttesen: > > /boot/loader.conf: > > kern.ipc.semmni=3D256 > kern.ipc.semmns=3D2048 > > > /etc/sysctl.conf: > > kern.ipc.somaxconn=3D2048 This one is new to me. > > # Suggestions by Vivek Khera from postgresql performance > kern.ipc.shm_use_phys=3D1 > kern.ipc.shmmax=3D1073741824 > kern.ipc.shmall=3D262144 > kern.ipc.semmsl=3D512 > kern.ipc.semmap=3D256 > > > This is on a four-way woodcrest at 3 GHz and 16 GB ram so you may want > to adjust the values. Thank you for this valuable info. I will use it as a starting point. Regards, Axel --------------------------------------------------------------------- Axel Rau, =E2=98=80Frankfurt , Germany +49 69 9514 = 18 0 From owner-freebsd-database@FreeBSD.ORG Mon Mar 19 14:37:03 2007 Return-Path: X-Original-To: freebsd-database@FreeBSD.ORG Delivered-To: freebsd-database@FreeBSD.ORG Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 38DF516A4FF; Mon, 19 Mar 2007 14:37:03 +0000 (UTC) (envelope-from olli@lurza.secnetix.de) Received: from lurza.secnetix.de (lurza.secnetix.de [83.120.8.8]) by mx1.freebsd.org (Postfix) with ESMTP id B734A13C448; Mon, 19 Mar 2007 14:37:02 +0000 (UTC) (envelope-from olli@lurza.secnetix.de) Received: from lurza.secnetix.de (qdovsj@localhost [127.0.0.1]) by lurza.secnetix.de (8.13.4/8.13.4) with ESMTP id l2JEZwRP036992; Mon, 19 Mar 2007 15:36:03 +0100 (CET) (envelope-from oliver.fromme@secnetix.de) Received: (from olli@localhost) by lurza.secnetix.de (8.13.4/8.13.1/Submit) id l2JEZwjn036991; Mon, 19 Mar 2007 15:35:58 +0100 (CET) (envelope-from olli) Date: Mon, 19 Mar 2007 15:35:58 +0100 (CET) Message-Id: <200703191435.l2JEZwjn036991@lurza.secnetix.de> From: Oliver Fromme To: freebsd-database@FreeBSD.ORG, freebsd-amd64@FreeBSD.ORG, Axel.Rau@chaos1.de In-Reply-To: <1FDE5616-AF00-416F-B8E8-1D6A584B1126@chaos1.de> X-Newsgroups: list.freebsd-database User-Agent: tin/1.8.2-20060425 ("Shillay") (UNIX) (FreeBSD/4.11-STABLE (i386)) MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-2.1.2 (lurza.secnetix.de [127.0.0.1]); Mon, 19 Mar 2007 15:36:03 +0100 (CET) Cc: Subject: Re: New Opteron box, dedicated to PostgreSQL X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: freebsd-database@FreeBSD.ORG, freebsd-amd64@FreeBSD.ORG, Axel.Rau@chaos1.de List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 19 Mar 2007 14:37:03 -0000 Axel Rau wrote: > Oliver Fromme wrote: > > > Be sure to disable background fsck via /etc/rc.conf. I've > > seen it breaking file systems under certain circumstances. > > Good to know. What about softupdates? It makes a bit of a difference, but not a big one. Note that you should enable fsync in the PostgreSQL config anyway, which defeats the purpose of softupdates somewhat. If you disable fsync (and you know what you're doing), using softupdates will probably improve i/o performance significantly, but I haven't tried that. Note that you might lose data if you setup your DB server that way. Best regards Oliver PS: Please respect the Reply-To header. I don't need to receive a separate copy of the mail; I'm reading the list. -- Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing b. M. Handelsregister: Registergericht Muenchen, HRA 74606, Geschäftsfuehrung: secnetix Verwaltungsgesellsch. mbH, Handelsregister: Registergericht Mün- chen, HRB 125758, Geschäftsführer: Maik Bachmann, Olaf Erb, Ralf Gebhart FreeBSD-Dienstleistungen, -Produkte und mehr: http://www.secnetix.de/bsd "If you think C++ is not overly complicated, just what is a protected abstract virtual base pure virtual private destructor, and when was the last time you needed one?" -- Tom Cargil, C++ Journal From owner-freebsd-database@FreeBSD.ORG Mon Mar 19 15:38:58 2007 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 57C1516A400; Mon, 19 Mar 2007 15:38:58 +0000 (UTC) (envelope-from vivek@khera.org) Received: from yertle.kcilink.com (yertle.kcilink.com [74.92.149.58]) by mx1.freebsd.org (Postfix) with ESMTP id 2A8B713C4B8; Mon, 19 Mar 2007 15:38:57 +0000 (UTC) (envelope-from vivek@khera.org) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id 4EC2CB81E; Mon, 19 Mar 2007 11:38:57 -0400 (EDT) In-Reply-To: <7489C7AA-BD33-4144-ABD4-73338CD5C7C7@Chaos1.DE> References: <8CDBEAF9-0945-4CD2-89C4-58AA63850ECC@Chaos1.DE> <111EFCD1-0B88-4646-BCF5-B13D1F3BBCB0@khera.org> <7489C7AA-BD33-4144-ABD4-73338CD5C7C7@Chaos1.DE> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Content-Transfer-Encoding: 7bit From: Vivek Khera Date: Mon, 19 Mar 2007 11:38:56 -0400 To: freebsd-database@freebsd.org X-Mailer: Apple Mail (2.752.2) Cc: FreeBSD AMD list Subject: Re: New Opteron box, dedicated to PostgreSQL X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 19 Mar 2007 15:38:58 -0000 On Mar 18, 2007, at 7:51 AM, Axel Rau wrote: > This box will have an Areca ARC-1261ML (RAID 1 for OS and WAL, RAID > 0 with 7xRAID1 for pg_data). > Any hints beside the usual partition alignment and stripe size of > 128kB ? > Do you use ufs2 with softupdates? You don't value your data? Why not RAID10? I use UFS2 with softupdates. I generally use the default RAID stripe sizes. Postgres works in 8k pages, so if you have a lot of locality in your db reference, larger stripes might help. I don't know. From owner-freebsd-database@FreeBSD.ORG Mon Mar 19 17:25:52 2007 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 9CC6F16A405; Mon, 19 Mar 2007 17:25:52 +0000 (UTC) (envelope-from vivek@khera.org) Received: from yertle.kcilink.com (yertle.kcilink.com [74.92.149.58]) by mx1.freebsd.org (Postfix) with ESMTP id 710BA13C468; Mon, 19 Mar 2007 17:25:52 +0000 (UTC) (envelope-from vivek@khera.org) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id 9AB12B81E; Mon, 19 Mar 2007 13:25:51 -0400 (EDT) In-Reply-To: References: <8CDBEAF9-0945-4CD2-89C4-58AA63850ECC@Chaos1.DE> <111EFCD1-0B88-4646-BCF5-B13D1F3BBCB0@khera.org> <7489C7AA-BD33-4144-ABD4-73338CD5C7C7@Chaos1.DE> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Content-Transfer-Encoding: 7bit From: Vivek Khera Date: Mon, 19 Mar 2007 13:25:50 -0400 To: freebsd-database@freebsd.org X-Mailer: Apple Mail (2.752.2) Cc: FreeBSD AMD list Subject: Re: New Opteron box, dedicated to PostgreSQL X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 19 Mar 2007 17:25:52 -0000 On Mar 19, 2007, at 11:38 AM, Vivek Khera wrote: > You don't value your data? Why not RAID10? eek never mind... re-read your message and saw that is what you're doing... From owner-freebsd-database@FreeBSD.ORG Tue Mar 20 10:05:45 2007 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 3826D16A409 for ; Tue, 20 Mar 2007 10:05:45 +0000 (UTC) (envelope-from freebsd-database@m.gmane.org) Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by mx1.freebsd.org (Postfix) with ESMTP id E8FCA13C459 for ; Tue, 20 Mar 2007 10:05:44 +0000 (UTC) (envelope-from freebsd-database@m.gmane.org) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1HTagI-0004F9-MF for freebsd-database@freebsd.org; Tue, 20 Mar 2007 10:31:02 +0100 Received: from lara.cc.fer.hr ([161.53.72.113]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 20 Mar 2007 10:31:02 +0100 Received: from ivoras by lara.cc.fer.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 20 Mar 2007 10:31:02 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: freebsd-database@freebsd.org From: Ivan Voras Date: Tue, 20 Mar 2007 10:30:34 +0100 Lines: 10 Message-ID: References: <8CDBEAF9-0945-4CD2-89C4-58AA63850ECC@Chaos1.DE> <9012051A-E60C-4408-B772-B2A733CE23F8@chaos1.de> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: lara.cc.fer.hr User-Agent: Thunderbird 1.5.0.10 (X11/20060911) In-Reply-To: <9012051A-E60C-4408-B772-B2A733CE23F8@chaos1.de> Sender: news Cc: freebsd-amd64@freebsd.org Subject: Re: New Opteron box, dedicated to PostgreSQL X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 20 Mar 2007 10:05:45 -0000 Axel Rau wrote: >> kern.ipc.somaxconn=2048 > This one is new to me. Shouldn't make a difference, it's TCP "backlog" size - how many clients will be allowed to wait on the server's accept() call. It shouldn't make a difference except if you have *really* high connections/sec, and then only up to maximum connections you allow. It's practically never a factor in performance.