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

InnoDB statistics update may temporarily cause wrong index cardinalities

    XMLWordPrintable

Details

    • 10.1.18

    Description

      This is a continuation of MDEV-10649, but this time, it is about index cardinalities.

      Under concurrent load, if you're lucky, you may get a very wrong index cardinality estimates.

      Here's a way to observe:
      Apply psergey-sept11-stop-at-index-update.diff. This patch adds capability to stop the index cardinality update code at the right place.

      Then, start the server and create the test dataset:

      create table ten(a int) engine=myisam;
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table one_k(a int) engine=myisam;
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      create table t0 (a int) engine=myisam;
      insert into t0 values (1),(2),(3);
       
      create table t1 (
        pk int primary key,
        col1 int not null,
        col2 int not null,
        key(col1)
      ) engine= innodb;
       
      insert into t1 select a,a,a from one_k;
      analyze table t1;
      

      Run a query and observe t1.rows=1 which is a precise index statistics:

       
      explain select * from t0, t1 where t1.col1=t0.a;
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref     | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      |    1 | SIMPLE      | t0    | ALL  | NULL          | NULL | NULL    | NULL    |    3 | Using where |
      |    1 | SIMPLE      | t1    | ref  | col1          | col1 | 4       | j5.t0.a |    1 |             |
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      

      Now, make innodb's statistics update stop in the middle of update:

      system touch /tmp/dict_stats_analyze_index_must_stop
      

      Do something to cause auto statistics update (we are running with innodb_stats_auto_recalc=ON, which is the default).

      insert into t1 select 5000+pk,5000+col1, 5000+col2 from t1 limit 2000;
      Query OK, 1000 rows affected (0.37 sec)
      Records: 1000  Duplicates: 0  Warnings: 0
      

      OPTIONAL: You can check the server stderr and see this line:

        AAA: dict_stats_analyze_index called dict_stats_empty_index for col1
      

      Then, run the test query again:

      flush tables;
      explain select * from t0, t1 where t1.col1=t0.a;
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref     | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      |    1 | SIMPLE      | t0    | ALL  | NULL          | NULL | NULL    | NULL    |    3 | Using where |
      |    1 | SIMPLE      | t1    | ref  | col1          | col1 | 4       | j5.t0.a | 1000 |             |
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      

      Note rows=1000 ! The real value is rows=1, both before and after the update.
      The value one is seeing is about table_rows/2.

      Make the stats calculation finish:

      system rm  /tmp/dict_stats_analyze_index_must_stop
      

      wait a few seconds, and check again:

      flush tables;
      explain select * from t0, t1 where t1.col1=t0.a;
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref     | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      |    1 | SIMPLE      | t0    | ALL  | NULL          | NULL | NULL    | NULL    |    3 | Using where |
      |    1 | SIMPLE      | t1    | ref  | col1          | col1 | 4       | j5.t0.a |    1 |             |
      +------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
      

      rows=1, the correct value is back.

      Attachments

        1. mdev10790-mysql-5.7.diff
          1 kB
          Sergei Petrunia
        2. mdev10790-mysql-5.7.log
          6 kB
          Sergei Petrunia
        3. psergey-sept11-stop-at-index-update.diff
          1 kB
          Sergei Petrunia

        Issue Links

          Activity

            People

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