A NOTE has been added to this issue.
======================================================================
http://www.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: 28-Jan-10 14:12 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://www.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://www.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');
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
======================================================================
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
