Hello Zhang,

I think the slowness is the size of the tables msgrcpt and also the amount of records in the database in MySQL amavis

In my case see the values :

-> Table msgrcpt: 1,626,486 records -> 426MB
-> Table msg: 1,723,211 records -> 1.2 GB

If we implement a routine cleanup of these tables keeping only the last two or three days would not solve the problem? I see no need to keep 30 days of registration.

Perhaps using the instructions:

echo "DELETE msgrcpt msgrcpt .* FROM INNER JOIN Message USING (mail_id) msgs.content WHERE IN ('U', 'M', 'H', 'O', 'C') AND time_num <UNIX_TIMESTAMP (DATE_SUB (NOW (), INTERVAL 2 DAY)) "| mysql-u root-p amavisd

echo "DELETE FROM msgs WHERE msgs.content IN ('U', 'M', 'H', 'O', 'C') AND time_num <UNIX_TIMESTAM

Do you think this could solve?

Tks,

Marcelo Martinatti==== Provide basic information to help troubleshoot ====
- iRedMail version: iRedMail-0.7.3 / iRedAdmin-Pro-MySQL-1.3.0
- Linux/BSD distribution name and version: Debian Squeeze 64
- Any related log? Log is helpful for troubleshooting.
====

Dear all,

Here's patch and steps to fix this terrible MySQL performance issue:
http://www.iredmail.org/forum/post11991.html#p11991

Please reply this forum post to share with us whether it works for you or not.

Thanks very much for all guys who contributed solutions and help test. really appreciate your help.

Hi Zhang,

I've applied the patch. Please, I am with the following question: this function is deleting records from the database amavisd after how long (how many days?)

    def deleteRecordsOlderThanDays(self, days=0):
        # Delete old sent/received mails from table amavisd.msgs, amavisd.msgrcpt.
        if days > 0 and session.get('RemovedSentReceived') is not True:
            try:
                # msgs.content:
                #   - U: unchecked
                #   - M: bad-mime
                #   - H: bad-header
                #   - O: oversized
                #   - C: clean

                self.db.query('''
                    DELETE msgrcpt.* FROM msgrcpt
                    INNER JOIN msgs ON msgrcpt.mail_id=msgs.mail_id
                    WHERE msgs.content IN ('U', 'M', 'H', 'O', 'C')
                        AND msgs.time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))
                    ''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS
                )
                session['RemovedSentReceived'] = True
            except:
                pass

        # Always return True.
        return True

There is the possibility of defining a specific number of days, depending on the needs of each host? Even manually?

Tks,

Marcelo Martinatti

Hey Zhang,

I found another similar problem related to this.

on my iredmail0.7.4-rc3 test servers cluster running iredadmin pro mysql 1.3,
everytime I visit the dashboard page, it takes a long time to load when there's over 100K received/sent (had over 800Kreceived during 1hr mailbomb test) emails in to past 24hr statistic.

I've disabled spam/virus quarantine during this test, so there's shouldn't be any amavisquarantinelog cleanup needed

Most likely this is caused by the top10 sender/recipient query code.
Other pages run smoothly, only the dashboard takes almost 3 minutes to load.