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

Versioned InnoDB table doesn't auto-create partitions in time due to imprecise statistics

    XMLWordPrintable

    Details

      Description

      I am not sure if anything can be done about it, possibly it will just have to be documented as a limitation. In this case, please add "Documentation" to the component and reassign to the documentation team.

      For InnoDB tables, intermediate statistics are often incorrect. Apparently, partition auto-creation (for LIMIT-based partitioning) relies on these statistics. It can cause regular and partition overflow, especially in busy environments.

      In the example below, LIMIT is 1000 rows, and each UPDATE updates 1500 rows. It means at least one new partition is expected to be created upon each upgrade (as I understand, the current logic can only create one, even if the update is larger than the limit). However, even that isn't happening.

      Since the values are non-deterministic, they can differ upon different executions, but the problem itself should still be visible.

      create or replace table t1 (a int) engine=InnoDB with system versioning partition by system_time limit 1000 auto;
      insert into t1 select seq from seq_1_to_1500;
      update t1 set a = a + 1;
      select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      update t1 set a = a + 1;
      select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      analyze table t1;
      select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      

      bb-10.5-midenok-MENT-654 7db57b2bb

      MariaDB [test]> create or replace table t1 (a int) with system versioning partition by system_time limit 1000 auto;
      Query OK, 0 rows affected (0.653 sec)
       
      MariaDB [test]> insert into t1 select seq from seq_1_to_1500;
      Query OK, 1500 rows affected (0.890 sec)
      Records: 1500  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> update t1 set a = a + 1;
      Query OK, 0 rows affected (1.665 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      +----------------+------------+
      | partition_name | table_rows |
      +----------------+------------+
      | p0             |          0 |
      | p1             |          0 |
      | pn             |         47 |
      +----------------+------------+
      3 rows in set (0.011 sec)
       
      MariaDB [test]> update t1 set a = a + 1;
      Query OK, 1500 rows affected (1.471 sec)
      Rows matched: 1500  Changed: 1500  Inserted: 1500  Warnings: 0
       
      MariaDB [test]> select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      +----------------+------------+
      | partition_name | table_rows |
      +----------------+------------+
      | p0             |       1500 |
      | p1             |          0 |
      | pn             |         47 |
      +----------------+------------+
      3 rows in set (0.002 sec)
       
      MariaDB [test]> analyze table t1;
      +---------+---------+----------+----------+
      | Table   | Op      | Msg_type | Msg_text |
      +---------+---------+----------+----------+
      | test.t1 | analyze | status   | OK       |
      +---------+---------+----------+----------+
      1 row in set (0.160 sec)
      

      MariaDB [test]> select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      +----------------+------------+
      | partition_name | table_rows |
      +----------------+------------+
      | p0             |       3000 |
      | p1             |          0 |
      | pn             |       1500 |
      +----------------+------------+
      3 rows in set (0.009 sec)
      

      Please also note there are no warnings at any point.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration