We put our MySQL tmp path on an SSD and that improved the situation a
lot, but we have the same problem.
My (poor man) opinion is that using MySQL 5.6 fulltext capabilities is
the only solution...
Il 04/03/15 13:21, Jure Pečar ha scritto:
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
--
*Andrea Brancatelli
Schema31 S.p.A.
Responsabile IT*
ROMA - FIRENZE - PALERMO - BOLOGNA
ITALY
Tel: +39.06.98358472
Cell: +39.331.2488468
Fax: +39.055.71880466
Società del gruppo SC31 ITALIA
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
