このページは機械翻訳したものです。

8.5.8 InnoDB ディスク I/O の最適化

SQL 操作のためのデータベース設計およびチューニング方法のベストプラクティスに従っているが、ディスク I/O アクティビティが多いためにデータベースの速度がまだ遅い場合は、これらのディスク I/O 最適化を検討してください。 Unix top ツールまたは Windows タスクマネージャーに、ワークロードの CPU 使用率が 70% 未満であることが示されている場合、ワークロードはディスクに依存している可能性があります。

• バッファプールサイズの増加

  テーブルデータが InnoDB バッファプールにキャッシュされると、ディスク I/O を必要とせずにクエリーによって繰り返しアクセスできます。 バッファープールサイズは、innodb_buffer_pool_size オプションで指定します。 このメモリー領域は、通常、innodb_buffer_pool_size を 50 から 75% のシステムメモリーに構成することをお薦めします。 詳細は、セクション8.12.3.1「MySQL のメモリーの使用方法」 を参照してください。

• フラッシュ方法の調整

  GNU/Linux および Unix の一部のバージョンでは、Unix fsync() 呼び出し (これは InnoDB がデフォルトで使用します) および類似のメソッドによるファイルのディスクへのフラッシュが驚くほど低速です。 データベースの書き込みパフォーマンスが問題である場合、innodb_flush_method パラメータを O_DSYNC に設定してベンチマークを実行します。

• fsync しきい値の構成

  デフォルトでは、InnoDB が新しいログファイルやテーブルスペースファイルなどの新しいデータファイルを作成すると、ファイルはディスクにフラッシュされる前にオペレーティングシステムキャッシュに完全に書き込まれるため、大量のディスク書込みアクティビティが一度に発生する可能性があります。 オペレーティングシステムキャッシュから定期的にデータを強制的に小さいフラッシュするには、innodb_fsync_threshold 変数を使用してしきい値をバイト単位で定義します。 バイトしきい値に達すると、オペレーティングシステムキャッシュの内容がディスクにフラッシュされます。 デフォルト値の 0 では、デフォルトの動作が強制されます。つまり、ファイルがキャッシュに完全に書き込まれた後にのみ、データがディスクにフラッシュされます。

  複数の MySQL インスタンスが同じストレージデバイスを使用している場合は、より小さい定期的なフラッシュを強制的に実行するためのしきい値を指定すると有益です。 たとえば、新しい MySQL インスタンスとそれに関連付けられたデータファイルを作成すると、ディスク書込みアクティビティが大きくなり、同じストレージデバイスを使用する他の MySQL インスタンスのパフォーマンスが低下する可能性があります。 しきい値を構成すると、書込みアクティビティでのこのようなサージの回避に役立ちます。

• Linux でネイティブ AIO を使用した noop または deadline I/O スケジューラの使用

  InnoDB は、Linux で非同期 I/O サブシステム (ネイティブ AIO) を使用して、データファイルページの先読みおよび書込みリクエストを実行します。 この動作は、デフォルトで有効になっている innodb_use_native_aio 構成オプションによって制御されます。 ネイティブ AIO では、I/O スケジューラのタイプが I/O のパフォーマンスに大きく影響します。 通常、noop および deadline I/O スケジューラをお薦めします。 ベンチマークを実行して、ワークロードおよび環境に最適な結果を提供する I/O スケジューラを決定します。 詳細は、セクション15.8.6「Linux での非同期 I/O の使用」を参照してください。

• x86_64 アーキテクチャーに Solaris 10 上の直接 I/O を使用

  Solaris 10 for x86_64 Architecture (AMD Opteron) で InnoDB ストレージエンジンを使用する場合は、InnoDB 関連ファイルに直接 I/O を使用して、InnoDB パフォーマンスの低下を回避します。 InnoDB 関連ファイルを格納するために使用される UFS ファイルシステム全体にダイレクト I/O を使用するには、それを forcedirectio オプションでマウントします。mount_ufs(1M) を参照してください。 (Solaris 10/x86_64 のデフォルトではこのオプションを使用しません)。 ダイレクト I/O をファイルシステム全体ではなく、InnoDB ファイル操作にのみ適用するには、innodb_flush_method = O_DIRECT を設定します。 この設定では、InnoDB はデータファイルへの I/O (ログファイルへの I/O ではなく) に fcntl() ではなく、directio() を呼び出します。

• Solaris 2.6 以上でのデータおよびログファイルの RAW 記憶域の使用

  任意のリリースの Solaris 2.6 および任意のプラットフォーム (sparc/x86/x64/amd64) で、innodb_buffer_pool_size 値が大きい InnoDB ストレージエンジンを使用する場合は、前に説明した forcedirectio マウントオプションを使用して、raw デバイスまたは別の直接 I/O UFS ファイルシステム上の InnoDB データファイルおよびログファイルでベンチマークを実行します。 (ログファイルのダイレクト I/O が必要な場合、innodb_flush_method を設定する代わりに、マウントオプションを使用する必要があります。) Veritas ファイルシステム VxFS のユーザーは、convosync=direct マウントオプションを使用してください。

  ダイレクト I/O ファイルシステムに、MyISAM テーブルのファイルなど、ほかの MySQL データファイルを配置しないでください。 実行ファイルやライブラリは、ディレクト I/O ファイルシステムに配置しないでください。

• 追加のストレージデバイスの使用

  RAID 構成の設定には、追加のストレージデバイスを使用できます。 関連情報については、セクション8.12.1「ディスク I/O の最適化」を参照してください。

  または、InnoDB テーブルスペースデータファイルおよびログファイルを別の物理ディスクに配置できます。 詳細は、次のセクションを参照してください:

  • セクション15.8.1「InnoDB の起動構成」

  • セクション15.6.1.2「外部でのテーブルの作成」

  • 一般的なテーブルスペースの作成

  • セクション15.6.1.4「InnoDB テーブルの移動またはコピー」

• 非ローテーション記憶域の検討

  通常、非ローテーション記憶域を使用すると、ランダムな I/O 操作の場合はパフォーマンスが向上し、順次 I/O 操作の場合はローテーション記憶域が向上します。 ローテーションストレージデバイスと非ローテーションストレージデバイスにデータファイルとログファイルを分散する場合は、主に各ファイルで実行される I/O 操作のタイプを考慮してください。

  ランダム I/O-oriented ファイルには、通常、file-per-table および general tablespace データファイル、undo tablespace ファイルおよび temporary tablespace ファイルが含まれます。 順次 I/O-oriented ファイルには、InnoDB system tablespace ファイル (MySQL 8.0.20 および change buffering より前の doublewrite buffering のため)、MySQL 8.0.20 で導入された二重書き込みファイル、およびリンク binary log ファイルや redo log ファイルなどのログファイルが含まれます。

  非ローテーション記憶域を使用する場合は、次の構成オプションの設定を確認します:

  • innodb_checksum_algorithm

    crc32 オプションでは、高速チェックサムアルゴリズムが使用されるため、高速ストレージシステムにお薦めします。

  • innodb_flush_neighbors

    ローテーションストレージデバイス用に I/O を最適化します。 非ローテーション記憶域の場合、またはローテーション記憶域と非ローテーション記憶域の混在の場合は無効にします。 デフォルトでは無効になっています。

  • innodb_idle_flush_pct

    アイドル期間中のページフラッシュに制限を設定できます。これにより、非ローテーション型ストレージデバイスの存続期間を延長できます。 MySQL 8.0.18 で導入されました。

  • innodb_io_capacity

    一般に、デフォルト設定の 200 は、ローエンドの非ローテーション型ストレージデバイスには十分です。 高エンドのバス接続デバイスの場合は、1000 などの高い設定を検討してください。

  • innodb_io_capacity_max

    デフォルト値 2000 は、非ローテーション記憶域を使用するワークロードを対象としています。 ハイエンドのバス接続された非ローテーション型ストレージデバイスの場合は、2500 などの高い設定を検討してください。

  • innodb_log_compressed_pages

    redo ログが非ローテーション記憶域にある場合は、このオプションを無効にしてロギングを減らすことを検討してください。 Disable logging of compressed pages を参照してください。

  • innodb_log_file_size

    redo ログが非繰返し記憶域にある場合は、このオプションを構成してキャッシュと書込みの組合せを最大化します。

  • innodb_page_size

    ディスクの内部セクターサイズと一致するページサイズの使用を検討してください。 早期世代 SSD デバイスは、多くの場合、セクターサイズが 4KB です。 一部の新しいデバイスには、16K バイトのセクターサイズがあります。 デフォルトの InnoDB ページサイズは 16KB です。 ページサイズをストレージデバイスのブロックサイズに近づけると、ディスクに書き換えられる変更されていないデータの量が最小限に抑えられます。

  • binlog_row_image

    バイナリログが非ローテーションストレージ上にあり、すべてのテーブルに主キーがある場合は、ロギングを減らすためにこのオプションを minimal に設定することを検討してください。

  オペレーティングシステムで TRIM サポートが有効になっていることを確認します。 通常は、デフォルトで有効になっています。

• バックログを回避するための I/O 容量の増加

  InnoDB チェックポイント操作のため、スループットが周期的に低下する場合、innodb_io_capacity 構成オプションの値を増加することを考慮します。 値を大きくすると、フラッシュが頻繁になり、スループットを低下させる可能性のある作業のバックログが避けられます。

• フラッシュが遅れない場合の I/O 容量の削減

  InnoDB フラッシュ操作によって、システムが遅くならない場合は、innodb_io_capacity 構成オプションの値を小さくすることを考慮します。 一般に、このオプション値はできるかぎり小さくしますが、前の箇条書きで示したように、スループットに周期的な低下が発生するほど小さくしないでください。 オプション値を小さくすることができる一般的なシナリオでは、SHOW ENGINE INNODB STATUS からの出力に、次のような組み合わせが示されることがあります。

  • 履歴リストの長さが短く、数千未満です。

  • 挿入バッファーマージ数が挿入された行数に近いです。

  • バッファープール内の変更されたページが、一貫してバッファープールの innodb_max_dirty_pages_pct をはるかに下回っています。 (サーバーが一括挿入を実行していないときに測定します。変更されたページの一括挿入時に、パーセンテージが大幅に高くなるのは正常です。)

  • Log sequence number - Last checkpoint が、InnoDB ログファイルの合計サイズの 7/8 未満か、理想的には 6/8 未満です。

• Fusion-io デバイスへのシステムテーブルスペースファイルの格納

  二重書込みバッファ関連の I/O 最適化を利用するには、二重書込み記憶域を含むファイルを、アトミック書込みをサポートする Fusion-io デバイスに格納します。 (MySQL 8.0.20 より前では、二重書込みバッファ記憶域はシステムテーブルスペースデータファイルに存在していました。 MySQL 8.0.20 では、記憶域は二重書込みファイルに存在します。 セクション15.6.4「二重書き込みバッファー」を参照してください。) 二重書き込みストレージ領域ファイルがアトミック書き込みをサポートする Fusion-io デバイスに配置されると、二重書き込みバッファーは自動的に無効になり、Fusion-io アトミック書き込みがすべてのデータファイルに使用されます。 この機能は Fusion-io ハードウェアでのみサポートされ、Linux の Fusion-io NVMFS でのみ有効になります。 この機能を最大限に活用するには、O_DIRECT の innodb_flush_method 設定をお薦めします。

  注記

  二重書込みバッファ設定はグローバルであるため、Fusion-io ハードウェアに存在しないデータファイルの二重書込みバッファも無効になります。

• 圧縮されたページのロギングの無効化

  InnoDB テーブルの compression 機能を使用する場合、圧縮されたデータが変更されると、再圧縮された pages のイメージが redo log に書き込まれます。 この動作は innodb_log_compressed_pages によって制御され、リカバリ中に異なるバージョンの zlib 圧縮アルゴリズムが使用された場合に発生する可能性のある破損を防ぐためにデフォルトで有効になっています。 zlib のバージョンが変更されないことが確実な場合は、innodb_log_compressed_pages を無効にして、圧縮データを変更するワークロードの redo ログ生成を減らします。