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 [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
