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

ALTER .. COPY removes and doesn't re-create InnoDB table/index statistics

    XMLWordPrintable

Details

    Description

      InnoDB persistent statistics behaves quite unexpectedly upon ALTER .. COPY (and differently non-copying ALTER).
      Below is an original report demonstrating it via a vector key on 11.7. See comments for a generic test case without vectors, applicable to older versions

      With vector key:

      7fce19bd215ac0671855044520092aa4210049d1

      MariaDB [test]> create or replace table t (pk int primary key, a int, v vector(1) not null, vector(v)) engine=InnoDB;
      Query OK, 0 rows affected (0.110 sec)
       
      MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test';
      +------------+
      | table_name |
      +------------+
      | t          |
      | t#i#01     |
      +------------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test';
      +------------+-----------------+--------------+
      | table_name | index_name      | stat_name    |
      +------------+-----------------+--------------+
      | t          | PRIMARY         | n_diff_pfx01 |
      | t          | PRIMARY         | n_leaf_pages |
      | t          | PRIMARY         | size         |
      | t#i#01     | GEN_CLUST_INDEX | n_diff_pfx01 |
      | t#i#01     | GEN_CLUST_INDEX | n_leaf_pages |
      | t#i#01     | GEN_CLUST_INDEX | size         |
      | t#i#01     | layer           | n_diff_pfx01 |
      | t#i#01     | layer           | n_diff_pfx02 |
      | t#i#01     | layer           | n_leaf_pages |
      | t#i#01     | layer           | size         |
      | t#i#01     | tref            | n_diff_pfx01 |
      | t#i#01     | tref            | n_leaf_pages |
      | t#i#01     | tref            | size         |
      +------------+-----------------+--------------+
      13 rows in set (0.001 sec)
      

      MariaDB [test]> alter table t add index (a);
      Query OK, 0 rows affected (0.140 sec)              
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test';
      Empty set (0.001 sec)
       
      MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test';
      Empty set (0.001 sec)
      

      I suppose it is expected that separate records are created for the index pseudo-table t#i#01; however, it is unclear why an innocent operation on the table (in this case an addition of another index, but also removal of any of the indexes) clears all table/index statistics.

      It doesn't happen without vector key in otherwise identical circumstances; instead, statistical records for the new index are added, as would be reasonable to expect:

      MariaDB [test]> create or replace table t (pk int primary key, a int, v vector(1) not null) engine=InnoDB;
      Query OK, 0 rows affected (0.080 sec)
       
      MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test';
      +------------+
      | table_name |
      +------------+
      | t          |
      +------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test';
      +------------+------------+--------------+
      | table_name | index_name | stat_name    |
      +------------+------------+--------------+
      | t          | PRIMARY    | n_diff_pfx01 |
      | t          | PRIMARY    | n_leaf_pages |
      | t          | PRIMARY    | size         |
      +------------+------------+--------------+
      3 rows in set (0.001 sec)
      

      MariaDB [test]> alter table t add index (a);
      Query OK, 0 rows affected (0.043 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test';
      +------------+
      | table_name |
      +------------+
      | t          |
      +------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test';
      +------------+------------+--------------+
      | table_name | index_name | stat_name    |
      +------------+------------+--------------+
      | t          | PRIMARY    | n_diff_pfx01 |
      | t          | PRIMARY    | n_leaf_pages |
      | t          | PRIMARY    | size         |
      | t          | a          | n_diff_pfx01 |
      | t          | a          | n_diff_pfx02 |
      | t          | a          | n_leaf_pages |
      | t          | a          | size         |
      +------------+------------+--------------+
      7 rows in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.