Partition maintainance script for Mysql

A simple way for adding future partitions or purging old partitions is through mysql procedures. Later we can use any scheduler or a cronjob to call this procedure daily.

Here I will provide a sample procedure for maintainance of a daily partitioned table based on a datetime column. Please do look at my previous post on mysql range partitioning in the following post. I have taken the example from this post and modified it based on our requirement.

First we will create a sample table along with a partition for our testing purposes.

mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
mysql> alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16092015 values less than ( to_seconds('2015-09-17 00:00:00') ));

Now lets create a table for holding list of tables and number of partitions to preserve/drop.

mysql> create table test.part_tables(dbname varchar(50),tabname varchar(50),numpart int,nextpart int);

Lets go ahead and insert a sample row there :

mysql> insert into test.part_tables values('test','test_partitions',30,7);

So, we will be preserving only last 30 days partitions and make sure we have next 7 days partitions for ‘test_partitions’ table.

Mysql Procedure 1 (part_maint) : This procedure takes in table list from our meta-table i.e part_tables and runs partition maintainance for each table :

DELIMITER $

DROP PROCEDURE IF EXISTS `test`.`part_maint` $
CREATE PROCEDURE `test`.`part_maint` ()
BEGIN
DECLARE var_dbname varchar(50);
DECLARE var_tabname varchar(50);
DECLARE var_numpart int;
DECLARE var_nextpart int;
DECLARE done INT DEFAULT FALSE;
DECLARE _output TEXT DEFAULT '';
DECLARE cursor_p CURSOR FOR select dbname,tabname,numpart,nextpart from test.part_tables;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cursor_p;
read_loop: LOOP
FETCH cursor_p INTO var_dbname, var_tabname, var_numpart, var_nextpart;
IF done THEN
LEAVE read_loop;
END IF;
SET _output = concat('Partitioning - ', var_dbname,'.', var_tabname);
SELECT _output;
CALL test.UpdatePartitions(var_dbname,var_tabname,var_numpart,var_nextpart);
SET _output = concat('---------------------------------------------');
SELECT _output;
END LOOP;
CLOSE cursor_p;

END $
DELIMITER ;

Above procedure is calling  ‘UpdatePartitions’ procedure for each table.

Mysql Procedure 2 (UpdatePartitions) :

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`UpdatePartitions` $$
CREATE PROCEDURE `test`.`UpdatePartitions` (dbname varchar(50), tblname varchar(50), numpart int, nextpart int)
BEGIN
  -- numpart is the number of previous partitions to keep
  -- nextpart is the number of next partitions to create
  DECLARE maxpart decimal(38);
  DECLARE partition_count int;
  DECLARE minpart decimal(38);
  DECLARE newpart decimal(38);
  DECLARE _output TEXT DEFAULT '';

  SELECT COUNT(*)
    INTO partition_count
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname;
    SET _output = concat('Number of partitions present=',partition_count);
    SELECT _output;

  WHILE (partition_count > numpart+nextpart)
  DO
    SELECT MIN(PARTITION_DESCRIPTION)
       INTO minpart
       FROM INFORMATION_SCHEMA.PARTITIONS
       WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname;
    SET _output = concat('Dropping partition - ',tblname,'_',date_format(date_sub(from_seconds(minpart),INTERVAL 1 DAY),'%d%m%Y'));
    SELECT _output;
    SET @sql := CONCAT('ALTER TABLE ',tblname,' DROP PARTITION ',CONCAT(tblname,'_',date_format(date_sub(from_seconds(minpart),INTERVAL 1 DAY),'%d%m%Y')),';');
     PREPARE stmt FROM @sql;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
    SELECT COUNT(*)
      INTO partition_count
      FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname;
  END WHILE;

  SELECT MAX(PARTITION_DESCRIPTION)
    INTO maxpart
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname;

  -- create enough partitions based on nextpart 

WHILE (maxpart < to_seconds(CURDATE() + INTERVAL nextpart+1 DAY))
  DO
    SET newpart := maxpart;
    SET _output = concat('Adding partition - ',tblname,'_',date_format(from_seconds(newpart),'%d%m%Y'));
    SELECT _output;
    SET @sql := CONCAT('ALTER TABLE ',tblname,' ADD PARTITION (PARTITION ',CONCAT(tblname,'_',date_format(from_seconds(newpart),'%d%m%Y')), ' values less than(' ,to_seconds(date_add(from_seconds(newpart),INTERVAL 1 DAY)), ') );');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT MAX(PARTITION_DESCRIPTION)
      INTO maxpart
      FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname;
  END WHILE;

END $$

DELIMITER ;

Lets add a cronjob for running the ‘part_maint’ procedure daily.

1) Write a simple shell script to call the procedure :

cat /tmp/part_maint.sh
#/bin/bash
date
echo '----------------------------------------'
mysql -uroot -S/var/lib/mysql1/data/mysql.sock test -N -B -e "CALL part_maint"
printf "\n"

2) Add crontab entry to run daily at 3PM :

00 16 * * * /tmp/part_maint.sh >> /tmp/part_maint.log

Leave a Reply

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