db performance

[Robert Fitzpatrick]

On Thu, 2008-01-17 at 16:34 -0500, Bill Moran wrote:
> In response to Robert Fitzpatrick <lists at 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