[#MDEV-23640] Versioned InnoDB table doesn't auto-create partitions in time due to imprecise statistics

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.

[MDEV-23640] Versioned InnoDB table doesn't auto-create partitions in time due to imprecise statistics Created: 2020-03-24 Updated: 2021-04-23 Resolved: 2021-04-23
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB, Versioned Tables
Affects Version/s:	N/A
Fix Version/s:	N/A

[MDEV-23640] Versioned InnoDB table doesn't auto-create partitions in time due to imprecise statistics Created: 2020-03-24 Updated: 2021-04-23 Resolved: 2021-04-23