h.reindl at thelounge
Nov 13, 2009, 6:11 AM
Post #2 of 3
(577 views)
Permalink
|
|
Re: DBMail 2.2.13 query not using the index with MySQL 5.1
[In reply to]
|
|
Update your mysql!
http://bugs.mysql.com/bug.php?id=46508
The problem is that debian/ubuntu have an idiotic update-policy
resulting in having the sam ebugs over years or damaging
working software by patching-to-death
Am 13.11.2009 14:49, schrieb John Feuerstein:
> Hi,
>
> we've encountered a serious issue while testing DBMail 2.2.13 with MySQL
> server version 5.1. Since this is a MySQL bug I didn't file one against
> DBMail, but it won't be bad to let the mailinglist know. Actually I
> filed the bug against the Ubuntu package, because they ship the broken
> MySQL server version with unmodified DBMail.
>
> Here is the full bugreport:
> https://bugs.launchpad.net/ubuntu/+source/dbmail/+bug/482121
>
>> Using current DBMail (2.2.11 as packaged or 2.2.13 from upstream)
>> with the current MySQL server version (5.1) shipped with Ubuntu is
>> unusable. The MySQL server does not use the index and falls back to a
>> full table scan. This is a regression between MySQL Server 5.0 and
>> 5.1 and is reported upstream as a serious bug:
>>
>> http://bugs.mysql.com/bug.php?id=38745
>>
>> The amount of time needed for a query not using the index grows with
>> the table size and if the whole table doesn't fit into RAM (that's
>> usually the case with dbmail) it will cause huge amounts of disk IO.
>> We have rendered a server completely unusable because of this in the
>> initial testing phase while copying around 20G of Maildir-based
>> accounts to DBMail using imapsync.
>>
>> Just to name some numbers, before and after the fix, the same query
>> took >100s compared to <1s respectively. Since this query happens
>> _very_ often, it's a serious issue and people just starting out with
>> DBMail and small databases won't notice it immediately.
>>
>> The problem is non-existant with MySQL server version 5.0, which uses
>> the index just fine. 5.1 needs the index to be used explicitely.
>>
>> The problematic query as found in dbmail-message.c (line 747 and
>> following as of 2.2.13):
>>
>> static struct DbmailMessage * _fetch_full(struct DbmailMessage *self)
>> { char *query_template = "SELECT messageblk, is_header " "FROM
>> %smessageblks " "WHERE physmessage_id = %llu " "ORDER BY
>> messageblk_idnr"; return _retrieve(self, query_template); }
>>
>> Changing it to explicitely use the correct index (USE INDEX()) fixes
>> it:
>>
>> static struct DbmailMessage * _fetch_full(struct DbmailMessage *self)
>> { char *query_template = "SELECT messageblk, is_header " "FROM
>> %smessageblks USE INDEX(physmessage_id_index) " "WHERE physmessage_id
>> = %llu " "ORDER BY messageblk_idnr"; return _retrieve(self,
>> query_template); }
>>
>> The same change can be applied to _fetch_head().
>
> Best regards,
> John Feuerstein
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
--
Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna | Hofmühlgasse 17
software-development / cms-solutions
phone: +43 (1) 595 3999 33
cellular: +43 (676) 40 221 40
icq: 154546673
mailto:h.reindl [at] thelounge
http://www.thelounge.net/
|
signature.asc
