Automatically Dropping Old Partitions in MySQL and MariaDB

Geoff MonteeMariaDB, MySQL, Partitioning10 Comments

A MariaDB Support customer recently asked how they could automatically drop old partitions after 6 months. MariaDB and MySQL do not have a mechanism to do this automatically out-of-the-box, but it is not too difficult to create a custom stored procedure and an event to call the procedure on the desired schedule. In this blog post, I will show one way to do that.

Partitioned table definition

For this demonstration, I’ll use a table definition based on one from MySQL’s documentation on range partitioning, with some minor changes:

DROP TABLE IF EXISTS db1.quarterly_report_status;

CREATE TABLE db1.quarterly_report_status (
   report_id INT NOT NULL,
   report_status VARCHAR(20) NOT NULL,
   report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
   PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00')),
   PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00')),
   PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00')),
   PARTITION p201701 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-02-01 00:00:00')),
   PARTITION p201702 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-03-01 00:00:00')),
   PARTITION p201703 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00')),
   PARTITION p201704 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-05-01 00:00:00')),
   PARTITION p201705 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-06-01 00:00:00')),
   PARTITION p201706 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00')),
   PARTITION p201707 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-08-01 00:00:00')),
   PARTITION p201708 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-09-01 00:00:00')),
   PARTITION p_default VALUES LESS THAN (MAXVALUE)
);

The most significant change is that the partition naming scheme is based on the date. This will allow us to more easily determine which partitions to remove.

Stored procedure definition

The stored procedure itself contains some comments that explain what it does, so I will let the code speak for itself, for the most part:

DROP PROCEDURE IF EXISTS db1.drop_old_partitions;

DELIMITER $$
CREATE PROCEDURE db1.drop_old_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_keep int)
   LANGUAGE SQL
   NOT DETERMINISTIC
   SQL SECURITY INVOKER
BEGIN  
   DECLARE done INT DEFAULT FALSE;
   DECLARE current_partition_name varchar(64);
   -- We'll use this cursor later to get
   -- the list of partitions to drop.
   -- @last_partition_name_to_keep will be
   -- set later.
   DECLARE cur1 CURSOR FOR 
   SELECT partition_name 
   FROM information_schema.partitions 
   WHERE TABLE_SCHEMA = p_schema 
   AND TABLE_NAME = p_table 
   AND PARTITION_NAME != 'p_default' 
   AND PARTITION_NAME < @last_partition_name_to_keep;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   
   -- Now we get the last month of data that we want to keep
   -- by subtracting p_months_to_keep from the current date.
   -- Note that it will actually keep p_months_to_keep+1 partitions,
   -- since the current month is not complete.
   SET @date = CURDATE();
   SET @months_to_keep = p_months_to_keep;   
   SET @q = 'SELECT DATE_SUB(?, INTERVAL ? MONTH) INTO @last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st USING @date, @months_to_keep;
   DEALLOCATE PREPARE st;
   
   -- Then we format the last month in the same format used
   -- in our partition names.
   SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y%m'') INTO @formatted_last_month_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   
   -- And then we use the formatted date to build the name of the
   -- last partition that we want to keep. This partition name is
   -- assigned to @last_partition_name_to_keep, which is used in
   -- the cursor declared at the start of the procedure.
   SET @q = 'SELECT CONCAT(''p'', @formatted_last_month_to_keep) INTO @last_partition_name_to_keep';
   PREPARE st FROM @q;
   EXECUTE st;
   DEALLOCATE PREPARE st;
   
   SELECT CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep);
   
   -- And then we loop through all partitions returned by the cursor,
   -- and those partitions are dropped.
   OPEN cur1;

   read_loop: LOOP
      FETCH cur1 INTO current_partition_name;
   
      IF done THEN
         LEAVE read_loop;
      END IF;

      SELECT CONCAT('Dropping partition: ', current_partition_name);
   
      -- First we build the ALTER TABLE query.
      SET @schema = p_schema;
      SET @table = p_table;
      SET @partition = current_partition_name;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' DROP PARTITION '', @partition) INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      
      -- And then we prepare and execute the ALTER TABLE query.
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
   END LOOP;
END$$
DELIMITER ;

Let's try running the procedure as a test:

MariaDB [(none)]> SHOW CREATE TABLE db1.quarterly_report_status;
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| quarterly_report_status | CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_default VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702                                  |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201610                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611                            |
+--------------------------------------------------------+
1 row in set (0.01 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612                            |
+--------------------------------------------------------+
1 row in set (0.04 sec)

+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701                            |
+--------------------------------------------------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> SHOW CREATE TABLE db1.quarterly_report_status;
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| quarterly_report_status | CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
(PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
 PARTITION p_default VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As you can see from the above output, several partitions were dropped.

Event definition

We want our stored procedure to run automatically every month, so we can use an event to do that.
Before testing the event, we need to do two things:

  • We need to recreate the table with the original definition, so that it has all of the original partitions.
  • We need to ensure that event_scheduler=ON is set, and if not, we need to set it.
  • MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | event_scheduler | OFF   |
    +-----------------+-------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]> SET GLOBAL event_scheduler=ON;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | event_scheduler | ON    |
    +-----------------+-------+
    1 row in set (0.00 sec)

    And then we can run the following:

    CREATE EVENT db1.monthly_drop_old_partitions_event
       ON SCHEDULE
       EVERY 1 MONTH
       STARTS NOW()
    DO
       CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6);

    Since we specified STARTS NOW(), the event ran immediately. We can confirm this by looking at the table definition again:

    MariaDB [(none)]> SHOW CREATE TABLE db1.quarterly_report_status;
    +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
    +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | quarterly_report_status | CREATE TABLE `quarterly_report_status` (
      `report_id` int(11) NOT NULL,
      `report_status` varchar(20) NOT NULL,
      `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated))
    (PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB,
     PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB,
     PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB,
     PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB,
     PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB,
     PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB,
     PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB,
     PARTITION p_default VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
    +-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    Conclusion

    Thanks to the flexibility of stored procedures and events, it is relatively easy to automatically drop old partitions in MySQL and MariaDB. Has anyone else implemented something like this?

    Note: You can find part 2 of this blog series here.

10 Comments on “Automatically Dropping Old Partitions in MySQL and MariaDB”

  1. Nice post.

    We had this very same functionality (that was replaced by more complex code, mainly to handle subpartitions) and there is one interesting enhancement you should consider: automate the creation of the new partitions in this same function by accepting another parameter (how many monthly partitions in the future to maintain) and executing (after dropping the old partitions) the creation of these new partitions or (if your application can introduce rows with dates in the future) the reorganization of the p_default into them.

    Also (depending on your target MySQL version) you should take a look at https://bugs.mysql.com/bug.php?id=49754 and create a p_old partition so the first partition does not contain any data. Do not forget to delete and re-create it in every execution to include dates smaller than the oldest partition to keep.

    1. Hi Pablo,

      Thanks for the feedback!

      Yes, I agree that adding new partitions would be a good improvement. Perhaps I’ll show an example of that too in a future blog post.

      Thanks for the reference to that bug report too. It does sound like it would be useful to have an empty first partition in some older versions of MySQL.

    2. If you’re interested, I posted a part 2 of this blog that implements something similar to what you suggested.

      http://www.geoffmontee.com/automatically-dropping-old-partitions-in-mysql-and-mariadb-part-2/

    3. I have yet to find a use case where SUBPARITIONs provide any performance benefit. Do you have a case?

  2. If you are using a DATETIME instead of a TIMESTAMP, I suggest you include an empty “first” partition, since it is always checked. The reason for this wasted check has something to do with invalid dates.

    1. Hi Rick,

      Thanks for the feedback! I see that the MySQL documentation seems to imply that partition pruning with DATETIME types should work properly in some cases if the table is partitioned with “RANGE COLUMNS”:

      “Beginning with MySQL 5.5.0, the optimizer can also perform pruning for WHERE conditions that involve comparisons of the preceding types on multiple columns for tables that use RANGE COLUMNS or LIST COLUMNS partitioning.

      This type of optimization can be applied whenever the partitioning expression consists of an equality or a range which can be reduced to a set of equalities, or when the partitioning expression represents an increasing or decreasing relationship. Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. In addition, in MySQL 5.5, pruning can be applied for such tables when the partitioning expression uses the TO_SECONDS() function.”

      https://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html

      Do you think the empty first partition is still necessary if the table is partitioned with “RANGE COLUMNS” on a DATETIME column, and it is partitioned with one of those functions mentioned above?

      1. Probably the need for the empty first partition applies only to BY RANGE(date) and BY RANGE(datetime). And probably BY RANGE COLUMNS(…) is the best workaround. Meanwhile, there have been dozens of forum question “why is it not pruning” by confused users.

  3. Normally, p_default (I call it `p_future`) should be empty, and “tomorrow’s” partition should be created before tomorrow starts. And do it with `REORGANIZE PARTITION p_future INTO p_2017…, p_future`.

    Reason #1: Since the partition is empty, the REORG is virtually instantaneous.

    Reason #2: p_default is a safety partition. If your “create next partition” cron job fails (say, the machine is down at the instant cron would fire off), this partition will collect the “future” stuff without errors or loss. Then the REORG will recover the rows that accidentally got into p_default and rebuild it as empty again. This technique protected me from disaster at least once.

    Speaking of cron failing — It is better to run the partition maintenance script more often than necessary. And have it check the existing partitions and do only what is necessary. After I fixed the broken cron, this technique quickly cleaned up the bloated p_future, and all was well.

    I provide more details here: http://mysql.rjweb.org/doc.php/partitionmaint

  4. Nice post!

    Dropping partitions is very expensive if you have a large buffer pool. I have seen it taking 2 seconds with a 200GB pool on modern hardware. In this time all other queries are blocked.

    Can you add a SELECT SLEEP(5) in between of dropping each partition to make this behaviour less painful?

    1. Thanks! Good suggestion! I’ve posted a part 2 of this blog that contains a change like that:

      http://www.geoffmontee.com/automatically-dropping-old-partitions-in-mysql-and-mariadb-part-2/

Leave a Reply

Your email address will not be published. Required fields are marked *

3,669 Spambots Blocked by Simple Comments