Re: Mysql CPU usage above 100% with empty mail queues

As I found out the hard way, innoDB db's don't shrink.....

-- 
Rick Zeman
Manager of Information Technology
Melwood Horticultural Training Center
301.599.4574 - HelpDesk
301.599.4560 - MyDesk
http://www.melwood.org


>>> "Douglas Mortensen" <dougimpalanetworks.com> 12/3/2007 3:16:46 PM
>>>
Lastly, although things seem to be running well for over 90
minutes now,
the mysqld cpu usage still is running between 80-95%, which
I'm thinking
is high for not having to process a lot of messages right
now. Again,
maybe this is just because my bayes db is so big, which I'm
hoping will
shrink when my newly added cron job runs early tomorrow AM.
We'll see.
But if anyone else has any insight to share between now and
then, I'd
appreciate it.

 

Thanks,

--

Doug Mortensen

Impala Networks

Network Consultant

 

From: Douglas Mortensen 
Sent: Monday, December 03, 2007 1:09 PM
To: 'maia-usersrenaissoft.com'
Subject: RE: Mysql CPU usage above 100% with empty mail
queues

 

Doing some more looking around, I just found
www.maiamailguard.com/maia/wiki/BayesAutoExpire 

 

I've implemented its suggestions. We'll see what happens to
my bayes
table tomorrow when it runs. Maybe then I'll be able to turn
bayes back
on. For now, things seem to have run very smoothly for over
1 hour now
that bayes is turned off. So I guess we'll wait and see.

 

If nobody responds to any of my posts, I hope that this at
least will
help someone in the future.  

 

 

--

Doug Mortensen

Impala Networks

Network Consultant

 

From: Douglas Mortensen 
Sent: Monday, December 03, 2007 12:31 PM
To: 'maia-usersrenaissoft.com'
Subject: RE: Mysql CPU usage above 100% with empty mail
queues

 

Looks like the queue started filling up again. Just a few
minutes ago I
found that our queues were again up around 1200 messages.
This time what
I did is turned off bayesian filtering (use_bayes 0 in
/etc/mail/spamassassin/local.cf), and restarted
amavisd-new.

 

This seemed to really help things without me needing to
touch mysql.

 

However, mysqld is still taking up at least 80-95% CPU
constantly.

 

Any ideas? Do I need to wipe out my bayes_token table? Is it
supposed to
ever expire old tokens, or just continually grow until my
server finally
dies from having like 9,999,999,999,999 records in that
table (which I
suppose would eventually happen if it's not cleaned
out/recycled
somehow)?

 

Again, thanks in advance for any ideas and expertise.

 

--

Doug Mortensen

Impala Networks

Network Consultant

 

From: Douglas Mortensen 
Sent: Monday, December 03, 2007 12:07 PM
To: 'maia-usersrenaissoft.com'
Subject: Mysql CPU usage above 100% with empty mail queues

 

Hello,

 

I am concerned that there may be something wrong with my
maia database
or mysql server on my debian-etch maia system. Right now the
mail queues
are pretty much empty. I'm processing anywhere between 1-3
messages per
second. This usually doesn't put much of a load on our
server. However,
right now mysql is taking anywhere between 80-110% CPU
constantly
(multi-core CPU).

 

This may have been related to a little issue we had about 30
minutes ago
with the queues suddenly having about 1200 unprocessed
messages in them.
I couldn't find a problem from any of the following logs:

 

/var/log/mail.log (or its variants mail.err, mail.warn,
mail.info)

/var/log/clamav/clamav.log

/var/log/clamav/freshclam.log

/var/log/messages

 

I don't know why /var/log/mysql.log & mysql.err are
always empty.
Logrotate is rotating them, but nothing gets written to
them. In
/etc/mysql/my.cnf it says "Error logging goes to
syslog. This is a
Debian improvement  ", but
I haven't seen it writing any mysql stuff
to /var/log/mysql.log or mysql.err or to /var/log/messages.

 

Maybe this is because there have been no messages to write.
Anyway, I'm
kind of worried. I took the following steps to alleviate the
issue with
the 1200 messages in the queue:

 

*         Purged all records from awl table  in mysql
(using
phpMyAdmin). It had over 3,000,000 records, and I thought
this could be
causing problems.

o   This didn't get things going though.

*         Restarted clamav-deamon, amavisd-new (maia), and
postfix

o   This didn't seem to speed things up either.

*         Restarted mysql

o   This really got things going after a minute or two, it
started
processing messages pretty quickly again

 

Prior to each of these steps, messages were being processed,
but just
much more slowly than normal.

 

So now, for at least 20 minutes, it seems like things are
going well.
But I'm worried about why mysqld is taking so much CPU
usage.

 

If you have any ideas, I would greatly appreciate your
help!!  J

 

 

Also, here's some info about the existing mysqld tables in
maia, which
I'm getting from phpMyAdmin:

 

awl:762 records (80KB)

bayes_seen: 726,873 records (100MB)

bayes_token: 1,278,672 records (172MB)

maia_mail: 270,253 records (1.4GB)

maia_mail_recipients: 235,397 records (123.3MB)

maia_sa_rules: 1,131 records (192KB)

maia_sa_rules_triggered: 2,094,101 records (167.2MB)

maia_stats_history: 2,336 records (464KB)

 

All other tables are under 250 records, which I'm assuming
is pretty
small and shouldn't cause any negative impact.

 

Thanks again for your help in advance.

 

__

Douglas Mortensen

      Network Consultant

         Impala Networks

 

              CCNA

            A+, Linux+, Network+

 

 

 

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users