10.5.8 Optimizing InnoDB Disk I/O

10.5.8 Optimizing InnoDB Disk I/O

데이터베이스 설계 모범 사례와 SQL 연산 튜닝 기법을 따랐음에도 불구하고, 디스크 I/O 활동이 많아서 데이터베이스가 여전히 느리다면, 다음과 같은 디스크 I/O 최적화를 고려하십시오. Unix의 top 도구나 Windows Task Manager에서 해당 워크로드의 CPU 사용률이 70% 미만으로 나타난다면, 해당 워크로드는 디스크 바운드일 가능성이 큽니다.

• Increase buffer pool size

테이블 데이터가 InnoDB 버퍼 풀에 캐시되어 있으면, 쿼리가 디스크 I/O 없이 반복적으로 해당 데이터에 접근할 수 있습니다. 버퍼 풀의 크기는 innodb_buffer_pool_size 옵션으로 지정합니다. 이 메모리 영역은 매우 중요하므로, innodb_buffer_pool_size를 시스템 메모리의 50~75%로 설정하는 것이 일반적으로 권장됩니다. 자세한 내용은 Section 10.12.3.1, “How MySQL Uses Memory”를 참고하십시오.

• Adjust the flush method

일부 GNU/Linux 및 Unix 버전에서는 Unix fsync() 호출 및 유사한 방법을 사용하여 파일을 디스크로 플러시하는 작업이 예상외로 느릴 수 있습니다. 데이터베이스 쓰기 성능에 문제가 있다면, innodb_flush_method 파라미터를 O_DSYNC로 설정하여 벤치마크를 수행해 보십시오.

• Configure a threshold for operating system flushes

기본적으로 InnoDB가 새로운 로그 파일이나 테이블스페이스 파일과 같은 새로운 데이터 파일을 생성할 때, 해당 파일은 디스크로 플러시되기 전에 운영체제 캐시에 전체가 기록되며, 이로 인해 한 번에 많은 디스크 쓰기 활동이 발생할 수 있습니다. 운영체제 캐시에서 디스크로 더 작고 주기적인 플러시를 강제하려면, innodb_fsync_threshold 변수를 사용하여 바이트 단위의 임계값을 정의할 수 있습니다. 바이트 임계값에 도달하면, 운영체제 캐시의 내용이 디스크로 플러시됩니다. 기본값 0은, 파일이 캐시에 완전히 기록된 후에만 디스크로 데이터를 플러시하는 기본 동작을 강제합니다.

더 작고 주기적인 플러시를 강제하기 위한 임계값을 지정하는 것은 여러 MySQL 인스턴스가 동일한 스토리지 장치를 사용하는 경우에 유리할 수 있습니다. 예를 들어, 새로운 MySQL 인스턴스와 그에 연관된 데이터 파일을 생성하면, 디스크 쓰기 활동이 급격히 증가하여 동일한 스토리지 장치를 사용하는 다른 MySQL 인스턴스의 성능을 저해할 수 있습니다. 임계값을 설정하면 이러한 쓰기 활동 급증을 방지하는 데 도움이 됩니다.

• Use fdatasync() instead of fsync()

fdatasync() 시스템 호출을 지원하는 플랫폼에서는, innodb_use_fdatasync 변수를 사용하여 운영체제 플러시 시 fsync() 대신 fdatasync()를 사용할 수 있습니다. fdatasync() 시스템 호출은 이후 데이터 검색에 필요하지 않은 한 파일 메타데이터 변경 사항을 플러시하지 않으므로, 잠재적인 성능 이점을 제공합니다.

fsync, O_DSYNC, O_DIRECT와 같은 innodb_flush_method 설정의 일부는 fsync() 시스템 호출을 사용합니다. innodb_use_fdatasync 변수는 이러한 설정을 사용할 때 적용됩니다.

• Use a noop or deadline I/O scheduler with native AIO on Linux

InnoDB는 Linux에서 데이터 파일 페이지에 대한 리드 어헤드와 쓰기 요청을 수행하기 위해 비동기 I/O 서브시스템(native AIO)을 사용합니다. 이 동작은 기본적으로 활성화되어 있는 innodb_use_native_aio 구성 옵션에 의해 제어됩니다. native AIO를 사용하는 경우, I/O 스케줄러의 유형이 I/O 성능에 더 큰 영향을 미칩니다. 일반적으로 noop 및 deadline I/O 스케줄러가 권장됩니다. 특정 워크로드와 환경에서 어떤 I/O 스케줄러가 최상의 결과를 제공하는지 알아보기 위해 벤치마크를 수행하십시오. 자세한 내용은 Section 17.8.6, “Using Asynchronous I/O on Linux”를 참고하십시오.

• Use direct I/O on Solaris 10 for x86_64 architecture

Solaris 10의 x86_64 아키텍처(AMD Opteron)에서 InnoDB 스토리지 엔진을 사용할 때, InnoDB 관련 파일에 direct I/O를 사용하여 InnoDB 성능 저하를 피하십시오. InnoDB 관련 파일을 저장하는 전체 UFS 파일 시스템에 direct I/O를 사용하려면, forcedirectio 옵션을 사용하여 해당 파일 시스템을 마운트하십시오. mount_ufs(1M)를 참고하십시오. (Solaris 10/x86_64의 기본값은 이 옵션을 사용하지 않는 것입니다.) 전체 파일 시스템이 아닌 InnoDB 파일 연산에만 direct I/O를 적용하려면, innodb_flush_method = O_DIRECT 로 설정하십시오. 이 설정에서 InnoDB는 데이터 파일에 대한 I/O에 대해 fcntl() 대신 directio()를 호출합니다(로그 파일 I/O에는 적용되지 않습니다).

• Use raw storage for data and log files with Solaris 2.6 or later

어떤 플랫폼(sparc/x86/x64/amd64)이든 Solaris 2.6 이상 버전에서 큰 innodb_buffer_pool_size 값과 함께 InnoDB 스토리지 엔진을 사용할 때, 로(raw) 장치 또는 별도의 direct I/O UFS 파일 시스템에 InnoDB 데이터 파일과 로그 파일을 두고, 앞에서 설명한 것처럼 forcedirectio 마운트 옵션을 사용하여 벤치마크를 수행하십시오. (로그 파일에 direct I/O를 사용하려면 innodb_flush_method를 설정하는 것이 아니라 마운트 옵션을 사용하는 것이 필요합니다.) Veritas 파일 시스템 VxFS 사용자라면 convosync=direct 마운트 옵션을 사용해야 합니다.

MyISAM 테이블용 파일과 같은 다른 MySQL 데이터 파일은 direct I/O 파일 시스템에 두지 마십시오. 실행 파일이나 라이브러리는 반드시 direct I/O 파일 시스템에 두지 말아야 합니다.

• Use additional storage devices

추가 스토리지 장치는 RAID 구성을 위해 사용할 수 있습니다. 관련 정보는 Section 10.12.1, “Optimizing Disk I/O”를 참고하십시오.

또는, InnoDB 테이블스페이스 데이터 파일과 로그 파일을 서로 다른 물리 디스크에 둘 수도 있습니다. 자세한 내용은 다음 섹션을 참조하십시오:

• Section 17.8.1, “InnoDB Startup Configuration”

• Section 17.6.1.2, “Creating Tables Externally”

• Creating a General Tablespace

• Section 17.6.1.4, “Moving or Copying InnoDB Tables”

• Consider non-rotational storage

비회전(non-rotational) 스토리지는 일반적으로 랜덤 I/O 연산에서 더 나은 성능을 제공하며, 회전 스토리지는 순차 I/O 연산에서 더 나은 성능을 제공합니다. 데이터 파일과 로그 파일을 회전 및 비회전 스토리지 장치에 분산 배치할 때, 각 파일에서 주로 수행되는 I/O 연산 유형을 고려하십시오.

랜덤 I/O 위주의 파일에는 일반적으로 file-per-table 및 general tablespace 데이터 파일, undo tablespace 파일, temporary tablespace 파일 등이 포함됩니다. 순차 I/O 위주의 파일에는 InnoDB system tablespace 파일, doublewrite 파일, 그리고 binary log 파일 및 redo log 파일과 같은 로그 파일이 포함됩니다.

비회전 스토리지를 사용할 때 다음 구성 옵션 설정을 검토하십시오:

• innodb_checksum_algorithm

  crc32 옵션은 더 빠른 체크섬 알고리즘을 사용하며, 빠른 스토리지 시스템에서 권장됩니다.

• innodb_flush_neighbors

  회전 스토리지 장치에 대한 I/O를 최적화합니다. 비회전 스토리지 또는 회전/비회전 혼합 스토리지에서는 비활성화하십시오. 기본적으로 비활성화되어 있습니다.

• innodb_idle_flush_pct

  유휴 기간 동안 페이지 플러시에 제한을 두어, 비회전 스토리지 장치의 수명을 연장하는 데 도움이 될 수 있도록 합니다.

• innodb_io_capacity

  기본 설정값 10000은 일반적으로 충분합니다.

• innodb_io_capacity_max

  기본값인 (2 * innodb_io_capacity)는 대부분의 워크로드를 위한 것입니다.

• innodb_log_compressed_pages

  redo 로그가 비회전 스토리지에 있는 경우, 로깅을 줄이기 위해 이 옵션을 비활성화하는 것을 고려하십시오. Disable logging of compressed pages를 참조하십시오.

• innodb_redo_log_capacity

  redo 로그가 비회전 스토리지에 있는 경우, 캐싱 및 쓰기 결합을 최대화하도록 이 옵션을 구성하십시오.

• innodb_page_size

  디스크의 내부 섹터 크기와 일치하는 페이지 크기 사용을 고려하십시오. 초기 세대 SSD 장치는 종종 4KB 섹터 크기를 가지며, 일부 최신 장치는 16KB 섹터 크기를 가집니다. 기본 InnoDB 페이지 크기는 16KB입니다. 페이지 크기를 스토리지 장치 블록 크기에 가깝게 유지하면, 변경되지 않은 데이터를 디스크에 다시 쓰는 양을 최소화할 수 있습니다.

• binlog_row_image

  바이너리 로그가 비회전 스토리지에 있고 모든 테이블에 프라이머리 키가 있는 경우, 로깅을 줄이기 위해 이 옵션을 minimal로 설정하는 것을 고려하십시오.

운영체제에서 TRIM 지원이 활성화되어 있는지 확인하십시오. 일반적으로 기본적으로 활성화되어 있습니다.

• Increase I/O capacity to avoid backlogs

InnoDB checkpoint 연산으로 인해 주기적으로 처리량이 떨어지는 경우, innodb_io_capacity 구성 옵션의 값을 높이는 것을 고려하십시오. 더 높은 값은 더 빈번한 flushing을 유발하여, 처리량이 떨어지는 원인이 되는 작업 백로그를 피할 수 있게 합니다.

• Lower I/O capacity if flushing does not fall behind

시스템이 InnoDB flushing 연산에서 뒤처지지 않는다면, innodb_io_capacity 구성 옵션의 값을 낮추는 것을 고려하십시오. 일반적으로 이 옵션 값은 가능한 한 낮게 유지하되, 앞의 항목에서 언급한 것처럼 주기적인 처리량 하락을 초래하지 않을 정도로만 낮춥니다. 옵션 값을 낮출 수 있는 전형적인 시나리오에서는, SHOW ENGINE INNODB STATUS 출력에서 다음과 같은 조합을 볼 수 있습니다:

• History list length가 몇 thousand 이하로 낮다.

• Insert buffer merge가 rows inserted 수와 거의 비슷하다.

• 버퍼 풀의 modified 페이지 수가 항상 버퍼 풀의 innodb_max_dirty_pages_pct 아래 수준이다. (서버가 벌크 insert를 수행하지 않을 때 측정해야 합니다. 벌크 insert 동안에는 modified 페이지 비율이 상당히 증가하는 것이 정상입니다.)

• Log sequence number - Last checkpoint가 InnoDB log files 전체 크기의 7/8 미만, 이상적으로는 6/8 미만이다.

• Store system tablespace files on Fusion-io devices

doublewrite 버퍼 관련 I/O 최적화를 활용하기 위해, doublewrite 스토리지 영역을 포함하는 파일을 atomic write를 지원하는 Fusion-io 장치에 저장할 수 있습니다. (doublewrite 버퍼 스토리지 영역은 doublewrite 파일에 존재합니다. Section 17.6.4, “Doublewrite Buffer”를 참고하십시오.) doublewrite 스토리지 영역 파일이 atomic write를 지원하는 Fusion-io 장치에 배치되면, doublewrite 버퍼는 자동으로 비활성화되고, 모든 데이터 파일에 대해 Fusion-io atomic write가 사용됩니다. 이 기능은 Fusion-io 하드웨어에서만 지원되며, Linux에서 Fusion-io NVMFS에 대해서만 활성화됩니다. 이 기능을 최대한 활용하려면, innodb_flush_method 설정을 O_DIRECT로 사용하는 것이 권장됩니다.

참고

doublewrite 버퍼 설정은 글로벌이므로, Fusion-io 하드웨어에 존재하지 않는 데이터 파일에 대해서도 doublewrite 버퍼가 비활성화됩니다.

• Disable logging of compressed pages

InnoDB 테이블 compression 기능을 사용할 때, 압축 데이터에 변경이 발생하면 재압축된 pages의 이미지가 redo log에 기록됩니다. 이 동작은 innodb_log_compressed_pages 에 의해 제어되며, 복구 중에 다른 버전의 zlib compression 알고리즘이 사용될 경우 발생할 수 있는 손상을 방지하기 위해 기본적으로 활성화되어 있습니다. zlib 버전이 변경되지 않을 것임을 확신할 수 있다면, innodb_log_compressed_pages 를 비활성화하여 압축 데이터를 수정하는 워크로드에 대해 redo 로그 생성량을 줄이십시오.