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

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

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

        Activity

          No workflow transitions have been executed yet.

          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.