Cheap way to create near constant size range partitions in MySQL

Question

I'm using MySQL 5.7 Percona.

My current design uses naive day-by-day partitioning, which adds new partition for next time period on regular basis.

CREATE TABLE `foo` (
  ...
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (UNIX_TIMESTAMP(`created_at`)) (
    PARTITION `foo_1640995200` VALUES LESS THAN (1640995200) ENGINE = InnoDB, # 2022-01-01 00:00:00
    PARTITION `foo_1641081600` VALUES LESS THAN (1641081600) ENGINE = InnoDB, # 2022-01-02 00:00:00
    PARTITION `foo_1641168000` VALUES LESS THAN (1641168000) ENGINE = InnoDB  # 2022-01-03 00:00:00
);

The issue with that approach is that my data distribution is uneven. Some partitions have 1M rows, some have 50M. Which leads to another issue - amount of opened tables during some long range selects like SELECT * FROM foo WHERE created_at > NOW() - INTERVAL 1 YEAR.

I want to optimize it to simply extend last partition if amount of rows is below some threshold instead of creating partition for next day. Like:

SELECT `table_rows`
FROM `information_schema`.`partitions`
WHERE table_schema = DATABASE()
    AND partition_name = 'foo_1641168000';

-- only 1M rows, no need for new partition, extend existing one:

ALTER TABLE `foo` REORGANIZE PARTITION `foo_1641168000` INTO (
    PARTITION `foo_1641254400` VALUES LESS THAN (1641254400) ENGINE = InnoDB  # 2022-01-04 00:00:00
);

However this operation despite being simple range change completly rewrites partition foo_1641168000 data. Despite the fact that all data from existing partition fit into new definition. Which is no-go due to table locks and excessive I/O usage.

Is there any way to achieve this without rewriting data?

BTW: My hacky idea was to add recent data to another table foo_recent and when it grows to certain size install it as partition in foo using EXCHANGE PARTITION .. WITHOUT VALIDATION. But this is dirty and worse both in terms of performance and syntax - queries must work on tables union or be ran on two tables independently with result merging.

user_1766831 · Answer

REORGANIZE will read the 'from' partitions and write the 'to' partitions. Costly -- unless the 'froms' are empty.

Have a partition called 'future' that is LESS THAN MAXVALUE and is 'always' empty.

You are stuck with copying over lots of data.

Plan A:

Each night, before midnight, do this if the 'last' partition (before 'future') is getting "big":

REORGANIZE last, future 
      INTO last, soon, future;

Set the LESS THAN (for 'last') to end at midnight tonight. Set the LESS THAN for 'soon' to, say, a month from now. (This is the only big copy.)

Plan B:

The following may be a viable alternative. (I just thought of it; I have not tried it.) Each night, see if the "last" (before "future") is "big enough". When it is, do these steps (just before each midnight):

Use "transportable tablespaces" to remove the big partition from the table. (Note: a partition is essentially a table, so this action is only touching "meta" information. I'm pretty sure no data is copied.)
Turn right around and again use "transportable tablespaces" to put it back into the partitioned table, but with a different LESS THAN -- set to midnight tonight.
REORGANIZE future INTO soon, future; -- Both of those are empty, so this is quite fast. (The LESS THAN for 'soon' is some time in the future. I hesitate to make it "MAXVALUE", but that might work and be even simpler.)

If you try it and it works, let me know. I would like to add it to my Partition Maintenance blog

Cheap way to create near constant size range partitions in MySQL

1 Answers