0

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...

1 Answer 1

0

[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.)

9
  • We have an app with a rolling ~6months of accessible data. Data prior is not accessible to users & can effectively be deleted. We were looking to partition on a "created date" column. We only need 1 partition of active data & 1 partition of inactive data which we could then drop after moving from active. One partition per month created in advance also makes sense. At any one time we would have ~6 partitions. The downside is we won't be able to update the application to specify which partition to query so it will always have to scan every partition. Better to have 1 big partition or 6 smaller? Jun 22 at 19:05
  • "which partition to query" -- Just query the table; don't worry about which partition(s) are needed.
    – Rick James
    Jun 22 at 20:14
  • 1 big / 6 small. Based on what you have said, I would do weekly partitions (about 28 partitions). See the discussion in my link.
    – Rick James
    Jun 22 at 20:16
  • Thank you for your insightful comments - After posting the initial question I started to seriously consider that we could just run a job daily to delete a day's worth of data. This wouldn't incur the overhead of having to drop and recreate the primary key for example. One of our tables is already at 100 M rows which means the prerequisite schema changes will be very painful to deploy. Would you view deleting rows from the table (in off hours, of course) as a viable alternative to partitioning? Jun 22 at 23:39
  • @user19339351 - DELETE is much slower than DROP PARTITION. The PK never needs to be dropped and recreated.
    – Rick James
    Jun 23 at 0:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.