1 Topic by broth

  • broth
  • Member
  • Offline
  • Registered: 2012-04-22
  • Posts: 145

Topic: Amavis MySQL table cleanup/maintenence

==== Required information ====
- iRedMail version: 0.8.3
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Linux/BSD distribution name and version: Debian 6.0
- Related log if you're reporting an issue:
====

Hello!
My avamis MySQL tables are growing and getting slow, especially the msgs table.
What can be done to optimize these tables or cut them down?
Are there any special maintenance scripts to remove older messages from these table, e.g. all messages older than 30 days?
The iRedAdmin-Pro web interface is getting quite slow, too when logging in.

Maybe there is some regular maintenance job broken on that system?
Please help smile

Best regards,
Bernhard

----

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

2 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

There's a cron job script shipped in iRedAdmin-Pro: tools/cleanup_amavisd_db.py, you can use it to delete older records. Please just open this file and read the top lines for basic usage.

3 Reply by broth

  • broth
  • Member
  • Offline
  • Registered: 2012-04-22
  • Posts: 145

Re: Amavis MySQL table cleanup/maintenence

Hello Zhang,
perfect, working like a charm! Thank you very much!
Great work!
Best regards,
Bernhard

4 Reply by broth

  • broth
  • Member
  • Offline
  • Registered: 2012-04-22
  • Posts: 145

Re: Amavis MySQL table cleanup/maintenence

BTW: Is it intended not to clean up the table amavisd.msgs?
That table has around 484000 rows and the login to iRedMail-Pro does take some time due to the statistics queries.

5 Reply by hata_ph (edited by hata_ph 2013-01-12 10:56:07)

  • hata_ph
  • Member
  • Offline
  • Registered: 2010-01-13
  • Posts: 226

Re: Amavis MySQL table cleanup/maintenence

Sorry for hitchhike this thread but I was wondering it is safe to delete all the data in amavsid.msgs and amavsid.msgrcpt?

I am using below SQL script to delete the data in msgs and msgrcpt but it seem it only delete content IN ('S', 's', 'V', 'Y') AND quar_type = 'Q' data. How about quar_type = 'C'? I got around 4000+ rows of data in msgs table...do i need to clean it too?

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))

http://www.iredmail.org/forum/post12114.html#p12114

6 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

broth wrote:

BTW: Is it intended not to clean up the table amavisd.msgs?
That table has around 484000 rows and the login to iRedMail-Pro does take some time due to the statistics queries.

It's supposed to be deleted with this script (cleanup_amavisd_db.py). There must be a bug if it doesn't do so.

7 Reply by broth

  • broth
  • Member
  • Offline
  • Registered: 2012-04-22
  • Posts: 145

Re: Amavis MySQL table cleanup/maintenence

From how I understand the script it does cleanup quarantined mails only.
This SQL query does remove them

        DELETE FROM msgs
        WHERE
            content IN ('S', 's', 'V', 'Y')
            AND quar_type = 'Q'
            AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))

Following the next queries are removing unreferenced records. Thats fine.

I have many clean messages (content tag C) in my table.
Maybe you could include a command line parameter wether to delete only quarantined mails or anything older than X days:

DELETE FROM msgs
        WHERE time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 60 DAY))

I won't say there's a bug, it's only a missing feature smile
@Zhang: Can you implement the CLI switch and/or the modified query?

8 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

Hi broth,

You can add this sql command in the file directly, or try below patch. Let me know whether it works for you or not.

diff -r 46eeed2e9cf8 tools/cleanup_amavisd_db.py
--- a/tools/cleanup_amavisd_db.py    Mon Jan 07 00:56:38 2013 +0800
+++ b/tools/cleanup_amavisd_db.py    Sat Jan 12 17:49:22 2013 +0800
@@ -98,6 +98,11 @@
         WHERE msgs.time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))
         ''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS
     )
+    conn.query('''
+        DELETE FROM msgs
+        WHERE time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))
+        ''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS
+    )
 elif sql_dbn == 'postgres':
     conn.query('''
         DELETE FROM msgrcpt
@@ -107,6 +112,11 @@
             AND msgs.time_iso < CURRENT_TIMESTAMP - INTERVAL '%d DAYS'
         ''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS
     )
+    conn.query('''
+        DELETE FROM msgs
+        WHERE time_iso < CURRENT_TIMESTAMP - INTERVAL '%d DAYS'
+        ''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS
+    )
 
 # delete unreferenced records from tables msgrcpt, quarantine and maddr
 logger.info('Delete unreferenced records from table msgrcpt.')

9 Reply by broth

  • broth
  • Member
  • Offline
  • Registered: 2012-04-22
  • Posts: 145

Re: Amavis MySQL table cleanup/maintenence

Hello Zhang,
the patch works, thank you!
Maybe you should include a command line switch to select between deleting only quarantined messages or all messages older than x days.
Best regards,
Bernhard

10 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

broth wrote:

Maybe you should include a command line switch to select between deleting only quarantined messages or all messages older than x days.

No plan yet, sorry.

== UPDATE ==
I removed 'content IN ('S', 's', 'V', 'Y') AND' in SQL commands, so that it won't check msg type (banned, spammy, virus, etc).

11 Reply by pang

  • pang
  • Member
  • Offline
  • Registered: 2012-08-08
  • Posts: 32

Re: Amavis MySQL table cleanup/maintenence

Hello Zhang,

Both tables msgs and  msgrcpt of our amavis DB have over 1 million records now and we want to apply the above patch to remove old records older than 30 days but we don't know how to do this.

Could you please explain in details how to make use of the patch such as what file name  we should save it to and how to patch it to the original file "cleanup_amavisd_db.py"? Thank you!

12 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

Hi pang,

Do you have iRedAdmin-Pro? if so, please simply execute file shipped within it:

# cd /path/to/iRedAdmin-Pro/
# python tools/cleanup_amavisd_db.py

It may take a long time if you have many records in 3 tables: msgs, msgrcpt, quarantine.

If you're not running the latest iRedAdmin-Pro, please mail us (support at iredmail dot org) to get download link.

13 Reply by pang

  • pang
  • Member
  • Offline
  • Registered: 2012-08-08
  • Posts: 32

Re: Amavis MySQL table cleanup/maintenence

ZhangHuangbin wrote:

Hi pang,

Do you have iRedAdmin-Pro? if so, please simply execute file shipped within it:

# cd /path/to/iRedAdmin-Pro/
# python tools/cleanup_amavisd_db.py

It may take a long time if you have many records in 3 tables: msgs, msgrcpt, quarantine.

If you're not running the latest iRedAdmin-Pro, please mail us (support at iredmail dot org) to get download link.


Hi Zhang,

We are using iRedAdmin-Pro 1.5 and I have executed python tools/cleanup_amavisd_db.py but it did not cleanup old records in table "msgs", there were over one million records there, thanks.

14 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

This script was buggy in iRedAdmin-Pro-MySQL-1.6.0 and earlier versions, please upgrade to the latest iRedAdmin-Pro-MySQL-1.7.0 and try again. So sorry about this trouble.

Also, if you don't need these mail log at all, you can delete all records in all tables in Amavisd database, except table "quarantine" (you'd better review them with iRedAdmin-Pro before releasing or deleting them).

15 Reply by pang

  • pang
  • Member
  • Offline
  • Registered: 2012-08-08
  • Posts: 32

Re: Amavis MySQL table cleanup/maintenence

ZhangHuangbin wrote:

This script was buggy in iRedAdmin-Pro-MySQL-1.6.0 and earlier versions, please upgrade to the latest iRedAdmin-Pro-MySQL-1.7.0 and try again. So sorry about this trouble.

Also, if you don't need these mail log at all, you can delete all records in all tables in Amavisd database, except table "quarantine" (you'd better review them with iRedAdmin-Pro before releasing or deleting them).


Hi Zhang,

Ok, thanks. I'll upgrade our iredadmin-Pro from 1.5.0 to 1.7. But when I check upgrade guidelines, I only see upgrade instructions from 1.5.1 to 1.6 then 1.6 to 1.7.  How about upgrade from 1.5.0 to 1.5.1?

16 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

pang wrote:

How about upgrade from 1.5.0 to 1.5.1?

As mentioned in the wiki: "You can upgrade iRedAdmin-Pro-MySQL-1.1.0 or later versions to 1.7.0 directly by following upgrade tutorial for v1.2.0 or later versions."

So, you can upgrade iRedAdmin-Pro-MySQL-1.5.0 to 1.7.0 directly. Please upgrade iRedMail to the latest stable release first.

17 Reply by pang (edited by pang 2013-09-26 17:35:34)

  • pang
  • Member
  • Offline
  • Registered: 2012-08-08
  • Posts: 32

Re: Amavis MySQL table cleanup/maintenence

ZhangHuangbin wrote:
pang wrote:

How about upgrade from 1.5.0 to 1.5.1?

As mentioned in the wiki: "You can upgrade iRedAdmin-Pro-MySQL-1.1.0 or later versions to 1.7.0 directly by following upgrade tutorial for v1.2.0 or later versions."

So, you can upgrade iRedAdmin-Pro-MySQL-1.5.0 to 1.7.0 directly. Please upgrade iRedMail to the latest stable release first.


Thanks Zhang. BTW, I have already truncated most tables of the amavisd DB, but the top command still shows the amavisd processes cpu usage often goes over 50%, is this normal?
Is there any way we may tune the performance to lower amavisd processes cpu usage? Thanks!

18 Reply by ZhangHuangbin

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

Re: Amavis MySQL table cleanup/maintenence

pang wrote:

but the top command still shows the amavisd processes cpu usage often goes over 50%, is this normal?

Is it a busy mail server?

Amavisd is configured to log basic info of in/out emails in SQL server, and it will invoke SpamAssassin for content-based spam scanning, invokes ClamAV for virus scanning.

19 Reply by pang

  • pang
  • Member
  • Offline
  • Registered: 2012-08-08
  • Posts: 32

Re: Amavis MySQL table cleanup/maintenence

ZhangHuangbin wrote:
pang wrote:

but the top command still shows the amavisd processes cpu usage often goes over 50%, is this normal?

Is it a busy mail server?

Amavisd is configured to log basic info of in/out emails in SQL server, and it will invoke SpamAssassin for content-based spam scanning, invokes ClamAV for virus scanning.



Ok, the mail server has 700 email accounts. So it means the two amavisd processes cpu usage also includes SpamAssassin and ClamAV's cpu usage.
Thanks Zhang.