MySQL: automatic partitions surely would be nice
In Deleting data we have been looking at a process that loads data into MySQL, leveraging partitions to make it easier and faster to later get rid of the data again. For this, we created three processes, a data loader process, and two observers - one for creating partitions, and one for deleting them.
The observer processes have been running ANALYZE TABLES and then polling INFORMATION_SCHEMA.PARTITIONS every 1/10th of a second to check if intervention is needed. They then have been dynamically generating the necessary ALTER TABLE statements maintaining the proper partitioning of the table by adding and dropping additional partitions.
That is cumbersome and should not be necessary.
Using SQL to maintain partitions
It is possible to prepare and execute dynamic DDL in MySQL, using PREPARE, EXECUTE and DEALLOCATE PREPARE.
So I can do the following, if I numb myself sufficiently to actually write and generate code in procedural SQL:
kris@localhost [kris]> set @next_name := "p3";
Query OK, 0 rows affected (0.00 sec)
kris@localhost [kris]> set @next_limit := "30000";
Query OK, 0 rows affected (0.00 sec)
kris@localhost [kris]> select
-> concat("alter table data add partition ( partition ",
-> @next_name,
-> " values less than (",
-> @next_limit,
-> "))") as cmd into @cmd;
Query OK, 1 row affected (0.00 sec)
kris@localhost [kris]> select @cmd;
+-------------------------------------------------------------------------+
| @cmd |
+-------------------------------------------------------------------------+
| alter table data add partition ( partition p3 values less than (30000)) |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
kris@localhost [kris]> prepare s from @cmd;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
kris@localhost [kris]> execute s;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
kris@localhost [kris]> deallocate prepare s;
Query OK, 0 rows affected (0.00 sec)
kris@localhost [kris]> show create table data\G
Table: data
Create Table: CREATE TABLE `data` (
`id` int NOT NULL AUTO_INCREMENT,
`d` varchar(64) NOT NULL,
`e` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`id`)
(PARTITION p1 VALUES LESS THAN (10000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (20000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (30000) ENGINE = InnoDB)
1 row in set (0.00 sec)
I could put the logic of the partitioner and dropper into stored procedures and use the MySQL Event Scheduler to have this running in the background at all times to maintain the partitions on the data table.
Except that would still fail if I insert too many rows in a single transaction.
I may be able to run the calls to my stored procedures in an insert trigger, but that is overhead I’d rather not have for each row inserted. Also, I am pretty confident that the trigger will also trigger a number of bugs in rarely used code paths. :-)
Wishful thinking
An actual solution would be a less cumbersome notation for partitions, specifically range partitioning by an auto_increment primary key, and range partitioning along a time dimensions.
I should be able to write how many partitions of which bucket size I want instead of maintaining a giant if-then-else of VALUES LESS THAN statements. SQL is supposed to be a declarative language, after all.
For the time dimension, I should be able to specify the same, in retention time and intervals for the buckets. MySQL would then create and drop partitions automatically, and generate the names for them automatically, too.
So something like the following made-up syntax:
-- maintain 10 buckets,
-- equivalent to
-- VALUES LESS THAN (<previous value> + 10000)
--
-- When new buckets are autogenerated at the top, and the number is larger than 10,
-- drop the lowest one.
--
create table data (
`id` int NOT NULL AUTO_INCREMENT,
`d` varchar(64) NOT NULL,
`e` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY AUTOMATIC RANGE (`id`) ( PARTITIONS 10 VALUES (10000))
-- maintain 10 buckets
-- equivalent to
-- VALUES LESS THAN (UNIX_TIMETSTAMP(<previous value> + INTERVAL 1 DAY))
--
-- When new buckets are autogenerated at the end, and the number is larger than 10,
-- drop the oldest one.
create table data (
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`d` varchar(64) NOT NULL,
`e` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY AUTOMATIC TIME RANGE (`created`) (PARTITIONS 10 VALUES (INTERVAL 1 DAY))
This would get rid of any manually maintained procedures, events, triggers, and most importantly, implementations, and specify procedurally how and when partitions are created and how long they are kept.
Not quite as automatic as Cassandra TTL fields, but a large step forward.