1 Topic by broth

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

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

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 8,121

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: 19

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: 19

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: 195

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
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 8,121

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: 19

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
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 8,121

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: 19

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
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 8,121

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