Skip site navigation (1)Skip section navigation (2)
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: <http://docs.FreeBSD.org/cgi/mid.cgi?1200606767.7281.90.camel>