Re: imap search query
Andrea Brancatelli <[email protected]>
| Newsgroups | gmane.mail.imap.dbmail |
|---|---|
| Organization | Schema31 S.p.a. |
| Message-ID | <[email protected]> |
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 [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail