[MySQL][InnoDB] 테이블 압축

■ 압축 테이블 소개

프로세서와 캐시 메모리는 디스크 저장 장치보다 속도가 빨라지므로 많은 작업 부하가 디스크 바인딩됩니다. 데이터 압축을 통해 적은 CPU 사용 비용으로 데이터베이스 크기를 줄이고 I/O를 줄이며 처리량을 향상시킬 수 있습니다. 압축은 특히 자주 사용하는 데이터를 메모리에 유지하기에 충분한 RAM이있는 시스템에서 읽기 집약적인(select 쿼리의 범위가 많은) 응용 프로그램에 특히 유용합니다.

 

ROW_FORMAT = COMPRESSED로 작성된 InnoDB 테이블은 구성된 innodb_page_size 값보다 디스크에서 더 작은 페이지 크기를 사용할 수 있습니다. 페이지가 작을수록 디스크에서 읽고 쓰는데 적은 I/O가 필요하며 특히 SSD 장치에 유용합니다.

 

압축된 페이지 크기는 CREATE TABLE 또는 ALTER TABLE KEY_BLOCK_SIZE 매개 변수를 통해 지정됩니다. 페이지 크기가 다르면 시스템 테이블스페이스가 압축 된 테이블을 저장할 수 없으므로 테이블이 파일당 테이블 스페이스 또는 시스템 테이블스페이스가 아닌 일반 테이블스페이스에 배치되어야합니다. 자세한 내용은 14.6.3.2 절“테이블 당 테이블 스페이스”및 14.6.3.3 절“일반 테이블 스페이스”를 참조하십시오.

 

압축 수준은 KEY_BLOCK_SIZE 값에 관계없이 동일합니다. KEY_BLOCK_SIZE에 더 작은 값을 지정하면 점점 더 작은 페이지의 I/O 이점을 얻을 수 있습니다. 그러나 너무 작은 값을 지정하면 각 페이지의 여러 행에 맞도록 데이터 값을 압축 할 수 없을때 페이지를 재구성하기 위한 추가 오버 헤드가 있습니다. 각 인덱스의 키 열 길이에 따라 테이블에 대해 KEY_BLOCK_SIZE가 얼마나 작은 지에 대한 엄격한 제한이 있습니다. 너무 작은 값을 지정하면 CREATE TABLE 또는 ALTER TABLE 문이 실패합니다.

 

버퍼 풀에서 압축 된 데이터는 KEY_BLOCK_SIZE 값을 기반으로 한 페이지 크기와 함께 작은 페이지로 유지됩니다. 열 값을 추출하거나 업데이트하기 위해 MySQL은 압축되지 않은 데이터로 버퍼 풀에 압축되지 않은 페이지를 만듭니다. 버퍼 풀 내에서 압축되지 않은 페이지에 대한 모든 업데이트는 동등한 압축 페이지로 다시 작성됩니다. 공간이 필요할 때 압축되지 않은 페이지가 버퍼 풀에서 제거 된 후 다음 액세스에서 다시 압축 해제되지만 압축 된 페이지와 압축되지 않은 페이지의 추가 데이터를 수용하기 위해 버퍼 풀의 크기를 조정해야 할 수도 있습니다.

 

■ 압축 테이블 만들기

압축 테이블은 파일당 테이블 스페이스 또는 일반 테이블 스페이스에서 작성할 수 있습니다. InnoDB 시스템 테이블 스페이스에는 테이블 압축을 사용할 수 없습니다. 시스템 테이블 스페이스 (공간 0, .ibdata 파일)는 사용자가 만든 테이블을 포함 할 수 있지만 내부 시스템 데이터도 포함하며 압축되지 않습니다. 따라서 압축은 테이블당 파일 또는 일반 테이블 스페이스에 저장된 테이블 (및 인덱스)에만 적용됩니다.

 

▶ 파일별 테이블 스페이스에서 압축 테이블 작성

테이블당 파일 테이블 스페이스에 압축 테이블을 생성하려면 innodb_file_per_table을 활성화하고 (MySQL 5.6.6의 기본값) innodb_file_format을 Barracuda로 설정해야합니다. MySQL 구성 파일 (my.cnf 또는 my.ini)에서 또는 SET 문을 사용하여 이러한 매개 변수를 동적으로 설정할 수 있습니다.

 

innodb_file_per_table 및 innodb_file_format 옵션을 구성한 후 CREATE TABLE 또는 ALTER TABLE 문에서 ROW_FORMAT = COMPRESSED문 또는 KEY_BLOCK_SIZE문중에 하나 또는 둘 다를 지정하여 테이블당 파일 테이블 스페이스에 압축된 테이블을 작성합니다.

 

예를 들어 다음 문장을 사용할 수 있습니다.

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1
 (c1 INT PRIMARY KEY)
 ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

 

▶ 일반 테이블 스페이스에서 압축 테이블 생성

일반 테이블 스페이스에서 압축 된 테이블을 작성하려면 테이블 스페이스가 작성 될 때 지정되는 일반 테이블 스페이스에 대해 FILE_BLOCK_SIZE를 정의해야합니다. FILE_BLOCK_SIZE 값은 innodb_page_size 값과 관련하여 유효한 압축 페이지 크기 여야하며 CREATE TABLE 또는 ALTER TABLE KEY_BLOCK_SIZE 절에 의해 정의 된 압축 테이블의 페이지 크기는 FILE_BLOCK_SIZE / 1024와 같아야합니다. 예를 들어, innodb_page_size = 16384이고 FILE_BLOCK_SIZE = 8192 인 경우 테이블의 KEY_BLOCK_SIZE는 8이어야합니다. 

 

다음 예제는 일반 테이블 스페이스 작성 및 압축 된 테이블 추가를 보여줍니다. 이 예에서는 기본 innodb_page_size가 16K라고 가정합니다. FILE_BLOCK_SIZE가 8192이면 압축 테이블의 KEY_BLOCK_SIZE가 8이어야합니다.

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

 

노트

+ ROW_FORMAT = COMPRESSED를 지정하면 KEY_BLOCK_SIZE를 생략 할 수 있습니다. KEY_BLOCK_SIZE 설정의 기본값은 innodb_page_size 값의 절반입니다.

+ 유효한 KEY_BLOCK_SIZE 값을 지정하면 ROW_FORMAT = COMPRESSED를 생략 할 수 있습니다. 압축이 자동으로 활성화됩니다.

+ KEY_BLOCK_SIZE에 가장 적합한 값을 결정하려면 일반적으로이 절에 대해 다른 값을 사용하여 동일한 테이블의 여러 복사본을 만든 다음 결과 .ibd 파일의 크기를 측정하고 각각의 실제 작업 부하 성능을 확인합니다. 일반 테이블 스페이스의 경우 테이블을 삭제해도 일반 테이블 스페이스 .ibd 파일의 크기가 줄어들지 않으며 운영 체제로 디스크 공간이 리턴되지 않습니다.

+ KEY_BLOCK_SIZE 값은 힌트로 취급됩니다. 필요한 경우 InnoDB에서 다른 크기를 사용할 수 있습니다. 테이블 당 파일 테이블 스페이스의 경우 KEY_BLOCK_SIZE는 innodb_page_size 값보다 작거나 같아야합니다. innodb_page_size 값보다 큰 값을 지정하면 지정된 값이 무시되고 경고가 발행되며 KEY_BLOCK_SIZE가 innodb_page_size 값의 절반으로 설정됩니다. innodb_strict_mode=ON 인 경우 잘못된 KEY_BLOCK_SIZE 값을 지정하면 오류가 반환됩니다. 일반 테이블 스페이스의 경우 유효한 KEY_BLOCK_SIZE 값은 테이블 스페이스의 FILE_BLOCK_SIZE 설정에 따라 다릅니다. 자세한 정보는 14.6.3.3 절“일반 테이블 스페이스”를 참조하십시오.

+ 32KB 및 64KB 페이지 크기는 압축을 지원하지 않습니다. 자세한 내용은 innodb_page_size 설명서를 참조하십시오.

+ InnoDB 데이터 페이지의 기본 압축되지 않은 크기는 16KB입니다. 옵션 값의 조합에 따라 MySQL은 테이블 스페이스 데이터 파일 (.ibd 파일)에 1KB, 2KB, 4KB, 8KB 또는 16KB의 페이지 크기를 사용합니다. 실제 압축 알고리즘은 KEY_BLOCK_SIZE 값의 영향을 받지 않습니다. 이 값은 각 압축 된 청크의 크기를 결정하며, 이는 각 압축 된 페이지에 포장 할 수있는 행 수에 영향을 줍니다.

+ 테이블당 파일 테이블 스페이스에서 압축 된 테이블을 작성할 때 KEY_BLOCK_SIZE를 InnoDB 페이지 크기와 동일하게 설정하면 일반적으로 많은 압축이 발생하지 않습니다. 예를 들어 KEY_BLOCK_SIZE = 16을 설정하면 일반적인 InnoDB 페이지 크기가 16KB이므로 일반적으로 압축이 많이 이루어지지 않습니다. 이 설정은 긴 BLOB, VARCHAR 또는 TEXT 열이 많은 테이블에 여전히 유용 할 수 있습니다. 이러한 값은 압축이 잘되고 14.9.1.5 절“InnoDB 테이블의 압축 작동 방식”에 설명 된 것처럼 오버플로 페이지가 더 적을 수 있기 때문입니다. 일반 테이블 스페이스의 경우 InnoDB 페이지 크기와 동일한 KEY_BLOCK_SIZE 값은 허용되지 않습니다.

+ 테이블의 모든 인덱스 (클러스터형 인덱스 포함)는 CREATE TABLE 또는 ALTER TABLE 문에 지정된 것과 동일한 페이지 크기를 사용하여 압축됩니다. ROW_FORMAT 및 KEY_BLOCK_SIZE와 같은 테이블 속성은 InnoDB 테이블에 대한 CREATE INDEX 구문의 일부가 아니며, 지정된 경우 무시됩니다 (지정된 경우 SHOW CREATE TABLE 문의 출력에 표시됩니다).

 

▶  압축 테이블에 대한 제한 사항

+ 5.1 이전의 MySQL 버전은 압축 테이블을 처리 할 수 ​​없습니다.

+ 압축 된 테이블은 InnoDB 시스템 테이블 스페이스에 저장할 수 없습니다.

+ 일반 테이블 스페이스는 여러 테이블을 포함 할 수 있지만 압축 된 테이블과 압축되지 않은 테이블은 동일한 일반 테이블 스페이스 내에 공존 할 수 없습니다.

+ 압축은 테이블 정의 옵션인 ROW_FORMAT이어도 개별 행이 아닌 전체 테이블 및 모든 관련 인덱스에 적용됩니다.

 

■ InnoDB 테이블의 압축 튜닝

대부분의 경우 InnoDB 데이터 스토리지 및 압축에 대해 설명된 내부 최적화는 시스템이 압축된 데이터와 잘 작동하도록 합니다. 그러나 압축의 효율성은 데이터의 특성에 따라 다르므로 압축 된 테이블의 성능에 영향을 미치는 결정을 내릴 수도 있습니다.

 

+ 압축 할 테이블.

+ 사용할 압축 페이지 크기.

+ 시스템이 데이터 압축 및 압축 해제에 소비하는 시간과 같은 런타임 성능 특성에 따른 버퍼 풀의 크기를 조정할지 여부. 워크로드가 데이터웨어 하우스 (주로 쿼리) 또는 OLTP 시스템 (쿼리 및 DML 혼합)과 유사한 지 여부

+ 시스템이 압축 된 테이블에서 DML 작업을 수행하고 데이터가 분산되는 방식으로 인해 런타임에 비용이 많이 들어간(시스템 자원이 많이 투입된) 압축 실패가 발생하는 경우 추가적인 고급 구성 옵션을 조정할 수 있습니다.

 

▶ 압축 사용시기

일반적으로 압축은 적절한 수의 문자열을 포함하고 기록된 것보다 훨씬 자주 데이터를 읽는 테이블에서 가장 잘 작동합니다. 압축이 특정 상황에 도움이 되는지 여부를 예측할 수 있는 보장된 방법이 없기 때문에 항상 대표적인 구성에서 실행되는 특정 워크로드 및 데이터 세트로 테스트해야 합니다. 압축 할 테이블을 결정할 때 다음 요소를 고려해서 선택해야 합니다.

 

▶ 데이터 특성 및 압축

데이터 파일의 크기를 줄이는 데있어 압축 효율성을 결정하는 핵심 요소는 데이터 자체의 특성입니다. 압축은 데이터 블록에서 반복되는 바이트 문자열을 식별하여 작동합니다. 즉 전체적으로 반복되는 데이터가 적거나 없고, 데이터가 복합적일경우 압축이 굉장히 힘든 경우입니다. 일반적인 데이터는 종종 반복되는 값을 가지므로 효과적으로 압축됩니다. CHAR, VARCHAR, TEXT 또는 BLOB 열에 정의되어 있든 문자열은 데이터 내용에 따라 잘 압축됩니다. 반면, 대부분 이진 데이터 (정수 또는 부동 소수점 숫자) 또는 이전에 압축 된 데이터 (예 : JPEG 또는 PNG 이미지)를 포함하는 테이블은 일반적으로 잘 압축되지 않거나 전혀 압축되지 않을 수 있습니다.

 

각 InnoDB 테이블에 대해 압축을 설정할지 여부를 선택합니다. 테이블과 모든 인덱스는 동일한(압축된) 페이지 크기를 사용합니다. 테이블의 모든 열에 대한 데이터를 포함하는 프리머리키(클러스터) 인덱스가 보조 인덱스보다 더 효과적으로 압축 될 수 있습니다. 긴 행이 있는 경우 압축을 사용하면 DYNAMIC행 형식에 설명 된대로 긴 열 값이 "오프 페이지"로 저장 될 수 있습니다. 이러한 오버플로 페이지는 잘 압축 될 수 있습니다. 이러한 고려 사항을 고려할 때 많은 응용 프로그램의 경우 일부 테이블이 다른 테이블보다 더 효과적으로 압축되므로 작업 부하가 압축된 테이블의 하위 집합으로만 가장 잘 작동한다는 것을 알 수 있습니다.

 

특정 테이블을 압축할지 여부를 결정하려면 테스트를 통해 결정합니다. 압축되지 않은 테이블의 .ibd 파일 복사본에 LZ77 압축 (gzip 또는 WinZip 등)을 구현하는 유틸리티를 사용하면 데이터를 얼마나 효율적으로 압축 할 수 있는지 대략적으로 알 수 있습니다. MySQL은 페이지 크기 (기본적으로 16KB)를 기준으로 청크 단위로 데이터를 압축하기 때문에 파일 기반 압축 도구보다 MySQL 압축 테이블에서 압축률이 낮을 수 있습니다. 사용자 데이터 외에, 페이지 형식에는 압축되지 않은 일부 내부 시스템 데이터가 포함됩니다. 파일 기반 압축 유틸리티는 훨씬 더 많은 양의 데이터를 검사 할 수 있으므로 MySQL이 개별 페이지에서 찾을 수 있는 것보다 큰 파일에서 더 많은 반복 문자열을 찾을 수 있습니다.

 

특정 테이블에서 압축을 테스트하는 다른 방법은 압축되지 않은 테이블의 일부 데이터를 테이블 당 파일 공간 테이블 스페이스의 유사한 압축 테이블 (모두 동일한 인덱스가 있음)로 복사하고 .ibd 파일의 크기 결과를 보는 것입니다.  예를 들면 다음과 같습니다.

mysql> USE test;
mysql> SET GLOBAL innodb_file_per_table=1;
mysql> SET GLOBAL innodb_file_format=Barracuda;
mysql> SET GLOBAL autocommit=0;

--백만 또는 두 개의 행으로 압축되지 않은 테이블을 만듭니다.
mysql> CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> INSERT INTO big_table SELECT * FROM big_table;
mysql> COMMIT;
mysql> ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;

mysql> SHOW CREATE TABLE big_table\G;
mysql> select count(id) from big_table;

--압축되지 않은 테이블에 필요한 공간을 확인합니다.(\!는 OS명령어를 실행할 수 있도록 합니다.)
mysql> \! ls -l data/test/big_table.ibd

mysql> CREATE TABLE key_block_size_4 LIKE big_table;
mysql> ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;

INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;

-- 특정 압축 설정이 된 압축 테이블에 대해 필요한 공간 확인
mysql> \! 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

 

특정 워크로드에 압축이 효율적인지 확인하려면 다음을 수행합니다.

+ 간단한 테스트를 위해 다른 압축 테이블이 없는 MySQL 인스턴스를 사용하고 INFORMATION_SCHEMA.INNODB_CMP 테이블에 대해 쿼리를 실행합니다.

+ 여러 압축 테이블이있는 워크로드와 관련된보다 정교한 테스트를 위해서는 INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX 테이블에 대해 쿼리를 실행합니다. INNODB_CMP_PER_INDEX 테이블의 통계는 수집 비용이 많이 들기 때문에 해당 테이블을 쿼리하기 전에 innodb_cmp_per_index_enabled 구성 옵션을 활성화해야 하며 이러한 테스트를 개발 서버 또는 중요하지 않은 슬레이브 서버로 제한 할 수 있습니다.

+ 테스트중인 압축 테이블에 대해 일반적인 SQL 문을 실행합니다.

+ INFORMATION_SCHEMA.INNODB_CMP 또는 INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX 테이블을 쿼리하고 COMPRESS_OPS와 COMPRESS_OPS_OK를 비교하여 성공적인 압축 작업과 전체 압축 작업의 비율을 조사합니다.

+ 높은 비율의 압축 작업이 성공적으로 완료되면 테이블이 압축에 적합 할 수 있습니다.

+ 압축 실패 비율이 높은 경우 innodb_compression_level, innodb_compression_failure_threshold_pct 및 innodb_compression_pad_pct_max 옵션을 조정하고 추가 테스트를 시도 할 수 있습니다.

 

▶ 데이터베이스 압축과 응용 프로그램 압축

애플리케이션 또는 테이블에서 데이터를 압축할지 여부를 결정합니다. 동일한 데이터에 대해 두 가지 압축 유형을 모두 사용하면 안됩니다. 애플리케이션에서 데이터를 압축하고 결과를 압축 된 테이블에 저장하면 추가 공간 절약이 거의 불가능하며 이중 압축은 CPU주기를 낭비합니다.

 

▶ 데이터베이스에서 압축

압축을 활성화하면 MySQL 테이블 압축이 자동으로 수행되어 모든 열과 인덱스 값에 적용됩니다. LIKE와 같은 연산자로 열을 계속 테스트 할 수 있으며 인덱스 값이 압축 된 경우에도 정렬 작업에서 인덱스를 계속 사용할 수 있습니다. 인덱스는 종종 데이터베이스 전체 크기의 큰 부분이므로 압축하면 스토리지, I/O 또는 프로세서 시간이 크게 절약 될 수 있습니다. 압축 및 압축 해제 조작은 데이터베이스 서버에서 발생하며, 예상되는 로드를 처리 할 수있는 크기의 강력한 시스템 일 수 있습니다.

 

▶ 응용 프로그램에서 압축

응용 프로그램의 텍스트와 같은 데이터를 데이터베이스에 삽입하기 전에 일부 열은 압축하고 다른 열은 압축하지 않아도 압축되지 않는 데이터의 오버 헤드를 줄일 수 있습니다. 이 접근 방식은 데이터베이스 서버가 아닌 클라이언트 시스템에서 압축 및 압축 해제에 CPU주기를 사용합니다. 이는 많은 클라이언트가 있는 분산 응용 프로그램에 적합하거나 클라이언트 기계에 여분의 CPU주기가 있는 경우에 적합합니다.

 

▶ 하이브리드 접근법

물론 이러한 접근 방식을 결합 할 수 있습니다. 일부 응용 프로그램의 경우 일부 압축 테이블과 압축되지 않은 테이블을 사용하는 것이 적절할 수 있습니다. 외부에서 일부 데이터를 압축하고 (압축되지 않은 테이블에 저장) MySQL이 응용 프로그램의 다른 테이블을 압축하도록 하는 것이 가장 좋습니다. 항상 그렇듯이, 초기 설계 및 실제 테스트는 올바른 결정을 내리는 데 가치가 있습니다.

 

▶ 워크로드 특성 및 압축

압축할 테이블 (및 페이지 크기)을 선택하는 것 외에도 작업량은 성능을 결정하는 또 다른 주요 결정 요소입니다. 애플리케이션이 업데이트가 아닌 읽기에 작업에 의해 많이 처리되는 경우, 인덱스 페이지가 MySQL에서 압축 된 데이터를 위해 유지하는 페이지당 "수정 로그"를 위한 공간이 부족해 추후에 더 적은 수의 페이지를 재구성하고 다시 압축해야합니다. 업데이트가 색인화되지 않은 열이나 BLOB 또는 "오프 페이지"로 저장되는 큰 문자열을 포함하는 열을 주로 변경하는 경우 압축 오버 헤드가 발생 될 수 있습니다. 테이블에 대한 유일한 변경 사항이 단순하게 증가하는 기본 키를 사용하는 INSERT이고 보조 인덱스가 거의없는 경우 인덱스 페이지를 재구성하고 다시 압축 할 필요가 거의 없습니다. MySQL은 압축되지 않은 데이터를 수정하여 압축 된 페이지의 “인-플레이스(해당자리 위치)”행을 “삭제-표시”를 하고 삭제할 수 있으므로 테이블에서 DELETE 작업이 상대적으로 효율적입니다.

 

일부 환경에서는 데이터를 로드하는데 걸리는 시간이 런타임 검색만큼 중요 할 수 있습니다. 특히 데이터웨어 하우스 환경에서 많은 테이블이 읽기 전용이거나 대부분이 읽기 작업일 수 있습니다. 이 경우 디스크 읽기 수를 줄이거 나 스토리지 비용을 크게 절약하지 않는 한 증가 된로드 시간 측면에서 압축 가격(자원 사용률)을 지불하는 것이 허용 될 수도 있고 그렇지 않을 수도 있습니다.

 

기본적으로 압축은 CPU 시간을 사용하여 데이터를 압축 및 압축 해제 할 때 가장 잘 작동합니다. 따라서 워크로드가 CPU 바운드가 아니라 I/O 바운드인 경우 압축이 전체 성능을 향상시킬 수 있음을 알 수 있습니다. 다른 압축 구성으로 응용 프로그램 성능을 테스트 할 때는 계획된 프로덕션 시스템 구성과 유사한 플랫폼에서 테스트해야 합니다.

 

▶ 구성 특성 및 압축

디스크에서 데이터베이스 페이지를 읽고 쓰는 것이 시스템 성능의 가장 느린 측면입니다. 압축은 CPU 시간을 사용하여 데이터를 압축 및 해제하여 I/O를 줄이려고 시도하며 I/O가 프로세서주기에 비해 상대적으로 부족한 리소스인 경우 가장 효과적입니다.

 

빠른 멀티 코어 CPU가 있는 다중 사용자 환경에서 실행할때 특히 그렇습니다. 압축된 테이블의 페이지가 메모리에있는 경우, MySQL은 종종 페이지의 압축되지 않은 사본에 대해 버퍼 풀에서 추가 메모리 (일반적으로 16KB)를 사용합니다. 적응형 LRU 알고리즘은 워크로드가 I/O 바운드 또는 CPU 바운드 방식으로 실행되는지 여부를 고려하기 위해 압축된 페이지와 압축되지 않은 페이지간에 메모리 사용의 균형을 유지하려고합니다. 여전히 버퍼 풀 전용 메모리가 더 많은 환경은 메모리가 많이 제한된 환경보다 압축 테이블을 사용할 때 더 잘 실행됩니다.

 

▶ 압축 페이지 크기 선택

압축 된 페이지 크기의 최적 설정은 테이블 및 해당 인덱스에 포함 된 데이터의 유형 및 분포에 따라 다릅니다. 압축 된 페이지 크기는 항상 최대 레코드 크기보다 커야 합니다. 그렇지 않으면 B-트리 페이지 압축에 명시된대로 작업이 실패 할 수 있습니다.

 

압축된 페이지 크기를 너무 크게 설정하면 공간이 낭비되지만 페이지를 자주 압축 할 필요는 없습니다. 압축 된 페이지 크기가 너무 작게 설정되면 삽입 또는 업데이트에 시간이 걸리는 재 압축이 필요할 수 있으며 B- 트리 노드를 더 자주 분할해야하므로 데이터 파일이 커지고 색인화 효율이 떨어집니다.

 

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

 

■ Monitoring InnoDB Table Compression at Runtime

 

전체 응용 프로그램 성능, CPU 및 I/O 사용률 및 디스크 파일 크기는 응용 프로그램에 대한 효과적인 압축의 지표입니다. 이 섹션은 “테이블 압축 조정”의 성능 조정 조언을 바탕으로하며 초기 테스트 중에 나타나지 않을 수있는 문제를 찾는 방법을 보여줍니다.

 

압축된 테이블에 대한 성능 고려 사항을 자세히 살펴 보려면 “압축 정보 스키마 테이블 사용”에 설명 된 INFORMATION SCHEMA 테이블을 사용하여 런타임시 압축 성능을 모니터 할 수 있습니다. 이 테이블은 내부 메모리 사용 및 전체적으로 사용된 압축률을 반영합니다.

 

INNODB_CMP 테이블은 사용중인 각 압축 페이지 크기 (KEY_BLOCK_SIZE)에 대한 압축 활동에 대한 정보를 보고합니다. 이 테이블의 정보는 시스템 전체에 적용됩니다. 데이터베이스의 모든 압축 테이블에 대한 압축 통계를 요약합니다. 이 데이터를 사용하여 다른 압축 테이블에 액세스하지 않을 때 이러한 테이블을 검사하여 테이블 압축 여부를 결정할 수 있습니다. 서버에서 오버 헤드가 상대적으로 낮으므로 프로덕션 서버에서 주기적으로 쿼리하여 압축 기능의 전반적인 효율성을 확인할 수 있습니다.

 

INNODB_CMP_PER_INDEX 테이블은 개별 테이블 및 인덱스의 압축 활동에 대한 정보를 보고합니다. 이 정보는 압축 효율성을 평가하고 한 번에 하나의 테이블 또는 인덱스의 성능 문제를 진단하는데 보다 타겟팅되고 유용합니다. (각 InnoDB 테이블은 클러스터 된 인덱스로 표시되기 때문에 MySQL은 이 컨텍스트에서 테이블과 인덱스를 크게 구별하지 않습니다.) INNODB_CMP_PER_INDEX 테이블은 상당한 오버 헤드를 포함하므로 개발 서버에 더 적합합니다. 서로 다른 워크로드, 데이터 및 압축 설정의 영향, 그외 다른 실수로 이 모니터링 오버 헤드가 발생하지 않도록하려면 INNODB_CMP_PER_INDEX 테이블을 쿼리하기 전에 innodb_cmp_per_index_enabled 구성 옵션을 활성화해야합니다.

 

고려해야 할 주요 통계는 압축 및 압축 해제 조작을 수행하는 데 소요 된 시간 및 시간입니다. MySQL은 수정 후에 압축 된 데이터를 포함하기에 B-트리 노드가 너무 가득 차면 분할하므로 "성공적인"압축 작업 수와 전체 작업 수를 비교해야 합니다. INNODB_CMP 및 INNODB_CMP_PER_INDEX 테이블의 정보와 전체 애플리케이션 성능 및 하드웨어 자원 활용에 따라 하드웨어 구성을 변경하거나 버퍼 풀 크기를 조정하거나 다른 페이지 크기를 선택하거나 압축된 다른 테이블 세트를 선택할 수 있습니다.

 

압축 및 압축 해제에 필요한 CPU 시간이 길면 더 빠른 멀티 코어 CPU로 변경하면 동일한 데이터, 응용 프로그램 작업 부하 및 압축 된 테이블 집합으로 성능을 향상시킬 수 있습니다. 버퍼 풀의 크기를 늘리면 성능에 도움이되므로 더 압축되지 않은 페이지가 메모리에 남아있어 메모리에 존재하는 페이지를 압축된 형식으로 압축 해제 할 필요성이 줄어 듭니다.

 

전체적으로 많은 수의 압축 작업 (응용 프로그램의 INSERT, UPDATE 및 DELETE 작업 수와 데이터베이스 크기와 비교)은 압축된 테이블 중 일부가 효과적인 압축을 위해 너무 많이 업데이트되고 있음을 나타낼 수 있습니다. 그렇다면 더 큰 페이지 크기를 선택하거나, 압축에 알맞은 테이블을 찾아내야 합니다.

 

총 압축 조작 수(COMPRESS_OPS)에서 "성공적인"압축 조작(COMPRESS_OPS_OK)의 비율이 높으면 시스템이 제대로 작동하고있는 것입니다. 비율이 낮 으면 MySQL이 B-트리 노드를 더 자주 재구성, 재 압축 및 분할하는 것입니다. 이 경우 일부 테이블을 압축하지 말고 일부 압축 테이블의 경우 KEY_BLOCK_SIZE를 늘려야 합니다. 응용 프로그램에서 "압축 실패"수가 총계의 1% 또는 2%를 초과하게하는 테이블의 압축을 해제 할 수 있습니다. (데이터로드와 같은 임시 작업 중에는 이러한 실패 비율이 허용 될 수 있습니다).

 

■ InnoDB 테이블에서 압축 작동 방식

 

이 섹션에서는 InnoDB 테이블 압축에 대한 일부 내부구현 세부사항에 대해 설명합니다. 여기에 제공된 정보는 성능 조정에 도움이 될 수 있지만 압축의 기본 사용법에 대해서는 설명하지 않습니다.

 

▶ 압축 알고리즘

일부 운영 체제는 파일 시스템 수준에서 압축을 구현합니다. 파일은 일반적으로 고정 크기 블록으로 나뉘며 가변 크기 블록으로 압축되어 쉽게 조각화됩니다. 블록 내부의 무언가가 수정 될 때마다 전체 블록은 디스크에 기록되기 전에 재 압축됩니다. 이러한 특성으로 인해 이 압축 기술은 업데이트 집약적인 데이터베이스 시스템에서 사용하기에 적합하지 않습니다.

 

MySQL은 LZ77 압축 알고리즘으로 구현된 잘 알려진 zlib 라이브러리를 사용하여 압축을 구현합니다. 이 압축 알고리즘은 CPU 사용률과 데이터 크기 감소 모두에서 충분히 검증되고 또한 강력하며 효율적입니다. 이 알고리즘은 "무손실"이므로 압축되지 않은 원본 데이터는 항상 압축 된 형식으로 재구성 할 수 있습니다. LZ77 압축은 압축할 데이터 내에서 반복되는 데이터 시퀀스를 찾아서 작동합니다. 데이터의 값 패턴에 따라 압축률이 결정되지만 일반적인 사용자 데이터는 대부분 50 % 이상 압축됩니다.

 

응용 프로그램에서 수행하는 압축 또는 일부 다른 데이터베이스 관리 시스템의 압축 기능과 달리 InnoDB 압축은 사용자 데이터와 인덱스 모두에 적용됩니다. 대부분의 경우 인덱스는 전체 데이터베이스 크기의 40-50% 이상을 구성 할 수 있으므로 이 차이가 중요합니다. 데이터 세트에 대해 압축이 제대로 작동하는 경우 InnoDB 데이터 파일 (테이블당 파일 공간 또는 일반 테이블 공간 .ibd 파일)의 크기는 압축되지 않은 크기의 25 % ~ 50 % 또는 더 작을 수 있습니다. 워크로드에 따라 이 작은 데이터베이스는 CPU 사용률 증가 측면에서 적당한 비용으로 I/O가 감소하고 처리량이 증가 할 수 있습니다. innodb_compression_level 구성 옵션을 수정하여 압축 수준과 CPU 오버 헤드 간의 균형을 조정할 수 있습니다.

 

InnoDB 데이터 스토리지 및 압축

InnoDB 테이블의 모든 사용자 데이터는 B-트리 인덱스(클러스터형 인덱스)를 구성하는 페이지에 저장됩니다. 다른 데이터베이스 시스템에서는이 유형의 인덱스를 "인덱스 구성 테이블"이라고합니다. 인덱스 노드의 각 행에는 (사용자 지정 또는 시스템 생성) 기본 키의 값과 테이블의 다른 모든 열이 포함됩니다.

 

InnoDB 테이블의 보조 인덱스는 또한 B-트리이며, 인덱스키와 클러스터형 인덱스의 행에 대한 포인터입니다. 포인터는 실제로 테이블의 기본키 값으로, 인덱스키 및 기본키 이외의 열이 필요한 경우 클러스터된 인덱스에 액세스하는데 사용됩니다. 보조 인덱스 레코드는 항상 단일 B-트리 페이지에 맞아야합니다.

 

B-트리 노드 (클러스터 및 보조 인덱스 모두)의 압축은 다음 섹션에서 설명하는 것처럼 긴 VARCHAR, BLOB 또는 TEXT 열을 저장하는 데 사용되는 오버플로 페이지 압축과는 다르게 처리됩니다.

 

▶ B-트리 페이지 압축

B- 트리 페이지는 자주 업데이트되므로 특별한 처리가 필요합니다. B-트리 노드가 분할되는 횟수를 최소화하고 컨텐츠를 압축 해제하고 다시 압축해야 할 필요성을 최소화하는 것이 중요합니다.

 

MySQL이 사용하는 한가지 기술은 B-트리 노드의 일부 시스템 정보를 압축되지 않은 형태로 유지하여 특정 위치에서 업데이트를 용이하게 하는 것입니다. 예를 들어, 압축 조작없이 행을 삭제 표시하고 삭제할 수 있습니다.

 

또한 MySQL은 인덱스 페이지가 변경될 때 불필요한 압축 해제 및 재 압축을 피하려고 시도합니다. 각 B-트리 페이지 내에서 시스템은 압축되지 않은 "수정 로그"를 유지하여 페이지의 변경 사항을 기록합니다. 전체 페이지를 완전히 재구성하지 않고도 작은 레코드의 업데이트 및 삽입을 이 수정 로그에 쓸 수 있습니다.

 

수정 로그를 위한 공간이 부족하면 InnoDB는 페이지를 압축 해제하고 변경 사항을 적용한 후 페이지를 다시 압축합니다. 재 압축이 실패하면(압축 실패라고 알려진 상황) B-트리 노드가 분할되고 업데이트 또는 삽입이 성공할 때까지 프로세스가 반복됩니다.

 

OLTP 애플리케이션과 같이 쓰기 집약적인 워크로드에서 잦은 압축 실패를 피하기 위해 MySQL은 때때로 페이지에 일부 빈 공간 (패딩)을 예약하므로 수정 로그가 더 빨리 채워지고 페이지가 분할되지 않도록 충분한 공간이있는 동안 페이지가 다시 압축됩니다. 각 페이지에 남아있는 패딩 공간의 양은 시스템이 페이지 분할 빈도를 추적함에 따라 달라집니다. 압축 된 테이블에 대한 빈번한 쓰기를 수행하는 사용중인 서버에서 innodb_compression_failure_threshold_pct 및 innodb_compression_pad_pct_max 구성 옵션을 조정하여 이 메커니즘을 미세 조정할 수 있습니다.

 

일반적으로 MySQL에서는 InnoDB 테이블의 각 B-트리 페이지가 적어도 두 개의 레코드를 수용 할 수 있어야합니다. 압축 테이블의 경우이 요구 사항이 완화되었습니다. B-트리 노드의 리프 페이지(기본 키 또는 보조 인덱스)는 하나의 레코드만 수용하면되지만 해당 레코드는 압축되지 않은 형식으로 페이지별 수정 로그에 맞아야합니다. innodb_strict_mode가 ON이면 MySQL은 CREATE TABLE 또는 CREATE INDEX 동안 최대 행 크기를 확인합니다. 행이 맞지 않으면 다음 오류 메시지가 표시됩니다. ERROR HY000 : 행이 너무 큽니다.

 

이 오류 메시지는 레코드가 너무 큰 인덱스의 이름을 지정하거나 특정 인덱스 페이지의 인덱스 레코드 길이 또는 최대 레코드 크기를 언급하지 않는다.

 

innodb_strict_mode가 OFF 일 때 테이블을 작성하고 후속 INSERT 또는 UPDATE 문이 압축 된 페이지의 크기에 맞지 않는 색인 항목을 작성하려고 시도하면 다음과 같은 에러가 출력되고 작업이 실패합니다.[ERROR 42000 : Row size to large](이 오류 메시지는 레코드가 너무 큰 인덱스의 이름을 지정하거나 특정 인덱스 페이지의 인덱스 레코드 길이 또는 최대 레코드 크기를 언급하지 않습니다.) 이 문제를 해결하려면 ALTER TABLE을 사용하여 테이블을 다시 빌드하고 선택합니다. 압축된 페이지 크기가 더 크면(KEY_BLOCK_SIZE), 컬럼 선행 인덱스를 줄이거 나 ROW_FORMAT = DYNAMIC 또는 ROW_FORMAT = COMPACT를 사용하여 압축을 완전히 비활성화합니다.

 

innodb_strict_mode는 일반 테이블 스페이스에는 적용되지 않으며 압축된 테이블도 지원합니다. 일반 테이블 스페이스에 대한 테이블 스페이스 관리 규칙은 innodb_strict_mode와 독립적으로 엄격하게 적용됩니다.

 

▶ BLOB, VARCHAR 및 TEXT 열 압축

InnoDB 테이블에서 기본 키의 일부가 아닌 BLOB, VARCHAR 및 TEXT 열은 별도로 할당된 오버플로우 페이지에 저장 될 수 있습니다. 이러한 열을 페이지 외부열 이라고합니다. 해당 값은 단일 링크 된 오버 플로우 페이지 목록에 저장됩니다.

 

ROW_FORMAT = DYNAMIC 또는 ROW_FORMAT = COMPRESSED로 작성된 테이블의 경우, 길이 및 전체 행 길이에 따라 BLOB, TEXT 또는 VARCHAR 열의 값이 페이지 외부에 완전히 저장될 수 있습니다. 페이지 외부에 저장된 열의 경우 클러스터형 인덱스 레코드에는 열당 하나씩 오버플로 페이지에 대한 20 바이트 포인터만 포함됩니다. 열이 페이지 외부에 저장되는지 여부는 페이지 크기와 행의 총 크기에 따라 다릅니다. 행이 너무 길어서 클러스터형 인덱스 페이지에 완전히 들어 가지 않으면 MySQL은 로구(Row)가 클러스터형 인덱스 페이지에 맞을 때까지 오프 페이지 스토리지에 가장 긴 열을 선택합니다. 위에서 언급한 것처럼 로우(row)가 압축된 페이지에 적합하지 않으면 오류가 발생합니다.

 

참고

ROW_FORMAT = DYNAMIC 또는 ROW_FORMAT = COMPRESSED로 작성된 테이블의 경우, 40 바이트 이하인 TEXT 및 BLOB 컬럼은 항상 인라인으로 저장됩니다.

 

이전 버전의 MySQL에서 작성된 테이블은 ROW_FORMAT = REDUNDANT 및 ROW_FORMAT = COMPACT만 지원하는 Antelope 파일 형식을 사용합니다. 이러한 형식에서 MySQL은 기본키와 함께 BLOB, VARCHAR 및 TEXT열의 첫 768바이트를 클러스터된 인덱스 레코드에 저장합니다. 768 바이트 접두부 뒤에는 나머지 열 값을 포함하는 오버 플로우 페이지에 대한 20바이트 포인터가 옵니다.

 

테이블이 압축형식인 경우 오버플로우 페이지에 기록된 모든 데이터는 "있는 그대로"압축됩니다. 즉, MySQL은 zlib 압축 알고리즘을 전체 데이터 항목에 적용합니다. 압축된 오버플로 페이지에는 데이터 이외에도 페이지 체크섬과 다음 오버플로 페이지에 대한 링크로 구성된 압축되지 않은 헤더와 트레일러가 포함되어 있습니다. 따라서 텍스트 데이터의 경우와 같이 데이터를 압축 할 수 있는 경우 BLOB, TEXT 또는 VARCHAR 열을 길게하면 스토리지를 상당히 절약 할 수 있습니다. JPEG와 같은 이미지 데이터는 일반적으로 이미 압축되어 있으므로 압축 된 테이블에 저장하면 큰 이점이 없습니다. 이중 압축은 공간을 거의 또는 전혀 절약하기 위해 CPU주기를 낭비 할 수 있습니다.

 

오버플로 페이지의 크기는 다른 페이지와 동일합니다. 페이지 외부에 저장된 열 10 개가 포함 된 행은 열의 총 길이가 8K 바이트인 경우에도 오버플로 페이지 10 개를 차지합니다. 압축되지 않은 테이블에서 10 개의 압축되지 않은 오버 플로우 페이지는 160K 바이트를 차지합니다. 페이지 크기가 8K인 압축 테이블에서는 80K바이트만 차지합니다. 따라서 열값이 긴 테이블에 압축 테이블 형식을 사용하는 것이 종종 더 효율적입니다.

 

테이블 파일당 테이블스페이스의 경우 16K 압축 페이지 크기를 사용하면 BLOB, VARCHAR 또는 TEXT 컬럼에 대한 스토리지 및 I / O 비용을 줄일 수 있습니다. B- 트리 노드 자체가 압축되지 않은 형식인것처럼 많은 페이지를 가져가더라도 이러한 데이터는 압축률이 높으므로 오버플로 페이지가 더 적게 필요합니다. 일반 테이블 스페이스는 16K 압축 페이지 크기 (KEY_BLOCK_SIZE)를 지원하지 않습니다.

 

▶ 압축과 InnoDB 버퍼 풀

압축된 InnoDB 테이블에서 각 압축된 페이지 (1K, 2K, 4K 또는 8K)는 16K 바이트의 압축되지 않은 페이지 (또는 innodb_page_size가 설정된 경우 더 작은 크기)에 해당합니다. 페이지의 데이터에 액세스하기 위해 MySQL은 압축 된 페이지가 아직 버퍼풀에 없는 경우 디스크에서 압축 된 페이지를 읽은 다음 페이지를 원래 형식으로 압축 해제합니다. 이 섹션에서는 InnoDB가 압축 테이블의 페이지와 관련하여 버퍼 풀을 관리하는 방법에 대해 설명합니다.

 

I/O를 최소화하고 페이지를 압축 해제해야 할 필요성을 줄이기 위해 때때로 버퍼 풀에는 압축 및 압축되지 않은 형태의 데이터베이스 페이지가 모두 포함됩니다. 다른 필요한 데이터베이스 페이지를 위한 공간을 확보하기 위해 MySQL은 압축 된 페이지를 메모리에 남겨두고 버퍼 풀에서 압축되지 않은 페이지를 제거 할 수 있습니다. 또는 페이지가 한동안 액세스되지 않은 경우 압축 된 페이지 형식의 디스크가 다른 데이터를 위한 공간을 확보하기 위해 디스크에 기록 될 수 있습니다. 따라서, 주어진 시간에, 버퍼 풀은 페이지의 압축 및 압축되지 않은 형식을 포함하거나 페이지의 압축된 형식만 포함하거나 둘 다 포함하지 않을 수 있습니다.

 

MySQL은 메모리에 보관할 페이지와 LRU (Least-Recently-Used) 목록을 사용하여 어느 페이지를 제거해야하는지 추적하여 핫(자주 액세스하는) 데이터가 메모리에 유지될 수 있도록 하는 경향이 있습니다. 압축 테이블에 액세스 할 때 MySQL은 적응형 LRU 알고리즘을 사용하여 메모리에서 압축 및 압축되지 않은 페이지의 적절한 균형을 달성합니다. 이 적응형 알고리즘은 시스템이 I/O 바인딩 또는 CPU 바인딩 방식으로 실행 중인지 여부에 민감합니다. 목표는 CPU가 사용중일 때 페이지를 압축 해제하는데 너무 많은 처리 시간을 소비하지 않고 CPU에 압축 된 페이지를 압축 해제하는 데 사용할 수있는 여분의 주기(메모리에 있을 수 있도록 함)가 있을때 과도한 I/O를 수행하지 않도록하는 것입니다. 시스템이 I/O 바운드인 경우 알고리즘은 두 디스크가 아닌 압축되지 않은 페이지의 복사본을 제거하여 다른 디스크 페이지가 메모리 상주가 되도록 더 많은 공간을 확보하는 것을 선호합니다. 시스템이 CPU 바운드인 경우 MySQL은 압축된 페이지와 압축되지 않은 페이지를 모두 제거하여 "핫"페이지에 더 많은 메모리를 사용할 수 있으며 압축된 형식의 메모리에서만 데이터를 압축해제를 할 필요성을 줄입니다.

 

▶ 압축과 InnoDB Redo 로그 파일

압축된 페이지가 데이터 파일에 기록되기 전에 MySQL은 페이지의 사본을 리두 로그에 기록합니다 (마지막으로 데이터베이스에 기록 된 이후에 재 압축 된 경우). zlib 라이브러리가 업그레이드되지 않고 압축된 데이터와의 호환성 문제가 발생하는 경우에도 리두 로그를 응급 복구에 사용할 수 있도록 하기 위해 수행됩니다. 따라서 압축을 사용할 때 로그 파일 크기가 약간 커지거나 더 자주 체크 포인트가 필요할 수 있습니다. 로그 파일 크기 또는 체크포인트 빈도의 증가량은 재구성 및 재 압축이 필요한 방식으로, 압축된 페이지가 수정되는 횟수에 따라 다릅니다.

 

압축된 테이블에는 Barracuda 파일 형식이 필요합니다. 테이블당 파일 테이블스페이스에서 압축된 테이블을 작성하려면 innodb_file_per_table을 사용하도록 설정하고 innodb_file_format을 Barracuda로 설정해야합니다. 일반 테이블 스페이스에서 압축된 테이블을 작성할 때 innodb_file_format 설정에 의존하지 않습니다.

 

■ OLTP 워크로드 압축

일반적으로 InnoDB압축 기능은 주로 데이터 웨어하우스 구성과 같은 읽기 전용 또는 대부분의 읽기 작업에 권장되었습니다. 빠르고 상대적으로 작고 비싼 SSD 스토리지 장치의 등장으로 OLTP 워크로드에도 압축이 매력적입니다. 트래픽이 많은 대화식 웹사이트는 INSERT, UPDATE 및 DELETE 작업을 자주 수행하는데 이와 비슷한 환경에서 압축된 테이블을 사용하여 스토리지 요구 사항과 초당 I/O 작업 (IOPS)을 줄일 수 있습니다.

 

MySQL 5.6에 도입된 구성 옵션을 사용하면 쓰기 집약적인 작업을 위한 성능 및 확장성을 강조하여 특정 MySQL 인스턴스에 대한 압축 작동 방식을 조정할 수 있습니다.

+ innodb_compression_level을 사용하면 압축 정도를 높이거나 낮출 수 있습니다. 값이 클수록 압축하는 동안 CPU 오버헤드가 증가하면서 더 많은 데이터를 스토리지 장치에 맞출 수 있습니다. 값이 작을수록 저장 공간이 중요하지 않거나 데이터가 특별히 압축되지 않을 때 CPU 오버 헤드를 줄일 수 있습니다.

+ innodb_compression_failure_threshold_pct는 압축된 테이블을 업데이트하는 동안 압축 실패에 대한 컷오프 지점을 지정합니다. 이 임계값이 전달되면 MySQL은 각각의 새로운 압축 페이지 내에 추가 여유 공간을 남기기 시작하여 innodb_compression_pad_pct_max에 의해 지정된 페이지 크기의 백분율까지 여유 공간의 양을 동적으로 조정합니다.

+ innodb_compression_pad_pct_max를 사용하면 전체 페이지를 다시 압축하지 않고도 각 페이지 내에 예약된 최대 공간을 조정하여 압축된 행에 대한 변경 사항을 기록할 수 있습니다. 값이 클수록 페이지를 다시 압축하지 않고도 더 많은 변경 사항을 기록할 수 있습니다. MySQL은 지정된 압축 작업 백분율이 런타임에 "실패"한 경우에만 압축된 페이지를 분할하는데, 이때 비용이 많이 드는 작업이 필요한 경우에만 각 압축 테이블내의 페이지에 대해 가변적인 여유 공간을 사용합니다.

+ innodb_log_compressed_pages를 사용하면 재 압축된 페이지의 이미지를 리두 로그에 쓰지 못하게 할 수 있습니다. 압축된 데이터를 변경하면 다시 압축이 발생할 수 있습니다. 이 옵션은 복구 중에 다른 버전의 zlib압축 알고리즘을 사용하는 경우 발생할 수 있는 손상을 방지하기 위해 기본적으로 사용됩니다. zlib 버전이 변경되지 않을 것으로 확신되면 innodb_log_compressed_pages를 비활성화하여 압축된 데이터를 수정하는 워크로드에 대한 재실행 로그 생성을 줄이십시오.

 

압축된 데이터를 작업하려면 압축 및 압축되지 않은 페이지의 페이지를 동시에 메모리에 유지해야하기 때문에 OLTP 스타일 워크로드로 압축을 사용할 때 innodb_buffer_pool_size 구성 옵션의 값을 늘려야 합니다.

 

■ SQL 압축 구문 경고와 에러

이 절에서는 테이블당 파일 테이블 스페이스 및 일반 테이블 스페이스와 함께 테이블 압축 기능을 사용할 때 발생할 수 있는 구문 경고 및 오류에 대해 설명합니다.

 

▶ 테이블당 테이블 스페이스에 대한 SQL 압축 구문 경고 및 오류

innodb_strict_mode가 활성화된 경우 (기본값) CREATE TABLE 또는 ALTER TABLE 문에서 ROW_FORMAT = COMPRESSED 또는 KEY_BLOCK_SIZE를 지정하면 innodb_file_per_table이 비활성화되거나 innodb_file_format이 Barracuda가 아닌 Antelope로 설정된 경우 다음 오류가 발생합니다.

ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

 

참고

현재 구성에서 압축된 테이블을 사용할 수 없는 경우 테이블이 작성되지 않습니다.

 

innodb_strict_mode가 비활성화 된 경우, innodb_file_per_table이 비활성화 일때 CREATE TABLE 또는 ALTER TABLE 문에 ROW_FORMAT = COMPRESSED 또는 KEY_BLOCK_SIZE를 지정하면 다음과 같은 경고가 생성됩니다.

 

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.        |
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4.                            |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=DYNAMIC.                          |
+---------+------+---------------------------------------------------------------+

innodb_file_format이 Barracuda가 아닌 Antelope로 설정된 경우 유사한 경고가 발행됩니다.

 

참고

이 메시지는 오류가 아닌 경고일 뿐이며 옵션이 지정되지 않은 것처럼 압축없이 테이블이 작성됩니다.

 

“non-strict”동작을 통해 소스 데이터베이스에 압축된 테이블이 있더라도 mysqldump를 통해 덤프받은 파일을 압축 테이블을 지원하지 않는 데이터베이스로 가져올 수 있습니다. 이 경우 MySQL은 작업을 방해하지 않고 ROW_FORMAT = COMPACT로 테이블을 만듭니다.

 

덤프 파일을 새 데이터베이스로 가져 와서 원래 데이터베이스에 있는 그대로 테이블을 다시 작성하려면 서버가 구성 매개 변수 innodb_file_format 및 innodb_file_per_table에 대한 올바른 설정을 하도록 합니다.

 

KEY_BLOCK_SIZE 속성은 ROW_FORMAT이 COMPRESSED로 지정되거나 생략된 경우에만 허용됩니다. 다른 ROW_FORMAT과 함께 KEY_BLOCK_SIZE를 지정하면 SHOW WARNINGS로 볼 수 있는 경고가 생성됩니다. 그러나 테이블은 압축되지 않습니다. 지정된 KEY_BLOCK_SIZE는 무시됩니다.

 

Level	Code	Message
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.

 

innodb_strict_mode를 사용하여 실행중인 경우, KEY_BLOCK_SIZE와 COMPRESSED 이외의 ROW_FORMAT을 조합하면 경고가 아닌 오류가 발생하고 테이블이 작성되지 않습니다.

 

표 14.6.“ROW_FORMAT 및 KEY_BLOCK_SIZE 옵션”에서는 CREATE TABLE 또는 ALTER TABLE과 함께 사용되는 ROW_FORMAT 및 KEY_BLOCK_SIZE 옵션에 대한 개요를 제공합니다.

 

Table 14.6 ROW_FORMAT과 KEY_BLOCK_SIZE 옵션들

Option	Usage Notes	Description
ROW_FORMAT= REDUNDANT	MySQL 5.0.3 이전에 사용 된 스토리지 형식	ROW_FORMAT = COMPACT보다 덜 효율적입니다. 이전 버전과의 호환성을 위해서입니다.
ROW_FORMAT= COMPACT	Default storage format since MySQL 5.0.3	클러스터 된 인덱스 페이지에 768 바이트의 긴 열 값의 접 두부를 저장하고 나머지 바이트는 오버 플로우 페이지에 저장합니다.
ROW_FORMAT= DYNAMIC	파일당 테이블 스페이스 요구사항 innodb_file _format=Barracuda	적합한 경우 클러스터 된 인덱스 페이지 내에 값을 저장하십시오. 그렇지 않은 경우 오버 플로우 페이지에 대한 20 바이트 포인터 만 저장합니다 (접두사 없음)
ROW_FORMAT= COMPRESSED	파일당 테이블스페이스 요구사항 innodb_file _format=Barracuda	zlib을 사용하여 테이블 및 인덱스 압축
KEY_BLOCK_ SIZE=n	파일당 테이블스페이스 require innodb_file _format=Barracuda	압축 된 페이지 크기를 1, 2, 4, 8 또는 16 킬로바이트로 지정합니다. ROW_FORMAT = COMPRESSED를 의미합니다. 일반 테이블 스페이스의 경우, InnoDB 페이지 크기와 동일한 KEY_BLOCK_SIZE 값은 허용되지 않습니다.

 

표 14.7“InnoDB 엄격(Strict) 모드가 꺼져있을 때 CREATE / ALTER TABLE 경고 및 오류”는 CREATE TABLE 또는 ALTER TABLE 문의 구성 매개 변수 및 옵션 조합에서 발생하는 오류 조건과 옵션이 SHOW  TABLE STATUS 출력에 나타나는 방식을 요약합니다.

 

innodb_strict_mode가 OFF이면 MySQL은 테이블을 만들거나 변경하지만 아래에 표시된대로 특정 설정을 무시합니다. MySQL 오류 로그에서 경고 메시지를 볼 수 있습니다. innodb_strict_mode가 ON이면 이러한 지정된 옵션 조합이 오류를 생성하고 테이블이 작성되거나 변경되지 않습니다. 오류 조건에 대한 자세한 설명을 보려면 SHOW ERRORS 문을 실행합니다. 예 :

 

Table 14.7 CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF

Syntax	Warning or Error Condition	Resulting ROW_FORMAT, as shown in SHOW TABLE STATUS
ROW_FORMAT=REDUNDANT	None	REDUNDANT
ROW_FORMAT=COMPACT	None	COMPACT
ROW_FORMAT = COMPRESSED 또는 ROW_FORMAT = DYNAMIC 또는 KEY_BLOCK_SIZE가 지정됨	innodb_file_format = Barracuda 및 innodb_file_per_table이 모두 사용 가능하지 않으면 테이블 당 파일 테이블 스페이스에 대해 무시됩니다. 일반 테이블 스페이스는 innodb_file_format 및 innodb_file_per_table 설정에 관계없이 모든 행 형식 (일부 제한이 있음)을 지원합니다. 단원 14.6.3.3,“일반 테이블 스페이스”를 참조하십시오.	테이블 당 파일 테이블 스페이스의 기본 행 형식; 일반 테이블 스페이스에 지정된 행 형식
유효하지 않은 KEY_BLOCK_SIZE가 지정되었습니다. (1, 2, 4, 8 또는 16이 아님)	KEY_BLOCK_SIZE가 무시됨	지정된 행 형식 또는 기본 행 형식
ROW_FORMAT = COMPRESSED 및 유효한 KEY_BLOCK_SIZE가 지정되었습니다.	없음 지정된 KEY_BLOCK_SIZE 사용	COMPREAAED
KEY_BLOCK_SIZE는 REDUNDANT, COMPACT 또는 DYNAMIC 행 형식으로 지정됩니다.	KEY_BLOCK_SIZE는 무시됩니다	REDUNDANT, COMPACT or DYNAMIC
ROW_FORMAT은 (는) REDUNDANT, COMPACT, DYNAMIC 또는 COMPRESSED 중 하나가 아닙니다.	MySQL 파서가 인식하면 무시됩니다. 그렇지 않으면 오류가 발생합니다.	기본 행 형식 또는 N / A

 

 

innodb_strict_mode가 ON이면 MySQL은 잘못된 ROW_FORMAT 또는 KEY_BLOCK_SIZE 매개 변수를 거부하고 오류를 발생시킵니다. innodb_strict_mode가 OFF 인 경우 MySQL은 무시 된 잘못된 매개 변수에 대한 오류 대신 경고를 발행합니다. innodb_strict_mode는 기본적으로 ON입니다.

 

innodb_strict_mode가 ON이면 MySQL은 잘못된 ROW_FORMAT 또는 KEY_BLOCK_SIZE 파라미터를 거부합니다. 이전 버전의 MySQL과의 호환성을 위해 기본적으로 엄격 모드는 활성화되어 있지 않습니다. 대신, MySQL은 무시된 유효하지 않은 파라미터에 대해 경고 (오류 아님)를 발행합니다.

 

SHOW TABLE STATUS를 사용하여 선택한 KEY_BLOCK_SIZE를 볼 수 없습니다. SHOW CREATE TABLE 문은 KEY_BLOCK_SIZE를 표시합니다 (테이블 작성시 무시 된 경우에도). MySQL에서는 테이블의 실제 압축 페이지 크기를 표시 할 수 없습니다.

 

▶ 일반 테이블 스페이스에 대한 SQL 압축 구문 경고 및 오류

+ 테이블 스페이스 작성시 일반 테이블 스페이스에 대해 FILE_BLOCK_SIZE가 정의되지 않은 경우 테이블 스페이스에는 압축된 테이블이 포함될 수 없습니다. 압축 된 테이블을 추가하려고하면 아래와 같이 오류가 리턴됩니다.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPRESSED
       KEY_BLOCK_SIZE=8;
ERROR 1478 (HY000): InnoDB: Tablespace `ts1` cannot contain a COMPRESSED table

 

+ KEY_BLOCK_SIZE가 유효하지 않은 테이블을 일반 테이블 스페이스에 추가하려고하면 다음 예와 같이 오류가 반환됩니다.

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED
       KEY_BLOCK_SIZE=4;
ERROR 1478 (HY000): InnoDB: Tablespace `ts2` uses block size 8192 and cannot
contain a table with physical page size 4096

 

일반 테이블 스페이스의 경우 테이블의 KEY_BLOCK_SIZE는 테이블 스페이스의 FILE_BLOCK_SIZE를 1024로 나눈 값과 같아야합니다. 예를 들어, 테이블 스페이스의 FILE_BLOCK_SIZE가 8192 인 경우 테이블의 KEY_BLOCK_SIZE는 8이어야합니다.

 

+ 압축되지 않은 행(Row) 형식의 테이블을 압축된 테이블을 저장하도록 구성된 일반 테이블 스페이스에 추가하려고하면 다음 예제와 같이 오류가 리턴됩니다.

mysql> CREATE TABLESPACE `ts3` ADD DATAFILE 'ts3.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMAT=COMPACT;
ERROR 1478 (HY000): InnoDB: Tablespace `ts3` uses block size 8192 and cannot
contain a table with physical page size 16384

 

innodb_strict_mode는 일반 테이블 스페이스에는 적용되지 않습니다. 일반 테이블 스페이스에 대한 테이블 스페이스 관리 규칙은 innodb_strict_mode와 독립적으로 엄격하게 적용됩니다.

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※