| Bug #29582 | huge memory consumption with union, subselect, joins | ||
|---|---|---|---|
| Submitted: | 5 Jul 2007 18:57 | Modified: | 3 Aug 2007 2:29 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.42, 5.0.46BK | OS: | Any |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
| Tags: | bfsm_2007_07_19, Leak, Memory, UNION | ||
[5 Jul 2007 21:14]
MySQL Verification Team
the query above takes 20 seconds on a release build of mysqld, but >10 minutes on a full debug build. I don't know why that would be..
[18 Jul 2007 15:50]
Sergey Petrunya
Ok, the memory consumption is caused by this call:
mysql_select( various fake_select_lex's members )
this invokes JOIN::optimize which allocates memory on the execution MEM_ROOT. When the union is within the subquery, this causes a total of O(#subselect-executions) memory to be allocated.
The fix seems to be to replace the generic mysql_select() call with
if (optimization already done)
{
join->exec();
// some join->cleanup()-like call here
}
which is known not to consume memory.
[25 Jul 2007 8:11]
Sergey Petrunya
The ETA is 2007-07-27.
[30 Jul 2007 13:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/31817 ChangeSet@1.2532, 2007-07-30 17:12:34+04:00, sergefp@mysql.com +3 -0 BUG#29582: huge memory consumption with union, subselect, joins: - Don't call mysql_select() several times for the select that enumerates the temporary table with the results of the UNION. Making this call for every subquery execution caused O(#enumerated-rows-in-the-outer-query) memory allocations. - Instead, call join->reinit() and join->exec(), and = disable constant table detection for such joins, = provide special handling for table-less constant subqueries.
[30 Jul 2007 13:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/31818 ChangeSet@1.2532, 2007-07-30 17:19:49+04:00, sergefp@mysql.com +3 -0 BUG#29582: huge memory consumption with union, subselect, joins: - Don't call mysql_select() several times for the select that enumerates the temporary table with the results of the UNION. Making this call for every subquery execution caused O(#enumerated-rows-in-the-outer-query) memory allocations. - Instead, call join->reinit() and join->exec(), and = disable constant table detection for such joins, = provide special handling for table-less constant subqueries.
[31 Jul 2007 11:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/31884 ChangeSet@1.2532, 2007-07-31 15:19:06+04:00, sergefp@mysql.com +3 -0 BUG#29582: huge memory consumption with union, subselect, joins: - Don't call mysql_select() several times for the select that enumerates a temporary table with the results of the UNION. Making this call for every subquery execution caused O(#enumerated-rows-in-the-outer-query) memory allocations. - Instead, call join->reinit() and join->exec(), and = disable constant table detection for such joins, = provide special handling for table-less constant subqueries.
[1 Aug 2007 10:15]
Sergey Petrunya
Pushed into 5.0-opt at 2007-07-31 13:46:41.
[2 Aug 2007 19:13]
Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:15]
Bugs System
Pushed into 5.0.48
[3 Aug 2007 2:29]
Paul DuBois
Noted in 5.0.48, 5.1.21 changelogs. Certain statements with unions, subqueries, and joins could result in huge memory consumption.
Description: A query in this form uses a few hundred MB of ram during execution. So, for a 32-bit server, you'll be out of memory if two clients execute this at the same time: I narrowed down the culprit to be the UNION in the derived table. Without the UNION, the memory doesn't get wasted as much. The general form of the query is: SELECT SQL_CALC_FOUND_ROWS COUNT(DISTINCT ...), IF(SUM(DISTINCT ...) IS NOT NULL, SUM(DISTINCT ...), 0), IF(... IS NOT NULL, true, false) FROM t1 INNER JOIN ... INNER JOIN ... LEFT OUTER JOIN ( SELECT DISTINCT ... FROM ... WHERE ...=5 AND ...=1 ) ... ON ... WHERE ( ...=true OR ... IS NULL ) AND ... IN ( SELECT .... FROM ..... INNER JOIN ... ON ..... INNER JOIN ... ON ..... WHERE ....=1 AND (..., ...)=(5, 1) AND ...=0 UNION SELECT ..... FROM ..... INNER JOIN ... ON ...... INNER JOIN ... ON ...... INNER JOIN ... ON ...... WHERE ...=2 AND ...=1 AND ...=5 ) AND ...>'2007-02-02 11:09:13.75' GROUP BY .... ORDER BY ... DESC LIMIT 20 How to repeat: simpler testcase will be uploaded later. Suggested fix: .