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

fix innodb-adaptive-hash-index scalability with multiple threads

    XMLWordPrintable

Details

    Description

      InnoDB adaptive indexes has performance problems when running queries in parallel that uses InnoDB hash indexes (at least on the same tables).

      Here is an example of running the same SELECT query with 10-256 concurrent threads.
      (The query normally takes about 0.36 seconds):

      Total execution with 10 threads time: 1 seconds
      Total execution with 32 threads time: 1 seconds
      Total execution with 64 threads time: 3 seconds
      Total execution with 128 threads time: 5 seconds
      Total execution with 256 threads time: 9 seconds

      When using --innodb-adaptive-hash-index=1 we get:

      Total execution with 10 threads time: 1 seconds
      Total execution with 32 threads time: 5 seconds
      Total execution with 64 threads time: 9 seconds
      Total execution with 128 threads time: 18 seconds
      Total execution with 256 threads time: 50 seconds

      MySQL 5.7 does not have this issue. It has roughly the same speed with and without the hash indexes.

      In the above query, there was a driving table with a 400 rows doing lookups in a child table using 200 lookups on a secondary keys for every row from the driving table.
      Using innodb-adaptive-hash-index=1 speeds up the query with 37% when running a query in one thread but it slows down drastically when running 32-256 concurrent threads.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              monty Michael Widenius
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.