A NOTE has been added to this issue.
======================================================================
http://dbmail.org/mantis/view.php?id=836
======================================================================
Reported By: waza123
Assigned To:
======================================================================
Project: DBMail
Issue ID: 836
Category: IMAP daemon
Reproducibility: always
Severity: minor
Priority: normal
Status: new
target:
======================================================================
Date Submitted: 28-Jan-10 00:24 CET
Last Modified: 29-Jan-10 09:13 CET
======================================================================
Summary: Slow query..
Description:
And again, IMAPD is too slow..
Here is logs from mysql
# Time: 100127 11:48:47
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 133 Lock_time: 0 Rows_sent: 0 Rows_examined: 16077033
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 5969 AND m.message_idnr
BETWEEN 886017 AND 889972 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
# Time: 100127 11:50:04
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 97 Lock_time: 0 Rows_sent: 0 Rows_examined: 16077033
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 5969 AND m.message_idnr
BETWEEN 886017 AND 889972 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
# Time: 100127 20:42:34
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 106 Lock_time: 0 Rows_sent: 0 Rows_examined: 16132997
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 8762 AND m.message_idnr
BETWEEN 847922 AND 889477 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
# Time: 100128 1:15:52
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 103 Lock_time: 0 Rows_sent: 0 Rows_examined: 16150734
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 24 AND m.message_idnr BETWEEN
317320 AND 320980 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
latest git. Server no load 1:20am at night.
A bit info about mailbox:
It's my stress test mailbox, it's receive a large amount of spam.
Unread inbox messages: 707
Unread spam messages: 13793
Mailbox size: 209 MB
What i do is:
Select spam box, then go to Page: 900 of 1380
And here you go, very slow query.
Advice ? Fix ?
Thanks.
======================================================================
----------------------------------------------------------------------
(0003005) waza123 (reporter) - 28-Jan-10 00:27
http://dbmail.org/mantis/view.php?id=836#c3005
----------------------------------------------------------------------
And about tables:
dbmail_header = 7,974,522 rows
dbmail_messages = 315,591 rows
dbmail_headervalue = 2,285,067 rows
----------------------------------------------------------------------
(0003009) paul (administrator) - 28-Jan-10 14:12
http://dbmail.org/mantis/view.php?id=836#c3009
----------------------------------------------------------------------
Could you please test if using explicit LEFT JOIN would improve the
response time?
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
LEFT JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id LEFT JOIN
dbmail_headername n ON h.headername_id=n.id LEFT JOIN dbmail_headervalue v
ON h.headervalue_id=v.id WHERE m.mailbox_idnr = 24 AND m.message_idnr
BETWEEN 317320 AND 320980 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
----------------------------------------------------------------------
(0003010) waza123 (reporter) - 28-Jan-10 15:28
http://dbmail.org/mantis/view.php?id=836#c3010
----------------------------------------------------------------------
Tell where to change it, in which cpp file and which line ?
----------------------------------------------------------------------
(0003011) waza123 (reporter) - 29-Jan-10 00:21
http://dbmail.org/mantis/view.php?id=836#c3011
----------------------------------------------------------------------
found , dbmail-imapsession.c , str: 648
will test now..
----------------------------------------------------------------------
(0003012) waza123 (reporter) - 29-Jan-10 00:25
http://dbmail.org/mantis/view.php?id=836#c3012
----------------------------------------------------------------------
ok, works.. it's now faster, but not enough
# Time: 100129 1:22:20
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 21966
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
LEFT JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id LEFT JOIN
dbmail_headername n ON h.headername_id=n.id LEFT JOIN dbmail_headervalue v
ON h.headervalue_id=v.id WHERE m.mailbox_idnr = 24 AND m.message_idnr
BETWEEN 441428 AND 446398 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
----------------------------------------------------------------------
(0003013) paul (administrator) - 29-Jan-10 09:13
http://dbmail.org/mantis/view.php?id=836#c3013
----------------------------------------------------------------------
Query time dropped from around 100 seconds to just 3 seconds! Sounds like a
massive improvement to me, especially given the size of the mailbox. If you
need more speedups you will most likely need to tweak your my.cnf. What
kind of hardware are you running on (disks, ram, cpu).
I'm kind of surprised to see 0 rows were returned in all cases. It's
possible of course, especially since those are spam messages. What happens
when you run that query manually, and perhaps add 'to','subject' and 'from'
to the list of headers?
Issue History
Date Modified Username Field Change
======================================================================
28-Jan-10 00:24 waza123 New Issue
28-Jan-10 00:25 waza123 Issue Monitored: waza123
28-Jan-10 00:25 waza123 Issue End Monitor: waza123
28-Jan-10 00:27 waza123 Note Added: 0003005
28-Jan-10 14:12 paul Note Added: 0003009
28-Jan-10 15:28 waza123 Note Added: 0003010
29-Jan-10 00:21 waza123 Note Added: 0003011
29-Jan-10 00:25 waza123 Note Added: 0003012
29-Jan-10 09:13 paul Note Added: 0003013
======================================================================
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
