MySQL administration tips for JDBC spool

[Diego Castillo <di...@inexbee.com>]

Hi all,

In order to enforce FIFO, I have started using JDBC spool recently. I
use James 2.1 with MySQL 3.23.55 on Win2K SP3.

I launch a load test against James of 4 mail/sec. This may not look a
lot, but I have a mailet that takes quite a long time to execute. I
observe the following:

1. Everything runs OK for about 2.5 hours (~36K mails).
2. After that, the server starts slowing down and mails begin to be
queued in spool for another 45 minutes (~11K mails).
3. Finally, James hangs: it stops accepting (no more mails into spool),
processing (no more mailet calls) and delivering mail.

The problem seems to come from the database. I can restart the server
and launch James; it starts consuming the mails from the spool queue,
but it is very slow and the MySQL daemon takes up most of the CPU time.

I am a novice to MySQL and my first guess is a common administration
task that I am not applying to my system. I have read about multiple
storage options (MyISAM, heap, InnoDB), external tools for checking
table integrity (myisamchk), etc. I think my database got corrupted due
to the intensive insert-delete that James performs.

Can anybody offer any recommendation on DB admin for James? Sort of
maintenance tasks to be applied every xxx mails in order to maintain the
JDBC spool performance...

Below I send the myisamchk information of my spool table, which I can't
interpret. Is there anything wrong in my figures?

I am considering switching back to the file-based spool because of these
database oddities. FIFO is very important for my project, but
reliability scores higher :-(

Regards,


Diego



C:\MySQL\data\mail>myisamchk -i spool
Checking MyISAM file: spool
Data records:    8077   Deleted blocks:    5675
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  78%  Packed:   95%  Max levels:  3
Total:    Keyblocks used:  78%  Packed:   95%

- check record links
Records:          8077  M.recordlength:  11236  Packed:          0%
Recordspace used: 100%  Empty space:       22%  Blocks/Record: 1.97
Record blocks:   15945  Delete blocks:    5675
Record data:  90760604  Deleted data: 20698668
Lost space:      32568  Linkdata:       120476


---------------------------------------------------------------------
To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: james-user-help@jakarta.apache.org