14.7.3 InnoDB 테이블의 압축 조정

대부분의 경우, InnoDB Data Storage and Compression 에 설명 된 내부적 인 최적화에 의해 압축 된 데이터를 사용하여 시스템이 올바르게 작동합니다. 그러나 압축 효율성은 데이터의 특성에 따라 다르기 때문에 압축 테이블의 성능에 영향을 미치는 결정을 할 수 있습니다.

이 섹션의 지침을 사용하면 이러한 아키텍처 및 구성에서 선택을 할 때 도움이됩니다. 장기간의 테스트를 실시하고 압축 테이블을 프로덕션 환경으로 마이그레이션 할 준비가되면 이러한 선택을 실제 상황에서 행한 경우의 효율성을 검증하는 방법 섹션 14.7.4 "런타임 압축 모니터링 " 을 참조하십시오.

압축을 사용하는 타이밍

일반적으로 압축은 적당한 수의 문자열 컬럼이 포함 데이터 쓰기보다 읽기 빈도가 훨씬 높은 테이블에서 최적으로 작동합니다. 특정 상황에서 압축의 이점을 얻을 수 있는지를 예측하기위한 보장 된 방법이 없으므로 반드시 대표적인 구성에서 수행하는 특정 워크로드 및 데이터 세트를 테스트하십시오. 압축 테이블을 결정할 때 다음 요소를 고려하십시오.

데이터의 특성과 압축

데이터 파일의 크기를 줄일 때 압축 효율성의 결정 요인이되는 것은 데이터 자체의 특성입니다. 압축은 데이터 블록에서 반복되는 바이트 문자열을 식별하여 작동하는 것을 기억하십시오. 완전히 랜덤 화 된 데이터는 최악의 케이스입니다. 많은 경우, 일반적인 데이터는 반복 값이 포함되어 있기 때문에 효율적으로 압축됩니다. CHAR , VARCHAR , TEXT 또는 BLOB 중의 컬럼에 정의되어 있는지에 관계없이 종종 문자열은 효율적으로 압축됩니다. 한편, 일반적으로 대부분의 바이너리 데이터 (정수 또는 부동 소수점)과 이전에 압축 된 데이터 (JPEG 또는 PNG 이미지 등)을 포함하는 테이블은 크게 또는 전혀 효율적으로 압축되지 않을 수 있습니다.

InnoDB 테이블마다 압축을 사용할지 여부를 선택합니다. 테이블 및 모든 인덱스는 같은 (압축 된) 페이지 크기 가 사용됩니다. 모든 테이블 컬럼의 데이터가 포함 된 기본 키 (클러스터) 인덱스 보조 인덱스보다 효율적으로 압축 될 가능성이 있습니다. 긴 라인이 존재하는 경우에 압축을 사용하면 섹션 14.9.3 "DYNAMIC 및 COMPRESSED 행 형식" 에서 설명한 바와 같이, 긴 컬럼 값이 "오프 페이지"에 저장 될 수 있습니다. 이러한 오버 플로우 페이지는 효율적으로 압축 될 가능성이 있습니다. 이러한 고려 사항을 고려하면 많은 응용 프로그램에서 일부 테이블이 다른보다 효율적으로 압축되고 압축 된 테이블의 부분 집합을 포함한 워크로드 만 잘 작동하는 경우도 있습니다.

특정 테이블을 압축할지 여부를 결정하려면 실험을 실시합니다. 비 압축 테이블의 .ibd 파일 의 사본에 LZ77 압축 ( gzip 이나 WinZip 등)이 구현 된 유틸리티를 사용하면 데이터를 압축 할 때의 효율성 어림셈을 얻을 수 있습니다. MySQL에서는 페이지 크기 (기본값은 16K 바이트)에 따라 청크 단위로 데이터가 압축되기 때문에 MySQL에서 압축 된 테이블에서 파일 기반의 압축 도구보다 낮은 압축률을 얻을 수 있다고 예측할 수 있습니다. 페이지 형식은 사용자 데이터 이외에, 압축되지 않은 내부 시스템 데이터도 일부 포함되어 있습니다. 파일 기반의 압축 유틸리티는 더 큰 데이터 청크를 조사 할 수 있기 때문에 MySQL의 각 페이지에서 찾을보다 많은 반복 문자열이 거대한 파일에서 찾을 수 있습니다.

특정 테이블에서 압축을 테스트하는 또 하나의 방법은 몇 가지 데이터를 압축 테이블에서 유사한 (동일한 인덱스를 모두 포함) 압축 테이블로 복사하고 그 결과로 생성되는 .ibd 파일의 크기를 확인하는 것입니다. 예 :

 use test;
 set global innodb_file_per_table = 1;
 set global innodb_file_format = Barracuda;
 set global autocommit = 0;

 - Create an uncompressed table with a million or two rows.
 create table big_table as select * from information_schema.columns;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 commit;
 alter table big_table add id int unsigned not null primary key auto_increment;

 show create table big_table \ G

 select count (id) from big_table;

 - Check how much space is needed for the uncompressed table.
\! ls -l data / test / big_table.ibd

 create table key_block_size_4 like big_table;
 alter table key_block_size_4 key_block_size = 4 row_format = compressed;

 insert into key_block_size_4 select * from big_table;
 commit;

 - Check how much space is needed for a compressed table
 - with particular compression settings.
 \! ls -l data / test / key_block_size_4.ibd

이 실험에서는 다음과 같은 수치가 생성되었습니다. 당연히, 테이블 구조와 데이터에 따라 수치가 크게 다를 수 있습니다.

-rw-rw----  1 cirrus  staff  310378496 Jan  9 13:44 data/test/big_table.ibd
-rw-rw----  1 cirrus  staff  83886080 Jan  9 15:10 data/test/key_block_size_4.ibd

특정 워크로드 에 압축이 효율적인지 여부를 확인하려면 :

데이터베이스 압축 및 응용 프로그램의 압축

응용 프로그램 및 테이블의 어느 데이터를 압축할지 여부를 결정합니다. 동일한 데이터에서 두 종류의 압축을 사용하지 마십시오. 응용 프로그램에서 데이터를 압축하고 그 결과를 압축 테이블에 저장하면 추가 공간이 절약 될 가능성이 크게 낮아지고, 이중 압축해서 간단하게 CPU 사이클이 낭비 될뿐입니다.

데이터베이스에서 압축

이것을 사용하면 MySQL 테이블의 압축이 자동으로되고 모든 컬럼과 인덱스 값에 적용됩니다. LIKE 같은 연산자를 포함 컬럼 계속 테스트 할 인덱스 값이 압축되어있는 경우에도 정렬 작업으로 인덱스를 계속 사용할 수 있습니다. 종종 인덱스가 데이터베이스 전체 크기의 상당한 비율을 차지하기 때문에 압축을 사용하면 스토리지 I / O 또는 프로세서 시간이 크게 절약 될 수 있습니다. 압축 및 압축 해제 작업은 예상 부하를 처리 할 수​​ 있도록 크기 조정 된 강력한 시스템이 될 가능성이 높은 데이터베이스 서버에서 발생합니다.

응용 프로그램에서 압축

텍스트 등의 데이터를 응용 프로그램에서 압축 한 후 데이터베이스에 삽입 할 경우 일부 컬럼 압축되지만 나머지는 압축되지 않은 것으로 효율적으로 압축되지 않은 데이터에 오버 헤드가 절약 될 성이 있습니다. 이 방식은 압축 및 압축 해제를위한 CPU 사이클이 데이터베이스 서버가 아닌 클라이언트 시스템에서 사용되기 때문에 다수의 클라이언트를 포함하는 분산 응용 프로그램이나, 여분의 CPU 사이클을 갖춘 클라이언트 시스템에 적합 있는 경우가 있습니다.

하이브리드 접근

당연히 이러한 방식은 결합 할 수 있습니다. 일부 응용 프로그램은 몇 가지 압축 테이블과 일부 비 압축 테이블을 사용하는 것이 적절할 수 있습니다. 일부 데이터를 외부로 압축하고 (그것을 압축되지 않은 테이블에 저장) 응용 프로그램에서 다른 테이블 (의 일부)를 MySQL로 압축 할 수 있도록하는 것이 가장 좋은 방법 일 수 있습니다. 평소 올바른 결정을 내리기에는 사전 설계 및 실제 테스트가 중요합니다.

워크로드의 특성과 압축

압축 테이블 (및 페이지 크기)를 선택하는 것 외에도 워크로드는 또 다른 성능의 주요 결정 요인이기도합니다. 응용 프로그램이 업데이트가 아니라 읽기로 점유되어있는 경우, 압축 된 데이터 용으로 MySQL에서 개최되는 페이지 당 "변경 로그"를위한 공간이 인덱스 페이지에서 모두 소모 된 후에 재구성 다시 압축해야하는 페이지가 줄어 듭니다. 업데이트는 인덱스없는 컬럼 또는 그들이 들어있는 BLOB 과 우연히 "오프 페이지"에 포함되는 큰 문자열이 주로 변경 될 경우에는 압축 오버 헤드가 허용 될 가능성이 있습니다. 단조롭게 증가하는 기본 키를 사용하는 INSERT 테이블에 단 하나의 변화이며, 보조 인덱스가 거의없는 경우에는 인덱스 페이지를 다시 구성 및 다시 압축 할 필요도 거의 없습니다. MySQL에서는 비 압축 데이터를 변경하는 것으로, "적절하게"압축 된 페이지의 데이터에 "삭제 표시"에서 제거 할 수 있기 때문에 테이블에서 DELETE 작업은 비교적 효율적으로 이루어집니다 .

환경에 따라 데이터의로드 시간이 실시간 검색만큼이나 중요하다 할 수 있습니다. 특히 데이터웨어 하우스 환경에서는 수많은 테이블이 읽기 전용 또는 읽기가 대부분되어있을 수 있습니다. 이러한 경우, 결과적으로 소수의 디스크 읽기 및 스토리지 비용 절감이 중요한 경우를 제외하고 로딩 시간이 길어진다는 점에서 압축 희생을 허용 할 경우와 허용되지 않는 경우가 있습니다 .

본래는 데이터를 압축 및 압축 해제 할 때 CPU 시간을 사용할 수있는 경우 압축이 최적으로 작동합니다. 따라서 워크로드가 CPU 바운드가 아닌 I / O 바운드 인 경우 압축을 사용하여 전반적인 성능을 개선 할 수있는 것을 알 수있을 것입니다. 다양한 압축 구성에서 응용 프로그램의 성능을 테스트 할 때 계획 한 운영 시스템 구성과 같은 플랫폼에서 테스트하십시오.

구성의 특성과 압축

데이터베이스 페이지 의 디스크 읽기 및 디스크에 기록 시스템 성능의 가장 느린 측면이다. 압축은 CPU 시간을 사용하여 데이터를 압축 및 압축 해제하여 I / O 감소하려고 시도하여 프로세서 사이클에 비해 I / O가 비교적 적은 자원 일 때 가장 효율성 이 높아집니다.

많은 경우 이것은 특히 고속의 멀티 코어 CPU가 탑재 된 여러 사용자 환경에서 작동 할 때 적용됩니다. 압축 테이블의 페이지가 메모리에있을 때는, MySQL에서는 종종 페이지의 비 압축 복사 용 버퍼 풀 에서 추가 메모리 (일반적으로 16K 바이트)가 사용됩니다. 적응 형 LRU 알고리즘은 I / O 바운드와 CPU 바운드의 두 방식으로 워크로드가 작동하는지에 관계없이 여겨진다 압축 된 페이지와 비 압축 페이지간에 메모리 사용의 균형을 조정하려고 시도됩니다. 메모리가 매우 제한되어있는 구성보다 버퍼 풀 전용 메모리가 더 탑재 된 구성의 것이 압축 테이블을 사용할 때 제대로 작동하는 경향이 있습니다.

압축 된 페이지 크기 선택

압축 된 페이지 크기에 대한 최적의 설정은 테이블 및 인덱스에 포함 된 데이터 유형 및 분포에 따라 다릅니다. 압축 된 페이지의 크기는 항상 최대 레코드 크기보다 크게하도록하십시오. 그렇지 않으면, Compression of B-Tree Pages 에 언급 한 바와 같이, 작업이 실패 할 수 있습니다.

압축 된 페이지 크기 설정이 너무 크면 일부 영역이 낭비되지만 자주 페이지를 압축 할 필요가 없습니다. 압축 된 페이지 크기가 너무 작 으면 삽입하거나 갱신 할 때에 시간이 걸리는 재 압축이 필요할 수 있고 더 빈번한 B 트리 노드의 분할이 필요할 수도 있습니다. 이렇게하면 데이터 파일이 커져 인덱싱 효율성이 떨어집니다.

일반적으로 압축 된 페이지 크기는 8K 바이트 또는 4K 바이트로 설정됩니다. InnoDB 테이블의 최대 행 크기가 약 8K하면, 일반적으로 KEY_BLOCK_SIZE=8 은 안전한 선택입니다.