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 00:25 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');
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
======================================================================
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
