Re: Mysql CPU usage above 100% with empty mailqueues

Hi Douglas,

 

In the past I had the same problems that you with Mysql on debian stable. My advice is, find out which version of mysql are you running (if it is debian stable “etch”), probably you are running 5.0.33. This version still have some issues with multi-core machines. If this is true (5.0.33), it’s a very good ideia to upgrade to the last mysql stable version (5.0.46). This last version solved many issues with innodb escalation problem in multi-core machines. This may help you speed up the things. I’m now running the 5.0.45 version icc (intel C/C+ compiled) with 2.6.23 kernel version, and this helped distribute that load equally for every core and improved mysql performance.

 

After this, and if your problems continues, you should do some more tuning to your mysql config. The next two links, should be a good starting point.

 

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

http://www.maiamailguard.com/maia/wiki/MySQLTuning

 

You should also run a cronjob to expire bayes and maia quarantine, in daily baisis. And you sould run once a week the AWL pruning. This three scripts should  help you shrink the your maia database, but not the innodb address space. Like Rick told, cannot be shrink by “normal” operation but this shouldn217;t be your problem.

 

Joao Carmona

 

 

 

OK. So you're saying that the filesize of the database is contributing to the high CPU usage of mysqld?

--
Doug Mortensen
Network Consultant
Impala Networks
P: 505.327.7300



-----Original Message-----
From: Rick Zeman [ RZemanmelwood.org">mailto:RZemanmelwood.org]
Sent: Mon 12/3/2007 2:58 PM
To: Douglas Mortensen; maia-usersrenaissoft.com
Subject: Re: [Maia-users] Mysql CPU usage above 100% with empty mailqueues

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&quot; <dougimpalanetworks.com>; 12/3/2007 3:16:46 PM
>&gt;>
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.&nbsp;





--

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:



*&nbsp; &nbsp;   ; &nbsp; 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.

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

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

* &nbsp;   ; &nbsp;  Restarted mysql

o &nbsp; 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!!&nbsp; 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

   ; &nbsp; Network Consultant

 ; &nbsp; &nbsp; &nbsp;  Impala Networks



&nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  CCNA

 &nbsp; &nbsp; &nbsp; &nbsp;   ; A+, Linux+, Network+