1 Topic by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Topic: Help!!! Mysql use 99.9% of CPU!!!

==== Provide required information to help troubleshoot and get quick answer ====
- iRedMail version:
- Linux/BSD distribution name and version:
- Any related log? Log is helpful for troubleshooting.
====

Hi,

I have iredadmin pro mysql and i try to restart my server but always I try to enter in iredadmin mysql start to use all CPU'S!!!

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

It happens when i try to login in iredadmin web interface.

The page stuck and not load and mysql it load all cpu cicle available...

Please help...

3 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

posa68 wrote:

It happens when i try to login in iredadmin web interface.

The page stuck and not load and mysql it load all cpu cicle available...

Please help...

Now finally i'm able to login in iredadmin panel, but it took so long: almost 1/2 minutes with mysql (i see it in top command) that goes to 99.9% of cpu.
After successful login mysql come back to a normal occupation cicle...

Any hints?

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

May i know which version of the iRedMail and iRedAdmin-Pro you're running?

As mentioned in upgrade tutorial (0.7.3 -> 0.7.4) of iRedMail, you need to create addition indexes for certain columns:
http://iredmail.org/wiki/index.php?titl … d_database

Also, how many records do you have in MySQL database "amavisd"? Did you remove old records? Too many records in table "msgs" and "msgrcpt" (with complex SQL queries) will cause high CPU usage.

5 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:

May i know which version of the iRedMail and iRedAdmin-Pro you're running?

As mentioned in upgrade tutorial (0.7.3 -> 0.7.4) of iRedMail, you need to create addition indexes for certain columns:
http://iredmail.org/wiki/index.php?titl … d_database

Also, how many records do you have in MySQL database "amavisd"? Did you remove old records? Too many records in table "msgs" and "msgrcpt" (with complex SQL queries) will cause high CPU usage.

Sorry for the dumb question: how can I see the version in use of iredmail+iredadmin-pro?

Tnx

6 Reply by posa68 (edited by posa68 2012-04-13 21:04:14)

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:

May i know which version of the iRedMail and iRedAdmin-Pro you're running?

As mentioned in upgrade tutorial (0.7.3 -> 0.7.4) of iRedMail, you need to create addition indexes for certain columns:
http://iredmail.org/wiki/index.php?titl … d_database

Also, how many records do you have in MySQL database "amavisd"? Did you remove old records? Too many records in table "msgs" and "msgrcpt" (with complex SQL queries) will cause high CPU usage.

This is what i have in amavisd:

maddr -> 12958 records 1.5 MB
msgs -> 29983 records 15.4 MB
msgrcpt -> 37248 records 7.0 MB

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

You can find iRedMail version number in Postfix config file, for example, /etc/postfix/mysql/catchall_maps.cf.
iRedAdmin-Pro version number is displayed in "Dashboard" page.

8 Reply by posa68 (edited by posa68 2012-04-13 23:04:14)

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:

You can find iRedMail version number in Postfix config file, for example, /etc/postfix/mysql/catchall_maps.cf.
iRedAdmin-Pro version number is displayed in "Dashboard" page.

iRedAdmin-Pro is v. 1.3.0

The file catchall_maps.cf contains no version of iredadmin:

user        = vmail
password    = **********************
hosts       = 127.0.0.1
port        = 3306
dbname      = vmail
query       = SELECT alias.goto FROM alias,domain WHERE alias.address='%d' AND alias.address=domain.domain AND alias.active=1 AND domain.active=1 AND domain.backupmx=0

But I think it's a 0.7.2 (as far as I remember)

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

Well, please upgrade iRedMail to the latest stable release first. Upgrade tutorial is available here:
http://www.iredmail.org/doc.html

10 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:

Well, please upgrade iRedMail to the latest stable release first. Upgrade tutorial is available here:
http://www.iredmail.org/doc.html

Can I upgradre iredmail without upgrade iredadmin-Pro?

Or upgrade process can have side effect alson on iredamin?

Tnx.

11 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

posa68 wrote:

Can I upgradre iredmail without upgrade iredadmin-Pro?

Yes.

12 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:
posa68 wrote:

Can I upgradre iredmail without upgrade iredadmin-Pro?

Yes.

Ok, thanks.

Ps.

It's possibile upgrade to latest 0.7.4 directly from 0.7.2?

13 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:
posa68 wrote:

Can I upgradre iredmail without upgrade iredadmin-Pro?

Yes.

I'm sorry, I realize that upgrade process is just a follow process of what is written here:

http://iredmail.org/wiki/index.php?titl … .7.2-0.7.3

And here:

http://iredmail.org/wiki/index.php?titl … .7.3-0.7.4

I'm not sure to understand in the first link the upgrade of iRedAdmin-0.1.6...
I have iredadminPro, so if i do not upgrade open source iredadmin, the upgrade process is limited to upgrade phpmyadmin, roundcube and MySQLbackend?

It's correct?

thanks again and sorry for the stupid questions.

14 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

posa68 wrote:

I'm not sure to understand in the first link the upgrade of iRedAdmin-0.1.6...
I have iredadminPro, so if i do not upgrade open source iredadmin, the upgrade process is limited to upgrade phpmyadmin, roundcube and MySQLbackend?

It's correct?

Yes, you're right.

Since you have iRedAdmin-Pro installed, you can completely remove iRedAdmin open source edition.

15 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:
posa68 wrote:

I'm not sure to understand in the first link the upgrade of iRedAdmin-0.1.6...
I have iredadminPro, so if i do not upgrade open source iredadmin, the upgrade process is limited to upgrade phpmyadmin, roundcube and MySQLbackend?

It's correct?

Yes, you're right.

Since you have iRedAdmin-Pro installed, you can completely remove iRedAdmin open source edition.

Ok, I upgraded to latest version of iredmail (0.7.4), it seems that problem is not completely gone, but shurely mitigated.
There's some other things i must lookup to?

Thanks!

16 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

Well, what's the problem now? It's a little faster than before, but still not fast as excepted? How long does it take?
Did you delete old records in Amavisd database? e.g. table msgs, msgrcpt, quarantine.

17 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:

Well, what's the problem now? It's a little faster than before, but still not fast as excepted? How long does it take?
Did you delete old records in Amavisd database? e.g. table msgs, msgrcpt, quarantine.

Hi Zhang, i returned on this topic because the access to admin page is still too much slow.

Now the server is upgraded to latest v., I have this numbers of records in MySQL amavisd database:

maddr -> 22856 rows
msgrcpt -> 39371 rows
msgs -> 32076

Wich table can I reset?

And generally speaking:
What is the max number of records I must see inside amavisd DB?
How many time can I reset this tables and how can I control it?

Thanks...

18 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

Do you have cron job to delete old records?
Reference: http://www.iredmail.org/forum/post12114.html#p12114

19 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:

Do you have cron job to delete old records?
Reference: http://www.iredmail.org/forum/post12114.html#p12114

No, I don't have this cron job, I'll check it now.

Meantime, which table/records can I reset?

Thanks.

20 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

posa68 wrote:

Meantime, which table/records can I reset?

Major tables are: msgs, msgrcpt, quarantine.

21 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:
posa68 wrote:

Meantime, which table/records can I reset?

Major tables are: msgs, msgrcpt, quarantine.

Thanks, now the dashboard is loaded more quickcly.

I see that statistics is started again, it's normal, right?

22 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

Yes.

In next release of iRedAdmin-Pro, it won't try to remove old records anymore, so it should be quicker.

23 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

ZhangHuangbin wrote:

Yes.

In next release of iRedAdmin-Pro, it won't try to remove old records anymore, so it should be quicker.

Hi,

Sorry if I resume this old post, but the problem on quarantine messages it's happening again.

I don't see the old post on how to make a cron job to delete old messages from mysql, can you describe how to do that or maybe you have an example?

Plus: I have iRedAdminPro 1.5.0 this release don't remove old records?

I try to remove manually in Mysql with this 2 commands:

DELETE FROM msgs WHERE content IN ('S', 's', 'V', 'Y') AND quar_type = 'Q' AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))
--
DELETE msgrcpt.* FROM msgrcpt INNER JOIN msgs ON msgrcpt.mail_id=msgs.mail_id WHERE msgs.content IN ('U', 'M', 'H', 'O', 'C') AND msgs.time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))

Only this second command delete some records, but not so much...

Thanks in advance...

Bye.

24 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: Help!!! Mysql use 99.9% of CPU!!!

iRedAdmin-Pro ships a python script which works with cron job: tools/cleanup_amavisd_db.py.

# USAGE:
#
#   1: Make sure you have correct database settings in iRedAdmin config file,
#      settings.ini, under section [amavisd].
#
#   2: Set proper values of below two parameters in libs/settings_local.py
#      (recommended) or libs/settings.py. For example:
#
#       AMAVISD_REMOVE_MAILLOG_IN_DAYS = 30
#       AMAVISD_REMOVE_QUARANTINED_IN_DAYS = 7
#
#   3: Test this script in command line directly, make sure no errors in output
#      message.
#
#       # python /path/to/cleanup_amavisd_db.py
#
#   4: Setup a cron job to execute this script daily. For example: execute
#      this script at 1:30AM.
#
#       30  1   *   *   *   python /path/to/cleanup_amavisd_db.py >/dev/null
#
# That's all.

25 Reply by posa68

  • posa68
  • Member
  • Offline
  • Registered: 2011-06-30
  • Posts: 111

Re: Help!!! Mysql use 99.9% of CPU!!!

Thank you very much, this helps.
The problem is not completely gone: when i enter in admin interface the page hang out for about 40/50 seconds, but is better than before...

Thanks again.