Mysql – Create Range Partition on existing large MySQL table

MySQLpartitioning

I have inherited a database that contains a large table (relative to the rest of the database – 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 days can be deleted.

I'm looking to partition this table by range (preferably by day), then run an event to keep things tidy by dropping partitions outside of the retention period.

The data in my table goes back around 6 months, so the first thing I'm going to do is run a manual delete on anything older than 60 days so I''m starting with a clean slate.

Then I need to create the partitions, but do I really have to list all my partitions in the alter table statement? i.e.

ALTER TABLE AuditData
PARTITION BY RANGE( TO_DAYS(date) ) ( 
PARTITION part1 VALUES LESS THAN( TO_DAYS('2018-01-01 00:00:00') ), 
PARTITION part2 VALUES LESS THAN( TO_DAYS('2018-02-01 00:00:00') ), 
PARTITION part3 VALUES LESS THAN( TO_DAYS('2018-03-01 00:00:00') ), 
PARTITION part4 VALUES LESS THAN( TO_DAYS('2018-04-01 00:00:00') ), 
PARTITION part5 VALUES LESS THAN( TO_DAYS('2018-05-01 00:00:00') ),

… and so on

Is there a way to create the partition just by saying, partition the existing data by day?

I understand I will need to run a daily job to create a partition.

Thanks

Best Answer

Unfortunately, no shortcut.

Do have an empty partition for "future" data; REORGANIZE to tomorrow and a fresh future. More discussion.

The ALTER will copy the entire table over, so be prepared for not being able to use the table for some time.

Related Solutions

Mysql – Where is the old partition in thesql show create table

You don't have plessthanmax part? When you set up a partition (that "moves" daily) the best practice is to set up a plessthanmax part to avoid errors. Each day you create a new partition and reorganise the plessthanmax like this :

For December 29

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p24 VALUES LESS THAN (to_days('2012-12-29')), partition plessthanmax values less than maxvalue)

For December 30

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p25 VALUES LESS THAN (to_days('2012-12-30')), partition plessthanmax values less than maxvalue)

Max.

Mysql – Partition by year and sub-partition by month thesql

I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.

In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date) but only by HASH(MONTH(nav_date)) or KEY(MONTH(nav_date)).

So to solve the problem I decided to create a new month column in my table and then I added an index to it. Then I subpartioned by KEY(month) without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.

Follows the full example:

CREATE TABLE `my_example` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `value` double DEFAULT NULL,
    `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
    `timestamp` datetime NOT NULL,
    `last_modified` datetime NOT NULL,
    `month` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`,`timestamp`, `month`),
    KEY `in_is_deleted` (`is_deleted`),
    KEY `in_last_modified` (`last_modified`),
    KEY `in_timestamp` (`timestamp`),
    KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`) 
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition

Best Answer

Related Solutions

Mysql – Where is the old partition in thesql show create table

Mysql – Partition by year and sub-partition by month thesql

Related Question