26

Re: login to iRedAdmin-Pro is too slow

Hi @borthner,

Thanks very much for your feedback, i will test it and commit into the iRedAdmin-Pro. smile

27 (edited by M Martinatti 2011-10-20 01:31:53)

Re: login to iRedAdmin-Pro is too slow

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

Post's attachments

SQL_amavis.jpg
SQL_amavis.jpg 88.6 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

28

Re: login to iRedAdmin-Pro is too slow

M Martinatti wrote:

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?

It should work as expected as a cron job.

I just want to let iRedAdmin-Pro customers do less, and all are handled in iRedAdmin-Pro automatically and transparently.
Currently, the problem is terrible performance of the MySQL command used in iRedAdmin-Pro (DELETE), will try to fix it in next release.
Also, may i contact you to help test this fix?

29

Re: login to iRedAdmin-Pro is too slow

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

30 (edited by wayneliao38 2011-10-24 17:12:47)

Re: login to iRedAdmin-Pro is too slow

ZhangHuangbin wrote:

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

==== Provide basic information to help troubleshoot ====
- iRedMail version:
- Linux/BSD distribution name and version:
- Any related log? Log is helpful for troubleshooting.
====

An error when runnung Alert Tables... command

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

31

Re: login to iRedAdmin-Pro is too slow

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

32

Re: login to iRedAdmin-Pro is too slow

Hi Martinatti,

You can define a DAY in file "libs/settings.py" in setting "AMAVISD_REMOVE_MAILLOG_IN_DAYS".
It's 7 days by default, you can change it to whatever you want, e.g. 10 days.

AMAVISD_REMOVE_MAILLOG_IN_DAYS = 10

33

Re: login to iRedAdmin-Pro is too slow

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.

34

Re: login to iRedAdmin-Pro is too slow

New version of iRedAdmin-Pro won't try to cleanup quarantined mails, so it should be fine.
Thanks very much for your feedback. smile