I have noticed that MySQL responds well to regular "OPTIMIZE TABLE spool" you could try running that evary 10K or so, it reclaims wasted space and re-organises keys.
You could try dropping the indices, bizzarely enough while you will experience some loss of performance you may gain greater consistency at the lower level. The spool table is basically always empty, but achieves that with a great deal of insert/delete it is probably not necessary to have any indices on this table other than the primary key unless you expect transient peak capacity to be extremly large. d. > -----Original Message----- > From: Diego Castillo [mailto:[EMAIL PROTECTED]] > Sent: 04 February 2003 18:17 > To: [EMAIL PROTECTED] > Cc: Diego Castillo, INEXBEE > Subject: MySQL administration tips for JDBC spool > > > 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: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
