Hello,
I see sudden spikes of load on our dbmail mysql servers. Looking into the slow
query logs I usualy find these:
SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts p
LEFT JOIN dbmail_partlists l ON p.id=l.part_id
LEFT JOIN dbmail_physmessage s ON l.physmessage_id=s.id
LEFT JOIN dbmail_messages m ON m.physmessage_id=s.id
LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr = b.mailbox_idnr
WHERE b.mailbox_idnr=87 AND m.status IN (0,1) AND (l.part_key > 1 OR
l.is_header=0) AND p.data LIKE BINARY '%Gls%'
ORDER BY m.message_idnr
I believe these are IMAP searches.
Mysql processlist show these in "copy to tmp table" state, which kills IO,
especially if there's more than one running at the same time. These queries
take anywhere up to 20min to complete and rows_examined shows numbers over 350k.
My guess is that users do a search on a large mailbox.
Apart from telling them not to do that, what else can I do to improve
performance? Can anything be done at all to improve "like binary" performance
apart from setting up a separate indexing server such as solr? Is binary search
even necessary?
DBMail 3.1.10, MySQL 5.5.41
--
Jure Pečar
http://jure.pecar.org
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
