테이블 압축

• 테이블 압축은 운영체제나 하드웨어에 대한 제약 없이 사용할 수 있기 때문에 일반적으로 활용도가 더 높은 편이다.
• 테이블 압축은 우선 디스크의 데이터 파일 크기를 줄일 수 있기 때문에 그만큼의 이득은 있다.

테이블 압축의 단점

• 버퍼 풀 공간 활용률이 낮음
• 쿼리 처리 성능이 낮음
• 빈번한 데이터 변경 시 압축률이 떨어짐

이러한 단점들이 발생하는 이유를 이해하려면 내부적으로 어떻게 압축이 실행되어 디스크에 저장되는지, 그리고 압축된 데이터 페이지들이 버퍼 풀에 어떻게 적재되어 사용하는지를 이해해야 한다.

압축 테이블 생성

• 테이블 압축을 사용하기 위한 전제 조건으로 압축을 사용하려는 테이블이 별도의 테이블 스페이스를 사용해야 한다.

생성 조건

• innodb_file_per_table 시스템 변수가 ON으로 설정된 상태에서 테이블이 생성돼야 한다.
• 테이블 압축을 사용하는 테이블은 생성할 때 ROW_FORMAT=COMPRESSED 옵션을 명시해야 한다.
  • 생략 시 자동으로 해당 옵션이 추가되어 생성된다.
• KEY_BLOCK_SIZE 옵션을 이용해 압축된 페이지의 타깃 크기(목표 크기)를 명시한다.
  • 값은 $2n(n≥2)$으로만 설정할 수 있다.
  • InnoDB 스토리지 엔진의 페이지 크기(innodb_page_size)가 16KB라면 KEY_BLOCK_SIZE 옵션은 4KB 또는 8KB만 설정할 수 있다.
• 페이지 크기가 32KB 또는 64KB인 경우에는 테이블 압축을 적용할 수 없다.

-- innodb_file_per_table 시스템 변수 ON
SET GLOBAL innodb_file_per_table=ON;

-- ROW_FORMAT 옵션과 KEY_BLOCK_SIZE 옵션을 모두 명시
CREATE TABLE compressed_table (
	c1 INT PRIMARY KEY
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

-- KEY_BLOCK_SIZE 옵션만 명시 (8KB)
CREATE TABLE compressed_table (
	c1 INT PRIMARY KEY
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

💡 innodb_file_per_table 시스템 변수가 0인 상태에서 제너럴 테이블스페이스(General Tablespace)에 생성되는 테이블은 테이블 압축을 사용할 수도 있고 그러지 못할 수도 있으니 매뉴얼의 제너럴 테이블스페이스 제약 사항을 검토해야 한다.

테이블 압축의 작동 방식

InnoDB 스토리지 엔진이 압축을 적용하는 방법

1. 데이터 페이지를 압축

   1-1. 압축된 결과가 KEY_BLOCK_SIZE 값 이하이면 그대로 디스크에 저장 (압축 완료)

   1-2. 압축된 결과가 KEY_BLOCK_SIZE 값을 초과하면 원본 페이지를 스플릿(split)해서 2개의 페이지에 저장한다.

2. 나뉜 페이지 각각에 대해 1번 단계를 반복 실행

정리

• 압축 적용에 사용되는 KEY_BLOCK_SIZE 옵션은 압축된 페이지가 저장될 페이지의 크기를 지정한다.
• 테이블 압축에서는 InnoDB 스토리지 엔진의 “InnoDB I/O 레이어”에서는 아무런 역할을 하지 않는다.
• 테이블 압축 방식에서 가장 중요한 것은 원본 데이터 페이지의 압축 결과가 목표 크기(KEY_BLOCK_SIZE )보다 작거나 같을 때까지 반복해서 페이지를 스플릿하는 것이다.
  • 목표 크기가 잘못 설정되면 MySQL 서버의 처리 성능이 급격히 떨어질 수 있으니 주의하자.

KEY_BLOCK_SIZE 결정

테이블 압축에서 가장 중요한 부분은 압축된 결과가 어느 정도가 될지를 예측해서 KEY_BLOCK_SIZE 를 결정하는 것이다.

• 테이블 압축을 적용하기 전에 먼저 KEY_BLOCK_SIZE 를 4KB 또는 8KB로 테이블을 생성해서 샘플 데이터를 저장해보고 적절한지 판단하는 것이 좋다.
  • 샘플 데이터는 많으면 많을 수록 더 정확한 테스트가 가능하다.
  • 최소한 테이블의 데이터 페이지가 10개 정도는 생성되도록 테스트 데이터를 INSERT 해보는 것이 좋다.
• 테스트를 실행하기 전에 innodb_cmp_per_index_enabled 시스템 변수를 ON으로 변경해야 인덱스별로 압축 실행 횟수와 성공 횟수가 기록된다.
• 압축 실패율(compression_failure_pct 칼럼)은 3~5% 미만으로 유지할 수 있게 KEY_BLOCK_SIZE 를 선택하는 것이 좋다.
  • 압축 실패율이 높으면 디스크로 기록하기 전에 압축하는 과정에서 꽤 오랜 시간이 걸릴 것이라고 예측할 수 있다.
• 압축 실패율이 높다고 해서 압축을 사용하지 말아야 한다는 것을 의미하지는 않는다.
• 압축 실패율이 그다지 높지 않은 경우라고 하더라도 테이블의 데이터가 매우 빈번하게 조회되고 변경된다면 압축은 고려하지 않는 것이 좋다.
• 테이블 압축은 zlib를 이용해 압축을 실행하는데, 예상외로 압축 알고리즘은 많은 CPU 자원을 소모한다.

-- 기존 테이블과 동일한 구조로, 테이블 압축을 사용하는 예제 테이블 생성
CREATE TABLE ...

-- innodb_cmp_per_index_enabled 변수를 ON으로 설정
SET GLOBAL innodb_cmp_per_index_enabled=ON;

-- 기존 테이블의 데이터를 그대로 압축 테스트 테이블로 저장
INSERT INTO ... 

-- 인덱스별로 압축 횟수와 성공 횟수, 압축 실패율을 조회
SELECT 
	table_name, index_name, compress_ops, compress_ops_ok,
	(compress_ops-compress_ops_ok)/compress_ops*100 as compression_failure_pct
FROM
	informaion_schema.INNODB_CMP_PER_INDEX;

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

• InnoDB 스토리지 엔진은 압축된 테이블의 데이터 페이지를 버퍼 풀에 적재하면 다음 상태 2개 버전을 관리한다.
  • 압축된 상태
  • 압축이 해제된 상태
• 그래서 InnoDB 스토리지 엔진은 다음 리스트들을 별도로 관리한다.
  • 디스크에서 읽은 상태 그대로의 데이터 페이지 목록을 관리하는 LRU 리스트
  • 압축된 페이지들의 압축 해제 버전인 Unzip_LRU 리스트
• MySQL 서버에는 압축된 테이블과 압축되지 않은 테이블이 공존하므로 결국 LRU 리스트는 다음과 같이 압축된 페이지와 압축되지 않은 페이지를 모두 가질 수 있다.
  • 압축이 적용되지 않은 테이블의 데이터 페이지
  • 압축이 적용된 테이블의 압축된 데이터 페이지
• Unzip_LRU 리스트는 압축이 적용되지 않은 테이블의 데이터 페이지는 가지지 않는다.
  • 압축이 적용된 테이블에서 읽은 데이터 페이지만 관리한다.
  • 압축을 해제한 상태의 데이터 페이지 목록이 관리된다.

문제점

• InnoDB 스토리지 엔진은 압축된 테이블에 대해서는 버퍼 풀의 공간을 이중으로 사용함으로써 메모리를 낭비하는 효과를 가진다.
• 압축된 페이지에서 데이터를 읽거나 변경하기 위해서는 압축을 해제해야 한다.
  • 압축 및 압축 해제 작업은 CPU를 상대적으로 많이 소모하는 작업이다.

어댑티브 알고리즘

• 이러한 두 가지 단점을 보완하기 위해 Unzio_LRU 리스트를 별도로 관리하고 있다가 MySQL 서버로 유입되는 요청 패턴에 따라서 적절히(Adaptive) 다음과 같은 처리를 한다.
  • InnoDB 버퍼 풀의 공간이 필요한 경우에는 LRU 원본 데이터 페이지(압축된 형태)는 유지하고, Unzip_LRU 리스트에서 압축 해제된 버전은 제거해서 버퍼 풀의 공간을 확보한다.
  • 압축된 데이터 페이지가 자주 사용되는 경우에는 Unzio_LRU 리스트에 압축 해제된 페이지를 계속 유지하면서 압축 및 압축 해제 작업을 최소화한다.
  • 압축된 데이터 페이지가 사용되지 않아서 LRU 리스트에서 제거되는 경우에는 Unzip_LRU 리스트에서도 함께 제거된다.
• InnoDB 스토리지 엔진은 버퍼 풀에서 압축 해제된 버전의 데이터 페이지를 적절한 수준으로 유지하기 위해 다음과 같은 어댑티브(적응적인) 알고리즘을 사용한다.
  • CPU 사용량이 높은 서버에서는 가능하면 압축과 압축 해제를 피하기 위해 Unzip_LRU의 비율을 높여서 유지하고
  • Disk IO 사용량이 높은 서버에서는 가능하면 Unzip_LRU 리스트의 비율을 낮춰서 InnoDB 버퍼 풀의 공간을 더 확보하도록 작동한다.

테이블 압축 관련 설정

• 테이블 압축을 사용할 때 연관된 시스템 변수가 몇 가지 있는데, 모두 페이지의 압축 실패율을 낮추기 위해 필요한 튜닝 포인트를 제공한다.

innodb_cmp_per_index_enabled

• 테이블 압축이 사용된 테이블의 모든 인덱스별로 압축 성공 및 압축 실행 횟수를 수집하도록 설정한다.
• 비활성화(OFF)되면 테이블 단위의 압축 성공 및 압축 실행 횟수만 수집된다.
• 테이블 단위로 수집된 정보는 information_schema.INNODB_CMP 테이블에 기록된다.
• 인덱스 단위로 수집된 정보는 information_schema.INNODB_CMP_PER_INDEX 테이블에 기록된다.

innodb_compression_level

• InnoDB의 테이블 압축은 zlib 압축 알고리즘만 지원하는데, 이때 innodb_compression_level 시스템 변수를 이용해 압축률을 설정할 수 있다.
• 0~9까지의 값 중에서 선택할 수 있다.
  • 값이 작을수록 압축 속도는 빨라지지만 저장 공간은 커질 수 있다.
  • 값이 커질수록 속도는 느려질 수 있지만 압축률은 높아진다.
• 기본값은 압축 속도와 압축률 모두 중간 정도로 선택한 값인 6이다.
  • 여기서 압축 속도는 CPU 자원 소모량과 동일한 의미다.
  • 압축 속도가 빨라짐 = CPU 자원을 그만큼 적게 사용함

innodb_compression_failure_threshold_pct

• 테이블 단위로 압축 실패율이 innodb_compression_failure_threshold_pct 시스템 설정값보다 커지면 압축을 실행하기 전 원본 데이터 페이지의 끝에 의도적으로 일정 크기의 빈 공간을 추가한다.
• 추가된 빈 공간은 압축률을 높여서 압축 결과가 KEY_BLOCK_SIZE 보다 작아지게 만드는 효과를 낸다.
• 여기서 추가하는 빈 공간을 패딩(Padding)이라고 한다.

innodb_compression_pad_pct_max

• 패딩 공간은 압축 실패율이 높아질수록 계속 증가된 크기를 가지는데, 추가할 수 있는 패딩 공간의 최대 크기는 innodb_compression_pad_pct_max 시스템 설정값 이상을 넘을 수 없다.
• innodb_compression_pad_pct_max 시스템 설정값에는 % 값을 설정한다.
  • 전체 데이터 페이지 크기 대비 패딩 공간의 비율을 의미한다.

innodb_log_compressed_pages

• InnoDB 스토리지 엔진은 MySQL 서버가 비정상적으로 종료했다가 다시 시작되는 경우 압축 알고리즘(zlib)의 버전 차이가 있더라도 복구 과정이 실패하지 않도록, 압축된 데이터 페이지를 그대로 리두 로그에 기록한다.
  • 이는 압축 알고리즘을 업그레이드할 때 도움이 되지만
  • 데이터 페이지를 통째로 리두 로그에 저장하는 것은 리두 로그의 증가량에 상당한 영향을 미칠 수도 있다.
• 압축을 적용한 후 리두 로그 용량이 매우 빠르게 증가한다거나 버퍼 풀로부터 더티 페이지가 한꺼번에 많이 기록되는 패턴으로 바뀌었다면 이 시스템 변수를 OFF로 설정한 후 모니터링해보는 것이 좋다.
• 기본값은 ON인데, 가능하면 이 상태를 유지하자.

Reference

참고 서적

📔 Real MySQL 8.0