From owner-freebsd-performance@FreeBSD.ORG Tue Mar 23 13:48:36 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 610E216A4CE for ; Tue, 23 Mar 2004 13:48:36 -0800 (PST) Received: from mail2.dbitech.ca (radius.wavefire.com [64.141.13.252]) by mx1.FreeBSD.org (Postfix) with SMTP id 0C57F43D2F for ; Tue, 23 Mar 2004 13:48:36 -0800 (PST) (envelope-from darcy@wavefire.com) Received: (qmail 15065 invoked from network); 23 Mar 2004 21:55:28 -0000 Received: from dbitech.wavefire.com (HELO 64.141.15.253) (darcy@64.141.15.253) by radius.wavefire.com with SMTP; 23 Mar 2004 21:55:28 -0000 From: Darcy Buskermolen Organization: Wavefire Technologies Corp. To: "Jason Coene" , Date: Tue, 23 Mar 2004 13:48:34 -0800 User-Agent: KMail/1.5.4 References: <200403232002.i2NK2Gam077177@mail.gotfrag.com> In-Reply-To: <200403232002.i2NK2Gam077177@mail.gotfrag.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200403231348.34301.darcy@wavefire.com> Subject: Re: FreeBSD, PostgreSQL, semwait and sbwait! 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: Tue, 23 Mar 2004 21:48:36 -0000 On March 23, 2004 12:02 pm, Jason Coene wrote: > Hello all, > > We're having a substantial problem with our FreeBSD 5.2 database server > running PostgreSQL - it's getting a lot of traffic (figure about 3,000 > queries per second), but queries are slow, and it's seemingly waiting on > other things than CPU time. > > The database server is a dual P4-2.8 w/ HT enabled (kernel finds 4 > processors), 2GB RAM, 4 disk Serial ATA on 3ware RAID, gigabit Ethernet > connection to web servers. It's running FreeBSD 5.2 and PostgreSQL 7.4.1. > > The server is taking a while to respond to both connections, and then > queries (between 1-3 seconds, on a query that should execute in 0.05 or > less). What does the following query show (on the DB while it's under load)? SELECT r.relname,l.mode, count(*) AS numlocks FROM pg_locks l, pg_class r WHERE r.oid = l.relation GROUP BY relname,mode; if you have any sort of insert/updates happening, you may be waiting for a lock on that table to be available. Things like foreign keys make big use of locking while they CASCADE. > _______________________________________________ > freebsd-performance@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-performance > To unsubscribe, send any mail to > "freebsd-performance-unsubscribe@freebsd.org" -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com