26 Reply by ZhangHuangbin

  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 7,931

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

Did you find any record in database "amavisd" older than days set in AMAVISD_REMOVE_MAILLOG_IN_DAYS?

27 Reply by posa68

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

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

ZhangHuangbin wrote:

Did you find any record in database "amavisd" older than days set in AMAVISD_REMOVE_MAILLOG_IN_DAYS?

I'm sorry for the lag but I see your reply just now...

I'm not sure I understand what you mean and how to do it...

28 Reply by ZhangHuangbin

  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 7,931

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

posa68 wrote:

I'm not sure I understand what you mean and how to do it...

For example, if you set 'AMAVISD_REMOVE_MAILLOG_IN_DAYS = 7' to keep old records for 7 days, is there any records older than 7 days after you execute the script to clean up database records?

29 Reply by posa68

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

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

ZhangHuangbin wrote:
posa68 wrote:

I'm not sure I understand what you mean and how to do it...

For example, if you set 'AMAVISD_REMOVE_MAILLOG_IN_DAYS = 7' to keep old records for 7 days, is there any records older than 7 days after you execute the script to clean up database records?

I think the script does'n clear the db, I have too many records inside amavisd:

maddr -> 79340 rows -> 14.1 MB
msgcrpt -> 90567 rows -> 20 MB
msgs -> 268087 rows -> 128.3 MB

How can I safely delete all unnecessary records?

Thanks...

30 Reply by ZhangHuangbin

  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 7,931

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

There are two time related columns in `amavisd.msgs`: time_num, time_iso, you can delete old records based on them.

Note: `msgrcpt.mail_id` = `msgs.mail_id`.

31 Reply by posa68 (edited by posa68 2012-12-21 00:20:41)

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

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

ZhangHuangbin wrote:

There are two time related columns in `amavisd.msgs`: time_num, time_iso, you can delete old records based on them.

Note: `msgrcpt.mail_id` = `msgs.mail_id`.

And if I wish to remove ALL this records?
Can I use statement "truncate tablename" ?

Thanks.

32 Reply by ZhangHuangbin

  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 7,931

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

posa68 wrote:

Can I use statement "truncate tablename" ?

Yes you can.

33 Reply by melaleuca5

  • melaleuca5
  • Member
  • Offline
  • Registered: 2011-10-28
  • Posts: 37

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

When running the script I get
Lock wait timeout exceeded; in the logs, any idea how to fix this

34 Reply by melaleuca5

  • melaleuca5
  • Member
  • Offline
  • Registered: 2011-10-28
  • Posts: 37

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

melaleuca5 wrote:

When running the script I get
Lock wait timeout exceeded; in the logs, any idea how to fix this

Looked like i have about 1.4 gig of logs so I truncate the tables manually seems like it works now.