MariaDB自动分区维护

2017-09-13 10:27:57来源:oschina作者:分秒人点击

分享

最近遇到一个问题,就是如何自动删除6个月之前的旧的分区。就MariaDB而言,并没有类似机制,可以让我们开箱即用,去完成这件事情。所以我们需要换一种思路,或许问题就可以很简单的被解决。我们可以创建一个存储过程和一个事件,然后按照预定的时间表来调用这个存储过程。实际上,我们还可以进一步创建一个存储过程,让该过程自动添加新的分区。


在本篇文章中,我们将展示如何编写执行这些任务的存储过程。


分区表的定义

在该演示中,我们将使用MySQL文档RANGE分区示例给出的数据表,稍做一些改动:


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 p_first VALUES LESS THAN ( UNIX_TIMESTAMP('2016-10-01 00:00:00')),
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_future VALUES LESS THAN (MAXVALUE)
);

最重要的改动是分区命名方案是基于日期的。这将使我们更容易地确定要删除哪些分区。


存储过程定义(创建新的分区)

存储过程本身也会包含一些注释,来说明它所作的事情。需要提出说明的是,我们没有使用ALTER TABLE ... ADD PARTITION 这样的语句,因为 p_future 分区已经覆盖了到 MAXVALUE 的结束范围。所以我们需要使用 ALTER TABLE ... REORGANIZE PARTITION 语句来代替。


DROP PROCEDURE IF EXISTS db1.create_new_partitions;
DELIMITER $
CREATE PROCEDURE db1.create_new_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_add int)
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE current_partition_name varchar(64);
DECLARE current_partition_ts int;
-- We'll use this cursor later to check
-- whether a particular already exists.
-- @partition_name_to_add 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_first'
AND PARTITION_NAME != 'p_future'
AND PARTITION_NAME = @partition_name_to_add;
-- We'll also use this cursor later
-- to query our temporary table.
DECLARE cur2 CURSOR FOR
SELECT partition_name, partition_range_ts
FROM partitions_to_add;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TEMPORARY TABLE IF EXISTS partitions_to_add;
CREATE TEMPORARY TABLE partitions_to_add (
partition_name varchar(64),
partition_range_ts int
);
SET @partitions_added = FALSE;
SET @months_ahead = 0;
-- Let's go through a loop and add each month individually between
-- the current month and the month p_months_to_add in the future.
WHILE @months_ahead <= p_months_to_add DO
-- We figure out what the correct month is by adding the
-- number of months to the current date
SET @date = CURDATE();
SET @q = 'SELECT DATE_ADD(?, INTERVAL ? MONTH) INTO @month_to_add';
PREPARE st FROM @q;
EXECUTE st USING @date, @months_ahead;
DEALLOCATE PREPARE st;
SET @months_ahead = @months_ahead + 1;
-- Then we format the month in the same format used
-- in our partition names.
SET @q = 'SELECT DATE_FORMAT(@month_to_add, ''%Y%m'') INTO @formatted_month_to_add';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
-- And then we use the formatted date to build the name of the
-- partition that we want to add. This partition name is
-- assigned to @partition_name_to_add, which is used in
-- the cursor declared at the start of the procedure.
SET @q = 'SELECT CONCAT(''p'', @formatted_month_to_add) INTO @partition_name_to_add';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
SET done = FALSE;
SET @first = TRUE;
-- And then we loop through the results returned by the cursor,
-- and if a row already exists for the current partition,
-- then we do not need to create the partition.
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO current_partition_name;
-- The cursor returned 0 rows, so we can create the partition.
IF done AND @first THEN
SELECT CONCAT('Creating partition: ', @partition_name_to_add);
-- Now we need to get the end date of the new partition.
-- Note that the date is for the non-inclusive end range,
-- so we actually need the date of the first day of the *next* month.
-- First, let's get a date variable for the first of the partition month
SET @q = 'SELECT DATE_FORMAT(@month_to_add, ''%Y-%m-01 00:00:00'') INTO @month_to_add';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
-- Then, let's add 1 month
SET @q = 'SELECT DATE_ADD(?, INTERVAL 1 MONTH) INTO @partition_end_date';
PREPARE st FROM @q;
EXECUTE st USING @month_to_add;
DEALLOCATE PREPARE st;
-- We need the date in UNIX timestamp format.
SELECT UNIX_TIMESTAMP(@partition_end_date) INTO @partition_end_ts;
-- Now insert the information into our temporary table
INSERT INTO partitions_to_add VALUES (@partition_name_to_add, @partition_end_ts);
SET @partitions_added = TRUE;
END IF;
-- Since we had at least one row returned, we know the
-- partition already exists.
IF ! @first THEN
LEAVE read_loop;
END IF;
SET @first = FALSE;
END LOOP;
CLOSE cur1;
END WHILE;
-- Let's actually add the partitions now.
IF @partitions_added THEN
-- First we need to build the actual ALTER TABLE query.
SET @schema = p_schema;
SET @table = p_table;
SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_future INTO ( '') INTO @query';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
SET done = FALSE;
SET @first = TRUE;
OPEN cur2;
read_loop: LOOP
FETCH cur2 INTO current_partition_name, current_partition_ts;
IF done THEN
LEAVE read_loop;
END IF;
-- If it is not the first partition,
-- then we need to add a comma
IF ! @first THEN
SET @q = 'SELECT CONCAT(@query, '', '') INTO @query';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
END IF;
-- Add the current partition
SET @partition_name =current_partition_name;
SET @partition_ts =current_partition_ts;
SET @q = 'SELECT CONCAT(@query, ''PARTITION '', @partition_name, '' VALUES LESS THAN ('', @partition_ts, '')'') INTO @query';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
SET @first = FALSE;
END LOOP;
CLOSE cur2;
-- We also need to include the p_future partition
SET @q = 'SELECT CONCAT(@query, '', PARTITION p_future VALUES LESS THAN (MAXVALUE))'') 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 IF;
DROP TEMPORARY TABLE partitions_to_add;
END$
DELIMITER ;

让我们运行一下新的过程:


MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status/G
*************************** 1. row ***************************
Table: quarterly_report_status
Create Table: 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 p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
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_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MariaDB [db1]> CALL db1.create_new_partitions('db1', 'quarterly_report_status', 3);
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201709|
+--------------------------------------------------------+
1 row in set (0.01 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201710|
+--------------------------------------------------------+
1 row in set (0.02 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201711|
+--------------------------------------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.09 sec)
MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status/G
*************************** 1. row ***************************
Table: quarterly_report_status
Create Table: 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 p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
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 p201709 VALUES LESS THAN (1506830400) ENGINE = InnoDB,
PARTITION p201710 VALUES LESS THAN (1509508800) ENGINE = InnoDB,
PARTITION p201711 VALUES LESS THAN (1512104400) ENGINE = InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

我们可以看到,跟我们预期的效果是一样的。


存储过程定义(删除旧的分区)

以下存储过程也包含一些注释,解释它的作用。


值得指出的是,该存储过程,通过 ALTER TABLE ... DROP PARTITION 语句分别删除旧分区。然后通过 ALTER TABLE ... REORGANIZE PARTITION 语句增加了 p_first 分区的范围,这样就填补了后面留下的空白。


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, p_seconds_to_sleep 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_first'
AND PARTITION_NAME != 'p_future'
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);
SET @first = TRUE;
-- 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;
IF ! @first AND p_seconds_to_sleep > 0 THEN
SELECT CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds');
SELECT SLEEP(p_seconds_to_sleep);
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;
SET @first = FALSE;
END LOOP;
CLOSE cur1;
-- If no partitions were dropped, then we can also skip this.
IF ! @first THEN
-- Then we need to get the date of the new first partition.
-- We need the date in UNIX timestamp format.
SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y-%m-01 00:00:00'') INTO @new_first_partition_date';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
SELECT UNIX_TIMESTAMP(@new_first_partition_date) INTO @new_first_partition_ts;
-- We also need to get the date of the second partition
-- since the second partition is also needed for REORGANIZE PARTITION.
SET @q = 'SELECT DATE_ADD(@new_first_partition_date, INTERVAL 1 MONTH) INTO @second_partition_date';
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
SELECT UNIX_TIMESTAMP(@second_partition_date) INTO @second_partition_ts;
SELECT CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date);
-- Then we build the ALTER TABLE query.
SET @schema = p_schema;
SET @table = p_table;
SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_first, '', @last_partition_name_to_keep, '' INTO ( PARTITION p_first VALUES LESS THAN ( '', @new_first_partition_ts, '' ), PARTITION '', @last_partition_name_to_keep, '' VALUES LESS THAN ( '', @second_partition_ts, '' ) ) '') 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 IF;
END$
DELIMITER ;

让我们运行一下新的过程:


MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status/G
*************************** 1. row ***************************
Table: quarterly_report_status
Create Table: 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 p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
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_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MariaDB [db1]> CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6, 5);
+--------------------------------------------------------------------------+
| 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('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (0.02 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (5.02 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611|
+--------------------------------------------------------+
1 row in set (5.02 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (5.03 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (10.03 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612|
+--------------------------------------------------------+
1 row in set (10.03 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (10.05 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (15.05 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701|
+--------------------------------------------------------+
1 row in set (15.05 sec)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (15.06 sec)
Query OK, 0 rows affected (15.11 sec)
MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status/G
*************************** 1. row ***************************
Table: quarterly_report_status
Create Table: 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 p_first 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_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

我们可以看到,正如我们所预期的,除了旧的分区被删除,我们还可以看到 p_first 的日期范围也被更新了。


存储过程定义(和其他过程捆绑)

在大多数情况下,同一时间执行所有分区维护可能会更好。因此,我们可以创建另一个存储过程,调用我们的其他两个存储过程。


DROP PROCEDURE IF EXISTS db1.perform_partition_maintenance;
DELIMITER $
CREATE PROCEDURE db1.perform_partition_maintenance(p_schema varchar(64), p_table varchar(64), p_months_to_add int, p_months_to_keep int, p_seconds_to_sleep int)
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
CALL db1.drop_old_partitions(p_schema, p_table, p_months_to_keep, p_seconds_to_sleep);
CALL db1.create_new_partitions(p_schema, p_table, p_months_to_add);
END$
DELIMITER ;

我们将分区表重新设置为原来的状态,然后运行新的存储过程。


MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status/G
*************************** 1. row ***************************
Table: quarterly_report_status
Create Table: 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 p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB,
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_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);
+--------------------------------------------------------------------------+
| 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('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (0.02 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (5.02 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611|
+--------------------------------------------------------+
1 row in set (5.02 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (5.03 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (10.03 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612|
+--------------------------------------------------------+
1 row in set (10.03 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (10.06 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (15.06 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701|
+--------------------------------------------------------+
1 row in set (15.06 sec)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (15.08 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201709|
+--------------------------------------------------------+
1 row in set (15.16 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201710|
+--------------------------------------------------------+
1 row in set (15.17 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201711|
+--------------------------------------------------------+
1 row in set (15.17 sec)
Query OK, 0 rows affected (15.26 sec)
MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status/G
*************************** 1. row ***************************
Table: quarterly_report_status
Create Table: 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 p_first 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 p201709 VALUES LESS THAN (1506830400) ENGINE = InnoDB,
PARTITION p201710 VALUES LESS THAN (1509508800) ENGINE = InnoDB,
PARTITION p201711 VALUES LESS THAN (1512104400) ENGINE = InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

该存储过程执行也跟我们预期结果一样。


过程多次执行

应该注意的是,这些存储过程可以比必要时更频繁地运行。如果在不需要添加或删除分区时运行这些过程,则该过程将不会执行任何工作。让我们重置表定义,然后试一下。


MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);
+--------------------------------------------------------------------------+
| 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('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (0.03 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (5.03 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201611|
+--------------------------------------------------------+
1 row in set (5.03 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (5.06 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (10.06 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201612|
+--------------------------------------------------------+
1 row in set (10.06 sec)
+---------------------------------------------------------+
| CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') |
+---------------------------------------------------------+
| Sleeping for 5 seconds|
+---------------------------------------------------------+
1 row in set (10.08 sec)
+---------------------------+
| SLEEP(p_seconds_to_sleep) |
+---------------------------+
|0 |
+---------------------------+
1 row in set (15.09 sec)
+--------------------------------------------------------+
| CONCAT('Dropping partition: ', current_partition_name) |
+--------------------------------------------------------+
| Dropping partition: p201701|
+--------------------------------------------------------+
1 row in set (15.09 sec)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (15.11 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201709|
+--------------------------------------------------------+
1 row in set (15.18 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201710|
+--------------------------------------------------------+
1 row in set (15.18 sec)
+--------------------------------------------------------+
| CONCAT('Creating partition: ', @partition_name_to_add) |
+--------------------------------------------------------+
| Creating partition: p201711|
+--------------------------------------------------------+
1 row in set (15.18 sec)
Query OK, 0 rows affected (15.28 sec)
MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);
+--------------------------------------------------------------------------+
| CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) |
+--------------------------------------------------------------------------+
| Dropping all partitions before: p201702|
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)

正如我们从上面的输出中看到的,这个过程第二次没有执行任何工作。


事件定义

我们希望我们的存储过程每个月自动运行,这样我们就可以使用一个事件来完成这个过程。在测试这个事件之前,我们需要做两件事:

我们需要用原始的定义重新创建表,这样它就有了所有的原始分区。
我们需要确保 event_scheduler=ON 已经设置好,如果没有设置,我们需要设置上。 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)

然后,我们可以运行以下内容:


DROP EVENT db1.monthly_perform_partition_maintenance_event;
CREATE EVENT db1.monthly_perform_partition_maintenance_event
ON SCHEDULE
EVERY 1 MONTH
STARTS NOW()
DO
CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);

然而,我们可以在这里修改完善一下。每月运行一次这个过程可能并不太理想,因为如果这个过程因为某种原因失败了,那么直到下个月它可能都不会再一次执行。出于这个原因,最好更频繁一下去运行这个过程,比如每天一次。正如上面所提到的,只有当分区维护是必要的时候,这个过程才会起作用,所以更频繁地执行这个过程也不会引起任何问题。


如果我们想每天运行一次程序,那么事件定义就变成:


DROP EVENT db1.monthly_perform_partition_maintenance_event;
CREATE EVENT db1.monthly_perform_partition_maintenance_event
ON SCHEDULE
EVERY 1 DAY
STARTS NOW()
DO
CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);
结论

由于存储过程和事件的灵活性,在MariaDB中自动执行分区维护相对容易。

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台