Hi Khanh I don't know the exact scenario how your MySQL halts though, it may be caused by the bug in the MySQL's query optimizer.
If you see a query "SELECT messageblk, is_header FROM dbmail_messageblks WHERE physmessage_id = NNN ORDER BY messageblk_idnr" doing a full table scan, the bug is your cause, and the attached patch (against 2.2 branch) should fix. Note that how the bug affecting can be seen in the attached mysql session log. In which, the 2nd "EXPLAIN SELECT ..." chose the incorrect key requiring a full table scan. FreeBSD 7.0-BETA2 as of Nov. 3 2007 MySQL 5.1.23-rc as of Dec. 14 2007 DBMail 2.2 as of Dec. 13 2007 -- UEMURA (fka. MAENAKA) Tetsuya <[EMAIL PROTECTED]> Posted on Tue, 13 May 2008 08:46:59 -0700 by author khanh.truong <[EMAIL PROTECTED]> > Hi, > > We've been testing DBMail for a few months now and did a test launch > yesterday. We have about 850 pop3/imap users accessing through the webmail, > Outlook and mobile devices. Things were fine when we tested with a few users, > but after we put it in production - we get about a hundred concurrent > connections - MySQL just grinds to a halt. I've tweaked so many settings but > nothing helped. I think the problem might be because the DBMail database > isn't indexed properly or has no indexing at all... I haven't verified yet. > > My question is: does the schema that comes with DBMail already have indexing > defined in it or do I have to do my own indexing and optimization? > > Our server is a dual 64bit xeon with 4GB of RAM on a RAID1 SCSI > FreeBSD 7 > MySQL 5.1 > > Some one on this list mentioned that they run DBMail with 5000 users no > problem so I'm wondering why ours crawed to a stop. > > Thanks > > Khanh > _______________________________________________ > DBmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail
patch-dbmail-message.c
Description: Binary data
mysql.log
Description: Binary data
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
