Spool Table in MySQL

[Jerry M <te...@malcolms.com>]

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: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org

Re: Spool Table in MySQL

[Danny Angus <da...@apache.org>]

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 <te...@malcolms.com> 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: server-user-unsubscribe@james.apache.org
> For additional commands, e-mail: server-user-help@james.apache.org
>
>

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