MySQL Bugs: #60158: Time wasted in cleanup_items(Item*) due to size of free

Bug #60158	Time wasted in cleanup_items(Item*) due to size of free_list
Submitted:	17 Feb 2011 12:33	Modified:	22 May 2011 15:05
Reporter:	Maciej F.	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S5 (Performance)
Version:	5.5.11	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	performance, regression

Description:
I've noticed strange behavior of mysql 5.5 on x86_64 (tested: 5.5.9,5.5.11 percona 5.5.8). My query is spending most of time in:
[code]
#0  0x00000000005f5429 in cleanup_items (item=0x363be88) at sql/sql_parse.cc:652
#1  0x000000000087fc7d in sp_head::execute (this=0x322c6d0, thd=0x3037cd0, merge_da_on_success=true) at sql/sp_head.cc:1423
#2  0x0000000000880faa in sp_head::execute_function (this=0x322c6d0, thd=0x3037cd0, argp=0x3111d88, argcount=1, return_value_fld=0x322c298) at sql/sp_head.cc:1941
[/code]
Query is a big select for sphinx index. One join and few functions which concatenates some rows from other tables.

 * After restarting server query runs ok for few times and then slows down.
 * I added counter near cleanup_items and it turns out that whenever i ran my query free_list gets bigger and bigger.
 * Reconnecting mysql client resets the size of free_list back to its normal size and query runs fast again (few times)
 * I dumped free_list->name's and it showed that free_list grows only when there are sp_instr's related to one line in cursor in my stored function:

SET output = CONCAT(output, 'a', att_id, a_separator, att_val,' ');

 * Query is working ok in mysql 5.1
 * As a workaround I've removed cursor with CONCAT in loop and repalced it with single query with IFs and GROUP_CONCAT
 * There is also a thread about this http://forum.percona.com/index.php/t/1753/

How to repeat:
Currently i am unable to reproduce this in test case :( only in production db.

Please, send a complete test case, entire code of stored procedure that demonstrates this problem.

I can reproduce the growing item list in cleanup_items (v 5.5.9) with simple date_add operations. Could be related to bug #60025. Test case:

CREATE PROCEDURE testen(p_maxcnt INT)
BEGIN
  DECLARE v_reminder DATETIME;
  DECLARE v_stop DATETIME;
  DECLARE v_cnt INT;
  
  SET v_cnt = 0;
  SET v_stop = DATE_ADD(sysdate(), INTERVAL 10 MINUTE);
  
  WHILE (v_cnt < p_maxcnt) DO
    SET v_cnt = v_cnt + 1;
    SET v_reminder = DATE_SUB(SYSDATE(), INTERVAL 80 YEAR);
    WHILE (v_reminder <= v_stop) DO
      SET v_reminder = DATE_ADD(v_reminder, INTERVAL 1 YEAR);
    END WHILE;
  END WHILE;
END;

CALL testen(100);

Workaround for bug #60025 is working. This may be a duplicate.

Duplicate of bug #60025.