==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 1.3.2
- Deployed with iRedMail Easy or the downloadable installer? NO
- Linux/BSD distribution name and version: Redhat 7.8
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.

Whenever I try to login into the admin panel, I usually get a 504 Gateway Timeout error from (nginx), reloading the page usually solves the problem but I still need to wait up to 40 seconds to get into the admin panel.

A fast htop inspection during logon, reveals a high MySQL processor usage during long time periods.

Enabling MySQL slow query I can't find that on logon time, iRedAdmin dashboard page make two queries that cause up to 45 seconds delay to load admin page, this long time periods cause the 504 gateway timeout error:

/usr/sbin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: (null)
Time                 Id Command    Argument
# Time: 2021-07-24T00:49:38.728593Z
# User@Host: amavisd[amavisd] @ localhost [127.0.0.1]  Id:    18
# Query_time: 23.753328  Lock_time: 0.000173 Rows_sent: 1  Rows_examined: 2265161
use amavisd;
SET timestamp=1627087778;
-- Get number of outgoing mails.
                              SELECT COUNT(msgs.mail_id) AS total
                              FROM msgs
                              RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                              RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                              RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                              WHERE msgs.quar_type <> 'Q'  AND sender.domain IN ('com.my.domain') AND msgs.time_num > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 86400 SECOND));


# Time: 2021-07-24T00:50:02.383284Z
# User@Host: amavisd[amavisd] @ localhost [127.0.0.1]  Id:    18
# Query_time: 23.652849  Lock_time: 0.000105 Rows_sent: 10  Rows_examined: 2259143
SET timestamp=1627087802;
-- Get top 10 senders.
                SELECT COUNT(msgs.mail_id) AS total,
                       sender.email_raw AS mail,
                       msgs.policy AS policy
                  FROM msgs
                 RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                 WHERE 1=1  AND sender.domain IN ('com.my.domain') AND msgs.time_num > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 86400 SECOND))
                 GROUP BY mail, policy
                 ORDER BY total DESC
                 LIMIT 10;

I verified that the DB cleanup script ran every day (cleanup_amavisd_db.py & cleanup_db.py) with a 7 day data retention period, however I'm still getting over 2 million records inside the msgs table of amavis DB.

After more than 3 years of a nice & fully functional deployment a simple table optimizations solve this particular problem, the command to execute is:

Make sure to ran this MySQL optimization on a maintenance windows, because of locking tables nature of the command. This can in potentially impact your users experience if you run the command on a live production server:

mysql -u root amavisd -e "optimize table maddr, mailaddr, msgrcpt, msgs, outbound_wblist, policy, quarantine, users, wblist;"

In my case the command take up to 4 minutes to accomplish and no one service reboot it's required.

After this simple maintenance, admin panel once again works like a brand new installation!

Hopefully this can help other users, best regards!

====