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

Performance degradation (suboptimal execution plan) on a query with expected range access

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • N/A
    • 10.1.1
    • Optimizer
    • None

    Description

      Observed on 10.0-mdev6384 development tree (10.0 with the patch for MDEV-6384 applied manually)
      Query:

       SELECT `pk` , MAX( `col_bigint_key` ) FROM `table10000_innodb_int_autoinc`  WHERE ( `col_smallint_key`  IN ( 255 , 255 ) OR  ( `pk` = 144 ) ) AND ( `col_bigint_key`  IN ( 3 , 155 ) AND `col_bigint_key`  IN ( 255 , 8 , 0 ) ) AND ( `col_bigint_key` IS  NULL OR  ( `col_bigint_key` <> 1 ) ) OR ( `col_smallint_key` IS  NULL AND `pk`  BETWEEN 121 AND 4 + 255 ) GROUP BY 1;

      Table:

      CREATE TABLE `table10000_innodb_int_autoinc` (
        `col_smallint_key` smallint(6) DEFAULT NULL,
        `col_bigint_key` bigint(20) DEFAULT NULL,
        `col_varchar_64_key` varchar(64) DEFAULT NULL,
        `col_varchar_10` varchar(10) DEFAULT NULL,
        `col_varchar_10_key` varchar(10) DEFAULT NULL,
        `col_varchar_64` varchar(64) DEFAULT NULL,
        `col_bigint` bigint(20) DEFAULT NULL,
        `pk` int(11) NOT NULL AUTO_INCREMENT,
        `col_smallint` smallint(6) DEFAULT NULL,
        PRIMARY KEY (`pk`),
        KEY `col_smallint_key` (`col_smallint_key`),
        KEY `col_bigint_key` (`col_bigint_key`),
        KEY `col_varchar_64_key` (`col_varchar_64_key`),
        KEY `col_varchar_10_key` (`col_varchar_10_key`)
      ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1

      (data dump is attached)

      10.0

      +------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+
      | id   | select_type | table                         | type  | possible_keys                           | key              | key_len | ref  | rows | filtered | Extra                                                               |
      +------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | table10000_innodb_int_autoinc | range | PRIMARY,col_smallint_key,col_bigint_key | col_smallint_key | 7       | NULL |   21 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+

      Execution:
      21 rows in set (0.00 sec)

      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Handler_commit             | 1     |
      | Handler_delete             | 0     |
      | Handler_discover           | 0     |
      | Handler_external_lock      | 0     |
      | Handler_icp_attempts       | 21    |
      | Handler_icp_match          | 21    |
      | Handler_mrr_init           | 0     |
      | Handler_mrr_key_refills    | 0     |
      | Handler_mrr_rowid_refills  | 0     |
      | Handler_prepare            | 0     |
      | Handler_read_first         | 0     |
      | Handler_read_key           | 22    |
      | Handler_read_last          | 0     |
      | Handler_read_next          | 21    |
      | Handler_read_prev          | 0     |
      | Handler_read_rnd           | 21    |
      | Handler_read_rnd_deleted   | 0     |
      | Handler_read_rnd_next      | 22    |
      | Handler_rollback           | 0     |
      | Handler_savepoint          | 0     |
      | Handler_savepoint_rollback | 0     |
      | Handler_tmp_update         | 0     |
      | Handler_tmp_write          | 21    |
      | Handler_update             | 0     |
      | Handler_write              | 0     |
      +----------------------------+-------+

      10.0-mdev6384

      -----------

      +------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
      | id   | select_type | table                         | type  | possible_keys                           | key     | key_len | ref  | rows  | filtered | Extra       |
      +------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
      |    1 | SIMPLE      | table10000_innodb_int_autoinc | index | PRIMARY,col_smallint_key,col_bigint_key | PRIMARY | 4       | NULL | 10000 |     0.21 | Using where |
      +------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)

      Execution:
      21 rows in set (0.06 sec)

      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Handler_commit             | 1     |
      | Handler_delete             | 0     |
      | Handler_discover           | 0     |
      | Handler_external_lock      | 0     |
      | Handler_icp_attempts       | 0     |
      | Handler_icp_match          | 0     |
      | Handler_mrr_init           | 0     |
      | Handler_mrr_key_refills    | 0     |
      | Handler_mrr_rowid_refills  | 0     |
      | Handler_prepare            | 0     |
      | Handler_read_first         | 1     |
      | Handler_read_key           | 0     |
      | Handler_read_last          | 0     |
      | Handler_read_next          | 10000 |
      | Handler_read_prev          | 0     |
      | Handler_read_rnd           | 0     |
      | Handler_read_rnd_deleted   | 0     |
      | Handler_read_rnd_next      | 0     |
      | Handler_rollback           | 0     |
      | Handler_savepoint          | 0     |
      | Handler_savepoint_rollback | 0     |
      | Handler_tmp_update         | 0     |
      | Handler_tmp_write          | 0     |
      | Handler_update             | 0     |
      | Handler_write              | 0     |
      +----------------------------+-------+

      The effect seems to be stable. Persistent statistics doesn't help.

      Attachments

        Issue Links

          Activity

            the problem is: get_range_limit_read_cost() has this condition

              if (table->quick_keys.is_set(keynr))
              {
                double quick_rows= table->quick_rows[keynr];
                double tmp;
                if (tab && table->quick_n_ranges[keynr] == 1)

            It is supposed to fire when there was a ref(const) which is equivalent to the quick select. Apparently quick_n_ranges==1 is a wrong condition.

            psergei Sergei Petrunia added a comment - the problem is: get_range_limit_read_cost() has this condition if (table->quick_keys.is_set(keynr)) { double quick_rows= table->quick_rows[keynr]; double tmp; if (tab && table->quick_n_ranges[keynr] == 1) It is supposed to fire when there was a ref(const) which is equivalent to the quick select. Apparently quick_n_ranges==1 is a wrong condition.

            A patch that should fix all regression and crash issues: psergey-mdev6454-attempt3.diff . The patch is against 10.0 tree, revision 4290, knielsen@knielsen-hq.org-20140711100647-nf3rdaf5ep26pgty.

            psergei Sergei Petrunia added a comment - A patch that should fix all regression and crash issues: psergey-mdev6454-attempt3.diff . The patch is against 10.0 tree, revision 4290, knielsen@knielsen-hq.org-20140711100647-nf3rdaf5ep26pgty.

            elenst, could you please make another test run with psergey-mdev6454-attempt3.diff? I'm interested in both performance regressions and crashes.

            psergei Sergei Petrunia added a comment - elenst , could you please make another test run with psergey-mdev6454-attempt3.diff? I'm interested in both performance regressions and crashes.

            Ok, I've now made a 10.1 tree with a fix for MDEV-6480, fix for this bug, and fix for MDEV-6384 all pushed. It is here: https://github.com/MariaDB/server/tree/bb-10.1-orderby-fixes.

            elenst, could you make another testing pass?

            psergei Sergei Petrunia added a comment - Ok, I've now made a 10.1 tree with a fix for MDEV-6480 , fix for this bug, and fix for MDEV-6384 all pushed. It is here: https://github.com/MariaDB/server/tree/bb-10.1-orderby-fixes . elenst , could you make another testing pass?

            Checked EXPLAINs again - there are no regressions in current 10.1. Too bad we can't add these testcases to the test suite.

            psergei Sergei Petrunia added a comment - Checked EXPLAINs again - there are no regressions in current 10.1. Too bad we can't add these testcases to the test suite.

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.