From owner-freebsd-amd64@FreeBSD.ORG Fri Mar 16 08:46:00 2007 Return-Path: X-Original-To: freebsd-amd64@FreeBSD.ORG Delivered-To: freebsd-amd64@FreeBSD.ORG Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 257DD16A403; Fri, 16 Mar 2007 08:46:00 +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 85BBD13C43E; Fri, 16 Mar 2007 08:45:59 +0000 (UTC) (envelope-from olli@lurza.secnetix.de) Received: from lurza.secnetix.de (bmtuhk@localhost [127.0.0.1]) by lurza.secnetix.de (8.13.4/8.13.4) with ESMTP id l2G88FTD030413; Fri, 16 Mar 2007 09:08:21 +0100 (CET) (envelope-from oliver.fromme@secnetix.de) Received: (from olli@localhost) by lurza.secnetix.de (8.13.4/8.13.1/Submit) id l2G88FBN030412; Fri, 16 Mar 2007 09:08:15 +0100 (CET) (envelope-from olli) Date: Fri, 16 Mar 2007 09:08:15 +0100 (CET) Message-Id: <200703160808.l2G88FBN030412@lurza.secnetix.de> From: Oliver Fromme To: freebsd-database@FreeBSD.ORG, Axel.Rau@chaos1.de, freebsd-amd64@FreeBSD.ORG In-Reply-To: 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]); Fri, 16 Mar 2007 09:08:21 +0100 (CET) Cc: Subject: Re: New Opteron box, dedicated to PostgreSQL X-BeenThere: freebsd-amd64@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: freebsd-database@FreeBSD.ORG, Axel.Rau@chaos1.de, freebsd-amd64@FreeBSD.ORG List-Id: Porting FreeBSD to the AMD64 platform List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 16 Mar 2007 08:46:00 -0000 Axel Rau 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 frags > Are these reliable? Personally I would leave the FS parameters at the defaults. The bsize/fsize defaults of 16k/2k are actually quite well- suited for PostgreSQL, as far as I can tell. (If there are people who have made different experience, I'd like to hear about it.) If you expect that only few, large tables will be used, then reducing the inode density (i.e. increasing the value of the -i option to newfs) might be a good idea. Note that PostgreSQL stores each object (table, index etc.) in its own file. Be sure to disable background fsck via /etc/rc.conf. I've seen it breaking file systems under certain circumstances. Instead, you might want want to give PWD's new gjournal code a try (it's in -current, but I think there's a port to RELENG_6, too). On a related note, setting up gmirror for RAID-1 has worked very well for me, including postgres machines (the balance algorithm "load" seems to work best with pgsql databases). Of course, the usual PostgreSQL tuning advices apply, e.g. increase the SysV IPC resources (i.e. kernel parameters for semaphores and shared memory), optimize postgresql.conf etc. There's currently a known bottleneck regarding SysV IPC on FreeBSD, if a lot of processes are waiting on the same semaphore, which can affect PostgreSQL under very high load. I don't know what the status of that is, but I think nobody is currently working on a fix. Best regards Oliver -- 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 PI: int f[9814],b,c=9814,g,i;long a=1e4,d,e,h; main(){for(;b=c,c-=14;i=printf("%04d",e+d/a),e=d%a) while(g=--b*2)d=h*b+a*(i?f[b]:a/5),h=d/--g,f[b]=d%g;}