Is reorganizing a partition effectively the same as copying data from one table to another?

Question

If I have a maxvalue partition as such:

PARTITION p0 VALUES LESS THAN MAXVALUE

and then reorganize the partition so that it has two partitions - one for older data and one for current data:

ALTER TABLE mytable REORGANIZE PARTITION p0 INTO (
    PARTITION p20220401 VALUES LESS THAN (TO_DAYS('2022-04-01')),
    PARTITION p0 VALUES LESS THAN MAXVALUE 
)

Should I expect the process of reorganizing the partition to require similar server resources (i/o, cpu, memory) as though the same amount of data were being copied to new tables?

Is there a good way to benchmark this where I could possibly predict what I might expect in production?

Thank you in advance for any insight...

Answer 1

[To the title question] Yes.

Use sane partition naming.

• p0 sounds like the partition with the smallest dates. Instead call it future.
• p20220401 should contain row starting with, not ending before April 1.

But if the "future" partition is still empty, the reorg will be very fast.

That is, reorg the empty partition called future into p_2022_07 (next month) and future just before July 1. Then just before 8/1, p_2022_07 will be full and it is time to create p_2022_08.

This will leave a trail of monthly partitions. It is [usually] not practical to worry about how many there are.

I discuss that issue (and others) in Partition

Note that SELECTs are quite happy to search one or many or even all partitions. "Partition pruning" will happen if the WHERE clause includes a test on the column being used as the "partition key".

Your description was not clear on why you think partitioning will help.

Show us the main SELECTs so I can help in designing the PRIMARY KEY and any secondary INDEXes. The tips are different than for non-partitioned tables.

Sample

Initially create the table with a strange PRIMARY KEY; it will never need to be changed or rebuilt:

CREATE TABLE (
    id BIGINT AUTO_INCREMENT,
    dt ... DATETIME,
    ...
    PRIMARY KEY(id, dt),
    ...
    PARTITION BY RANGE (TO_DAYS(dt)) ...
) ENGINE=InnoDB;

The weekly (or however often) DROP PARTITION will throw away a bunch of ids; no problem.

The weekly REORGANIZE PARTITION will build a new partition which will receive ids with higher values and dates that are newer.

(Since you don't have that PK, there is a big effort to ALTER; this would require a one-time downtime. Using pt-online-schema-change might be a way to avoid the downtime.)