Re: [dspam-users] postgresql and dspam

Mike Horwath wrote:
> On Fri, Aug 05, 2005 at 12:20:03PM +0800, James Andrewartha wrote:

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

Bonnie++ (done while postfix turned off, hence no dpsam/pgsql load):
Version 1.03 ------Sequential Output------ --Sequential Input- --Random-
                     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
quoll 4G 22016 42 20982 7 15462 3 32869 56 39013 4 155.9 0
                     ------Sequential Create------ --------Random Create--------
                     -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
               files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
                  16 3426 96 +++++ +++ +++++ +++ 3573 97 +++++ +++ 10795 100

hdparm -t (done while postfix on):
  Timing buffered disk reads: 136 MB in 3.02 seconds = 45.08 MB/sec

Which is a bit below what I've seen from a 7200.7, but still fairly good.

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

I can rebuild the fs, but I doubt that will help fix the root cause. Michael
Anthon also noticed my iowait is high, but I think that's because I have so
many transactions, and not vice versa.

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

Running purge.sql has dropped it down to 1.1GB, and I've stuck it in to run
daily before the nightly vacuum. This does seem to have decreased load a
bit, but I'm still seeing periods with hundreds of transactions/second and
50%+ iowait.

>>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?

What do you mean by this?

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

I turned on query logging, and the ones that take the most time are either
lots (up to 400, taking 3 seconds) of EXECUTE dspam_update_plan, or SELECT
uid, token, spam_hits, innocent_hits FROM dspam_token_data WHERE uid =
'####' and token in (<a list of 2000 tokens>), taking 2.5 seconds. That's
the top end, some only take .3 seconds or 1.5 seconds. The insert into
dspam_signature_data, although it's a large binary blob of data, is pretty fast.

-- 
James Andrewartha
Systems Administrator
Data Analysis Australia Pty Ltd