Strange performance of mysql 5.5 on x86_64

Polyglot Projects Sphinx & Full-Text Search
1

Hi

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.

2

Now that you have made this patch, is your server leaking memory? :slight_smile:

3

Well of course it is. But don’t you think that 82% qtime is little to much for cleaning up? :slight_smile:

Anyway… here is what i did:
[LIST=1]
[] 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,’ ');
I’ve removed cursor with CONCAT in loop and repalced it with single query with IFs and GROUP_CONCAT
[/LIST]
Now query runs fast and is spending time in os_file_pread and so on which seems to be correct.
I have no idea why function runnning perfectly fine on 5.1 is acting like that on 5.5 :frowning: