Date: Thu, 17 Jan 2008 16:34:20 -0500 From: Bill Moran <wmoran@potentialtech.com> To: robert@webtent.com Cc: Robert Fitzpatrick <lists@webtent.net>, FreeBSD <freebsd-questions@freebsd.org> Subject: Re: db performance Message-ID: <20080117163420.ba23dc30.wmoran@potentialtech.com> In-Reply-To: <1200604168.7281.65.camel@columbus.webtent.org> References: <1200602606.7281.48.camel@columbus.webtent.org> <20080117155336.0c38d86d.wmoran@potentialtech.com> <1200604168.7281.65.camel@columbus.webtent.org>
next in thread | previous in thread | raw e-mail | index | archive | help
In response to Robert Fitzpatrick <lists@webtent.net>: > On Thu, 2008-01-17 at 15:53 -0500, Bill Moran wrote: > > In response to Robert Fitzpatrick <lists@webtent.net>: > > > > > I also have assumed in the past that db performance could be better if I > > > get off the system RAID-5 and put it on 1+0? The system has 4 SATA > > > drives. > > > > That will speed things up if IO is your bottleneck, but you've not > > demonstrated that. > > > > Which machine in this system is the bottleneck? Are the Amavis machines > > timing out, or is the PostgreSQL server too slow? If I understand your > > description, it sounds like a network problem to me ... i.e., machines > > not on the same gateway as the PG server are experience slow network > > response (or dropped packets?) that's causing amavis to time out while > > trying to talk to PG. I would suggest investigating there first. > > The SA timeouts I'm finding on all the servers. Even the db server that > runs it's own amavisd process for backup purposes and some minor domains > just to make sure it is there and working. This is why I think you're > right, the pgsql db is too slow. Would I possibly see dramatic > differences in speed with the RAID switch? You're not even close to proposing a solution yet. Take a deep breath and take a little time to understand the problem before you start throwing hardware at it. 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. You need to investigate more, though. Otherwise you're just randomly flipping switches. 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? 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 -- Bill Moran http://www.potentialtech.com
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20080117163420.ba23dc30.wmoran>