백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.
1. 페이지 압축
- 디스크에 저장하는 시점에 페이지가 압축되어 저장됨
- 디스크에서 페이지를 읽어올 때 압축이 해제됨
압축 크기
- 한 페이지가 어느 정도로 압축될지는 모름
- 단, 특정 테이블의 페이지 크기는 모두 동일함
펀치홀
- 운영체제의 파일 압축 기능
- MySQL에서는 16KB를 유지하면서 압축 후, 남은 공간은 운영체제에 반납하여 공간을 절약함 (실제로는 압축된 페이지만 사용)
- 여러 문제점으로 인해 잘 사용되지 않음
문제점
- 운영체제 뿐만 아니라, 하드웨어 자체에서도 지원해야 함
- 파일 시스템 관련 명령어가 펀지 홀을 지원하지 못함 (복구하는 과정에서 남은 공간까지 가져올 수 있음)
설정
CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";
ALTER TABLE t1 COMPRESSION="zlib";
OPTIMIZE TABLE t1;
2. 테이블 압축
- 운영체제나 하드웨어에 대한 제약 없이 사용할 수 있음
단점
- 버퍼 풀 공간 활용률이 낮음
- 쿼리 처리 성능이 낮음
- 데이터 변경이 빈번할 경우, 압축율이 떨어짐
압축 테이블 생성
SET GLOBAL innodb_file_per_table=ON;
CREATE TABLE compressed_table (
c1 INT PRIMARY KEY
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
별도의 테이블 스페이스를 사용해야 함
- 'innodb_file_per_table' 이 "ON"된 상태에서 테이블을 생성해야 함 (각 테이블이 개별 .idb 파일로 생성됨)
ROW_FORMAT=COMPRESSED 옵션 명시
- 압축된 데이터가 한 페이지에 담기지 못하면 페이지가 스플릿되어 저장됨
- 일부 데이터는 현재 페이지에 남고 나머지는 새로운 페이지로 이동하여 저장됨
KEY_BLOCK_SIZE 옵션 명시
- 페이지의 목표 압축 크기 (4KB or 8KB만 가능함)
- 페이지 크기가 16KB 여야만 함
KEY_BLOCK_SIZE 결정
CREATE TABLE employees_comp4k (
emp_no int NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(14) NOT NULL,
gender enum('M', 'F') NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no),
KEY ix_firstname (first_name),
KEY ix_hiredate (hire_date),
) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
SET GLOBAL innodb_cmp_per_index_enabled=ON; # 인덱스 별 압축 실행 횟수 및 성공 횟수
INSERT INTO employees_comp4k SELECT * FROM employees; # 압축 테스트 테이블에 데이터 저장
SELECT table_name, index_name, compress_ops, compress_ops_ok,
(compress_ops-compress_ops_ok)/compress_ops * 100 as compression_failure_pct
FROM information_schema.INNODB_CMP_PER_INDEX;
압축된 결과가 어느 정도 될지를 예측해서 KEY_BLOCK_SIZE를 결정하는 것이 가장 중요
- 압축 전, 샘플 데이터로 4KB or 8KB 값으로 셋팅해서 적절한지 테스트하여 판단하기
- 일반적으로 압축 실패율을 3~5%로 유지할 수 있게 설정하는 것이 좋음
- 압축 실패율이 높을 수록 압축시간이 오래 걸림
압축된 페이지의 버퍼 풀 적재 및 사용
- 버퍼 풀은 압축된 페이지와 압축이 해제된 페이지 총 2개의 버전을 관리함
- 압축 작업에 드는 성능을 보존
- 빠른 읽기 성능
LRU 리스트
Unzip_LRU 리스트
설정
innodb_cmp_per_index_enabled
- 테이블 압축이 사용된 테이블의 모든 인덱스별로 압축 성공 및 실행 횟수 수집
- 테이블 단위로는 information_schema.INNODB_CMP 테이블에 기록됨
- 인덱스 단위로는 information_schema.INNODB_CMP_PER_INDEX 테이블에 기록됨
innodb_compression_level
- 압축율 설정 (0~9)
- 테이블 압축은 zlib 알고리즘만 지원됨
innodb_compression_failure_threshold_pct & innodb_compression_pad_pct_max
- 압축 실패율이 innodb_compression_failure_threshold_pct 값보다 커질 경우 페이지에 패딩이 붙기 시작함
- 추가된 공간은 압축율을 높여 압축 결과가 KEY_BLOCK_SIZE보다 작게 만듬
- 추가할 수 있는 패딩 크기는 innodb_compression_pad_pct_max 값을 넘어갈 수 없음
innodb_log_compressed_pages
- 비정상적으로 종료된 경우, 복구 시 압축된 페이지를 그대로 리두 로그에 기록