Log purging in Atmail made easy

PROBLEM

My log files are very large.

ENVIRONMENT

• On-Premise Server + WebMail Installations: Version 6.0 > Current Version
• Webmail Only Installations: Version 6.0 > Current Version

CAUSE

When using Atmail in production, its wise to setup a script to automate purging of logs for performance. Atmail records all user logins ( Webmail, POP3/IMAP ) , SMTP transactions ( Send / Received ) , Spam ( RBL, DKIM, Spamassassin ) and Virus logs.

If you have a production system in use for sometime, your logs may have millions of rows and using valuable memory/CPU resources via MySQL. While you can use the Atmail Webadmin and see graphs for logs over 1 year+, this can effect the performance of Atmail if your hardware and DB are not optimized.

RESOLUTION

1. We recommend the following setup via Cron, create the file below:
   /etc/cron.weekly/purge-atmail-logs.sh
   

   #!/bin/sh
   mysql -u[username] -p[password] atmaildbname < /usr/local/atmail/purge-atmail-logs.sql

2. Save the file and set permissions.

   chmod 755 /etc/cron.weekly/purge-atmail-logs.sh

3. Next, create the SQL query, change the date range as per your needs.
   /usr/local/atmail/purge-atmail-logs.sql

   delete from Log_Error where LogDate < DATE_SUB(NOW(), INTERVAL 4 MONTH);
   delete from Log_Login where LogDate < DATE_SUB(NOW(), INTERVAL 4 MONTH);
   delete from Log_RecvMail where LogDate < DATE_SUB(NOW(), INTERVAL 4 MONTH);
   delete from Log_SendMail where LogDate < DATE_SUB(NOW(), INTERVAL 4 MONTH);
   delete from Log_Spam where LogDate < DATE_SUB(NOW(), INTERVAL 4 MONTH);
   delete from Log_Virus where LogDate < DATE_SUB(NOW(), INTERVAL 4 MONTH);
    
   optimize table Log_Error;
   optimize table Log_Login;
   optimize table Log_RecvMail;
   optimize table Log_SendMail;
   optimize table Log_Spam;
   optimize table Log_Virus;

4. Save, then Cron will automatically purge logs older then 4 months, each week.

5. Try it out on your server

   /etc/cron.weekly/purge-atmail-logs.sh

6. Tweak as per your requirements, and remember to optimize your MySQL setup for the Atmail database needs.