Details
-
Bug
-
Status: Open (View Workflow)
-
Trivial
-
Resolution: Unresolved
-
N/A
-
None
Description
Auto-partitioning happens even when only not versioned columns are updated.
It is clearly designed to be this way, so in all likelihood it's only a documentation request.
The reason why I think it should be documented explicitly is that I was looking for the ways to alleviate DDL deadlocks upon DML (e.g. MDEV-25547) which will almost inevitably affect real-life instances – maybe not frequently, but when they do happen, the effect can be severe.
One approach I was thinking of was to be more selective with which columns are versioned. In some setups, when the history of updates to fields which are modified frequently in high-concurrent workflow isn't strictly needed, users may try to exclude them from versioning, in hope that in this case auto-partitioning will happen only when certain most important fields are updated, possibly during some special maintenance activities which are less time-critical.
Surely enough, it doesn't help.
Interval-based partitioning happens regardless whether the updated columns are versioned or not.
|
bb-10.6-midenok-MDEV-17554 b96b96f9f |
MariaDB [test]> create or replace table t (pk int primary key, a int without system versioning) with system versioning partition by system_time interval 1 hour auto; |
Query OK, 0 rows affected (0.131 sec) |
|
|
MariaDB [test]> insert into t values (1,1); |
Query OK, 1 row affected (0.014 sec)
|
|
|
MariaDB [test]> set timestamp= @@timestamp + 3601; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> update t set a = 2; |
Query OK, 1 row affected (0.105 sec)
|
Rows matched: 1 Changed: 1 Inserted: 0 Warnings: 0 |
|
|
MariaDB [test]> select * from t for system_time all; |
+----+------+ |
| pk | a |
|
+----+------+ |
| 1 | 2 |
|
+----+------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> show create table t; |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| t | CREATE TABLE `t` ( |
`pk` int(11) NOT NULL, |
`a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, |
PRIMARY KEY (`pk`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING |
PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2021-04-30 00:00:00' AUTO |
PARTITIONS 3 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
With limit-based partitioning, it can be even more confusing, because the partition creation will more likely happen not when the versioned field is updated, but on the next update, which can very well be to a non-versioned column, something like
MariaDB [test]> create or replace table t (pk int primary key, a int without system versioning, b int) engine=MyISAM with system versioning partition by system_time limit 10 auto; |
Query OK, 0 rows affected (0.038 sec) |
|
|
MariaDB [test]> insert into t select seq, 0, 0 from seq_1_to_20; |
Query OK, 20 rows affected (0.002 sec) |
Records: 20 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> # updating the versioned field, partition is not created |
MariaDB [test]> update t set b = 1 where pk < 5; |
Query OK, 4 rows affected (0.001 sec) |
Rows matched: 4 Changed: 4 Inserted: 4 Warnings: 0 |
|
|
MariaDB [test]> update t set b = 2 where pk < 8; |
Query OK, 7 rows affected (0.001 sec) |
Rows matched: 7 Changed: 7 Inserted: 7 Warnings: 0 |
|
|
MariaDB [test]> select partition_name, table_rows from information_schema.partitions where table_name = 't'; |
+----------------+------------+ |
| partition_name | table_rows |
|
+----------------+------------+ |
| p0 | 11 |
|
| pn | 20 |
|
+----------------+------------+ |
2 rows in set (0.002 sec) |
|
|
MariaDB [test]> # updating the non-versioned field, partition is created |
MariaDB [test]> update t set a = 1 where pk = 1; |
Query OK, 1 row affected (0.036 sec)
|
Rows matched: 1 Changed: 1 Inserted: 0 Warnings: 0 |
|
|
MariaDB [test]> select partition_name, table_rows from information_schema.partitions where table_name = 't'; |
+----------------+------------+ |
| partition_name | table_rows |
|
+----------------+------------+ |
| p0 | 11 |
|
| p1 | 0 |
|
| pn | 20 |
|
+----------------+------------+ |
3 rows in set (0.003 sec) |
Attachments
Issue Links
- relates to
-
MDEV-17554 Auto-create history partitions for system-versioned tables
-
- Closed
-