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

InnoDB locks dict_sys.latch for a long time during ALTER TABLE

    XMLWordPrintable

Details

    Description

      Our MariaDB servers with many thousands of clients sometimes encounter slow ALTER TABLE queries (up to 25 seconds) with adjacent read-only queries also being just as slow; all read-only queries apparently wait for the one ALTER TABLE to finish the commit.
      Using two BPF scripts (https://github.com/iovisor/bcc/pull/5108 and https://github.com/iovisor/bcc/pull/5112), I pinpointed this to contention on dict_sys.latch; a common backtrace looks like this:

        syscall
      		 
        unlock_and_close_files(std::vector<pfs_os_file_t, std::allocator<pfs_os_file_t> > const&, trx_t*)
      		 
        ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool)
      		 
        mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, MDL_request*, st_ddl_log_state*, TRIGGER_RENAME_PARAM*, Alter_table_ctx*, bool&, unsigned long long&, bool) [clone .constprop.0]
      		 
        mysql_alter_table(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, Table_specification_st*, TABLE_LIST*, Recreate_info*, Alter_info*, unsigned int, st_order*, bool, bool)
      		 
        Sql_cmd_alter_table::execute(THD*)
      		 
        mysql_execute_command(THD*, bool)
      		 
        mysql_parse(THD*, char*, unsigned int, Parser_state*)
      		 
        dispatch_command(enum_server_command, THD*, char*, unsigned int, bool)
      		 
        do_command(THD*, bool)

      The method ha_innobase::commit_inplace_alter_table indeed does most of its I/O while having dict_sys.latch locked exclusively. I believe that one should not do any I/O while holding such a (global) lock, one that is commonly locked for all queries, including read-only queries.
      This is a major scalability issue for us, because it is easy for (unprivileged) MariaDB users to lock up the whole daemon (including all databases and all catalogs) and it could be used to DoS the MariaDB server.

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35154 dict_sys_t::load_table() is holding exclusive dict_sys.latch for unnecessarily long time

          • Major - Major loss of function.
          • Confirmed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8069 DROP or rebuild of a large table may lock up InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15641 InnoDB crash while committing table-rebuilding ALTER TABLE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34999 ha_innobase::open() should not acquire dict_sys.latch twice

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35436 dict_stats_fetch_from_ps() unnecessarily acquires exclusive dict_sys.latch

          • Major - Major loss of function.
          • Open

          Activity

            People

              marko Marko Mäkelä
              maxk Max Kellermann
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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