[MySQL] MySQL 성능 개선 - 압축

MySQL 압축 기능에 대해 알아본다. 

DB 성능 개선 방법

DB 성능을 개선하는 방법은 여러가지가 있다. 이번에는 인덱스에 이어 압축에 대해 알아보자.

• 인덱스
• 파티셔닝
• 비싼 장비
• 압축
• 기타

아래는 압축 테이블 생성 SQL이다. InnoDB, ROW_FORMAT, KEY_BLOCK_SIZE 와 같은 옵션이 있는데 이 글이 끝나면 이해 될 것이다.

 

CREATE TABLE `log` (
  ...
) ENGINE=InnoDB AUTO_INCREMENT=159282952 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8 COMMENT='로그'

 

복습

https://mystudylab.tistory.com/162

• 디스크는 매우 느리다.
• 논리적 저장 단위.

 

배경지식 1: 압축

압축이란?

물질에 압력을 가하여 부피를 줄이는 것

 

데이터 압축?

특정한 목적을 갖고 원본 데이터 크기를 줄이는 것

1. 저장 공간 절약 (디스크, 메모리 둘 다)
2. 전송 효율 (이미지, 페이로드)
3. 보안?

압축의 3단계

내가 대충 추상화한 압축 및 사용 과정

1. 원본 데이터 압축
2. 데이터 보관 (or 전달)
3. 원본 데이터가 필요하여 압축 해제

Trade-Off

어떤 기술이나 트레이드 오프가 있을 수 있으며, CPU 사용량이 증가할 수 있다는 점을 기억하자.

• 얻는 것
  • 데이터 사이즈 감소
• 잃는 것
  • 압축/해제 과정에서 CPU 사용량 증가

 

배경지식 2: MySQL 아키텍처, 저장방식 (복습)

MySQL 아키텍처

• Client
• MySQL 서버
  • MySQL Engine (두뇌)
    • 쿼리 파싱 및 저처리기에서 구조전 문제 분석
    • 옵티마이저가 실행 계획 세움
    • Handler API 로 스토리지 엔진에 쓰기 또는 읽기 요청
  • Storage Engine (몸통)
    • 데이터 쓰기
    • 데이터 읽기
  • Hardware

위는 MySQL 아키텍처이다.  우리는 압축에 대해 알아볼 것이니  Storage Engine과 Hardware 영역에 집중하면 되며, 아래 동작 설명 보면 이해가 될 것.

SQL 처리 과정

아래는 SQL 처리 과정이다. 커넥션 통해 요청을 받아 Storage Engine 으로 요청 보내기 전까지 MysqlEngine.

1. 불특정 커넥션에서 쿼리 전달.

2. MysQL Engine 에서 쿼리 파싱, 구문 분석 및 실행 계획 세운 뒤 Handler API 통해 Storage Engine 로 요청 보냄.

3. Storage Engine 에서 실제 데이터 읽고, 씀.

 

Storage Engine

MySQL 은 스토리지 엔진으로 무엇을 사용할지 고를 수 있으며, 동시에 여러개를 사용할 수도 있다. 요즘엔 InnoDB가 주로 사용된다.

InnoDB 구조와 동작

 

• 메모리
  • Buffer Pool
    • 가장 핵심적인 부분으로 디스크의 데이터 파일, 인덱스 정보를 메모리에 캐시해 두는 공간. -> I/O 줄여야겠지?
    • 블록(페이지)는 일반적으로 버퍼 풀 거쳐 가져온다. 
      • 압축 데이터도 버퍼 풀에 올라간다.
    • 버퍼링 역할
    • Data Page Buffer
      • LRU 리스트: 디스크 읽은 블록
      • Unzip_LRU 리스트: 압축 해제 블록
    • Change Buffer
      
      • 부하를 줄이기 위해 변경사항을 모았다가 부하 적을 때 일괄적으로 반영함. -> 버퍼링.
      • ACID 보장하며, 체크포인트 발생할 경우 반영
    • Undo Log
  • Log Buffer
    • 로그 일괄 저장
• CPU
  • 체인지버퍼 머지 스레드
  • 데이터 페이지 기록 스레드
  • 로그 기록 스레드
• Disk
  
  • Undo Log
    • 트랜잭션 롤백 대비, 격리 수준
  • Redo Log
    • 변경사항만 기록하는 로그
    • 커밋 상태 추적
  • Doublewrite Buffer Files

 

CREATE TABLE `log` (
  ...
) ENGINE=InnoDB AUTO_INCREMENT=159282952 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8 COMMENT='로그'

 

 

저장 방식과 File-Per-Table

General Tablespace

• 단일 테이블 스페이스, 파일로 저장함
• 삭제 시 하나의 파일로 되어 있어서 공간 회수되지 않음.

File Per Tablespace

• 테이블 마다 개별 관리할 수 있음. 
• 삭제 시 시스템에 공간 자동 반환됨.
• 파일이 분리되어 디스크 경합 줄어듬
• 대부분 File-Per-Table 옵션을 키고 압축 테이블을 적용함 (General Tablespace 제약사항 확인 https://dev.mysql.com/doc/refman/8.0/en/general-tablespaces.html )

 

ROW_FORMAT?

- 테이블 레코드(ROW)를 저장하는 방식, 테이블을 생성할 때 어떤 방식을 사용할 지 지정할 수 있음.

- Barracuda는 Mysql 5.5 부터 지원.

COMPACT	- 기본 ROW_FORMAT REDUNDANT 보다 null 처리가 간결한 방식으로 가장 많이 사용. - REDUNDANT`에 비해 메타데이터 저장 공간이 적으며, 같은 크기의 메모리에 더 많은 데이터를 저장할 수 있도록 설계됨.
REDUNDANT	- 가장 옛날 ROW_FORMAT으로 현재는 거의 사용하지 않음.
DYNAMIC	- COMPACT 개선 버전 - 주로 `BLOB` 및 `TEXT` 데이터를 보다 효율적으로 저장. - 가변 길이를 가질 수 있는 칼럼 값을 row 데이터를 갖고 있는 page(inline page)와 별개로 external page에 관리하는 방식. - 이 포맷은 긴 데이터 조각을 외부 페이지에 저장하여 메인 페이지의 공간 절약. - 테이블을 InnoDB로 지정할 때는 innodb_file_format이 Barracuda 일 때만 사용 가능.
COMPRESSED	- DYNAMIC 기반으로 추가적인 페이지 레벨의 데이터 압축을 제공. 저장 공간을 최소화하면서 디스크 입출력 비용을 절감할 수 있도록 설계. - 디스크 절약할 수 있지만 압축을 풀 때 발생하는 부하가 있을 수 있어 주의해서 사용. - 테이블을 InnoDB로 지정할 때는 innodb_file_format이 Barracuda 일 때만 사용 가능.

 

SHOW VARIABLES LIKE 'innodb_file_format%'

innodb_block_size, KEY_BLOCK_SIZE

• innodb_block_size
  • 데이터 페이지 크기
• KEY_BLOCK_SIZE
  • 압축 후 저장될 페이지의 크기

 

MySQL 압축

종류

• 페이지 압축
  • 페이지 레벨에서 압축하는 기술
  • 제약이 많아 잘 쓰이지 않음
    • 운영체제가 펀치 홀 기능 지원해야함
    • 하드웨어가 펀치 홀 기능 지원해야함
• 테이블 압축
  • 운영체제, 하드웨어 제약 없이 사용 가능
  • 디스크 데이터 파일 크기 줄일 수 있음

 

동작

 

적용

- file-per-table 활성화

- innodb_block_size: 16KB

- KEY_BLOCK_SIZE: 8KB

 

/**
  innodb FILE_PER_TABLE 설정 확인 후 활성화
 */
SHOW VARIABLES LIKE 'innodb_file_per_table';

SET GLOBAL innodb_file_per_table=1;

/**
  innodb 페이지 사이즈 확인
 */
SHOW VARIABLES LIKE 'innodb_page_size';

/**
  innodb 파일 포맷 확인 Barracuda
 */
SHOW VARIABLES LIKE 'innodb_file_format%';
SET GLOBAL innodb_file_format=Barracuda;


-- compression 인덱스 기록
SHOW VARIABLES LIKE 'innodb_cmp_per_index_enabled';
SET GLOBAL innodb_cmp_per_index_enabled=1;

-- 적용 
DROP TABLE invoice8;
CREATE TABLE invoice8 LIKE invoice;
ALTER TABLE invoice8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
INSERT INTO invoice8 SELECT * FROM invoice;

 

결과

공간 측면

• Data_length 보면 차지하는 공간이 절반 정도 줄어든 것을 볼 수 있음
• 실패율은 3~5% 미만 (언제 실패하는 거지?)

SHOW TABLE STATUS LIKE 'invoice';

SHOW TABLE STATUS LIKE 'invoice8';

 

 

쿼리 성능 측면

음~

- 읽기, 쓰기 CPU 사용량?

- 쿼리 속도?

 

장점

• 디스크 공간 확보
• 블록 I/O 감소

단점

• 버퍼 풀 낭비
  • 압축 데이터는 LRU 리스트, Unzip_LRU 둘 다 속한다.
  • 공간 확보 메커니즘 있는데 패스
• CPU 사용량 증가

→ 버퍼 풀이 여유롭지 못하고 CPU 사용량이 높은 서버에 적용하면 성능 하락 원인이 될 수 있음

Adaptive 알고리즘

• CPU 사용량 높은 서버에서는 Unzip_LRU 비율 늘림
• 디스크 IO 사용량 높은 서버는 Unzip_LRU 비율 늘림 

관련 설정 

-- compression 인덱스 기록할지?
SHOW VARIABLES LIKE 'innodb_cmp_per_index_enabled';
SET GLOBAL innodb_cmp_per_index_enabled=1;

-- 압축 레벨, 작을 수록 압축 덜함 -> CPU 적게 사용해 빨라지고 데이터 크기는 늘어남
SHOW VARIABLES LIKE 'innodb_compression_level';

-- 이 값보다 실패율 높으면 압축률 높이기 위한 작업 수행(failure_threshold_pct_max 이하 패딩 집어 넣기)
SHOW VARIABLES LIKE 'failure_threshold_pct';

 

고려

• 페이지 스플릿 고려한 페이지 크기?
• 디스크 IO 사용량이 높은 서버인가? (테이블 쓰기, 읽기 빈번한지)
• 버퍼 풀 사이즈 충분한가?
• 실패율은 3~5% 미만 (언제 실패하는 거지?)

SELECT table_name, index_name, compress_ops, compress_ops_ok,
       (compress_ops - compress_ops_ok) / compress_ops * 100 as failure_pct
FROM information_schema.INNODB_CMP_PER_INDEX;

실제 적용 사례

• CCN? 에 사용 중
• 걷어낼까 생각 중
  • 이유? -> 압축으로 인한 성능 개선 효과가 미미했다.
• 쿼리 성능이 중요한 거라면 인덱스, 파티셔닝, 테이블 파티셔닝, 반정규화를 고려하자.

압축 하지마?

아래의 경우에 공간 효율 측면에서는 유용할 수 있다.

• 보관 목적 데이터를 다루는 경우.
  • 대용량 데이터 쌓는 DW?
• 저장 공간 비용 절약이 필요한 경우. (잘 적용하면 큰 효과 볼 수 있음)
• 메모리 여유가 있는 경우. (버퍼 풀 여유롭게 줄 수 있는 경우)

다시 보기

CREATE TABLE `client_log` (
  ...
) ENGINE=InnoDB AUTO_INCREMENT=159282952 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8 COMMENT='클라이언트 로그'

 

결론

• 쿼리 성능 개선 위해서라면 인덱스, 파티셔닝 먼저 고려하자.
• 저장 공간 줄이는 것이 목적이라면 압축 적용하면 좋다.