From owner-freebsd-database@FreeBSD.ORG Tue Aug 26 15:35:15 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 6A82916A4BF; Tue, 26 Aug 2003 15:35:15 -0700 (PDT) Received: from perrin.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id E718D43FBF; Tue, 26 Aug 2003 15:35:14 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.nxad.com (Postfix, from userid 1001) id 424AF20F00; Tue, 26 Aug 2003 15:35:14 -0700 (PDT) Date: Tue, 26 Aug 2003 15:35:14 -0700 From: Sean Chittenden To: Paul Pathiakis Message-ID: <20030826223514.GB44347@perrin.nxad.com> References: <200308250929.32143.paul@pathiakis.com> <200308261420.16894.ppathiakis@micropat.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200308261420.16894.ppathiakis@micropat.com> X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: paul@pathiakis.com Subject: Re: Tuning Postgresql on FreeBSD 5.1 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 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, 26 Aug 2003 22:35:15 -0000 [ Please post follow ups oon this topic to just -databases, this is off topic for performance@ ] > 5 large processes were kicked off. The processes started off ok but > then just stalled. They continued to run until completion but VERY > slowly. Many times I saw the processes in semwait states. However, > the CPU load never went over 0.80. Everything just kind of stalled > and waited. They consumed huge amounts of memory but never swapped. I don't think semaphores are swap backed, but I could be wrong. That said, if I'm right (I think I am), your backends are contending for a finite amount of shared memory/semaphores. Did you turn down values to the levels I suggested? It looks like you've over stated the amount of ram available in your system in your various configs (over use of md, overly large values in postgresql.conf, and an under sized effective_cache_size, etc). Is your effective_cache_size set correctly, btw? Run the following in /bin/sh and you'll get the correct value. echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" > I disabled HTT in the BIOS, but the OS still saw 4 cpus... I'm > going to again try to disable HTT and see what occurs in the kernel > boot. For right now, I've enable and modified all the parameters > according to what Sean suggested. I'm hoping they've turned up > something here. In the event that somehow I can't disable, I'll use > the cpu sysctl parameter that was suggested. The kernel manually walks the table. To really disable HTT, you have to recompile your kernel. -sc -- Sean Chittenden