Bug 515056 - hang: "order by" on dbmail_messageblks
hang: "order by" on dbmail_messageblks
Status: CLOSED WONTFIX
Product: Fedora
Classification: Fedora
Component: dbmail (Show other bugs)
10
All Linux
low Severity urgent
: ---
: ---
Assigned To: Bernard Johnson
Fedora Extras Quality Assurance
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2009-08-01 08:19 EDT by Harald Reindl
Modified: 2009-12-18 04:39 EST (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2009-12-18 04:39:05 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Patch for the problem-query (570 bytes, patch)
2009-08-01 12:44 EDT, Harald Reindl
no flags Details | Diff

  None (edit)
Description Harald Reindl 2009-08-01 08:19:51 EDT
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
Comment 1 Harald Reindl 2009-08-01 09:30:39 EDT
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
Comment 2 Harald Reindl 2009-08-01 10:26:34 EDT
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);
}
Comment 3 Bernard Johnson 2009-08-01 10:55:38 EDT
Please post your findings in the dbmail bug tracker upstream and let's see what the author says about this.
Comment 4 Harald Reindl 2009-08-01 11:16:38 EDT
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);
}
Comment 5 Harald Reindl 2009-08-01 12:44:01 EDT
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
Comment 6 Bug Zapper 2009-11-18 04:31:31 EST
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
Comment 7 Bug Zapper 2009-12-18 04:39:05 EST
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.

Note You need to log in before you can comment on or make changes to this bug.