1

Topic: iRedAdmin extreme slow login.

==== Required information ====
- iRedMail version:0.8.4 and v1.6.0
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):  Mysql
- Linux/BSD distribution name and version:  Centos 6.4
- Related log if you're reporting an issue:
====
Hello,

When i login to the iredadmin pro interface it takes a couple minutes to login.
I think that the problem is a giant quarantine amavisd sql table.
897 MB and 60852 rows
Do i need to clear the database manually?

Thanks

Jef

2

Re: iRedAdmin extreme slow login.

Resolved here http://www.iredmail.org/forum/topic5359 … login.html

whaw wrote:

==== Required information ====
- iRedMail version:0.8.4 and v1.6.0
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):  Mysql
- Linux/BSD distribution name and version:  Centos 6.4
- Related log if you're reporting an issue:
====
Hello,

When i login to the iredadmin pro interface it takes a couple minutes to login.
I think that the problem is a giant quarantine amavisd sql table.
897 MB and 60852 rows
Do i need to clear the database manually?

Thanks

Jef

3 (edited by whaw 2013-09-04 16:41:51)

Re: iRedAdmin extreme slow login.

Hello labasus,

Your link points to this post :-)

Thanks

Jef

4

Re: iRedAdmin extreme slow login.

I am running python /var/www/iredadmin/tools/cleanup_amavisd_db.py for the moment.
It takes very long, i hope its helping.
Its running for 2h30m + Delete from table `msgs`.

5

Re: iRedAdmin extreme slow login.

Please upgrade to the latest version, cause some indexes can be missed in DB...

6

Re: iRedAdmin extreme slow login.

Hi whaw,

Script 'tools/cleanup_amavisd_db.py' was improved in the latest iRedAdmin-Pro, it's recommended to upgrade your iRedMail and iRedAdmin-Pro to the latest stable release, and try 'tools/cleanup_amavisd_db.py' again, it deletes more records (correctly).

As @labasus mentioned, iRedMail creates some addition SQL indexes for Amavisd database, so please follow our upgrade tutorials and add them if it's mentioned in upgrade tutorial.

7

Re: iRedAdmin extreme slow login.

Hello,

My mail server is now up to date.
After running the script the database is still the same size.
msgs 121MB
quarantine 900MB

[root@mail tools]# python cleanup_amavisd_db.py
* [2013-09-05 17:18:56] Loading config file: /var/www/iRedAdmin-Pro-MySQL-1.7.0/tools/../settings.ini
* [2013-09-05 17:18:56] Backend: mysql
* [2013-09-05 17:18:56] Remove records of quarantined mails older than 7 days.
* [2013-09-05 17:18:56] Remove records of sent/received mails older than 7 days.
* [2013-09-05 17:18:56] Delete quarantined mails which older than 7 days
75.45 (1):
        DELETE FROM msgs
        WHERE
            quar_type = 'Q'
            AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))
       
* [2013-09-05 17:20:13] Delete incoming/outgoing emails which older than 7 days
* [2013-09-05 17:20:13] + Delete from table `msgrcpt`.
341.6 (2):
        DELETE msgrcpt.*
        FROM msgrcpt
        INNER JOIN msgs ON msgrcpt.mail_id=msgs.mail_id
        WHERE msgs.time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))
       
* [2013-09-05 17:25:55] + Delete from table `msgs`.
33684.16 (3):
        DELETE FROM msgs
        WHERE time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))
       
* [2013-09-06 02:47:20] Delete unreferenced records from table `msgrcpt`.
704.45 (4):
    DELETE FROM msgrcpt
    WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id)
   
* [2013-09-06 02:59:04] Delete unreferenced records from table `quarantine`.
6586.28 (5):
    DELETE FROM quarantine
    WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id)
   
* [2013-09-06 04:48:53] Delete unreferenced records from table `maddr`.
7200.59 (6):
    DELETE FROM maddr
    WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id)
        AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id)

Can i just delete the table contents in another program without any consequences?

Thx
Greets

Jef

8

Re: iRedAdmin extreme slow login.

whaw wrote:

* [2013-09-05 17:18:56] Remove records of quarantined mails older than 7 days.
* [2013-09-05 17:18:56] Remove records of sent/received mails older than 7 days.

Is it a busy server? if you don't really care about the mail log, how about set it to keep, for example, 3 days? or even shorter, 1 day? You can set it in file 'libs/settings.py' under iRedAdmin-Pro directory.

whaw wrote:

[Can i just delete the table contents in another program without any consequences?

Yes. It's just basic log info of emails, it's not the real email message stored on file system.

9

Re: iRedAdmin extreme slow login.

After a couple of days the tables are clean.
I enabled the cron job to maintain the tables clean.
Thx