Date: Thu, 17 Jan 2008 16:52:46 -0500 From: Robert Fitzpatrick <lists@webtent.net> To: Bill Moran <wmoran@potentialtech.com> Cc: FreeBSD <freebsd-questions@freebsd.org> Subject: Re: db performance Message-ID: <1200606767.7281.90.camel@columbus.webtent.org> 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>
next in thread | previous in thread | raw e-mail | index | archive | help
On Thu, 2008-01-17 at 16:34 -0500, Bill Moran wrote: > In response to Robert Fitzpatrick <lists@webtent.net>: > 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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?1200606767.7281.90.camel>