This is the biggest table with 16 GB data, "order by" is a killer here Even if you order by id this hangs away, i seen ONE query hang over 10 minutes SELECT messageblk, is_header FROM dbmail_messageblks WHERE physmessage_id=89842 ORDER BY messageblk; Test without "order by" runs in 0.10 seconds SELECT messageblk, is_header FROM dbmail_messageblks WHERE physmessage_id=89842; I would higly recommend to remove the sorting from query and do this in the few result-rows on the application-side. While this query runs you have huge disk-i/o and imapsync-shellscript has to wait before step to the next line/user The other querstion: is the ordering here really needed in any case? ________________________ Running is imapsync in the following form: imapsync --host1 'localhost' --user1 '****' --password1 '****' --authmech1 LOGIN --host2 'targetmachine' --user2 '****' --password2 '****' --authmech2 LOGIN --skipsize --expunge1 --fastio2 --delete2 --maxage 5 dbmail-2.2.11-8 rebuild on our buildmachine for F10 from the F11-src.rpm http://koji.fedoraproject.org/koji/buildinfo?buildID=113061
I also made a bugreport on mysql.com But not sure if there is really a way to fix this on mysql-layer because i do not know enough about the internals. Suggestion is not make "order by" on large tables even if there is a index, but never make this on a field with no index which is in this worst case a blob http://bugs.mysql.com/bug.php?id=46508
The query is located in dbmail-message.c line 741 Seems mysql uses the wrong index! Look at my third query with "FORCE INDEX" that will solve the problem So i need to know how to make a patch for rpmbuild/specfile mysql> explain SELECT SQL_NO_CACHE messageblk, is_header FROM dbmail_messageblks WHERE physmessage_id=89842 ORDER BY messageblk_idnr; +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | dbmail_messageblks | index | physmessage_id_index,physmessage_id_is_header_index | PRIMARY | 8 | NULL | 642706 | Using where | +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+--------+-------------+ 1 row in set (0.18 sec) SELECT SQL_NO_CACHE messageblk, is_header FROM dbmail_messageblks WHERE physmessage_id=89842; 2 rows in set (0.00 sec) SELECT SQL_NO_CACHE messageblk, is_header FROM dbmail_messageblks WHERE physmessage_id=89842 ORDER BY messageblk_idnr; runs since 727 seconds, so i stopped it mysql produces and i/o up to 25 MB per second! SELECT SQL_NO_CACHE messageblk, is_header FROM dbmail_messageblks FORCE INDEX (physmessage_id_index) WHERE physmessage_id=89842 ORDER BY messageblk_idnr; 2 rows in set (0.20 sec) /* * * retrieve the full message * */ 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); }
Please post your findings in the dbmail bug tracker upstream and let's see what the author says about this.
dbmail.org is offline this time :-( I had liked to make the bugreport upstream, here and on mysql.com, primary this is a error in mysql, deciding to use the wrongest index for this query. Please can you give me a src.rpm based on the latest F11 with changes this query So i run a new rpmbuild on our buildmachine and make many tests My problem is that we want to migrate our mailsystem next weekend after final-tests to dbmail and this is the only bug i found which hurts really. If you have two or three of this queries running and each of them is reading the whole table or whatelse it do with 20 MB/Sec. disk-io for many minutes you get a problem and for the hardware such things are also not really funny :-) I have here the unpacked source with the fix in "dbmail-message.c" and do not know how to get a patch-file for the dbmail.spec to make this on my own static struct DbmailMessage * _fetch_full(struct DbmailMessage *self) { char *query_template = "SELECT messageblk, is_header " "FROM %smessageblks " "FORCE INDEX (physmessage_id_index) " "WHERE physmessage_id = %llu " "ORDER BY messageblk_idnr"; return _retrieve(self, query_template); }
Created attachment 355884 [details] Patch for the problem-query So - After some minutes Google i have found how to make a patchfile See attachment * Our new build is finsished * Server is updated * imapsync runs * I see the new "FORCE INDEX"-querys and they are real fast
This message is a reminder that Fedora 10 is nearing its end of life. Approximately 30 (thirty) days from now Fedora will stop maintaining and issuing updates for Fedora 10. It is Fedora's policy to close all bug reports from releases that are no longer maintained. At that time this bug will be closed as WONTFIX if it remains open with a Fedora 'version' of '10'. Package Maintainer: If you wish for this bug to remain open because you plan to fix it in a currently maintained version, simply change the 'version' to a later Fedora version prior to Fedora 10's end of life. Bug Reporter: Thank you for reporting this issue and we are sorry that we may not be able to fix it before Fedora 10 is end of life. If you would still like to see this bug fixed and are able to reproduce it against a later version of Fedora please change the 'version' of this bug to the applicable version. If you are unable to change the version, please add a comment here and someone will do it for you. Although we aim to fix as many bugs as possible during every release's lifetime, sometimes those efforts are overtaken by events. Often a more recent Fedora release includes newer upstream software that fixes bugs or makes them obsolete. The process we are following is described here: http://fedoraproject.org/wiki/BugZappers/HouseKeeping
Fedora 10 changed to end-of-life (EOL) status on 2009-12-17. Fedora 10 is no longer maintained, which means that it will not receive any further security or bug fix updates. As a result we are closing this bug. If you can reproduce this bug against a currently maintained version of Fedora please feel free to reopen this bug against that version. Thank you for reporting this bug and we are sorry it could not be fixed.