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

Reply via email to