1

Topic: Slow loading of dashboard

Hello Zhang,

The loading of the dasboard takes very long.
Because of this it sometimes takes up to one minute for logging in.

When loading the dashboard I see that the mysqld proccess takes 100% cpu of one core.

Our system has 2 dual core intel Xeons with hyper-threading.

Is there any way to fix this?

With kind regards,

Luc Verhoeven

2

Re: Slow loading of dashboard

It must be caused by SQL queries in iRedAdmin-Pro, such as missing indexes. May i know how many records you have in MySQL table "amavisd.msgs"?

Another customer reported this about week ago, but my hotfix didn't work. I will dig it tonight, but i don't have similar environment to test it. Can you help test? Such as create addition SQL indexes, etc. I know you're running on a production server.

3 (edited by mike.f 2011-09-24 05:10:27)

Re: Slow loading of dashboard

hi all

same for me - mysqld spikes to 100%
using
# iRedAdmin-Pro-LDAP-1.6.2
# debian 6 - x64

amavisd.msgs is at 9,318

already tried to optimize all dbs but still that spike at login

Greetings

4

Re: Slow loading of dashboard

Trying to upgrade to 1.3.0 MySQL and logging on is almost at a standstil.  I reverted back to 1.2.1.
Simply accessing the login page is no problem, but loggin in it just sits there and eventually times out.  I have nothing in the quarantine.

5

Re: Slow loading of dashboard

There must be SQL performance issue in iRedAdmin-Pro SQL commands, still working on it.
So sorry about this trouble.

If it's unacceptable, please temporarily reverse to older version.

6 (edited by peterkorsuize 2011-09-28 19:00:27)

Re: Slow loading of dashboard

I've been experiencing the same issue with 1.3.0 MySQL. It does not always seem to happen. I suspect it only happens the first time you log in after a certain period of inactivity. Is there perhaps a process that updates statistics upon login at regular intervals?

I have switched on slow query logging (SET GLOBAL slow_query_log = 'ON';) now in the hope that I can catch the offending query in due time. Once I find it I'll post it here.

UPDATE. I noticed in a different thread that someone had found the SQL yet. But I'll post it here anyway to be complete
# Query_time: 9.193398  Lock_time: 0.000117 Rows_sent: 0  Rows_examined: 3968699
use amavisd;
SET timestamp=1317206960;
DELETE FROM msgrcpt WHERE mail_id IN (SELECT mail_id FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C') AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
                );

It definitely looks like an index is missing or not used (consider the rows examinded in a very under-utilized mail server)

7 (edited by smartdrive 2011-10-28 21:04:37)

Re: Slow loading of dashboard

I have the same issue with iRedMail Pro 1.3.0 Mysql on CentOS 5.7 x86_64.
After a research, I found the same query :

SET timestamp=1319805423;
DELETE FROM msgrcpt WHERE mail_id IN (SELECT mail_id FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C')                         AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)));

Sometimes, when the query is so long, I have a mysql error, #1205 - Lock wait timeout exceeded; try restarting transaction, and the mysqld proccess takes 100% cpu of one core.

Some extra-note :
avamisd.msgrcpt.mail_id INDEX 88545
amavisd.msgs.mail_id INDEX 87353

8

Re: Slow loading of dashboard

So sorry about this issue.

Here's a solution, it should work as expected:
http://www.iredmail.org/forum/post11991.html#p11991

Please don't forget to let us know whether it works for you or not. Thanks for your patient and feedback. smile

9 (edited by smartdrive 2011-10-29 07:42:14)

Re: Slow loading of dashboard

Hello,

Thanks for the fast reply.

When I try the patch, this is the result :

[root@server iRedAdmin-Pro-MySQL-1.3.0]# patch -p4 --dry-run < /root/delete_amavisd_db.patch
patching file libs/amavisd/log.py
Hunk #1 FAILED at 38.
1 out of 1 hunk FAILED -- saving rejects to file libs/amavisd/log.py.rej

I read the patch and I think this is for LDAP because I don't find the same function used :
patch : self.db.query
in libs/amavisd/log.py : I found self.db.delete

---

EDIT :

I created a new index :

mysql> ALTER TABLE msgs ADD INDEX msgs_idx_time_content (`time_num`,`content`);

And now :

mysql> EXPLAIN SELECT 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 30 DAY));
+----+-------------+---------+-------+----------------------------------------------------------+-----------------------+---------+----------------------+-------+--------------------------+
| id | select_type | table   | type  | possible_keys                                            | key                   | key_len | ref                  | rows  | Extra                    |
+----+-------------+---------+-------+----------------------------------------------------------+-----------------------+---------+----------------------+-------+--------------------------+
|  1 | SIMPLE      | msgs    | range | msgs_idx_time_num,msgs_idx_content,msgs_idx_time_content | msgs_idx_time_content | 4       | NULL                 | 35856 | Using where; Using index |
|  1 | SIMPLE      | msgrcpt | ref   | mail_id                                                  | mail_id               | 18      | amavisd.msgs.mail_id |     1 |                          |
+----+-------------+---------+-------+----------------------------------------------------------+-----------------------+---------+----------------------+-------+--------------------------+
2 rows in set (0.00 sec)

So, I edited libs/amavisd/log.py

#self.db.delete(
                #    'msgrcpt',
                #    where='''mail_id IN (SELECT mail_id FROM msgs WHERE %s)''' % sql,
                #)
                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,)

                #self.db.delete(
                #    'msgs',
                #    where='%s' % sql,
                #)

                self.db.delete(
                    'msgs',
                    where='''content IN ('U', 'M', 'H', 'O', 'C') \
                        AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS,
                )

Now, it works but the query :

DELETE FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C') AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));

has no index :

mysql> EXPLAIN SELECT * FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C') AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys                                            | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | msgs  | ALL  | msgs_idx_time_num,msgs_idx_content,msgs_idx_time_content | NULL | NULL    | NULL | 88658 | Using where |
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+-------+-------------+
1 row in set (0.12 sec)

It tooks me many second to log me in iRedAdmin.