Bug #2825 Executing a query consumes up to 100% CPU power
Submitted: 16 Feb 2004 7:03 Modified: 5 Mar 2004 2:59
Reporter: Hansjoerg Wingeier Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.18/4.1.2 OS:Windows (Windows 2000/Suse 9.0)
Assigned to: Michael Widenius CPU Architecture:Any

[16 Feb 2004 7:03] Hansjoerg Wingeier
We use IntelliBO 3.6 (www.intellibo.com)as JDO Implementation which creates the SQL-Statements. We found one statement, which IntelliBO creates that results in consuming almost 100% of CPU power by the mysqld-nt.exe process. It is not possible to kill the process. Moreover, it is also not possible to really kill the select for example in MySql-Front. The machine has to be rebooted.

We were able to reproduce the error on MySQL 4.0.15 and also on 4.0.18. 
On MySQL 3.23.51, we didn't face that problem.

How to repeat:
1. Install the attached sql which builds up the schema "pasistest" with all
2. run the attached query

Suggested fix:
[16 Feb 2004 7:04] Hansjoerg Wingeier
Skript to build the used schema

Attachment: lockedquerydata.sql (application/octet-stream, text), 67.22 KiB.

[16 Feb 2004 7:05] Hansjoerg Wingeier
Query that resulted in consuming almost 100% of CPU power

Attachment: query.sql (application/octet-stream, text), 1.92 KiB.

[4 Mar 2004 20:49] MySQL Verification Team
Thank you for the bug report I was able to repeat also on 4.1.2
from BK tree 1 week older.

This query arise a recursive call of the function:


static void

	find_best(join,rest_tables & ~real_table_bit,idx+1,

how the mysqld.trace shows:

T@4    : | | | | | >find_best_combination
T@4    : | | | | | | best: read_time: 4.7658e+019  record_count: 5.21181e+022
T@4    : | | | | | | best: read_time: 4.7658e+019  record_count: 5.21181e+022
T@4    : | | | | | | best: read_time: 3.89217e+017  record_count: 4.27197e+020
T@4    : | | | | | | best: read_time: 3.89217e+017  record_count: 4.27197e+020

The high CPU usage only is presented on Windows, on Suse the client
just hangs.
[5 Mar 2004 0:49] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at

Additional info:

The problem is that the join optimizer has two many options to consider and there is no obvious best way to do the join.

I have now fixed for 4.0.19 that one can break the join optimizer with an 'kill #thread-id"

In MySQL 5.0 we will have a new 'gready' join optimzer that should be able to quickly optimize queries of this kind.

Thanks for the test case!  We will use it to test the new join optimizer!
[5 Mar 2004 2:59] Sergei Golubchik
furtehrmore such a query is perfectly killable in 4.1 (since 4.1.0, iirc)