Re: [dspam-users] postgresql and dspam

Mike Horwath wrote:
> On Wed, Aug 03, 2005 at 12:37:18PM +1000, Michael Anthon wrote:
[RAM]
> RAM in any modern UNIX type system is used to do a few things,
> including caching of regular data from the filesystem, or in the case
> of a database server, you can raise the usage limits of the DB server
> process and gain a ton of speed.
>
> It is all based on tunables.

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.

>>>Bring your RAM up.
>>>
>>>A lot of disk I/O means that something is very busy, more than likely,
>>>you are hitting your VM system.
>
> Trashing swap is something you don't need vmstat to check for...
>
> To check on disk load, things like iostat do a better job. When you
> hop on over to FreeBSD 5.x and above, you can run gstat as well, which
> will give you a break down by mounted filesystem. Solaris can do
> basically the same thing with iostat by using '-x' or '-xpt' on 9 and
> above.

> 2MB/sec of what kind of traffic? If email, then yah, more RAM for
> sure.

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).

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?

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.

-- 
James Andrewartha
Systems Administrator
Data Analysis Australia Pty Ltd