Skip to content

Instantly share code, notes, and snippets.

@wwwted wwwted/partitioning.sql
Last active Apr 29, 2020

Embed
What would you like to do?
MySQL Partitioning automation
--
-- 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
--
CREATE DATABASE IF NOT EXISTS ted;
use ted;
DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
id INT 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) (
PARTITION p20200217 VALUES LESS THAN ('2020-02-17'),
PARTITION p20200218 VALUES LESS THAN ('2020-02-18'),
PARTITION p20200219 VALUES LESS THAN ('2020-02-19'),
PARTITION p20200220 VALUES LESS THAN ('2020-02-20'),
PARTITION p20200221 VALUES LESS THAN ('2020-02-21')
);
DROP TABLE IF EXISTS blogs;
CREATE TABLE blogs (
id INT 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 ('2020-02-17'),
PARTITION p20200218 VALUES LESS THAN ('2020-02-18'),
PARTITION p20200219 VALUES LESS THAN ('2020-02-19'),
PARTITION p20200220 VALUES LESS THAN ('2020-02-20'),
PARTITION p20200221 VALUES LESS THAN ('2020-02-21')
);
-- SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='logs' AND TABLE_SCHEMA='ted'\G
--
-- Internal stuff to handle partitioning.
-- Table MyPartTables contains one row per table to manage.
-- Insert table name, schema name, number of future partitions to create
-- and max number of partitions to keep before starting to remove old partitions
--
-- Two events will run once per day, first event will call a SP to add new partitions,
-- the second event will call a SP to remove any old partitions. This is done for
-- all tables that are recorded on MyPartTables.
--
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,
PRIMARY KEY (`schema_name`,`table_name`)
);
INSERT INTO MyPartTables VALUES ('ted','logs',2,20);
-- Handle partition for table ted.logs, create 2 partitions in the future and
-- keep only 20 partitions before starting to remove old partitions.
INSERT INTO MyPartTables VALUES ('ted','blogs',3,5);
-- Handle partition for table ted.blogs, create 3 partitions in the future 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 list CURSOR FOR SELECT `table_name`,`schema_name`,`no_of_future_partitions` 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;
-- DEBUG SELECT v_table_name,v_schema_name,v_no_future_part;
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 + 1;
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_FOR
MAT(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;
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;
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- DEBUG 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;
-- DEBUG SELECT @v_partitions_to_remove;
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @qry = CONCAT('ALTER TABLE ',v_table_name, ' DROP PARTITION ', @v_list_of_partitions_to_remove);
-- DEBUG SELECT @qry;
PREPARE stmt FROM @qry;
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.