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

Optimizer sometimes use "index" instead of "range" access for UPDATE where PK IN (small list of values)

    XMLWordPrintable

Details

    • 10.1.18

    Description

      There is a big table in a statement-based parallel replication setup, like this:

      CREATE TABLE `t` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `action` enum('update','create','delete','query') NOT NULL,
      `status` enum('unclaimed','claimed','error','completed','paused','cancelled','exemptable','violation','blocked') NOT NULL,
      ... more columns here ...
      `last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `error_message` text CHARACTER SET utf8,
      `partition_key` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      ... more multiple-column indexes here ...
      ) ENGINE=InnoDB AUTO_INCREMENT=3184963639 DEFAULT CHARSET=latin1
       
      select * from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME = 't'\G
      *************************** 1. row ***************************
      TABLE_CATALOG: def
      TABLE_SCHEMA: test
      TABLE_NAME: t
      TABLE_TYPE: BASE TABLE
      ENGINE: InnoDB
      VERSION: 10
      ROW_FORMAT: Compact
      TABLE_ROWS: 271226130
      AVG_ROW_LENGTH: 227
      DATA_LENGTH: 61576822784
      MAX_DATA_LENGTH: 0
      INDEX_LENGTH: 93808164864
      DATA_FREE: 27502051328
      AUTO_INCREMENT: 3184963639
      CREATE_TIME: 2016-08-05 13:08:55
      UPDATE_TIME: NULL
      CHECK_TIME: NULL
      TABLE_COLLATION: latin1_swedish_ci
      CHECKSUM: NULL
      CREATE_OPTIONS:
      TABLE_COMMENT:
      1 row in set (0.00 sec)
      

      Usually status is updated for several ids in a single UPDATE like this:

      mysql> EXPLAIN UPDATE test.t SET status = 'claimed' WHERE id IN (3191531520, 3191531548, 3191675779, 3191675794, 3191849444, 3191879324, 3192120630, 3192120649, 3192120666, 3192120681, 3192120694, 3192120710, 3192120722, 3192120732, 3192120742, 3192120754, 3192120765, 3192152806, 3192177807, 3192252972, 3192284688, 3192284434, 3192284713, 3192284461, 3192284485, 3192284748, 3192284506, 3192284763, 3192284777, 3192284528, 3192284790, 3192284556, 3192284574, 3192284590, 3192284615, 3192284368, 3192284634, 3192284383, 3192284651, 3192284400, 3192284415, 3192284671, 3192440124, 3192570421, 3192570439, 3192570456, 3192570471, 3192570487)\G
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: t
      type: range
      possible_keys: PRIMARY
      key: PRIMARY
      key_len: 8
      ref: NULL
      rows: 48
      Extra: Using where
      1 row in set (0.00 sec)
      

      So, we have range access and reasonable estimation of rows in range. We may get this plan hundreds of times in a row, but sometimes we end up with a totally different plan while this same query is executed by slave:

      2339259 system user test Connect 2594 updating UPDATE t SET status = 'claimed' WHERE id IN (3191531520, 3191531548, 3191675779, 3191675794, 3191849444, 3191879324, 3192120630, 3192120649, 3192120666, 3192120681, 3192120694, 3192120710, 3192120722, 3192120732, 3192120742, 3192120754, 3192120765, 3192152806, 3192177807, 3192252972, 3192284688, 3192284434, 3192284713, 3192284461, 3192284485, 3192284748, 3192284506, 3192284763, 3192284777, 3192284528, 3192284790, 3192284556, 3192284574, 3192284590, 3192284615, 3192284368, 3192284634, 3192284383, 3192284651, 3192284400, 3192284415, 3192284671, 3192440124, 3192570421, 3192570439, 3192570456, 3192570471, 3192570487) 0.000
       
      mysql> SHOW EXPLAIN FOR 2339259\G
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: t
      type: index
      possible_keys: PRIMARY
      key: PRIMARY
      key_len: 8
      ref: NULL
      rows: 1
      Extra: Using where
      1 row in set, 1 warning (0.01 sec)
      

      Optimizer used full index scan for the PRIMARY key (and estimated rows as 1), and this caused a very slow execution of the query and millions of locks set in the process that stalled all other parallel replication threads eventually in a "deadlock".

      I suspect there is a bug/corner case somewhere in the optimizer when it decides about indexes available for range access. Maybe this is caused by bad InnoDB statistics etc, but in any case I think we should prevent such an execution path.

      Attachments

        1. 10.0-debug-printouts.diff
          7 kB
        2. 5.7-debug-printouts.diff
          7 kB
        3. csc10649-mysql5.7.15.diff
          3 kB
        4. mdev10649.test
          4 kB
        5. mdev10649.test-output.txt
          5 kB
        6. mdev10649-log.txt
          6 kB
        7. mdev10649-mysql57.test
          4 kB
        8. mdev10649-r2.diff
          2 kB

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              valerii Valerii Kravchuk
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.