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

Default settings for partitioning and auto-partitioning by system time are not very practical

    XMLWordPrintable

    Details

      Description

      If partitioning structure is not specified, by default a versioned table is created with one history partition (and one current partition):

      MariaDB [test]> create or replace table t1 (a int) with system versioning partition by system_time limit 10000 auto;
      Query OK, 0 rows affected (0.400 sec)
       
      MariaDB [test]> show create table t1;
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                   |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
       PARTITION BY SYSTEM_TIME LIMIT 10000 AUTO_INCREMENT
      PARTITIONS 2 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.009 sec)
      

      Given that the hardcoded VERS_MIN_EMPTY for auto-partitioning is 1, it means that any DML on the table will trigger creation of a new partition, even a no-op DML:

      MariaDB [test]> delete from t1;
      Query OK, 0 rows affected (0.380 sec)
       
      MariaDB [test]> show create table t1;
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                   |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
       PARTITION BY SYSTEM_TIME LIMIT 10000 AUTO_INCREMENT
      PARTITIONS 3 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.012 sec)
      

      It doesn't seem to be reasonable. Maybe it makes sense to initially create 3 partitions by default, if partitioning structure is not specified? Or, limiting it to tables with auto-creation only, make sure that when auto-creation is added to a table (be it creating a new table or altering an existing one), this same DDL ensures that there are VERS_MIN_EMPTY+1 empty partitions at the end, so the next DML doesn't automatically cause creating of a new one?

        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:
                Resolved:

                  Git Integration