📔설명
페이지 압축, 테이블 압축을 알아보자
🥢페이지 압축
Transparent Page Compression이라고도 불림
MySQL 서버가 디스크에 저장하는 시점에 데이터 페이지가 압축, MySQL 서버가 디스크에서 데이터 페이지를 읽어올 때 압축 해제
=> 버퍼 풀에 데이터 페이지가 한 번 적재시 압축이 해제된 상태로만 데이터 페이지 관리
데이터 페이지를 압축한 결과가 적어도 하나의 테이블은 동일한 크기의 페이지로 통일돼야 함
- 16KB 페이지를 압축(압축 결과 7KB)
- MySQL 서버는 디스크에 압축된 결과 7KB 기록
(MySQL 서버는 압축 데이터 7KB에 9KB의빈 데이터를 기록) - 디스크에 데이터 기록 한 후, 7KB 이후의 공간 9KB에 대해
펀치 홀생성 - 파일 시스템은 7KB만 남기고 나머지 디스크의 9KB 공간은 다시
운영체제로 반납
- 운영체제뿐만 아니라
하드웨어자체에서도펀치 홀 기능을 지원해야 사용 가능 - 파일 시스템 관련 명령어가
펀치 홀을 지원하지 못함
-- 테이블 생성 시
create table t1 (
c1 int
) compression="zlib";
-- 테이블 변경 시
alter table t1 compression="zlib";
optimize table t1;🍴테이블 압축
단점
- 버퍼 풀
공간 활용률 낮음 - 쿼리 처리
성능 낮음 - 빈번한 데이터 변경 시
압축률 떨어짐
1. 압축 테이블 생성
테이블 압축 사용을 위한 전제 조건 : 압축을 사용하려는 테이블이 별도의 테이블 스페이스를 사용해야 함
=> innodb_file_per_table 변수가 ON
set global innodb_file_per_table=on;
--row_format=compressed해야 테이블 압축
create table com_table(
c1 int primary key
) row_format=compressed --생략 후 key_block_size만 입력시 자동 생성
key_block_size=8; --KB 단위Key_block_size 옵션 : 압축된 페이지가 저장될 페이지 크기 지정
- 16KB의 데이터 페이지를 압축
1.1 압축된 결과가 8KB 이하면 그대로 디스크에 저장 (압축 완료)
1.2 압축된 결과가 8KB 초과하면 원본 페이지를스플릿해서2개의 페이지에8KB씩 저장 - 나뉜 페이지 각각에 대해 1번 단계 반복 실행
2. KEY_BLOCK_SIZE 결정
KEY_BLOCK_SIZE를 4KB 또는 8KB로 생성하고, 샘플 데이터 저장 후 판단하는 것이 좋음
-- 테이블 압축 사용 예제 테이블 생성
create table employees_comp4k(
emp_no int not null,
birth_date date not null,
first_name varchar(14) not null,
last_name varchar(16) 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;
--employees 테이블 데이터를 그대로 압축 테스트 테이블로
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;
압축 실패율은 3~5% 미만으로 나오게 하는 것이 좋음
3. 압축된 페이지의 버퍼 풀 적재 및 사용
InnoDB 엔진은 압축된 테이블의 데이터 페이지를 버퍼 풀에 적재시 압축된 상태와 압축 해제된 상태 2개 버전 관리
=> 압축된 테이블에 대해선 버퍼 풀의 공간을 이중으로 사용함으로써 메모리 낭비
=> 압축된 페이지에서 데이터를 읽거나 변경하기 위해 압축 해제를 해야 하는데, CPU 많이 소모
위 두가지 단점을 보완하기 위해 Unzip_LRU 리스트를 별도로 관리하다가 MySQL 서버로 유입되는 요청 패턴에 따라 적절히 아래와 같이 수행
- InnoDB
버퍼 풀 공간이 필요한 경우,LRU 리스트에서 원본 데이터 페이지는 유지하고,Unzip_LRU 리스트에서 압축 해제된 버전은제거 압축된 데이터 페이지가 자주 사용되는 경우Unzip_LRU 리스트에 압축 해제된 페이지를 계속 유지하며 압축 및 압축 해제 작업최소화- 압축된 데이터 페이지가 사용되지 않아
LRU 리스트에서 제거시,Unzip_LRU 리스트에서도 함께제거
4. 테이블 압축 관련 설정
innodb_cmp_per_index_enabled: 테이블 압축이 사용된 테이블의모든 인덱스별로 압축 성공 및 압축 실행 횟수 수집innodb_compression_level:압축률설정innodb_compression_failure_threshold_pct와innodb_compression_pad_pct_max: 앞의 값보다압축 실패율이 커지면압축실행하기 전 원본 데이터 페이지 끝에 의도적으로 일정 크기의 빈 공간(패딩)을 추가
=> 추가된 빈 공간은압축률을높여서 압축 결과가key_block_size보다 작아지게 만듦
=> 패딩 공간은 압축 실패율이 높아질수록계속 증가된 크기를 가지며 최대 크기는 뒤의 시스템 변수 값을 넘지 못함innodb_log_compressed_pages: MySQL 서버가 비정상적으로 종료됐다가 다시 시작되는 경우,압축 알고리즘의 버전 차이가 있더라도 복구 실패하지 않도록압축된 데이터 페이지를 그대로리두 로그에 기록
MSSQL DBA 신입