partitionTest2.sql

partitionTest2.sql

-- I tested at /home/ted/sandboxes/MySQL-HOWTOs
-- Start MySQL (/home/ted/sandboxes/MySQL-HOWTOs/scripts/start.sh) and bring up client: mysql -uroot -proot -h127.0.0.1 -P3306
-- Run script: source /home/ted/labb/partitioning/partitioning.sql
-- Also tested in OCI, host 130.61.35.76
--
-- Remember to set group_concat_max_len to a high enough value.
-- I recommend setting it to at least 50000.

CREATE DATABASE IF NOT EXISTS ted2;
use ted2;

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
    id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    message VARCHAR(500) NOT NULL,
    code TINYINT UNSIGNED NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id,created)
);

ALTER TABLE logs PARTITION BY RANGE COLUMNS(created) SUBPARTITION BY HASH(id) SUBPARTITIONS 10 (
    PARTITION p20200217 VALUES LESS THAN ('2024-02-17'),
    PARTITION p20200218 VALUES LESS THAN ('2024-02-18'),
    PARTITION p20200219 VALUES LESS THAN ('2024-02-19'),
    PARTITION p20200220 VALUES LESS THAN ('2024-02-20'),
    PARTITION p20200221 VALUES LESS THAN ('2024-02-21')
);

DROP TABLE IF EXISTS blogs;
CREATE TABLE blogs (
    id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    message VARCHAR(500) NOT NULL,
    code TINYINT UNSIGNED NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id,created)
);

ALTER TABLE blogs PARTITION BY RANGE COLUMNS(created) (
    PARTITION p20200217 VALUES LESS THAN ('2024-02-17'),
    PARTITION p20200218 VALUES LESS THAN ('2024-02-18'),
    PARTITION p20200219 VALUES LESS THAN ('2024-02-19'),
    PARTITION p20200220 VALUES LESS THAN ('2024-02-20'),
    PARTITION p20200221 VALUES LESS THAN ('2024-02-21')
);

-- SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='logs' AND TABLE_SCHEMA='ted2'\G

-- 
-- Internal stuff to handle partitions 
-- Table MyPartTables contains one row per table to be managed.
-- Insert table name and schema is belongs to, you also need
-- to specify how many future partitions you want to create (range in days).
-- Last two arguments is max number of partitions to keep before starting
-- to remove old partitions and the number of days in each partition.
-- 

DROP TABLE IF EXISTS MyPartTables;
CREATE TABLE IF NOT EXISTS `MyPartTables` (
  `schema_name` VARCHAR(100) NOT NULL,
  `table_name` VARCHAR(100) NOT NULL,
  `no_of_future_partitions` INT NOT NULL,
  `max_partitions` INT NOT NULL,
  `no_of_days_per_partition` INT NOT NULL,
  PRIMARY KEY (`schema_name`,`table_name`)
);

INSERT INTO MyPartTables VALUES ('ted2','logs',2,20,1);
-- Handle partition for table ted2.logs, create 2 partitions in the future and
-- keep only 20 partitions before starting to remove old partitions.
INSERT INTO MyPartTables VALUES ('ted2','blogs',3,8,3);
-- Handle partition for table ted2.blogs, create partitions for 8 days in the future
-- with 3 days in each partition and keep only 5 partitions before starting to
-- remove old partitions.

DELIMITER ;;

-- Run SP AddPartition() once per day
DROP EVENT IF EXISTS `AddPartitions`;;
CREATE EVENT AddPartitions ON SCHEDULE EVERY 1 DAY
DO
  BEGIN
  CALL AddPartitions();
END ;;

--
-- SP that creates new partitions
-- Only works for RANGE partitions using DAY resolution
--
DROP PROCEDURE IF EXISTS `AddPartitions`;;
CREATE PROCEDURE `AddPartitions`()
BEGIN
  DECLARE done   BOOL DEFAULT FALSE;
  DECLARE maxp   INT DEFAULT 0;
  DECLARE pdate  INT DEFAULT 0;
  DECLARE pname  VARCHAR(20);    
  DECLARE v_table_name  VARCHAR(100);
  DECLARE v_schema_name  VARCHAR(100);
  DECLARE v_no_future_part INT;
  DECLARE v_no_of_days_per_partition INT;

  DECLARE list CURSOR FOR SELECT `table_name`,`schema_name`,`no_of_future_partitions`,`no_of_days_per_partition` FROM MyPartTables;

  DECLARE CONTINUE HANDLER FOR
  SQLSTATE '02000'
  SET done = TRUE;

  OPEN list;
  tloop: LOOP
    IF done THEN
      CLOSE list;
      LEAVE tloop;
    END IF;

    FETCH list INTO v_table_name,v_schema_name,v_no_future_part,v_no_of_days_per_partition;
    SELECT v_table_name,v_schema_name,v_no_future_part,v_no_of_days_per_partition;

    SET pdate=TO_DAYS(DATE(NOW() + INTERVAL (v_no_future_part) DAY));
    SELECT TO_DAYS(TRIM(BOTH "'" FROM MAX(PARTITION_DESCRIPTION))) INTO maxp
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME=v_table_name
           AND TABLE_SCHEMA=v_schema_name
           AND PARTITION_DESCRIPTION!="MAXVALUE";
    -- DEBUG' SELECT pdate, maxp;

    WHILE pdate > maxp DO
       SET maxp = maxp + v_no_of_days_per_partition;
       SET pname = CONCAT('p', DATE_FORMAT(FROM_DAYS(maxp), '%Y%m%d'));
       SET @qry = CONCAT('ALTER TABLE ',v_schema_name,'.',v_table_name, ' ADD PARTITION (PARTITION ',pname,' VALUES LESS THAN ("',DATE_FORMAT(FROM_DAYS(maxp),'%Y-%m-%d'),'"))');
       -- DEBUG SELECT @qry;
       PREPARE stmt FROM @qry;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
    END WHILE;
  END LOOP;
END ;;

-- Run SP RemovePartition() once per day
DROP EVENT IF EXISTS RemovePartitions;
CREATE EVENT RemovePartitions ON SCHEDULE EVERY 1 DAY
DO
  BEGIN
  CALL RemovePartitions();
END ;;

--
-- SP that removes old partitions once you have more than max_partitions
-- Only works for RANGE partitions using DAY resolution
--
DROP PROCEDURE IF EXISTS RemovePartitions;;
CREATE PROCEDURE RemovePartitions ()
BEGIN
  DECLARE done BOOL DEFAULT FALSE;
  DECLARE v_table_name  VARCHAR(100);
  DECLARE v_schema_name  VARCHAR(100);
  DECLARE v_max_partitions INT;
  DECLARE v_no_of_partitions_to_remove INT;

  DECLARE cur CURSOR FOR SELECT `table_name`,`schema_name`,`max_partitions` FROM MyPartTables;

  DECLARE CONTINUE HANDLER FOR
  SQLSTATE '02000'
  SET done = TRUE;

  OPEN cur;

  tloop: LOOP
    FETCH cur INTO v_table_name,v_schema_name,v_max_partitions;
    IF done THEN
      CLOSE cur;
      LEAVE tloop;
    END IF;
    -- DEBUG SELECT v_table_name,v_schema_name,v_max_partitions;
    -- SELECT v_table_name,v_schema_name,v_max_partitions;
    SET @qry = CONCAT('SELECT COUNT(DISTINCT(PARTITION_DESCRIPTION)) INTO @v_no_of_partitions ',
                     'FROM INFORMATION_SCHEMA.PARTITIONS ',
                     'WHERE TABLE_NAME="',v_table_name, '" ',
                     'AND TABLE_SCHEMA = "',v_schema_name, '" ');
    -- DEBUG SELECT @qry;
    -- SELECT @qry;
    PREPARE stmt FROM @qry;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- DEBUG SELECT @v_no_of_partitions, v_max_partitions;
    -- SELECT @v_no_of_partitions, v_max_partitions;

    IF @v_no_of_partitions > v_max_partitions THEN
      SET v_no_of_partitions_to_remove = @v_no_of_partitions - v_max_partitions;
      SET @qry = CONCAT('SELECT GROUP_CONCAT(DISTINCT(PARTITION_NAME)) INTO @v_list_of_partitions_to_remove FROM ( ',
                            'SELECT PARTITION_NAME ',
                            'FROM INFORMATION_SCHEMA.PARTITIONS ',
                            'WHERE TABLE_NAME = "',v_table_name, '" ',
                            'AND TABLE_SCHEMA = "',v_schema_name, '" ',
                            'AND PARTITION_DESCRIPTION!="MAXVALUE" ',
                            'ORDER BY PARTITION_DESCRIPTION LIMIT ',v_no_of_partitions_to_remove,
                            ' ) as slabb');
      -- DEBUG SELECT @qry;
      -- SELECT @qry;
      -- DEBUG SELECT _no_of_partitions_to_remove;
      -- SELECT v_no_of_partitions_to_remove;
      PREPARE stmt FROM @qry;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

      
      -- DEBUG SELECT @v_list_of_partitions_to_remove;
      -- SELECT @v_list_of_partitions_to_remove;

      SET @v_result = CONCAT('ALTER TABLE ',v_table_name, ' DROP PARTITION ', @v_list_of_partitions_to_remove);
      -- DEBUG SELECT @result;
      -- SELECT @v_result;
      PREPARE stmt FROM @v_result;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END IF;
  END LOOP;
END ;;

DELIMITER ;

-- Run some tests:
SELECT "========== Tables before AddPartitions ==========" as "";
SHOW CREATE TABLE logs\G
SHOW CREATE TABLE blogs\G
CALL AddPartitions();
SELECT "========== Tables after  AddPartitions ==========" as "";
SHOW CREATE TABLE logs\G
SHOW CREATE TABLE blogs\G
CALL RemovePartitions();
SELECT "========== Tables after RemovePartitions ===========" as "";
SHOW CREATE TABLE logs\G
SHOW CREATE TABLE blogs\G
SELECT "========== Contect of MyPartTables ==========" as "";
SELECT * FROM MyPartTables;
SHOW EVENTS;