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

CREATE TEMPORARY TABLE vs plain SELECT causes 280x perf drop: InnoDB takes unneeded locks

    XMLWordPrintable

Details

    • 10.0.22

    Description

      With the attached SQL and psdoit script to run queries concurrently, you can see that the CREATE TEMPORARY TABLE part of the query causes concurrency to tank. The related issue of MDEV-8684 is for problems in the InnoDB mutex code, while this bug is for the fact that InnoDB is down in lock_rec_convert_impl_to_exp() when it probably shouldn't be.

      It appears that CREATE TEMPORARY TABLE is triggering InnoDB to go and do a whole bunch of locking that it doesn't do when you just run a plain SELECT.

      The addition of CREATE TEMPORARY TABLE makes performance 280x worse.

       # mysql -u root < sql
       
       # time ./psdoit 1
       0.022s
       
       # time ./psdoit 10
       0.112s
       
       # time ./psdoit 100
       7.150s
       
       # time ./psdoit 200
       30.422s
       
       # time ./psdoit 400
       190.378s

      Things get very bad, very fast. We spend almost all our time in a locking
      food fight:

           37.42%  mysqld         mysqld              [.] ut_delay(unsigned long)                                                                                     
      		    |
      		    ---ut_delay(unsigned long)
      		       |          
      		       |--99.74%-- mutex_spin_wait(void*, bool, char const*, unsigned long)
      		       |          |          
      		       |          |--74.70%-- pfs_mutex_enter_func(ib_mutex_t*, char const*, unsigned long) [clone .constprop.71]
      		       |          |          lock_rec_convert_impl_to_expl(buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*)
      		       |          |          lock_clust_rec_read_check_and_lock(unsigned long, buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const
      		       |          |          sel_set_rec_lock(buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*, unsigned long, unsigned long, 
      		       |          |          row_search_for_mysql(unsigned char*, unsigned long, row_prebuilt_t*, unsigned long, unsigned long)
      		       |          |          |          
      		       |          |          |--76.07%-- ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)
      		       |          |          |          handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)
      		       |          |          |          handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)
      		       |          |          |          join_read_key2(THD*, st_join_table*, TABLE*, st_table_ref*)
      		       |          |          |          sub_select(JOIN*, st_join_table*, bool)
      		       |          |          |

      and:

           27.96%  mysqld         [kernel.kallsyms]   [k] _raw_spin_lock                                                                                   
      		    |
      		    ---_raw_spin_lock
      		       |          
      		       |--58.11%-- futex_wait_setup
      		       |          |          
      		       |          |--99.96%-- 0
      		       |          |          futex_wait
      		       |          |          do_futex
      		       |          |          sys_futex
      		       |          |          system_call
      		       |          |          |          
      		       |          |          |--64.91%-- __lll_lock_wait
      		       |          |          |          |          
      		       |          |          |          |--83.30%-- pthread_mutex_lock
      		       |          |          |          |          |          
      		       |          |          |          |          |--52.72%-- os_event_reset(os_event*)
      		       |          |          |          |          |          sync_array_reserve_cell(sync_array_t*, void*, unsigned long, char const*, unsigned long, unsi
      		       |          |          |          |          |          mutex_spin_wait(void*, bool, char const*, unsigned long)
      		       |          |          |          |          |          |          
      		       |          |          |          |          |          |--75.08%-- pfs_mutex_enter_func(ib_mutex_t*, char const*, unsigned long) [clone .constprop.7
      		       |          |          |          |          |          |          lock_rec_convert_impl_to_expl(buf_block_t const*, unsigned char const*, dict_index
      		       |          |          |          |          |          |          lock_clust_rec_read_check_and_lock(unsigned long, buf_block_t const*, unsigned cha
      		       |          |          |          |          |          |          sel_set_rec_lock(buf_block_t const*, unsigned char const*, dict_index_t*, unsigned
      		       |          |          |          |          |          |          row_search_for_mysql(unsigned char*, unsigned long, row_prebuilt_t*, unsigned long 

      Attachments

        1. psdoit
          0.6 kB
        2. sql
          314 kB

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              stewart-ibm Stewart Smith
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.