1

Topic: iredadmin pro 1.3.1 sql login very slow

- Linux/BSD distribution name and version:
Debian
- iRedMail version and backend (LDAP/MySQL):
iRedmail 0.7.4
iRedAdmin-Pro-MySQL-1.3.1
mysql-server-5.1 5.1.49-3

It takes about 10 minutes to open, once open it is fine.

2

Re: iredadmin pro 1.3.1 sql login very slow

Did you upgrade iRedMail from old version to 0.7.4? Some addition SQL indexes should be created for iRedMail-0.7.3 and earlier versions. SQL commands can be found in upgrade tutorial (0.7.3 -> 0.7.4):
http://iredmail.org/wiki/index.php?titl … .7.3-0.7.4

Please make sure you applied all upgrade steps by following upgrade tutorials STRICTLY.

3 (edited by darth_wells 2012-02-29 03:09:28)

Re: iredadmin pro 1.3.1 sql login very slow

Yes, I did upgrade from 0.7.3

I rechecked the settings, and now it takes about 6 minutes to open the mysql pro  panel with IE.

Firefox takes about 30 minutes

Suggestions:   Places to check?

4

Re: iredadmin pro 1.3.1 sql login very slow

It may be caused by low performance of some SQL queries. Did you add missed SQL indexes by following the upgrade tutorial?

5

Re: iredadmin pro 1.3.1 sql login very slow

Yes, I did not do mysql backend special & dovecot options, which I did do on 2/28

Dovecot options done
Add INDEXes for Amavisd database was already done
[Debian and Ubuntu special] Assign Apache daemon user to group "adm"  was already done
OpenLDAP backend special - not done - n/a
MySQL backend special  done

Do I need to reboot the server?  or anything else Apache?

Update: 3/13/2012
Seems to be getting worse, back up to 15 minutes in IE

6

Re: iredadmin pro 1.3.1 sql login very slow

How many records do you have in each tables of MySQL database "amavisd"? Do you have cron job to delete old records?

7 (edited by darth_wells 2012-03-15 03:21:52)

Re: iredadmin pro 1.3.1 sql login very slow

maddr        ~160,271 InnoDB utf8_general_ci 58.3 MiB -
mailaddr        0 MyISAM utf8_general_ci 2.0 KiB -
msgrcpt        ~306,258 InnoDB utf8_general_ci 80.6 MiB -
msgs        ~695,309 InnoDB utf8_general_ci 539.7 MiB -
policy        0 MyISAM utf8_general_ci 1.0 KiB -
quarantine        0 InnoDB utf8_general_ci 32.0 KiB -
users        0 MyISAM utf8_general_ci 2.0 KiB -
wblist        0 MyISAM utf8_general_ci 1.0 KiB -
8 table(s)  Sum ~1,161,838 MyISAM utf8_general_ci 678.6 MiB 0 B


1,161,838

8

Re: iredadmin pro 1.3.1 sql login very slow

~1,161,838

How do I setup a cleaning job for sql?

I know how to put in cron.

9

Re: iredadmin pro 1.3.1 sql login very slow

darth_wells wrote:

msgrcpt        ~306,258 InnoDB utf8_general_ci 80.6 MiB -
msgs        ~695,309 InnoDB utf8_general_ci 539.7 MiB -

Hmm, so many records in these two tables.

You can find below settings in libs/settings.py under iRedAdmin-Pro-MySQL:

###################################
# Amavisd related settings.
#

# Automatically remove SQL records of sent/received mails in Amavisd database
# when viewing sent/received mails. Only one time in each login session.
# Default is 90 days. Set to 0 to keep them forever.
AMAVISD_REMOVE_MAILLOG_IN_DAYS = 30

# Automatically remove SQL records of quarantined mails which older than
# specified days when list quarantined mails. Only one time in each login
# session.
# Since quarantined mails may take much disk space, it's better to release
# or remove them as soon as possible.
# Default is 30 days. Set to 0 to keep them forever.
AMAVISD_REMOVE_QUARANTINED_IN_DAYS = 7

If you don't need to store them for a long time, please try to reduce these two settings. For example, set 'AMAVISD_REMOVE_MAILLOG_IN_DAYS = 7' (one week). Then each time you login to iRedAdmin-Pro and access "System -> Sent Mails" or "System -> Received Mails", old records will be deleted automatically.

Another way is setting up cronjob to delete old records. Reference: http://www.iredmail.org/forum/post12114.html#p12114

10 (edited by darth_wells 2012-03-16 01:32:42)

Re: iredadmin pro 1.3.1 sql login very slow

from mysql prompt i ran

mysql>
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 3 DAY));

Is this the right command?

reduced to:
msgrcpt to            38,508
msgs                   640,945

is msgrcpt - messages received?
is msgs     - messages sent?

How do I get msgs to come down?

11

Re: iredadmin pro 1.3.1 sql login very slow

I don't understand how to put this into a cron job?

how does it know how to connect mysql?

================
// First one, used to delete quarantined mails which older than 7 days.
// Change "7 DAY" to whatever you want, e.g. 10, means delete mails older than 10 days.

DELETE FROM msgs WHERE content IN ('S', 's', 'V', 'Y') AND quar_type = 'Q' AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))

// Delete old data in table msgs and msgrcpt.

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 7 DAY))

12

Re: iredadmin pro 1.3.1 sql login very slow

Create a shell script to connect to MySQL server and perform these two commands, then add cron job to execute this shell script.

13

Re: iredadmin pro 1.3.1 sql login very slow

darth_wells wrote:

reduced to:
msgrcpt to            38,508
msgs                   640,945

Still so many records in table 'msgs'.

darth_wells wrote:

is msgrcpt - messages received?
is msgs     - messages sent?

Not exactly.
Refer to Amavisd official doc for more details: http://www.amavis.org/README.sql-mysql.txt

14 (edited by answerman 2012-06-20 05:10:14)

Re: iredadmin pro 1.3.1 sql login very slow

ZhangHuangbin wrote:
darth_wells wrote:

reduced to:
msgrcpt to            38,508
msgs                   640,945

Still so many records in table 'msgs'.

darth_wells wrote:

is msgrcpt - messages received?
is msgs     - messages sent?

Not exactly.
Refer to Amavisd official doc for more details: http://www.amavis.org/README.sql-mysql.txt

I've been trying to get my records reduced as well and it doesn't appear to be accomplishing much.  I have over 600,000 records in each table (msgrcpt and msgs) and running the DELETE command only reduced each by about 30,000.  Is it because I added the index "ADD INDEX msgs_idx_content (`content`)" after the records had already accumulated?

I know that upgrading to 1.4 from 1.3 will solve the slow login, but I'd still like to delete these old records.  There are less than 200,000 messages stored on the server, so these numbers seem rather high to me.