Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Strange performance of mysql 5.5 on x86_64

susidesuside EntrantCurrent User Role Beginner
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</pre>

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 [email protected]@ -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</pre>

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.

Comments

  • xaprbxaprb Mentor Inactive User Role Beginner
    Now that you have made this patch, is your server leaking memory? :)
  • susidesuside Entrant Current User Role Beginner
    Well of course it is. But don't you think that 82% qtime is little to much for cleaning up? :)

    Anyway... here is what i did:
    1. After restarting server query runs ok for few times and then slows down.
    2. I added counter near cleanup_items and it turns out that whenever i ran my query free_list gets bigger and bigger.
    3. Reconnecting mysql client resets the size of free_list back to its normal size and query runs fast again (few times)
    4. 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,' ');</pre>
      I've removed cursor with CONCAT in loop and repalced it with single query with IFs and GROUP_CONCAT
    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 :(
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.