Jerry, This is an interesting Question. We've known for years that mysql doesn't like the spool, and the reason, as you say, seems to be that it has to maintain a large highly fragmented index for a table which is normally empty. Dropping the indices gives you consistent performance from the spool, but it is woefully slow (well its true!). Rebuilding the indices regularly, e.g. cron as you say, is current recommended practice, but I wonder now if there is an alternate engine type that would be more suitable for this kind of use, MyISAM is fast and "lite" I've heard it described as a "bit bucket" engine, whereas innodb is fully transactional and has a more sophisticated tablespace model. I'm not sure that innodb would be any better, but why don't you try and let us know? It should be perfectly OK to run an "alter table ... innodb" statement on the empty spool table, but don't blame me if you break something. If that's no good try some of the other engines, I'd love to hear your results
I also wonder if it would be possible to redesign James so that it would work with primary keys only and the natural order, which would allow us to drop the indices all together. FWIW I've seen the same behaviour with Oracle 9 when using messaging, the same criteria (normally empty table, big index) result in the same behaviour, with the same solution (regularly rebuild the indices) d. On Fri, Oct 10, 2008 at 3:28 AM, Jerry M <[EMAIL PROTECTED]> wrote: > I have been running the same installation of James and MySQL on the same > system for several years. In the last 6 months I've been encountering a > problem. Every few days the MySQL CPU utilization goes up dramatically. I > found that if I optimize the SPOOL table in the James mail db, the CPU goes > to near 0... for a while. A day or so later, CPU has gone back up. > > I completely understand the nature of SPOOL table. It probably has 100,000 > records added and removed every day with all the spam, etc my server > processes. I know this causes the db to expand, and optimizing cleans it > back up. I guess I could write a cron job to go out everyday and run an > optimize. But I'm curious if I have the right table type. I don't really > know much about MyISAM vs. InnoDB. SPOOL is currently MyISAM. Would > changing it make it run more efficiently given the huge activity pace of > this table? Is there something else I might be doing wrong? > > Thx. > > Jerry > > > --------------------------------------------------------------------- > 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]
