[MySQL][InnoDB] 디스크 I/O와 파일공간 관리

■ InnoDB 디스크 I/O
InnoDB는 I/O가 진행되는 동안 다른 데이터베이스 작업이 진행되도록 허용하면서 많은 스레드를 만들어 I/O 작업을 처리함으로써 비동기 디스크 I/O를 사용합니다. Linux 및 Windows 플랫폼에서 InnoDB는 사용 가능한 OS 및 라이브러리 기능을 사용하여 "기본"비동기 I/O를 수행합니다. 다른 플랫폼에서 InnoDB는 여전히 I/O 스레드를 사용하지만 스레드는 실제로 I/O 요청이 완료 될 때까지 기다릴 수 있습니다. 이 기술을 "시뮬레이션 된"비동기 I/O라고합니다.

▶ Read-Ahead(미리읽기)
InnoDB가 데이터가 곧 필요할 가능성이 높다고 판단하면 미리 읽기 작업을 수행하여 해당 데이터를 버퍼 풀로 가져와 메모리에서 사용할 수 있게합니다. 연속된 데이터에 대해 몇 번의 큰 읽기 요청을하는 것이 여러 개의 작은 분산 요청을 만드는 것보다 더 효율적일 수 있습니다. InnoDB에는 두 가지 미리 읽기 휴리스틱이 있습니다.
+ 순차적 미리 읽기에서 InnoDB는 테이블 스페이스의 세그먼트에 대한 액세스 패턴이 순차적이라는 것을 알게되면 데이터베이스 페이지의 읽기 배치를 I/O 시스템에 미리 게시합니다.
+ 임의 미리 읽기에서 InnoDB가 테이블 스페이스의 일부 영역이 버퍼 풀로 완전히 읽혀지는 것으로 보이면 나머지 읽기를 I/O 시스템에 게시합니다.

▶ Doublewrite Buffer(버퍼 이중 쓰기)
InnoDB는 이중 쓰기 버퍼라는 구조를 포함하는 새로운 파일 플러시 기술을 사용하며 대부분의 경우 기본적으로 활성화됩니다 (innodb_doublewrite = ON). 충돌 또는 정전 후 복구에 안전을 추가하고 fsync() 작업의 필요성을 줄임으로써 대부분의 Unix계열에서 성능을 향상시킵니다.

데이터 파일에 페이지를 쓰기 전에 InnoDB는 먼저 이중쓰기 버퍼라는 연속 테이블 스페이스 영역에 페이지를 씁니다. 쓰기와 이중 쓰기 버퍼 플러시가 완료된 후에만 ​​InnoDB는 페이지를 데이터 파일의 적절한 위치에 씁니다. 페이지 쓰기 도중에 운영 체제, 스토리지 서브 시스템 또는 mysqld 프로세스 충돌이 발생하면 (찢어진 페이지 조건으로 인해) InnoDB는 나중에 복구 중에 버퍼 이중 쓰기에서 올바른 페이지 사본을 찾을 수 있습니다.

시스템 테이블 스페이스 파일 ( "ibdata 파일")이 원자 쓰기를 지원하는 Fusion-io 장치에 있는 경우 이중 쓰기 버퍼링이 자동으로 비활성화되고 Fusion-io 원자 쓰기가 모든 데이터 파일에 사용됩니다. 이중 쓰기 버퍼 설정은 전역이므로, 퓨전이 아닌 하드웨어에 상주하는 데이터 파일에 대해서는 이중 쓰기 버퍼링도 비활성화됩니다. 이 기능은 Fusion-io 하드웨어에서만 지원되며 Linux의 Fusion-io NVMFS에서만 활성화됩니다. 이 기능을 최대한 활용하려면 innodb_flush_method설정에서 O_DIRECT를 권장합니다.

■ 파일 공간 관리
innodb_data_file_path 구성 옵션을 사용하여 구성 파일에 정의한 데이터 파일은 InnoDB 시스템 테이블 스페이스를 형성합니다. 파일은 시스템 테이블 스페이스를 형성하기 위해 논리적으로 연결됩니다. 사용중인 스트라이핑이 없습니다. 시스템 테이블 스페이스내에서 테이블이 할당되는 위치를 정의할 수 없습니다. 새로 작성된 시스템 테이블 스페이스에서 InnoDB는 첫 번째 데이터 파일에서 시작하여 공간을 할당합니다.

시스템 테이블 스페이스에 모든 테이블과 인덱스를 저장하는데 따른 문제를 피하기 위해 새로 작성된 각 테이블을 별도의 테이블 스페이스 파일 (확장자 .ibd)에 저장하는 innodb_file_per_table 구성 옵션 (기본값)을 활성화 할 수 있습니다. 이 방법으로 저장된 테이블의 경우 디스크 파일 내에 조각화가 적고 테이블이 잘 리면 시스템 테이블 스페이스 내에서 InnoDB가 여전히 예약하지 않고 운영 체제로 공간이 반환됩니다.

일반 테이블 스페이스에 테이블을 저장할 수도 있습니다. 일반 테이블 스페이스는 CREATE TABLESPACE 구문을 사용하여 작성된 공유 테이블 스페이스입니다. 이들은 MySQL 데이터 디렉토리 외부에서 작성 될 수 있으며 여러 테이블을 보유 할 수 있으며 모든 행 형식의 테이블을 지원합니다.

▶ 페이지, 익스텐트, 세그먼트 및 테이블 스페이스
각 테이블 스페이스는 데이터베이스 페이지로 구성됩니다. MySQL 인스턴스의 모든 테이블 스페이스는 페이지 크기가 동일합니다. 기본적으로 모든 테이블 스페이스의 페이지 크기는 16KB입니다. MySQL 인스턴스를 작성할 때 innodb_page_size 옵션을 지정하여 페이지 크기를 8KB 또는 4KB로 줄일 수 있습니다. 페이지 크기를 32KB 또는 64KB로 늘릴 수도 있습니다.

페이지는 최대 16KB 크기의 페이지(64개의 연속 16KB 페이지 또는 128개의 8KB 페이지 또는 256개의 4KB 페이지)에 대해 1MB 크기로 그룹화됩니다. 페이지 크기가 32KB인 경우 익스텐트 크기는 2MB입니다. 페이지 크기가 64KB인 경우 익스텐트 크기는 4MB입니다. 테이블 스페이스 내의 "파일"을 InnoDB에서 세그먼트라고합니다. (이러한 세그먼트는 실제로 많은 테이블 스페이스 세그먼트를 포함하는 롤백 세그먼트와 다릅니다.)

세그먼트가 테이블 스페이스 내에서 커지면 InnoDB는 처음 32페이지를 한 번에 하나씩 할당합니다. 그 후 InnoDB는 전체 범위를 세그먼트에 할당하기 시작합니다. InnoDB는 한번에 최대 4개의 익스텐트를 큰 세그먼트에 추가하여 데이터가 양호하게 순차적으로 쌓이는것을 보장 할 수 있습니다.

InnoDB의 각 인덱스에 대해 두 개의 세그먼트가 할당됩니다. 하나는 B-트리의 비 리프 노드 용이고 다른 하나는 리프 노드 용입니다. 디스크에 리프 노드를 인접하게 유지하면 이러한 리프 노드에 실제 테이블 데이터가 포함되므로 순차적 I/O 작업이 향상됩니다.

테이블 스페이스의 일부 페이지에는 다른 페이지의 비트 맵이 포함되어 있으므로 InnoDB 테이블 스페이스의 일부 익스텐트를 전체적으로 세그먼트에 할당 할 수 없고 개별 페이지로만 할당 할 수 있습니다.

SHOW TABLE STATUS문을 실행하여 테이블 스페이스에서 사용 가능한 여유 공간을 요청하면 InnoDB는 테이블 스페이스에서 사용 가능한 범위를 보고합니다. InnoDB는 항상 정리 및 기타 내부 목적을 위해 어느 정도의 범위를 보유합니다. 이러한 예약된 범위는 여유 공간에 포함되지 않습니다.

테이블에서 데이터를 삭제하면 InnoDB는 해당 B-트리 인덱스를 축소합니다. 사용 가능한 공간이 다른 사용자에게 사용 가능한지 여부는 삭제 패턴이 개별 페이지 또는 범위를 테이블 공간에 사용 가능한지 여부에 따라 다릅니다. 테이블을 삭제하거나 테이블에서 모든 행을 삭제하면 다른 사용자에게 공간을 확보 할 수 있지만 삭제된 행은 제거 작업을 통해서만 물리적으로 제거되므로 트랜잭션 롤백 또는 일관된 읽기에 더 이상 필요하지 않으면 자동으로 발생합니다.

▶ 페이지가 테이블 행과 관련되는 방법
최대 행 길이는 4KB, 8KB, 16KB 및 32KB innodb_page_size 설정에 대한 데이터베이스 페이지의 절반보다 약간 작습니다. 예를 들어, 기본 16KB InnoDB 페이지 크기의 경우 최대 행 길이가 8KB보다 약간 작습니다. 64KB 페이지의 경우 최대 행 길이는 16KB보다 약간 작습니다.

행이 최대 행 길이를 초과하지 않으면 모든 행이 페이지 내에 로컬로 저장됩니다. 행이 최대 행 길이를 초과하면 행이 최대 행 길이 한계에 맞을 때까지 외부 오프 페이지 스토리지에 가변 길이 열이 선택됩니다. 가변 길이 열에 대한 외부 오프 페이지 스토리지는 행 형식에 따라 다릅니다.

+ 콤팩트 및 중복 행 형식
외부 오프 페이지 스토리지에 가변 길이 열을 선택하면 InnoDB는 첫 번째 768 바이트를 로컬 행에, 나머지는 오버플로 페이지에 외부로 저장합니다. 이러한 각 열에는 고유한 오버플로 페이지 목록이 있습니다. 768 바이트 접두부에는 열의 실제 길이를 저장하고 나머지 값이 저장되는 오버 플로우 목록을 가리키는 20바이트 값이 동반됩니다.

+ 동적 및 압축 행 형식
외부 오프 페이지 스토리지에 가변 길이 열을 선택하면 InnoDB는 20 바이트 포인터를 행에 로컬로 저장하고 나머지는 오버플로 페이지에 저장합니다. 14.11 절“InnoDB 행 형식”을 참조하십시오.

LONGBLOB 및 LONGTEXT 열은 4GB보다 작아야하며 BLOB 및 TEXT 열을 포함한 총 행 길이는 4GB보다 작아야합니다.

■ InnoDB 체크포인트
로그 파일을 매우 크게 만들면 체크포인트 중 디스크 I/O가 줄어들 수 있습니다. 로그 파일의 전체 크기를 버퍼 풀만큼 크거나 더 크게 설정하는 것이 좋습니다. 과거에는 대용량 로그 파일로 인해 MySQL 5.5부터 충돌 복구에 시간이 오래 걸리지만 충돌 복구 성능 향상으로 충돌 후 빠른 시작으로 큰 로그 파일을 사용할 수 있습니다. 엄밀히 말하면,이 성능 향상은 InnoDB Plugin 1.0.7 이상이 설치된 MySQL 5.1에서 사용할 수 있습니다. MySQL 5.5에서는 기본 InnoDB 스토리지 엔진에서 개선이 가능합니다.

▶ 체크포인트 처리 방법
InnoDB는 퍼지 체크 포인트로 알려진 체크 포인트 메커니즘을 구현합니다. InnoDB는 작은 배치로 버퍼 풀에서 수정 된 데이터베이스 페이지를 플러시합니다. 한 번의 일괄 처리로 버퍼 풀을 플러시 할 필요가 없으므로 검사 점 프로세스 중에 사용자 SQL 문의 처리가 중단됩니다.

응급 복구 중에 InnoDB는 로그 파일에 기록 된 체크 포인트 레이블을 찾습니다. 레이블 이전의 데이터베이스에 대한 모든 수정 사항이 데이터베이스의 디스크 이미지에 있음을 알고 있습니다. 그런 다음 InnoDB는 로그 파일을 검사 점에서 앞으로 스캔하여 기록된 수정 사항을 데이터베이스에 적용합니다.

■ 테이블 조각모음
보조 인덱스에 임의로 삽입하거나 삭제하면 인덱스가 조각화 될 수 있습니다. 조각화는 디스크의 인덱스 페이지의 물리적 순서가 페이지의 레코드의 인덱스 순서에 가깝지 않거나 인덱스에 할당된 64페이지 블록에 사용되지 않은 페이지가 많음을 의미합니다.

조각화의 한 가지 증상은 테이블이 "필수"보다 많은 공간을 차지한다는 것입니다. 그것이 정확히 얼마인지를 결정하기는 어렵습니다. 모든 InnoDB 데이터 및 인덱스는 B-트리에 저장되며 채우기 비율은 50%에서 100%까지 다양합니다. 조각화의 또 다른 증상은 이와 같은 테이블 스캔이“필요한”시간보다 더 많은 시간이 걸린다는 것입니다.

mysql> SELECT COUNT(*) FROM t WHERE non_indexed_column <> 12345;
위의 쿼리에서는 MySQL이 큰 테이블에 대해 가장 느린 쿼리 유형인 전체 테이블 스캔을 수행해야합니다.

인덱스 스캔 속도를 높이기 위해 주기적으로 "널(null) 즉 의미없는" ALTER TABLE 작업을 수행하면 MySQL이 테이블을 다시 작성합니다.
mysql> ALTER TABLE tbl_name ENGINE=INNODB

ALTER TABLE tbl_name FORCE를 사용하여 테이블을 다시 작성하는 "널"변경 조작을 수행 할 수도 있습니다.

ALTER TABLE tbl_name ENGINE = INNODB 및 ALTER TABLE tbl_name FORCE는 모두 온라인 DDL을 사용합니다.

조각 모음 작업을 수행하는 또 다른 방법은 mysqldump를 사용하여 테이블을 텍스트 파일로 덤프하고 테이블을 삭제 한 다음 덤프 파일에서 다시로드하는 것입니다.

인덱스 삽입이 항상 오름차순이고 레코드가 끝에서만 삭제되는 경우 InnoDB 파일 공간 관리 알고리즘은 인덱스에서 조각화가 발생하지 않도록합니다.

■ TRUNCATE TABLE 명령으로 디스크 공간 회수하기
InnoDB 테이블을 자때 운영 체제 디스크 공간을 확보하려면 테이블을 자체 .ibd 파일에 저장해야합니다. 테이블을 자체 .ibd 파일에 저장하려면 테이블을 만들 때 innodb_file_per_table을 활성화해야합니다. 또한 잘리는 테이블과 다른 테이블간에 외래 키 제약 조건이있을 수 없습니다. 그렇지 않으면 TRUNCATE TABLE 작업이 실패합니다. 그러나 동일한 테이블의 두 열 사이에 외래 키 제약 조건이 허용됩니다.

테이블이 잘리면 테이블이 삭제되어 새.ibd 파일로 다시 만들어지고 사용 가능한 공간이 운영 체제로 반환됩니다. 이는 InnoDB 시스템 테이블 스페이스 (innodb_file_per_table = OFF 일 때 생성 된 테이블)에 저장된 InnoDB 테이블과 공유 일반 테이블 스페이스에 저장된 테이블을 자르는 것과 대조적입니다. 여기서 테이블이 잘린후에는 InnoDB만 사용 가능한 공간을 사용할 수 있습니다.

또한 테이블을 자르고 디스크 공간을 운영 체제로 반환하는 기능은 실제 백업이 더 작을 수 있음을 의미합니다. 시스템 테이블 스페이스 (innodb_file_per_table = OFF 일 때 작성된 테이블) 또는 일반 테이블 스페이스에 저장된 잘림 테이블은 테이블 스페이스에 사용되지 않은 공간 블록을 남깁니다.