Last Updated on 5월 10, 2024 by Jade(정현호)
안녕하세요.
이번 포스팅에서는 MySQL의 데이터 압축 2가지에 대해서 확인해보록 하겠습니다.
Contents
데이터 압축 개요
MySQL 서버에서 디스크에 저장된 데이터 파일의 크기는 일반적인 쿼리의 처리 성능과도 직결되지만 백업 및 복구 시간과도 밀접하게 연결됩니다.
데이터 파일이 크면 클수록 쿼리를 처리하기 위해서 더 많은 데이터 페이지를 InnoDB 버퍼 풀로 읽어야 할 수도 있으며, 새로운 페이지가 버퍼 풀로 적재되기 때문에 그 만큼 더티 페이지가 더 자주 디스크로 기록되어야 합니다.
InnoDB의 압축 기능을 사용하여 데이터가 압축된 형태로 저장되는 테이블을 생성할 수 있습니다. 압축은 성능(raw performance)과 확장성을 모두 개선하는 데 도움이 될 수 있습니다.
압축은 디스크와 메모리 간에 전송되는 데이터가 적고 디스크와 메모리 공간을 덜 차지함을 의미합니다. 인덱스 데이터도 압축되기 때문에 보조 인덱스가 있는 테이블의 경우 장점이 커지게 됩니다.
SSD 저장 장치는 HDD 장치보다 보통 용량이 작기 때문에 디스크 사용량을 낮춰줄 수 있는 압축 기능이 특히 중요할 수 있습니다.
그래서 이러한 고민을 해소하기 위해서 여러 DBMS 에서는 데이터 압축 기능 제공하고 있으며, MySQL 에서는 크게 테이블 압축과 페이지 압축의 두가지 종류로 구분하여 지원하게 됩니다.
참고
포스팅은 Real MySQL 8.0 책 내용을 정리하였으며, MySQL Document 를 참조하였습니다.
페이지 압축(Page Compression)
페이지 압축 은 "Transparent Page Compression" 이라고도 불립니다. MySQL 서버가 디스크에 저장하는 시점에 데이터 페이지가 압축되어 저장되고, 반대로 MySQL 서버가 디스크에서 데이터 페이지를 읽어올 때 압축이 해제되기 때문입니다
InnoDB는 "file-per-table" 테이블스페이스에 있는 테이블에 대해 페이지 레벨의 압축 기능을 지원합니다. 페이지 압축은 CREATE TABLE 또는 ALTER TABLE에서 COMPRESSION 속성을 지정하여 활성화됩니다.
지원되는 압축 알고리즘에는 Zlib 및 LZ4 가 있습니다.
즉 버퍼 풀에 데이터 페이지가 한 번 적재되면 InnoDB 스토리지 엔진은 압축이 해제된 상태로만 데이터 페이지를 관리하게 됩니다. 그래서 MySQL 서버의 내부 코드에서는 압축 여부와 상관없이 투명(Transparent) 하게 동작하게 됩니다.
한가지의 문제점은 압축한 결과 용량이 얼마나 될지 예측이 불가능하지만 테이블은 동일한 크기의 페이지(블록) 으로 통일되어야 한다는 것이며, 페이지 압축 기능은 운영체제별 특정 버전의 파일시스템에서만 지원되는 sparse file 과 홀 펀칭(hole punching) 이라는 기능이 지원되어야 사용할 수 있습니다.
페이지 압축은 NTFS가 있는 Windows에서 지원되며, Linux는 커널 레벨에서 홀 펀칭(hole punching) 지원을 아래의 플렛폼 과 버전에서 지원합니다.
-
커널 버전 3.10.0-123 이상을 사용하는 RHEL 7 및 derived distributions
-
OEL 5.10(UEK2) 커널 버전 2.6.39 이상
-
OEL 6.5(UEK3) 커널 버전 3.8.13 이상
-
OEL 7.0 커널 버전 3.8.13 이상
-
SLE11 커널 버전 3.0-x
-
SLE12 커널 버전 3.12-x
-
OES11 커널 버전 3.0-x
-
Ubuntu 14.0.4 LTS 커널 버전 3.13 이상
-
Ubuntu 12.0.4 LTS 커널 버전 3.2 이상
-
데비안 7 커널 버전 3.2 이상
Note
위의 Linux 배포판에서 사용 가능한 모든 파일 시스템은 홀 펀칭(hole punching)을 지원하지 않을 수 있습니다.
즉 모든 파일시스템에서 사용할 수 있는 것은 아님을 의미합니다.
MySQL 서버는 아래의 내용과 으로 특정 테이블에 대해서 16KB 크기의 페이지를 유지하면서도 압축된 다양한 크기의 데이터 페이지를 디스크에 저장하고 압축된 만큼의 공간을 저장할 수 있습니다.
1) 16KB 페이지를 압축(압축 결과 7KB 로 가정)
2) MySQL 서버는 디스크에 압축된 결과 7KB를 기록(9KB 는 빈데이터를 기록하게 됨)
3) 디스크에 데이터 기록 후, 빈 공간 9KB에 대해서는 홀 펀칭(hole punching)을 생성
4) 파일 시스템은 7KB만 남기고 나머지 디스크 9KB 공간은 다시 운영체제로 반납
MySQL 서버의 페이지 압축 기능의 문제는 위에서 언급한 홀 펀칭(hole punching) 기능이 지원이 되는 특정 버전의 파일 시스템과 운영체제, 그리고 하드웨어 자체에서도 지원을 해야 사용할 수 있다는 점입니다.
또한 아직 파일시스템 관련 명령어에서 홀 펀칭(hole punching)을 지원하지 못한다는 것도 있습니다.
예를 들어 홀 펀칭(hole punching)을 지원하는 파일시스템을 사용하더라도 백업 복구나 또는 파일의 복사 이동의 cp 같은 명령어 또는 Xtrabackup 과 같은 명령어나 유틸리티 툴이 파일을 복사하게 되면 펀치홀 기능이 사리진 원본 사이즈로 복사가 될 수 있기 때문입니다.
특정 명령어로는 용량을 동일하게 할 수 있으나 복사 등의 작업에 일반적인 복사 명령어에 비해 매우 느리게 수행될 수도 있습니다.
[참조 내용]
그래서 이러한 여러 제약사항 등의 이유로 페이지 압축은 많이는 사용되지않는 기술 이긴 합니다.
페이지 압축을 이용하기 위해서는 테이블을 생성하거나 변경할 때 COMPRESSION 옵션을 사용하면 됩니다.
mysql> create table tb_test(no int) COMPRESSION="zlib"; OR mysql> create table tb_test(no int) COMPRESSION="Lz4";
Note
COMPRESSION 에서 사용할 수 있는 값은 ZLIB, LZ4, NONE 입니다
테이블 변경시(ALTER) 아래와 같이 진행하시면 됩니다.
mysql> alter table tb_test COMPRESSION="zlib"; mysql> optimize table t1;
ALTER TABLE 문에서 페이지 압축을 활성화할 수도 있습니다.
그러나 ALTER TABLE ... COMPRESSION은 테이블스페이스의 압축 속성만(딕셔너리만) 업데이트합니다.
새 압축 알고리즘을 설정한 후 발생하는 테이블스페이스에 대한 쓰기는 새 설정을 사용하지만 새 압축 알고리즘을 기존 페이지에 적용하려면 OPTIMIZE TABLE을 사용하여 테이블을 다시 작성해야 합니다.
페이지 압축 비활성화는 COMPRESSION=NONE 을 사용하면 되며, 설정 이후 쓰기 작업에 대해서는 더 이상 압축을 사용하지 않습니다. 다만 기존 페이지의 압축을 해제하려면 OPTIMIZER TABLE 을 사용하여 테이블을 재작성 해야 합니다.
mysql> alter table tb_test COMPRESSION="NONE"; mysql> optimize table t1;
테이블 압축(Table Compression)
이번에는 테이블 압축 방식에 대해서 확인해보도록 하겠습니다.
테이블 압축 개요
지금의 개인의 컴퓨터나 서버 컴퓨터의 프로세서와 캐시 메모리는 디스크 저장 장치보다 속도가 더 빨라 졌기 때문에 많은 작업 부하는 디스크와 연관되어 있습니다.
데이터 압축을 사용하면 적은 CPU 사용 비용으로 데이터베이스 크기 와 I/O 를 줄이며 처리량을 향상할 수 있습니다.
KEY_BLOCK_SIZE 시스템 변수에 더 작은 값을 지정하면 점점 더 작아지는 페이지의 I/O 이점을 얻을 수 있습니다.
다만 KEY_BLOCK_SIZE에 너무 작은 값을 지정하였을 경우 DB 의 페이지 사이즈가 KEY_BLOCK_SIZE 값보다 클 경우 페이지를 재구성하기(Split 작업) 위한 추가적인 오버헤드가 발생할 수 있습니다.
ROW_FORMAT=COMPRESSED 로 생성된(압축이 설정된) InnoDB의 테이블은 DB 에 설정된 innodb_page_size 값보다 디스크에서 더 작은 페이지 크기를 사용할 수 있습니다.
페이지가 작을수록 디스크에서 읽고 쓰는 데 더 적은 I/O가 필요하므로 SSD 장치에 특히 유용합니다.
테이블 압축은 페이지 압축과 달리 운영체제나 하드웨어에 대한 제약 없이 사용할 수 있기 때문에 일반적으로 활용도가 더 높은 편입니다.
테이블 압축은 데이터 파일의 크기를 줄일 수 있기 때문에 공간 소모에 대한 이득이 있지만 아래와 같은 단점도 있습니다.
- 버퍼 풀 공간 활용률이 낮음
- 쿼리 처리 성능이 낮음
- 빈번한 데이터 변경시 압축률이 떨어짐
압축 테이블 생성
테이블 압축을 사용하기 위한 전제 조건으로 압축을 사용하려는 테이블이 별도의 테이블 스페이스를 사용해야 합니다.
그러기 위해서는 innodb_file_per_table 파라미터가 ON 으로 설정되어 있는 상태에서 테이블을 생성해야 합니다.
innodb_file_per_table 파라미터가 활성화가 되어 있다면 테이블 생성시 ROW_FORMAT=COMPRESSED 옵션을 명시하면 됩니다.
KEY_BLOCK_SIZE 옵션을 통해서 압축된 페이지의 목표 크기를 명시하게 되며, 2n(n 값은 2 이상) 으로만 설정할 수 있습니다.
InnoDB 스토리지 엔진의 페이지 크기(innodb_page_size - default 16KB) 가 16KB 라면 KEY_BLOCK_SIZE 는 4KB 또는 8 K 등으로 설정할 수 있습니다.
그리고 32KB 및 64KB 페이지 크기(innodb_page_size)는 압축 기능을 지원하지 않아서 사용할 수 없습니다.
InnoDB supports 32KB and 64KB page sizes but these page sizes do not support compression
테이블 생성 예시
-- 먼저 innodb_file_per_table 변수 활성화
mysql> set global innodb_file_per_table=ON;
-- ROW_FORMAT 옵션과 KEY_BLOCK_SIZE 옵션을 모두 명시
mysql> create table compressed_table1
(
c1 INT PRIMARY KEY
)
ROW_FORMAT=COMPRESSED
KEY_BLOCk_SIZE=8;
-- KEY_BLOCK_SIZE 옵션만 명시
mysql> create table compressed_table2
(
c1 INT PRIMARY KEY
)
KEY_BLOCk_SIZE=8;
두 번째 테이블 생성 구문 에서는 ROW_FORMAT 옵션이 생략되면 자동으로 ROW_FORMAT=COMPRESSED 옵션이 추가되어 생성됩니다.
KEY_BLOCK_SIZE 에 명시된 옵션값은 KB 단위를 설정하면 됩니다. 위의 예시 에서는 KEY_BLOCK_SIZE 는 8KB를 의미합니다.
기존의 생성된 테이블을 압축하기위해서는 아래와 같이 alter 를 통해서 진행할 수 있습니다.
mysql> alter table 테이블명 ROW_FORMAT=COMPRESSED KEY_BLOCk_SIZE=8;
Note
innodb_file_per_table 시스템 변수가 0(OFF) 인 상태에서 제네럴 테이블스페이스(General Tablespace)에 생성되는 테이블도 테이블 압축을 사용할수는 있습니다.
하지만 제네럴 테이블스페이스의 FILE_BLOCK_SIZE 에 의해 압축을 사용 할수도 있고 그러지 못할수도 있습니다.
제네럴 테이블스페이스를 사용하는 테이블에 대해 압축을 고려 중이라면 제약 사항 확인이 필요 합니다.
압축 적용에 설정하는 KEY_BLOCk_SIZE 옵션은 압축된 페이지가 저장될 페이지의 크기를 지정하면 됩니다.
예를 들어, 현재 InnoDB 스토리지 엔진의 데이터 페이지(블록) 크기가 16KB, 그리고 KEY_BLOCK_SIZE 가 8로 설정하였다고 가정하면 데이터 페이지를 압축한 용량이 얼마가 될지 알 수가 없는데 어떻게 KEY_BLOCK_SIZE 를 테이블을 생성할 때 설정할 수 있을까요?
InnoDB 스토리지 엔진이 압축을 적용하는 프로세스는 아래와 같이 진행됩니다.
1. 16KB의 데이터 페이지를 압축
1.1 압축된 결과가 8KB 이하이면 그대로 디스크에 저장(압축 완료)
1.2압축된 결과가 8KB를 초과하면 원본 페이지를 스플릿(Split) 해서 2개의 페이지에 8KB씩 저장
2. 나뉜(Split) 페이지 각각에 대해서 1번 단계를 반복 실행
테이블 압축 방식에서 가장 중요한 것은 원본 데이터 페이지의 압축 결과가 목표 크기(KEY_BLOCK_SIZE) 보다 작거나 같을 때까지 반복해서 페이지를 스플릿(Split)하는 것입니다.
그래서 목표 크기를 잘못 설정하게 되면 MySQL 서버의 처리 성능이 급격히 떨어질 수 있으므로 주의가 필요 합니다.
KEY_BLOCK_SIZE 결정
테이블 압축에서 가장 중요한 부분은 압축된 결과가 어느 정도가 될지를 예측해서 KEY_BLOCK_SIZE 를 결정하는 것입니다.
그래서 테이블을 압축하기 전에 먼저 KEY_BLOCK_SIZE 를 4KB 또는 8KB 로 테이블을 생성해서 샘플 데이터를 저장해보고 적절한 지를 판단하는 것이 좋을 거라고 생각됩니다.
테스트 테이블은 최소한 데이터 페이지 10개 정도는 생성되도록 테스트 데이터를 적재하는 것이 좋을 거라고 생각됩니다.
(물론 실제 데이터를 COPY 하여 테스트해보는 것이 가장 좋을 것입니다)
예제 테이블을 생성 및 데이터를 추가하여 용량 등을 살펴보도록 하겠습니다.
포스팅에서 예제 테이블은 employees 테이블을 이용하였습니다.
예제 테이블 생성
예제 테이블은 MySQL Document의 employees-installation 에서 가이드 되고 있는 github 에서 테이블 DDL 구문과 데이터를 다운로드 받을 수 있습니다.
서버에서 github 에 있는 데이터 dump 파일을 직접 다운로드 받겠습니다.
wget https://github.com/datacharmer/test_db/raw/master/load_employees.dump
테스트를 위해서 다운로드 받은 파일을 아래와 같이 복사 및 내용 치환을 하도록 하겠습니다.
cp load_employees.dump load_employees_4k.dump cp load_employees.dump load_employees_8k.dump sed -i "s/employees/employees_comp_4k/g" load_employees_4k.dump sed -i "s/employees/employees_comp_8k/g" load_employees_8k.dump
이제 MySQL 서버에 접속하여 테이블 생성 및 데이터 로드를 진행하겠습니다.
-- DB 접속
mysql -u ....
-- 테스트를 하기 전에 innodb_cmp_per_index_enabled 파라미터를 ON 으로 변경
-- 인덱스 별로 압축 실행 횟수와 성공 횟수가 기록됨
set global innodb_cmp_per_index_enabled=ON;
-- 테이블 생성(Original)
-- 용량 비교를 위해 생성
mysql> CREATE TABLE employees (
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 idx_first_name(first_name),
KEY idx_hire_date(hire_date)
);
-- 압축 테이블 생성(4K)
mysql> CREATE TABLE employees_comp_4k (
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 idx_first_name(first_name),
KEY idx_hire_date(hire_date)
)
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
-- 압축 테이블 생성(8K)
mysql> CREATE TABLE employees_comp_8k (
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 idx_first_name(first_name),
KEY idx_hire_date(hire_date)
)
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
-- 데이터 로딩
mysql> source load_employees.dump;
mysql> source load_employees_4k.dump;
mysql> source load_employees_8k.dump;
위의 테이블 생성 구문은 기존의 테이블 생성 구문에서 INDEX 2개를 더 추가로 설정하였습니다.
압축 효율 및 용량 확인
3개의 테이블을 생성하여 데이터를 적재하였습니다. 압축이 된 결과를 확인해보도록 하겠습니다.
데이터베이스에서 InnoDB의 테이블의 압축 효율성을 확인
mysql> select page_size,compress_ops,compress_ops_ok,compress_time, coalesce((compress_ops-compress_ops_ok)/compress_ops * 100,0) as compression_failure_pct, uncompress_ops,uncompress_time from information_schema.INNODB_CMP; +-----------+--------------+-----------------+---------------+-------------------------+----------------+-----------------+ | page_size | compress_ops | compress_ops_ok | compress_time | compression_failure_pct | uncompress_ops | uncompress_time | +-----------+--------------+-----------------+---------------+-------------------------+----------------+-----------------+ | 1024 | 0 | 0 | 0 | 0.0000 | 0 | 0 | | 2048 | 0 | 0 | 0 | 0.0000 | 0 | 0 | | 4096 | 34724 | 27855 | 6 | 19.7817 | 6849 | 637044439 | | 8192 | 11479 | 9970 | 2 | 13.1457 | 1509 | 141909870 | | 16384 | 0 | 0 | 0 | 0.0000 | 0 | 0 | +-----------+--------------+-----------------+---------------+-------------------------+----------------+-----------------+
테이블에 대해서 데이터베이스 레벨로 page size 별로의 효율을 확인할 수 있습니다.
인덱스별 압축 횟수와 성공 횟수 그리고 압축 실패율을 조회
mysql> 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; +-------------------+----------------+--------------+-----------------+-------------------------+ | table_name | index_name | compress_ops | compress_ops_ok | compression_failure_pct | +-------------------+----------------+--------------+-----------------+-------------------------+ | employees_comp_4k | PRIMARY | 18636 | 13479 | 27.6722 | | employees_comp_4k | idx_first_name | 8321 | 7654 | 8.0159 | | employees_comp_4k | idx_hire_date | 7767 | 6722 | 13.4544 | | employees_comp_8k | PRIMARY | 8092 | 6593 | 18.5245 | | employees_comp_8k | idx_first_name | 1996 | 1996 | 0.0000 | | employees_comp_8k | idx_hire_date | 1391 | 1381 | 0.7189 | +-------------------+----------------+--------------+-----------------+-------------------------+
위의 조회 결과에서 각 컬럼별 의미는 다음과 같습니다.
compress_ops: 압축을 실행
compress_ops_ok: 압축 성공
compression_failure_pct: 정해진 KEY_BLOCK_SIZE 를 초과하여 데이터 페이지를 스플릿(split) 해서 다시 압축을 실행한 비중
Note: RESET 테이블
information_schema 내에 있는 COMPRESS 관련 딕셔너리에서 RESET 이름이 붙은 2개의 딕셔너리 테이블이 있습니다.
INNODB_CMP_RESET / INNODB_CMP_PER_INDEX_RESET
해당 딕셔너리를 조회하면(select) 기존의 누적된 데이터는 초기화가 됩니다.
위의 예제에서 INNODB_CMP_PER_INDEX 조회 결과에서 4k 테이블의 PRIMARY 키는 전체 18636번 압축을 실행하였고 그 중에서 13479번 성공하였습니다.
즉, 5157은 압축 결과 4KB를 초과하여 페이지를 스플릿(split) 해서 다시 압축을 실행했다는 의미입니다.
예제에서는 4k 테이블의 PRIMARY 키 압축 실패율은 27.67% 입니다.
KEY_BLOCK_SIZE 를 8KB 로 설정한 테이블에서도 PRIMARY 키의 압축 실패율도 높게 나온 것을 확인할 수 있습니다.
일반적으로 압축 실패율은 3~5% 미만으로 유지될 수 있도록 KEY_BLOCK_SIZE를 선택하는 것이 좋습니다.
이 결과를 기준으로 확인되는 것은 압축을 적용하면 압축 실패율이 꽤 높아서 InnoDB 버퍼풀에서 디스크로 기록되기 전에 압축하는 과정이 오랜 시간이 걸릴 것이라고 예측할 수 있습니다.
성능에 민감한 서비스라면 테이블은 압축을 적용하지 않는 것이 좋다고 판단할 수 있습니다.
물론 압축 실패율이 높다고 해서 실제 디스크의 데이터 파일 크기가 줄어들지 않는다는 뜻은 아닙니다.
데이터파일 조회
~# ls -alh employees*.ibd -rw-r-----. 1 mysql mysql 20M Nov 5 00:24 employees_comp_4k.ibd -rw-r-----. 1 mysql mysql 21M Nov 5 00:41 employees_comp_8k.ibd -rw-r-----. 1 mysql mysql 40M Nov 4 10:04 employees.ibd
위의 결과는 각 테이블의 데이터파일 과 사이즈입니다 (데이터에 따라서 결과는 달라 집니다)
압축하지 않은 원본테이블이 40MB 이고 압축시 4KB, 8KB 각각 20MB, 21MB 으로 확인되고 있습니다.
주목해야 할 내용은 4KB 와 8KB 압축 결과(사이즈)는 큰 차이가 나지 않는 다는 점입니다.
다만 압축을 진행하면서 KEY_BLOCK_SIZE 를 선택해야 한다면 압축 효율은 상대적으로 약간 좋지 않지만 압축 실패율이 많이 낮은 8KB를 선택하는 것이 좋을 것입니다.
압축 실패율이 높다고 해서 압축을 사용하지 말아야 한다는 것을 의미하는 것은 아닙니다. 예를 들어 INSERT 만 수행되는 로그성 테이블의 경우에는 한 번 Insert 되면 이후 다시는 변경되지 않을 것입니다.
그렇다면 한 번 정도는 압축 시도가 실패하여 스플릿 후 재압축을 한다고 하더라도 전체적으로 데이터 파일의 용량이 큰 폭으로 줄어든다면 큰 손해는 아닐 것입니다.
그 반대로 압축 실패율이 낮은 경우라도 테이블의 데이터가 매우 빈번하게 변경된다면 압축을 고려하지 않는 것이 맞을 수도 있을 것 같습니다.
테이블 압축은 zlib 를 이용해 압축을 하게 되고 압축 알고리즘이 예상외로 많은 CPU 리소스를 사용하게 됩니다.
압축 비활성화
먼저 INFORMATION_SCHEMA.INNODB_SYS_TABLES 를 통해서 테이블의 압축 정보를 확인할 수 있습니다.
-- 압축 하지 않은 일반 테이블 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test_db/tb_1'; +----------+--------------+------+--------+-------+-------------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+--------------+------+--------+-------+-------------+------------+---------------+------------+ | 148 | test_db/tb_1 | 33 | 4 | 144 | Barracuda | Dynamic | 0 | Single | +----------+--------------+------+--------+-------+-------------+------------+---------------+------------+ -- 압축된 테이블 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test_db/employees_comp_4k'; +----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+ | 156 | test_db/employees_comp_4k | 39 | 9 | 137 | Barracuda | Compressed | 4096 | Single | +----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+
압축된 테이블을 다시 비활성화(압축해제) 하려면 아래와 같이 alter 구문으로 테이블을 변경하면 됩니다.
-- 테이블 변경
mysql> ALTER TABLE employees_comp_4k ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
-- ROW_FORMAT 이 변경됨
mysql> SELECT *
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME LIKE 'test_db/employees_comp_4k';
+----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+
| 162 | test_db/employees_comp_4k | 33 | 9 | 150 | Barracuda | Dynamic | 0 | Single |
+----------+---------------------------+------+--------+-------+-------------+------------+---------------+------------+
-- INNODB_CMP_PER_INDEX 에서도 해당 테이블 정보가 삭제됨
mysql> 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;
+-------------------+----------------+--------------+-----------------+-------------------------+
| table_name | index_name | compress_ops | compress_ops_ok | compression_failure_pct |
+-------------------+----------------+--------------+-----------------+-------------------------+
| employees_comp_8k | PRIMARY | 5791 | 5716 | 1.2951 |
| employees_comp_8k | idx_first_name | 1996 | 1996 | 0.0000 |
| employees_comp_8k | idx_hire_date | 1391 | 1381 | 0.7189 |
+-------------------+----------------+--------------+-----------------+-------------------------+
압축된 페이지의 버퍼 풀 적재 및 사용
InnoDB 스토리지 엔진 은 압축된 테이블의 데이터 페이지를 버퍼 풀에 적재하게 되면 압축된 상태와 압축이 해제된 상태 2개 버전을 관리하게 됩니다.
그래서 InnoDB 스토리지 엔진은 디스크에서 읽은 상태 그대로의 데이터 페이지 목록을 관리하는 LRU 리스트와 압축된 페이지들의 압축 해제 버전인 Unzip_LRU 리스트를 별도로 관리하게 됩니다.
MySQL 서버에는 압축된 테이블과 압축되지 않은 테이블이 공존하므로 결국 LRU 리스트는 다음과 같이 압축된 페이지와 압축되지 않은 페이지를 모두 가질 수 있게 됩니다.
• 압축이 적용되지 않은 테이블의 데이터 페이지
• 압축이 적용된 테이블의 압축된 데이터 페이지
그리고 Unzip_LRU 리스트는 압축이 적용된 테이블에서 읽은 데이터 페이지만 관리합니다
(압축이 적용되지 않은 테이블의 데이터 페이지는 관리하지 않음)
물론 Unzip_LRU 리스트에는 압축을 해제한 상태의 데이터 페이지 목록이 관리됩니다.
결국은 InnoDB 스토리지 엔진은 압축된 테이블에 대해서 버퍼 풀의 공간을 이중으로 사용함으로써 메모리를 낭비하는 단점도 있게 됩니다.
LRU 와 Unzip_LRU 리스트에 대한 정보는 SHOW ENGINE INNODB STATUS 의 "BUFFER POOL AND MEMORY" 에서 확인할 수 있습니다.
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2191523840
Dictionary memory allocated 28174762
Buffer pool size 131072
Free buffers 8263
Database pages 123618
Old database pages 45470
Modified db pages 3183
LRU len: 123618, unzip_LRU len: 3693
<!!--- LRU 와 unzip_LRU 의 사용 현황
또 다른 문제점으로는 압축된 페이지에서 데이터를 읽거나 변경하기 위해서는 압축을 해체해야 한다는 것입니다.
압축 및 압축 해제 작업은 CPU 를 상대적으로 많이 소모하는 작업입니다.
이러한 단점을 보완하기 위해서 Unzip_LRU 리스트를 별도로 관리하고 있다가 유입되는 요청 패턴에 따라서 적절하게(Adaptive) 다음과 같은 처리를 수행하게 됩니다.
• InnoDB 버퍼풀의 공간이 필요한 경우에는 LRU 리스트에서 원본 데이터 페이지(압축된 형태)는 유지하고, Unzip_LRU 리스트에서는 압축이 해제된 버전은 제거해서 버퍼 풀의 공간을 확보합니다.
• 압축된 데이터 페이지가 자주 사용되는 경우에는 Unzip_LRU 리스트에 압축 해제된 페이지를 계속 유지하면서 압축 및 압축 해제 작업을 최소화합니다.
• 압축된 데이터 페이자가 사용되지 않아서 LRU 리스트에서 제거되는 경우에는 Unzip_LRU 리스트에서도 함께 제거됩니다.
InnoDB 스토리지 엔진은 버퍼 풀에서 압축 해제된 버전의 데이터 페이지를 적절한 수준으로 유지하기 위해 다음과 같은 어뎁티브 알고리즘을 사용하게 됩니다.
• CPU 사용량이 높은 서버에서는 가능하면 압축과 압축해제를 피하기 위해서 Unzip_LRU 비율을 높여서 유지합니다.
• Disk I/O 사용량이 높은 서버에서는 가능하면 Unzip_LRU 리스트의 비율을 낮춰서 버퍼 풀의 공간을 더 확보하도록 합니다.
테이블 압축 관련 설정
테이블 압축 을 사용할 때 연관된 시스템 변수가 몇 가지가 있습니다.
모두 페이지의 압축 실패율을 낮추기 위한 필요 튜닝 포인트 정보 등을 제공합니다.
innodb_cmp_per_index_enabled
테이블 압축이 사용된 테이블의 모든 인덱스의 압축 성공 및 압축 실행 횟수 를 수집하도록 하는 설정 파라미터입니다.
파라미터가 비활성화(OFF) 되면 테이블 단위의 압축 성공 및 압축 실행 횟수만 수집되게 됩니다.
테이블에 대한 정보는 information_schema.INNODB_CMP 에 기록되며, 인덱스에 대한 정보는 information_schema.INNODB_CMP_PER_INDEX 에 기록되게 됩니다
innodb_compression_level
InnoDB의 테이블 압축은 zlib 압축 알고리즘을 지원합니다. 이때 compression_level 시스템 변수를 이용해 압축률을 설정할 수 있습니다.
0 부터 9 까지 값 중에서 선택할 수 있으며 값이 작을 수록 압축 속도는 빨라지지만 압축율은 낮아질 수 있으며, 반대로 값이 커질수록 속도는 느려질 수 있지만 압축효율은 높아지게 됩니다.
기본값은 6으로 압축속도와 압축률 모두 중간정도로 설정되어 있습니다.
압축 속도는 CPU 자원 소모와 연관되어 있으며 압축 속도가 느리다는 것은 그 만큼 CPU 리소스를 많이 사용한다는 의미가 됩니다.
innodb_compression_failure_threshold_pct 와 innodb_compression_pad_pct_max
테이블 단위로 압축 실패율이 innodb_compression_failure_threshold_pct 설정 값 보다 커지면 압축을 실행하기 전 원본 데이터 페이지의 끝에 의도적으로 일정 크기의 빈 공간을 추가하게 됩니다.
즉, 추가된 빈 공간은 압축률을 높여서 압축 결과가 KEY_BLOCK_SIZE 보다 작아지게 만드는 효과를 내게 됩니다. 여기서 추가되는 빈 공간을 패딩(Padding) 이라고 하며, 이 패딩 공간은 압축 실패율이 높아질수록 계속 증가된 크기를 가지는데, 추가할 수 있는 패딩 공간의 최대 크기는 innodb_compression_pad_pct_max 설정 값 이상을 넘을 수 없습니다.
innodb_compression_pad_pct_max 설정 값에는 % 값을 설정하게 되고, 전체 데이터 페이지 크기 대비 패딩 공간의 비율을 의미하게 됩니다.
innodb_log_compressed_pages
MySQL 서버가 비정상적으로 종료되었다가 다시 시작되는 경우 압축 알고리즘(zlib)의 버전 차이가 있더라도 복구 과정이 실패하지 않도록 InnoDB 스토리지 엔진은 압축된 데이터 페이지를 그대로 리두 로그에 기록하게 됩니다.
이는 압축 알고리즘을 업그레이드할 때 도움이 되지만, 데이터 페이지를 통 채로 리두 로그에 저장하는 것이므로 리두 로그의 증가량에 상당한 영향을 미칠 수도 있게 됩니다.
압축을 적용한 후 리두 로그 용량이 매우 빠르게 증가한다든가 버퍼 풀로부터 더티 페이지가 한 번에 많이 기록되는 패턴으로 바뀌었다면 innodb_log_compressed_pages 파라미터를 OFF 로 설정 후에 추가적인 모니터링을 해보는 것이 좋을 것 같습니다.
innodb_log_compressed_pages 파라미터는 기본값이 ON 이며 특별한 사유가 없을 경우 기본값(ON) 으로 유지하는 것이 좋습니다.
Reference
Reference Book
• Real MySQL 8.0
Reference Link
• mysql.com/innodb-compression-background
• mysql.com/innodb-compression-tuning-monitoring
• mysql.com/general-tablespaces
• oracle.com//whats-new-in-mysql-5-5-468281.pdf
• percona.com/innodb-page-compression
• mysql.com/employees-installation
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
