From owner-freebsd-questions@FreeBSD.ORG Thu Jan 17 21:54:02 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 7482A16A4C7 for ; Thu, 17 Jan 2008 21:54:02 +0000 (UTC) (envelope-from lists@webtent.net) Received: from esmtp.webtent.net (esmtp.webtent.net [208.38.145.5]) by mx1.freebsd.org (Postfix) with ESMTP id 6B70B13C46E for ; Thu, 17 Jan 2008 21:54:02 +0000 (UTC) (envelope-from lists@webtent.net) Received: from localhost (localhost [127.0.0.1]) by esmtp.webtent.net (WebTent ESMTP Postfix Internet Mail Gateway) with ESMTP id B48737FE1A; Thu, 17 Jan 2008 16:54:01 -0500 (EST) Received: from esmtp.webtent.net ([127.0.0.1]) by localhost (mx1.webtent.net [127.0.0.1]) (amavisd-maia, port 10024) with ESMTP id 51272-05-2; Thu, 17 Jan 2008 16:53:53 -0500 (EST) Received: from [70.110.70.43] (columbus.webtent.org [70.110.70.43]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by esmtp.webtent.net (WebTent ESMTP Postfix Internet Mail Gateway) with ESMTP id 9CE227FE2C; Thu, 17 Jan 2008 16:52:51 -0500 (EST) From: Robert Fitzpatrick To: Bill Moran In-Reply-To: <20080117163420.ba23dc30.wmoran@potentialtech.com> References: <1200602606.7281.48.camel@columbus.webtent.org> <20080117155336.0c38d86d.wmoran@potentialtech.com> <1200604168.7281.65.camel@columbus.webtent.org> <20080117163420.ba23dc30.wmoran@potentialtech.com> Content-Type: text/plain Organization: WebTent Networking, Inc. Date: Thu, 17 Jan 2008 16:52:46 -0500 Message-Id: <1200606767.7281.90.camel@columbus.webtent.org> Mime-Version: 1.0 X-Mailer: Evolution 2.12.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: WebTent Mailguard 1.0.2a Cc: FreeBSD Subject: Re: db performance X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: robert@webtent.com List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 17 Jan 2008 21:54:02 -0000 On Thu, 2008-01-17 at 16:34 -0500, Bill Moran wrote: > In response to Robert Fitzpatrick : > I don't know anything about amavisd's usage of databases. If it's doing > a lot of small writes, then it's likely that getting off RAID 5 will make > a marked difference. I believe this is the case with SA learning on and auto-whitelisting. Disabling things like that are my last resort. > You need to investigate more, though. Otherwise you're just randomly > flipping switches. I really appreciate the pointers! > Watching top on the PG machine, how much RAM is in use? What is the > average CPU usage when you see timeouts? Run top -m io in another terminal > and see if a lot of IO is happening on the part of PostgreSQL ... is it > reads or writes? I see mainly postgres in the top 8-10 with mainly WRITEs of mainly less than 100 regularly, mostly less than 30 WRITES at a time. > > And what tuning have you done to PostgreSQL? PG doesn't perform well > without tuning. Install the pg_buffercache addon and see if you've got > enough shared_buffers to get decent performance out of it. Are you > running vacuum and analyze frequently? Turn on query timing and watch > the logs to see what queries are taking up time. > > Read the following links and follow the advice therein: > http://www.powerpostgresql.com/PerfList > http://www.revsys.com/writings/postgresql-performance.html > This is what I have setup now, thanks for the links, I'll re-check my tuning... mx1# cat /etc/sysctl.conf kern.ipc.shm_use_phys=1 kern.ipc.shmmax=1073741824 kern.ipc.shmall=262144 kern.ipc.semmsl=512 kern.ipc.semmap=256 I'm sure some of my tuning could use some help, like the shm_use_phys, maybe this is why my swap is not being used much? This is what I've changed from defaults in postgresql.conf... max_connections = 250 shared_buffers = 500MB work_mem = 64MB # min 64kB maintenance_work_mem = 256MB # min 1MB max_fsm_pages = 256000 -- Robert