Re: [dspam-users] postgresql and dspam

On Fri, Aug 05, 2005 at 12:20:03PM +0800, James Andrewartha wrote:
> Having read a few postgres tuning guides, I've upped shared_buffers
> to 4096, sort_mem to 8192 and vacuum_mem to 8192. I changed
> wal_sync_method to fdatasync and set checkpoint_segments to 6. I
> also got reduced the 5 daily vacuums that Debian ships with by
> default to one at 5am daily. This has reduced the load somewhat, but
> it's still not right.

I need to review some PostgreSQL documentation before I can give much
more detail.

How is your system doing for disk I/O? have you measured that yet?

> 2MB/s being written to disk, with very little being read from
> disk. There are some stats here http://quoll.daa.com.au/stats/ which
> show that I'm doing 200 write transactions/second for 1MB
> written/second, which implies most are 4kb (my ext3 block size).

That is a lot of I/O for 1MB of data.

Can you rebuild that filesystem with a larger block size?

> At a guess, dspam is hitting lots of random small bits of the
> database which are getting written out to disk. Also, my dspam
> database is now 3GB in size after a month's operation for about 35
> users - is this normal?

That seems large for 35 users.

My dspam3db size is ~180MB, one user.

> For reference, the load this server was under was handled adequately
> by a dual p3 733 with 512MB ram running the same software, except
> with spamassassin instead of dspam/postgresql.

CPU bound instead of disk bound. You are having a lot of I/O...

I run UseNet News servers - if I were doing 200 ops per second, I
would normally see more like 30MB/sec or higher.

Have you checked your indexes?

Have you done more than the standard online vacuum? Take the DB
offline, and try a full vacuum and see what happens.

-- 
Mike Horwath, reachable via drechsau@Geeks.ORG