Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5675

Performance: my_hash_sort_bin is called too often

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.8
    • Fix Version/s: 10.0.9
    • Component/s: None
    • Labels:
      None

      Description

      According to various profiler reports, my_hash_sort_bin() is among heaviest functions for simple SELECT queries.

      Firstly my_hash_sort_bin() is called too often:
      1. twice while acquiring MDL lock (same key)
      2. once while acquiring table from table cache (almost same key)
      3. once while releasing MDL lock (same key)

      For all the above cases it is sufficient to calculate value only once. Though table cache may need a bit more complex solution.

      Secondly my_hash_sort_bin() seem to do a lot memory accesses, more details here: http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5107.1.537

      perf report for non-patched version:

        3,77%  mysqld  mysqld               [.] MYSQLparse(THD*)
        1,93%  mysqld  mysqld               [.] make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*)
        1,70%  mysqld  [vdso]               [.] 0x00000000000008d8
        1,68%  mysqld  [kernel.kallsyms]    [k] __ticket_spin_lock
        1,37%  mysqld  libpthread-2.17.so   [.] pthread_mutex_lock
        1,35%  mysqld  [kernel.kallsyms]    [k] enqueue_entity
        1,18%  mysqld  [kernel.kallsyms]    [k] unix_stream_recvmsg
        1,15%  mysqld  mysqld               [.] dispatch_command(enum_server_command, THD*, char*, unsigned int)
        1,00%  mysqld  mysqld               [.] lex_one_token(void*, THD*)
        0,94%  mysqld  mysqld               [.] JOIN::optimize_inner()
        0,88%  mysqld  mysqld               [.] JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*)
        0,84%  mysqld  mysqld               [.] my_hash_sort_bin

      perf report for patched version:

        4,40%  mysqld  mysqld               [.] MYSQLparse(THD*)
        1,63%  mysqld  [kernel.kallsyms]    [k] __ticket_spin_lock
        1,63%  mysqld  [kernel.kallsyms]    [k] enqueue_entity
        1,58%  mysqld  [vdso]               [.] 0x000000000000070f
        1,49%  mysqld  libpthread-2.17.so   [.] pthread_mutex_lock
        1,43%  mysqld  mysqld               [.] make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*)
        1,28%  mysqld  [kernel.kallsyms]    [k] unix_stream_recvmsg
        1,16%  mysqld  mysqld               [.] dispatch_command(enum_server_command, THD*, char*, unsigned int)
        1,00%  mysqld  mysqld               [.] lex_one_token(void*, THD*)
        0,90%  mysqld  mysqld               [.] st_select_lex::add_table_to_list(THD*, Table_ident*, st_mysql_lex_string*, unsigned long, thr_lock_type, enum_mdl_type, List<Index_hint>*, List<String>*, st_mysq▒
        0,85%  mysqld  mysqld               [.] malloc
        0,84%  mysqld  mysqld               [.] JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*)
      ...
        0,40%  mysqld  mysqld               [.] my_hash_sort_bin

      perf report for patched version + reuse hash value in table cache:

        3,59%  mysqld  mysqld               [.] MYSQLparse(THD*)
        1,89%  mysqld  mysqld               [.] make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*)
        1,74%  mysqld  [kernel.kallsyms]    [k] __ticket_spin_lock
        1,55%  mysqld  [kernel.kallsyms]    [k] enqueue_entity
        1,48%  mysqld  [vdso]               [.] 0x00000000000008e6
        1,44%  mysqld  libpthread-2.17.so   [.] pthread_mutex_lock
        1,18%  mysqld  [kernel.kallsyms]    [k] unix_stream_recvmsg
        1,03%  mysqld  mysqld               [.] dispatch_command(enum_server_command, THD*, char*, unsigned int)
        0,99%  mysqld  mysqld               [.] JOIN::optimize_inner()
        0,98%  mysqld  mysqld               [.] JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*)
        0,88%  mysqld  mysqld               [.] malloc
        0,85%  mysqld  mysqld               [.] lex_one_token(void*, THD*)
      ...
        0,18%  mysqld  mysqld               [.] my_hash_sort_bin

      According to simple SELECT tests QPS went up from 14593 to 14843 (over 1.5%):

      CREATE TABLE IF NOT EXISTS t1(a INT) ENGINE=MEMORY;
      let $1=1000000;
      --disable_query_log
      --disable_result_log
      let $1=10000;
      while ($1)
      {
        SELECT * FROM t1;
        dec $1;
      }
      let $1=1000000;
      SELECT TIME_TO_SEC(NOW(4)) INTO @start_test;
      while ($1)
      {
        SELECT * FROM t1;
        dec $1;
      }
      --enable_result_log
      --enable_query_log
      SELECT ROUND(TIME_TO_SEC(NOW(4)) - @start_test, 4);
      DROP TABLE t1;

        Attachments

          Activity

            People

            Assignee:
            svoj Sergey Vojtovich
            Reporter:
            svoj Sergey Vojtovich
            Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.