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

Auto-create: Partition creation happens also when modified columns are not versioned

    XMLWordPrintable

    Details

      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

            Activity

              People

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

                Dates

                Created:
                Updated:

                  Git Integration