From owner-freebsd-performance@FreeBSD.ORG Sun Dec 5 20:58:51 2004 Return-Path: Delivered-To: freebsd-performance@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 9379816A52B for ; Sun, 5 Dec 2004 20:58:51 +0000 (GMT) Received: from mail.trippynames.com (mail.trippynames.com [38.113.223.19]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6D8BC43D1D for ; Sun, 5 Dec 2004 20:58:51 +0000 (GMT) (envelope-from sean@chittenden.org) Received: from localhost (localhost [127.0.0.1]) by mail.trippynames.com (Postfix) with ESMTP id 8D363A6C21; Sun, 5 Dec 2004 12:58:50 -0800 (PST) Received: from mail.trippynames.com ([127.0.0.1]) by localhost (rand.nxad.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 31841-05; Sun, 5 Dec 2004 12:58:49 -0800 (PST) Received: from [192.168.1.4] (dsl081-069-073.sfo1.dsl.speakeasy.net [64.81.69.73]) by mail.trippynames.com (Postfix) with ESMTP id 1E485A6C3A; Sun, 5 Dec 2004 12:58:48 -0800 (PST) In-Reply-To: <20041205204434.9341.qmail@web14105.mail.yahoo.com> References: <20041205204434.9341.qmail@web14105.mail.yahoo.com> Mime-Version: 1.0 (Apple Message framework v619) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <72207B58-4700-11D9-9EB9-000A95C705DC@chittenden.org> Content-Transfer-Encoding: 7bit From: Sean Chittenden Date: Sun, 5 Dec 2004 12:58:42 -0800 To: Claus Guttesen X-Mailer: Apple Mail (2.619) cc: freebsd-performance@freebsd.org Subject: Re: postgresql on FreeBSD 5.3 and high load X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 05 Dec 2004 20:58:51 -0000 >> Have you looked to see if you're using spin locks or >> not? Search for "amd64 x86_64 spin lock" in PostgreSQL's archives. >> I think spin locks were disabled for amd64. When you're at a load of >> 70, look in pg_catalog.pg_locks to see if you have any ungranted >> locks. > > Thank you for your input. > > Searched postgresl.org's archives, both wihtin and via > google, but only found information that related > primarily to Linux. Before I fired that off, I should've asked, if you do: SELECT * FROM pg_catalog.pg_locks WHERE granted = FALSE Are you getting any rows? If not, slow locks aren't the issue. Have you run iostat(1) to see if you're saturating your IO? >> Also, is your application primarily read? You may >> want to investigate >> using pgpool as a way of reducing the overhead for >> connection startup. > > There are more reads than writes, but I haven't found > the exact numbers (yet). Look in pg_catalog.pg_stat* for an idea of your workload. http://developer.postgresql.org/docs/postgres/monitoring- stats.html#MONITORING-STATS-VIEWS-TABLE If you can come back with a more specific problem other than "load is high," it's much easier to get more precise help regarding whatever the problem is. A load of 70 may just mean you've got a damn busy database. :) -sc -- Sean Chittenden