CODEWITHSUNDEEP
mysqlsqlmariadbpartitioning
Manu
Manu

Reputation: 165

Adding partition to already partitioned table by not touching existing partition data

I want to add new partitions to already partitioned table, this is straight forward if we use REORGANIZE as it's answered here, but in my case, I have huge data in p_max partition which is taking forever to REORGANIZE.

So, I want to add new partitions to the already partitioned table without REORGANIZING the current p_max partition so that new partitions get added instantly.

enter image description here

Upvotes: 0

Views: 1270

Answers (1)

Rick James
Rick James

Reputation: 142366

Simple: always have p_max empty. I like to call it future. Then, just before you need p_3, split future into p_3 and future. With no data in that partition, it is fast.

Details: http://mysql.rjweb.org/doc.php/partitionmaint#high_level_view_of_the_code

The "Why": http://mysql.rjweb.org/doc.php/partitionmaint#why_

(Both are in the same treatise on MySQL Partitioning.)

More

What are you PARTITIONing BY ...? (I can be less vague if I knew of your "BY ...".)

The best I can recommend is to take enough down-time to

ALTER TABLE ..
    REORGANIZE p_max to
        p_3 ... LESS THAN (...),
        p_4 ... LESS THAN (...),
        p_5 ... LESS THAN (...),
        p_max ... LESS THAN MAXVALUE;  -- empty

It may or may not be useful to have p_4 and p_5. It is 'cleaner' to break the data into whatever pattern you have established (eg, monthly), but it may not provide any benefit.

Note that that can leave a new p_max that is empty. Then, future reorgs can be fast.

As I note in my blog, p_max should always be empty, but if it accidentally gets filled with data, you won't be losing the data. Instead, the reorg will be slower (as you are observing).

Plan B This may be your best hope.

See the Percona Toolkit. It has pt-online-schema-update, which, I think, will handle Altering a Partitioned table.

Upvotes: 2

Related Questions