MySQL에서 파티셔닝은 성능 문제로 권장되는 옵션은 아니다.
하지만, 로그 데이터와 같이 주기적으로 과거 데이터를 삭제해야 되는 경우라면 파티셔닝이 매우 편리하다.
신규 파티션 추가와 오래된 파티션 삭제를 수작업으로 하기는 번거로울테니, 간단하게 이 과정을 자동화 해 줄 프로시저를 만들어 봄~
[파티션 관리 요건]
- 어떠한 이유에서든지 프로시저가 동작하지 않은 경우라도 데이터 INSERT 실패가 발생하지 않도록 할 것
- 프로시저가 일정 기간 동작하지 않고 다시 재가동 되는 경우에, 프로시저가 주기적으로 동작했던 것과 동일한 파티션 구조를 생성할 것
- 파티션 삭제시 Table Lock으로 인한 영향을 최소화 할 것
[파티션 생성 프로시저]
|
CREATE PROCEDURE create_partition(p_dbname varchar(255), p_tbname varchar(255), p_future INT, p_interval INT)
SQL SECURITY INVOKER
BEGIN
DECLARE x, max_pdesc, new_pdesc INT;
DECLARE pname VARCHAR(64);
DECLARE alter_cmd VARCHAR(1024);
-- 현재 테이블의 파티션의 가장 마지막 파티션의 HIGH VALUE 값을 구함
SELECT MAX(PARTITION_DESCRIPTION) - TO_DAYS(current_date()) INTO x FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION != 'MAXVALUE' ;
-- 파티션 미리 생성할 기한까지 지정한 interval에 맞도록 파티션 추가
-- 파티션 추가는 ADD PARTITION이 아닌 MAXVALUE 파티션의 REORGANIZE로 처리됨 WHILE x <= p_future DO SELECT CONCAT('p', DATE_FORMAT(current_date() + interval MAX(PARTITION_DESCRIPTION) - TO_DAYS(current_date()) day, '%Y%m%d')), MAX(PARTITION_DESCRIPTION), MAX(PARTITION_DESCRIPTION) + p_interval INTO pname, max_pdesc, new_pdesc FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!='MAXVALUE'; IF max_pdesc < new_pdesc THEN SET @alter_sql := CONCAT('ALTER TABLE ', p_dbname, '.', p_tbname, ' REORGANIZE PARTITION pMAXVALUE INTO (', 'PARTITION ', pname, ' VALUES LESS THAN (', new_pdesc, '), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE)' ); PREPARE alter_cmd FROM @alter_sql; EXECUTE alter_cmd; DEALLOCATE PREPARE alter_cmd; END IF; SET x = x + p_interval; END WHILE;
-- 파티션 정보 출력 (옵션)
SELECT current_date() + interval MAX(PARTITION_DESCRIPTION) - TO_DAYS(current_date()) - 1 day AS Last_Date,
COUNT(*) AS Partitions_For_Future
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION != 'MAXVALUE'
AND PARTITION_DESCRIPTION > TO_DAYS(current_date()) + 1;
END |
[파티션 삭제 프로시저]
|
CREATE PROCEDURE delete_partition(p_dbname varchar(255), p_tbname varchar(255), p_del_date INT)
SQL SECURITY INVOKER
BEGIN
DECLARE done INT;
DECLARE pname VARCHAR(64);
DECLARE alter_cmd VARCHAR(1024);
DECLARE deleted_partition VARCHAR(1024);
-- 삭제할 파티션 목록 취합
DECLARE cur CURSOR FOR
SELECT PARTITION_NAME FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!='MAXVALUE'
AND PARTITION_DESCRIPTION<=TO_DAYS(current_date()) - p_del_date ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET done = 0;
-- 삭제 대상 파티션 목록 확인 및 작업 완료 후 출력 (옵션)
SELECT GROUP_CONCAT(PARTITION_NAME) INTO deleted_partition
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!='MAXVALUE'
AND PARTITION_DESCRIPTION<=TO_DAYS(current_date()) - p_del_date ;
OPEN cur;
FETCH cur INTO pname;
WHILE done = 0 DO
-- MySQL 5.6 버전 이상인 경우 동일 스키마로 빈 테이블을 만들어서 PARTITION EXCHANGE 처리 후 DROP PARTITION
-- 파티션 삭제 처리 시간 지연으로 인한 Table Lock 영향을 최소화하기 위함
IF left(version(),3) >= '5.6' THEN
-- make empty table for exchange
SET @alter_sql := CONCAT('CREATE TABLE ', p_dbname, '._exchange_', p_tbname, ' LIKE ', p_dbname, '.' , p_tbname);
PREPARE alter_cmd FROM @alter_sql;
EXECUTE alter_cmd;
DEALLOCATE PREPARE alter_cmd;
SET @alter_sql := CONCAT('ALTER TABLE ', p_dbname, '._exchange_', p_tbname, ' REMOVE PARTITIONING');
PREPARE alter_cmd FROM @alter_sql;
EXECUTE alter_cmd;
DEALLOCATE PREPARE alter_cmd;
-- exchange
SET @alter_sql := CONCAT('ALTER TABLE ', p_dbname, '.', p_tbname, ' EXCHANGE PARTITION ', pname, ' WITH TABLE ', p_dbname, '._exchange_', p_tbname);
PREPARE alter_cmd FROM @alter_sql;
EXECUTE alter_cmd;
DEALLOCATE PREPARE alter_cmd;
-- drop tmp table
SET @alter_sql := CONCAT('DROP TABLE ', p_dbname, '._exchange_', p_tbname);
PREPARE alter_cmd FROM @alter_sql;
EXECUTE alter_cmd;
DEALLOCATE PREPARE alter_cmd;
END IF;
-- 파티션 삭제
SET @alter_sql := CONCAT('ALTER TABLE ', p_dbname, '.', p_tbname, ' DROP PARTITION ', pname);
PREPARE alter_cmd FROM @alter_sql;
EXECUTE alter_cmd;
DEALLOCATE PREPARE alter_cmd;
FETCH cur INTO pname;
END WHILE;
CLOSE cur;
-- 삭제 처리한 파티션 목록 출력 (옵션)
SELECT deleted_partition AS Deleted_Partitions ;
END
|
[동작 예시]
1) mydb.daily_table 테이블에 대해서 금일 날짜 기준으로 30일이 경과한 파티션을 삭제
mysql> call delete_partition('mydb', 'daily_table', 30);
+--------------------+
| Deleted_Partitions |
+--------------------+
| p20140610,p20140611 | --> 삭제된 파티션 목록 출력됨
+--------------------+
2) mydb.daily_table 테이블에 대해서 금일 날짜 기준으로 7일 후까지 1일 간격으로 파티션을 생성
mysql> call create_partition('mydb', 'daily_table', 7, 1);
+-----------+------------------+
| Last_Date | Partitions_For_Future |
+-----------+------------------+
| 2014-07-18 | 7 | --> 미리 생성되어 있는 파티션들의 개수와 수용 가능한 날짜 정보 출력
+-----------+------------------+
- 이벤트 스케줄러 미활성 상태인 경우 활성화, 이벤트 스케줄러 미지원 버전인 경우에는 crontab 등록
set global event_scheduler=on; -- my.cnf에도 추가
- 이벤트 생성 (프로시저는 mysql db에 생성한 것으로 가정)
DELIMITER $
drop event if exists evt_partition_management $
CREATE DEFINER=`root`@`localhost` EVENT evt_partition_management
ON SCHEDULE EVERY '1' DAY STARTS '2014-07-12 01:00:00' -- 스케쥴러 시작 시점은 반드시 현재 날짜 기준으로 미래 시점이어야 함
DO
BEGIN
call mysql.delete_partition('mydb', 'daily_table', 30);
call mysql.delete_partition('mydb', 'weekly_table', 180);
call mysql.create_partition('mydb', 'daily_table', 3, 1);
call mysql.create_partition('mydb', 'weekly_table', 14, 7);
END $
DELIMITER ;
[유의사항]
- 파티셔닝 키 컬럼이 datetime이 아닌 timestamp라면 HIGH VALUE 비교하는 부분 수정 필요 (date 타입은 사용 가능)
- MySQL이 아닌 MariaDB를 사용하는 경우 PARTITION EXCHANGE 부분에서 DB 버전 체크하는 부분 수정 필요
- 적용 대상 테이블은 사전에 RANGE 방식의 파티셔닝 구성이 되어 있어야 하며, MAXVALUE 파티션(파티션명 : pMAXVALUE)이 만들어져 있어야 함
