[MariaDB] 테이블스페이스, 파티셔닝 사용 방법

아래의 조건들을 만족하게 테이블스페이스와 파티셔닝을 구성해보겠습니다.

1.InnoDB 테이블 엔진을 기반

2.파일별 테이블스페이스 사용

3.날짜 칼럼을 기준으로 연 단위 파티셔닝

4.1년 보관 후 자동 삭제를 EVENT 스케줄러로 구성

 

파일별 테이블 스페이스 사용

아래의 SQL의 결과가 1(활성화)이면 파일별 테이블스페이스 활성화가 활성화된 상태

SELECT @@innodb_file_per_table;

 

1이 아니면 /etc/mysql/my.cnf 파일 수정 및 재시작

[mysqld]
innodb_file_per_table = ON

# 전체 재시작 (설정 변경 반영 필수 시)
sudo systemctl restart mariadb

# 상태 확인
sudo systemctl status mariadb

innodb_file_per_table : InnoDB가 각 테이블을 별도의 .ibd 파일로 저장하도록 설정하는 파라미터

 

활성화 시: 테이블 삭제 시 디스크 공간 즉시 회수 가능

비활성화 시: 시스템 테이블스페이스(ibdata1)에 모든 데이터 저장

 

현재 mariadb 버전에서는 기본적으로 파일별 테이블스페이스가 활성화된 상태

MySQL 5.6.6부터 innodb_file_per_table=ON이 기본값으로 설정

MariaDB 10.2.2 릴리스 노트

파일별 테이블스페이스가 활성화된 상태에서 생성되는 각 테이블의 .ibd 파일은 아래 경로에 저장

/var/lib/mysql/{database_name}/{table_name}.ibd

.ibd 파일은 테이블 데이터 증가에 따라 자동으로 확장되며, ALTER TABLE 없이도 파일 크기가 조절됨

 

연 단위 파티션 테이블 생성

파티션 테이블 생성 쿼리(파티션 키는 PK에 포함되야 함)

CREATE TABLE `batch_exec` (
	`batch_exec_datetime` DATETIME NOT NULL,
	`batch_nm` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
	`result_cd` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`result_msg` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`batch_exec_datetime`, `batch_nm`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
PARTITION BY RANGE (TO_DAYS(batch_exec_datetime)) (    
    PARTITION p2025 VALUES LESS THAN (TO_DAYS('2026-01-01')),
    PARTITION p2026 VALUES LESS THAN (TO_DAYS('2027-01-01')),
    PARTITION p2027 VALUES LESS THAN (TO_DAYS('2028-01-01')),
    PARTITION p2028 VALUES LESS THAN (TO_DAYS('2029-01-01')),
    PARTITION p2029 VALUES LESS THAN (TO_DAYS('2030-01-01')),
    PARTITION p2030 VALUES LESS THAN (TO_DAYS('2031-01-01')),
    PARTITION p2031 VALUES LESS THAN (TO_DAYS('2032-01-01')),
    PARTITION p2032 VALUES LESS THAN (TO_DAYS('2033-01-01')),
    PARTITION p2033 VALUES LESS THAN (TO_DAYS('2034-01-01')),
    PARTITION p2034 VALUES LESS THAN (TO_DAYS('2035-01-01')),
    PARTITION future VALUES LESS THAN MAXVALUE
);

기존 테이블을 파티션 테이블로 변경 쿼리

ALTER TABLE detectMst
PARTITION BY RANGE (TO_DAYS(datetime)) (    
    PARTITION p2025 VALUES LESS THAN (TO_DAYS('2026-01-01')),
    PARTITION p2026 VALUES LESS THAN (TO_DAYS('2027-01-01')),
    PARTITION p2027 VALUES LESS THAN (TO_DAYS('2028-01-01')),
    PARTITION p2028 VALUES LESS THAN (TO_DAYS('2029-01-01')),
    PARTITION p2029 VALUES LESS THAN (TO_DAYS('2030-01-01')),
    PARTITION p2030 VALUES LESS THAN (TO_DAYS('2031-01-01')),
    PARTITION p2031 VALUES LESS THAN (TO_DAYS('2032-01-01')),
    PARTITION p2032 VALUES LESS THAN (TO_DAYS('2033-01-01')),
    PARTITION p2033 VALUES LESS THAN (TO_DAYS('2034-01-01')),
    PARTITION p2034 VALUES LESS THAN (TO_DAYS('2035-01-01')),
    PARTITION future VALUES LESS THAN MAXVALUE
);

파티션 상세 정보 확인

SELECT * FROM information_schema.partitions
WHERE TABLE_SCHEMA = 'database이름' AND TABLE_NAME = 'table이름' AND PARTITION_NAME IS NOT NULL;

파티션 테이블별로 .ibd 파일 확인

 

자동 삭제 EVENT 스케줄러 생성

아래 sql 의 결과가 ON이면 스케줄러가 동작

SHOW VARIABLES LIKE 'event%';

 

my.cnf 파일 수정 및 mariadb 재시작

[mysqld]
event_scheduler = on

이벤트 생성

DELIMITER //
CREATE EVENT drop_test_table
	ON SCHEDULE 
		EVERY 1 YEAR 
		STARTS TIMESTAMP(CONCAT(YEAR(CURDATE()), '-01-02 00:00:00')) 
	ON COMPLETION NOT PRESERVE ENABLE 
DO BEGIN
-- 2년 전 연도 계산
    SET @yr = YEAR(CURDATE()) - 2;

    -- 파티션 이름 구성 (예: p2023)
    SET @pname = CONCAT('p', @yr);

    -- DROP PARTITION SQL 실행
    SET @sql = CONCAT('ALTER TABLE test_table DROP PARTITION ', @pname);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

 

.ibd 파일을 사용한 테이블 복구 테스트

1.기존 테이블과 동일한 구조의 복구 테이블 생성(백업된 .frm 파일이 있다면 참고)

2.테이블스페이스 분리

ALTER TABLE 복구_테이블명 DISCARD TABLESPACE;

3. .ibd 파일 복사

cp 원본_테이블명.ibd /var/lib/mysql/DB명/복구_테이블명.ibd
chown mysql:mysql /var/lib/mysql/DB명/복구_테이블명.ibd

4.테이블스페이스 임포트

ALTER TABLE 복구_테이블명 IMPORT TABLESPACE;

5.데이터 검증

CHECK TABLE 복구_테이블명;