I’ve noticed strange behavior of mysql 5.5 on x86_64 (tested: binary&compiled 5.5.9, percona 5.5.8). My query is spending most of time in:
#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
Query is a big select for sphinx index. One join and few functions which concatenates some rows from other tables.
The strange part is when i apply this:
— sql/sp_head.cc 2011-02-14 08:37:20.031690870 +0100+++ sql/sp_head.cc 2011-02-14 08:37:23.811681627 +0100@@ -1418,10 +1418,10 @@ thd->user_var_events_alloc= thd->mem_root; err_status= i->execute(thd, &ip);-+/* if (i->free_list) cleanup_items(i->free_list);-+/ / If we’ve set thd->user_var_events_alloc to mem_root of this SP
query runs ~6sec (vs. >35sec without patch) AND returns the same results as when cleanup_items is not disabled.
In MySql 5.1.31-1ubuntu2 x86_64 the same query takes ~6sec to run.
Qtimes were measured few times with query_cache_type=OFF
How is it possible that 5.5 runs slower than 5.1 and more importantly why results are correct after disabling cleanup_items?? Maybe something in my query or config is forcing cleanup_items to run more often than necessary?
TIA for any ideas.