Mailing List Archive: imap search query

imap search query

Mar 4, 2015, 4:21 AM

Post #1 of 9 (3306 views)

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

Re: imap search query [ In reply to ]

abrancatelli at schema31

Mar 4, 2015, 5:14 AM

Post #2 of 9 (3261 views)

Permalink

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

Re: imap search query [ In reply to ]

cmaurand at gmail

Mar 4, 2015, 4:23 PM

Post #3 of 9 (3255 views)

Permalink

I've found that the imap search will always fail and timeout. It has never
worked for me in any version. Whenever I've wanted to find a message, I've
had to write my own query and search for it manually.

--Curtis
On Mar 4, 2015 8:14 AM, "Andrea Brancatelli" <abrancatelli@schema31.it>
wrote:

> 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
>
>

Re: imap search query [ In reply to ]

pegasus at nerv

Mar 5, 2015, 8:28 AM

Post #4 of 9 (3250 views)

Permalink

On Wed, 4 Mar 2015 13:21:04 +0100
Jure PeÄar <pegasus@nerv.eu.org> wrote:

> 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

My conclusions about this query are:

* dbmail_mimeparts.data is type longblob
* ORDER BY requires temp table
* which could go to memory table, but it does not support blob/text types, therefore it goes to disk
* LIKE begins with %, therefore it cannot use indexes
* which means full table scan
* on disk :D

Also LIKE BINARY is many times (3+) slower then LIKE. The only difference between the two is that one is case sensitive while the other is not.

Things we could investigate:
* changing dbmail_mimeparts.data type to something that memory engine supports
* like / like binary ... do we need case sensitivity?
* mysql 5.6 full text search?
* external indexing?

Paul?

--

Jure PeÄar
http://jure.pecar.org
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: imap search query [ In reply to ]

lordvan at lordvan

Mar 5, 2015, 10:48 PM

Post #5 of 9 (3237 views)

Permalink

Am 05.03.2015 um 17:28 schrieb Jure PeÄar:
> On Wed, 4 Mar 2015 13:21:04 +0100
> Jure PeÄar <pegasus@nerv.eu.org> wrote:
>
>> 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
>
> My conclusions about this query are:
>
> * dbmail_mimeparts.data is type longblob
> * ORDER BY requires temp table
> * which could go to memory table, but it does not support blob/text types, therefore it goes to disk
> * LIKE begins with %, therefore it cannot use indexes
> * which means full table scan
> * on disk :D
>
> Also LIKE BINARY is many times (3+) slower then LIKE. The only difference between the two is that one is case sensitive while the other is not.
>
> Things we could investigate:
> * changing dbmail_mimeparts.data type to something that memory engine supports
> * like / like binary ... do we need case sensitivity?
> * mysql 5.6 full text search?
> * external indexing?
>
> Paul?
>
>

I recon something like supporting mysql 5.6 full text search would cause
problems, since it is not the only supported RDBMS. I think paul told me
once dbmail doesn'T use stored procedures, because some backend (i think
just sqlite) doesn't support it. So if one were to put in some full text
capability it would have to work everywhere - and emulate where not ? ..
which i guess would be difficult.

Re: imap search query [ In reply to ]

claaskaehler at yfx

Mar 5, 2015, 11:52 PM

Post #6 of 9 (3244 views)

Permalink

Is there really somebody that uses DBMail with SQLite??? Does this make
sense?

Am 06.03.15 um 07:48 schrieb Thomas Raschbacher:
> Am 05.03.2015 um 17:28 schrieb Jure PeÄar:
>> On Wed, 4 Mar 2015 13:21:04 +0100
>> Jure PeÄar <pegasus@nerv.eu.org> wrote:
>>
>>> 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
>> My conclusions about this query are:
>>
>> * dbmail_mimeparts.data is type longblob
>> * ORDER BY requires temp table
>> * which could go to memory table, but it does not support blob/text types, therefore it goes to disk
>> * LIKE begins with %, therefore it cannot use indexes
>> * which means full table scan
>> * on disk :D
>>
>> Also LIKE BINARY is many times (3+) slower then LIKE. The only difference between the two is that one is case sensitive while the other is not.
>>
>> Things we could investigate:
>> * changing dbmail_mimeparts.data type to something that memory engine supports
>> * like / like binary ... do we need case sensitivity?
>> * mysql 5.6 full text search?
>> * external indexing?
>>
>> Paul?
>>
>>
> I recon something like supporting mysql 5.6 full text search would cause
> problems, since it is not the only supported RDBMS. I think paul told me
> once dbmail doesn'T use stored procedures, because some backend (i think
> just sqlite) doesn't support it. So if one were to put in some full text
> capability it would have to work everywhere - and emulate where not ? ..
> which i guess would be difficult.
>
>
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: imap search query [ In reply to ]

abrancatelli at schema31

Mar 6, 2015, 1:11 AM

Post #7 of 9 (3252 views)

Permalink

There's already some Oracle-specific code in the codebase, it's, just
one query, it could be implemented with a specific code for Full Text
capable/not capable DBMS.

Il 06/03/15 07:48, Thomas Raschbacher ha scritto:
> Am 05.03.2015 um 17:28 schrieb Jure PeÄar:
>> On Wed, 4 Mar 2015 13:21:04 +0100
>> Jure PeÄar <pegasus@nerv.eu.org> wrote:
>>
>>> 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
>> My conclusions about this query are:
>>
>> * dbmail_mimeparts.data is type longblob
>> * ORDER BY requires temp table
>> * which could go to memory table, but it does not support blob/text types, therefore it goes to disk
>> * LIKE begins with %, therefore it cannot use indexes
>> * which means full table scan
>> * on disk :D
>>
>> Also LIKE BINARY is many times (3+) slower then LIKE. The only difference between the two is that one is case sensitive while the other is not.
>>
>> Things we could investigate:
>> * changing dbmail_mimeparts.data type to something that memory engine supports
>> * like / like binary ... do we need case sensitivity?
>> * mysql 5.6 full text search?
>> * external indexing?
>>
>> Paul?
>>
>>
> I recon something like supporting mysql 5.6 full text search would cause
> problems, since it is not the only supported RDBMS. I think paul told me
> once dbmail doesn'T use stored procedures, because some backend (i think
> just sqlite) doesn't support it. So if one were to put in some full text
> capability it would have to work everywhere - and emulate where not ? ..
> which i guess would be difficult.
>
>
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

--
*Andrea Brancatelli

Schema31 S.p.A.
Responsabile IT*

BO - FI - ROMA - PA
ITALY
Tel: +39.06.98358472
Cell: +39.331.2488468
Fax: +39.055.71880466
SocietÃ del gruppo SC31 ITALIA

Re: imap search query [ In reply to ]

lordvan at lordvan

Mar 6, 2015, 2:58 AM

Post #8 of 9 (3246 views)

Permalink

Am 06.03.2015 um 10:11 schrieb Andrea Brancatelli:
> There's already some Oracle-specific code in the codebase, it's, just
> one query, it could be implemented with a specific code for Full Text
> capable/not capable DBMS.
>

I don't have a problme with it myself ^^ someone just has to maintain
the code then .. btw afaik LIKE BINARY is mysql specific anyway (at
least postgres doesn't know that)

Regards

Re: imap search query [ In reply to ]

leithner at itronic

Mar 17, 2015, 4:37 AM

Post #9 of 9 (3213 views)

Permalink

It makes no sense to make a fulltext on the data column in the mimeparts
table, because most of the data is base64 encoded and/or is a attachment..

We may need a second table that only holds the (decoded) message part
(text/plain html/plain (maybe with removed html)). This table could have a
mysql/postgre fulltext column.

Alternative based on this table we can create our own fulltext engine.

But the thing that makes much more sense is to use an external search
engine like Sphinx or lucene or what ever.

Some nice SE Answers
http://stackoverflow.com/questions/737275/comparison-of-full-text-search-engine-lucene-sphinx-postgresql-mysql

Anyway it should be optional.

I also have a question is it possible to deactivate imaps search? because
if a query runs multiple minutes its highly possible that multiple people
starts a search and may kill the server?!

Am .03.2015, 11:58 Uhr, schrieb Thomas Raschbacher <lordvan@lordvan.com>:

> Am 06.03.2015 um 10:11 schrieb Andrea Brancatelli:
>> There's already some Oracle-specific code in the codebase, it's, just
>> one query, it could be implemented with a specific code for Full Text
>> capable/not capable DBMS.
>>
>
> I don't have a problme with it myself ^^ someone just has to maintain
> the code then .. btw afaik LIKE BINARY is mysql specific anyway (at
> least postgres doesn't know that)
>
> Regards
>
>

--
Harald Leithner

ITronic
Wiedner HauptstraÃŸe 120/5.1, 1050 Wien, Austria
Tel: +43-1-545 0 604
Fax: +43-1-786 23 88 26
Mobil: +43-699-123 78 4 78
Mail: leithner@itronic.at | itronic.at
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Mailing List Archive

Attached Files:

Attached Files: