TIL

6.2 테이블 압축

• 운영체제나 하드웨어에 대한 제약 없이 사용할 수 있다.
• 단점
  • 버퍼 풀 공간 활용률 낮음
  • 쿼리 처리 성능 낮음
  • 빈번한 데이터 변경 시 압축률 떨어짐

6.2.1 압축 테이블 생성

• innodb_fild_per_table=ON;
  • 테이블 압축을 사용하기 위해 테이블이 별도의 테이블 스페이스를 사용해야 한다.
  • 위 시스템 변수를 ON으로 서정하고 테이블을 생성해야 한다.
• ROW_FORMAT=COMPRESSED 명시
• KEY_BLOCK_SIZE 설정
  • 압축된 페이지 타깃 크기를 명시
  • 2n(n≥2)으로만 설정 가능하다.
  • InnoDB 버퍼 풀 페이지 크기가 16KB이면 4KB or 8KB
• 페이지 크기가 32KB or 64KB이면 압축을 적용할 수 없다.

SET GLOBAL innodb_file_per_table=ON;

CREATE TABLE compressed_table (
	c1 INT PRIMARY KEY
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

-- // KEY_BLOCK_SIZE만 명시해도 적용된다.

테이블 압축 과정

• 위 설정에서 압축되는 과정이다.
  1. 16KB 데이터 페이지를 압축
  1. 압축된 결과가 8KB 이하면 그대로 디스크에 저장 (압축 완료)
  2. 압축된 결과가 8KB를 초과하면 페이지를 스플릿해서 2개 페이지에 8KB씩 저장
     1. 나뉜 페이지에 대해 각각 “1”번 단계를 반복 실행
• 페이지 압축 결과가 목표 크기(KEY_BLOCK_SIZE)보다 작거나 같을 때까지 반복하기 때문에 목표 크기가 잘못 설정되면 서버 처리 성능이 급격히 떨어질 수 있다.

6.2.2 KEY_BLOCK_SIZE 결정

• 테이블 압축을 적용하기 전에 먼저 KEY_BLOCK_SIZE를 4KB 또는 8KB로 설정해 샘플 데이터를 저장해보고 적절한지 판단하는 것이 좋다.

설정 예시

• employees 테이블에 압축 적용을 고려하기 위해 샘플 테이블인 employees_comp4k와 employees_comp8k를 만들었다.
  • 각각은 이름대로 4KB, 8KB로 크기를 설정한 테이블이다.
• information_schema.INNODB_CMP_INDEX에서 압축 결과를 확인할 수 있다.

-- // 인덱스별로 압축 실행 횟수와 성공 횟수를 기록하기 위해
SET GLOBAL innodb_cmp_per_index_enalbed=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_INDEX;

table_name	index_name	compress_ops	compress_ops_ok	compression_failure_pct
employees_comp4k	PRIMARY	18635	13478	27.6737
employees_comp4k	ix_firstname	8320	7653	8.0168
employees_comp4k	ix_hiredate	7766	6721	134561

• PRIMARY 키는 총 18653번 압축해서 13478번 성공했다.
  • 나머지 5175번은 압축했다가 4KB를 초과해서 스플릿 후 재압축을 했다는 의미다.
  • 실패율이 27%이며 나머지 인덱스 2개도 실패율이 상당히 높다.
• 일반적으로 압축 실패율은 3~5% 미만으로 유지할 수 있게 KEY_BLOCK_SIZE를 설정하는 것이 좋다.

table_name	index_name	compress_ops	compress_ops_ok	compression_failure_pct
employees_comp8k	PRIMARY	8092	6593	18.5245
employees_comp8k	ix_firstname	1996	1996	0.0000
employees_comp8k	ix_hiredate	1391	1381	0.7189

• 위 표는 8KB로 설정했지만 PRIMARY 키 압축률은 여전히 높다.
  • 성능에 민감한 서비스라면 이 테이블에 압축은 적용하지 않는 것이 좋을 수도 있다.
• 압축 실패율이 높아도 디스크의 데이터 파일 크기는 줄어든다.
  • 위 예시에서는 4KB 압축 적용 후 30MB → 20MB로 줄어들었다.
  • 8KB 압축 적용 후에는 21MB로 줄어들었다.
• 따라서 위 경우 압축을 굳이 선택해야한다면 실패율은 낮으면서 압축 효율이 상대적으로 높은 8KB를 선택하는 것이 효율적이다.

압축 실패율이 높다고 해서 압축을 사용하지 말아야 한다는 것은 아니다. 삽입만 되는 테이블일 경우 (로그 파일) 스프릿 후 재압축이 빈번해도 전체적으로 데이터 파일 크기가 줄어든다면 이득이다. 하지만 빈번히 조회되고 수정되는 테이블에서는 압축이 CPU 자원을 많이 소모하기 때문에 압축을 고려하지 않는 것이 좋다.

6.2.3 압축된 페이지의 버퍼 풀 적재 및 사용

• InnoDB 버퍼 풀은 테이블의 데이터 페이지를 적재할 때 압축된 상태와 압축 해제된 상태 2개 버전을 관리한다.
  • LRU 리스트: 디스크에서 읽은 상태 그대로인 압축이 적용된 페이지 리스트
  • Unzip_LRU 리스트: 디스크에서 읽어 압축을 해제한 페이지 리스트
• 문제점
  • 버퍼 풀의 공간을 이중으로 사용함으로써 메모리를 낭비하게 된다.
  • 압축과 압축 해제 작업은 CPU를 많이 소모한다.
• 문제점 보완을 위한 즉흥적인(adaptive) 처리 방법
  • 버퍼 풀 공간이 필요한 경우에 LRU 리스트의 압축 페이지만 유지하고 Unzip_LRU 리스트의 압축 해제 버전은 제거한다.
  • 압축 데이터가 자주 사용되면 Unzip_LRU 리스트의 압축 해제 페이지를 계속 유지하면서 압축 및 압축 해제 작업을 최소화한다.
  • 압축 데이터가 사용되지 않으면 LRU와 Unzip_LRU 모두에서 제거한다.
• InnoDB는 압축 해제 버전 데이터 페이지를 적절한 수준으로 유지한다.
  • CPU 사용량이 높은 서버에서는 빈번한 압축 해제를 피하기 위해 Unzip_LRU 비율을 높여서 유지한다.
  • Disk IO 사용량이 높은 서버에서는 가능하면 Unzip_LRU 비율을 낮춰서 버퍼 풀 공간을 더 확보하도록 작동한다.

6.2.4 테이블 압축 관련 설정

• innodb_cmp_per_index_enabled
  • 압축이 적용된 테이블의 모든 인덱스별로 압축 성공 및 실행 횟수를 수집하도록 설정.
  • 비활성화되면 인덱스별이 아닌 테이블별로 압축 성공 및 횟수가 수집된다. i
  • information_schema.INNODB_CMP: 테이블별 기록
  • information_schema.INNODB_CMP_PER_INDEX: 인덱스별 기록
• innodb_compression_level
  • InnoDB에선 zlib 압축 알고리즘 지원
  • 이 변수를 이용해 압축률 설정 가능 (0~9, 기본값=6)
  • 값이 작을수록 압축 속도는 빠르지만 저장 공간은 커진다.
  • 압축이 빨라진다는 것은 CPU 자원을 적게 사용한다는 뜻
• innodb_compression_failure_threshold_pct
  • 테이블 단위로 압축 실패율이 이 설정값보다 커지면 압축하기 전에 원본 데이터 페이지 끝에 의도적으로 일정 크기의 빈 공간(패딩)을 추가한다.
  • 패딩은 압축률을 높여서 압축 결과가 KEY_BLOCK_SIZE보다 작아지게 만든다.
• innodb_compression_pad_pct_max
  • 압축 실패율이 높아질수록 패딩은 증가된 크기를 가진다.
  • 이 설정값 이상으로 패딩 크기는 증가될 수 없다.
  • %로 값을 설정하는데 이는 전체 데이터 페이지 크기 대비 패딩 공간의 비율을 의미한다.
• innodb_log_compressed_pages
  • 기본값 ON
  • MySQL 서버가 비정상 종료 후 재시작될 때 압축 알고리즘(zlib) 버전 차이가 있어도 복구 과정이 실패하지 않도록 압축된 데이터 페이지를 그대로 리두 로그에 기록한다.
  • 압축 알고리즘을 업그레이드할 때 도움이 된다.
  • 하지만 데이터 페이지 통째로 리도 로그에 저장하는 것은 리두 로그 증가량에 영향일 미칠 수 있다.
  • 압축 적용 후 리두 로그 용량에 문제가 생긴다면 이 변수를 OFF로 설정하고 모니터링해보는 것이 좋다.
    • 가능하면 ON으로 유지하는 것이 좋다.