MySQL Bugs: #2825: Executing a query consumes up to 100% CPU power

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:	None
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

Description:
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
   tables. 
2. run the attached query

Suggested fix:
--

Skript to build the used schema

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

Query that resulted in consuming almost 100% of CPU power

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

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:

/sql/sql_select.cc
~1900~

static void
find_best(....)
......

	}
	swap(JOIN_TAB*,join->best_ref[idx],*pos);
	find_best(join,rest_tables & ~real_table_bit,idx+1,
		  current_record_count,current_read_time);
        swap(JOIN_TAB*,join->best_ref[idx],*pos);

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.

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
    http://www.mysql.com/doc/en/Installing_source_tree.html

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!

furtehrmore such a query is perfectly killable in 4.1 (since 4.1.0, iirc)