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

Auto-create history partitions for system-versioned tables

Details

    Description

      Syntax change

      Keyword AUTO enables partition auto-creation.

      create table t1 (x int) with system versioning
      partition by system_time interval 1 hour auto;
       
      create table t1 (x int) with system versioning
      partition by system_time interval 1 month
      starts '2021-01-01 00:00:00' auto partitions 12;
       
      create table t1 (x int) with system versioning
      partition by system_time limit 1000 auto;
      

      Or with explicit partitions:

      create table t1 (x int) with system versioning
      partition by system_time interval 1 hour auto
      (partition p0 history, partition pn current);
      

      Description

      Before executing history-generating DML command add N history
      partitions, so that N would be sufficient for potentially generated
      history. N > 1 may be required when history is rotated by INTERVAL and
      timestamp was jumped to future further than interval value.

      If last history partition exceeds LIMIT records before DML command
      then new history partition is created and the history filling is
      switched to the new partition. Thus LIMIT does not carry strict
      limitation and the history partition size must be planned as LIMIT
      records plus average number of history one DML generates.

      Auto-creation is implemented by synchronous
      fast_alter_partition_table() call from the thread of the executed DML
      command before the command itself (by the fallback and retry mechanism
      similar to Discovery feature, see Open_table_context).

      The name for newly added partitions are generated like default
      partition names with extension of MDEV-22155 (which avoids name
      clashes by extending assignment counter to next free-enough gap).

      These DML commands trigger auto-creation:

      • DELETE (including multi-delete, excluding DELETE HISTORY)
      • UPDATE (including multi-update)
      • REPLACE (including REPLACE .. SELECT)
      • INSERT .. ON DUPLICATE KEY UPDATE
      • LOAD DATA .. REPLACE

      MDEV-23642 Locking timeout caused by auto-creation affects original DML

      The reasons for this are:

      • Do not disrupt main business process (the history is auxiliary
        service);
      • Consequences are non-fatal (history is not lost, but comes into wrong
        partition; fixed by partitioning rebuild);
      • There is more freedom for application to fail in this case or
        not: it may read warning info and find corresponding error number.
      • While non-failing command is easy to handle by an application and
        fail it, the opposite is hard to handle: there is no automatic
        actions to fix failed command and retry, DBA intervention is
        required and until then application is non-functioning.

      Attachments

        Issue Links

          Activity

            This first implementation is done in standalone thread. The drawback of this is that ALTER may be theoretically executed on unwanted table:

            1. Table t1 is locked with LOCK TABLES;
            2. ALTER TABLE t1 ADD PARTITION is initiated in parallel and blocked on lock;
            3. t1 is unlocked, dropped and new t1 is created.
            4. ALTER continues and adds partition to "unwanted" table.

            Current implementation does not have proper solution for this. But the ALTER will succeed only if new t1 is partitioned by SYSTEM_TIME and there is no partition by the name it tries to add. Though this behavior is generally unwanted, it has minor impact of one extra history partition in new table. In most cases ALTER will fail after old t1 is dropped, so this is low probability.

            midenok Aleksey Midenkov added a comment - This first implementation is done in standalone thread. The drawback of this is that ALTER may be theoretically executed on unwanted table: 1. Table t1 is locked with LOCK TABLES ; 2. ALTER TABLE t1 ADD PARTITION is initiated in parallel and blocked on lock; 3. t1 is unlocked, dropped and new t1 is created. 4. ALTER continues and adds partition to "unwanted" table. Current implementation does not have proper solution for this. But the ALTER will succeed only if new t1 is partitioned by SYSTEM_TIME and there is no partition by the name it tries to add. Though this behavior is generally unwanted, it has minor impact of one extra history partition in new table. In most cases ALTER will fail after old t1 is dropped, so this is low probability.

            This task is continued by MDEV-21747.

            midenok Aleksey Midenkov added a comment - This task is continued by MDEV-21747 .
            midenok Aleksey Midenkov added a comment - Please review bb-10.7-midenok-MDEV-17554
            serg Sergei Golubchik added a comment - - edited

            commit e9f1bd41a29 looks ok!
            please, push into preview-10.8-MDEV-17554-auto-create-partition (or any other few-word description that you prefer) and set the status to "In Testing".

            serg Sergei Golubchik added a comment - - edited commit e9f1bd41a29 looks ok! please, push into preview-10.8- MDEV-17554 -auto-create-partition (or any other few-word description that you prefer) and set the status to "In Testing".

            the branch name is now bb-10.8-MDEV-17554-auto-create-partition

            serg Sergei Golubchik added a comment - the branch name is now bb-10.8- MDEV-17554 -auto-create-partition

            Please rebase it on the current 10.9 or at least 10.8 before moving to testing.

            elenst Elena Stepanova added a comment - Please rebase it on the current 10.9 or at least 10.8 before moving to testing.
            midenok Aleksey Midenkov added a comment - - edited

            Rebased. Please test bb-10.9-MDEV-17554-auto-create-partition

            midenok Aleksey Midenkov added a comment - - edited Rebased. Please test bb-10.9- MDEV-17554 -auto-create-partition

            Testing done.

            lstartseva Lena Startseva added a comment - Testing done.

            People

              midenok Aleksey Midenkov
              umoser Ulrich Moser (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.