今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQL 8.4.0で変更されたInnoDB システム変数のデフォルト値を見ていくの日。

データベース MySQL

目次

とある日

2024-04-30にMySQL 8.4がリリースされました。

MySQL :: MySQL 8.4 リリース ノート :: MySQL 8.4.0 の変更点 (2024-04-30、LTS リリース)

バージョンリリースの変更に伴って初のLTSの登場で、「MySQL 8.4 の新機能」というページが新しく生えてたので見ていこうかなと思います。

「MySQL 8.4 の新機能」の中でも、システム変数が色々と変わってたのでゆっくり見ていきます。

MySQLの Innovation と Long-Term Support (LTS) バージョンのご紹介

MySQL 8.4 の新機能

InnoDB システム変数のデフォルト値が変更されました。 InnoDB 次の表に示すように、ストレージ エンジン に関連するいくつかのサーバー システム変数のデフォルト値がMySQL 8.4.0 で変更されました。

MySQL :: MySQL 8.4 リファレンスマニュアル :: 1.4 MySQL 8.0 以降の MySQL 8.4 の新機能

変更対象のシステム変数一覧

  1. innodb_buffer_pool_in_core_file
  2. innodb_buffer_pool_instances
  3. innodb_change_buffering
  4. innodb_dedicated_server
  5. innodb_adaptive_hash_index
  6. innodb_doublewrite_files
  7. innodb_doublewrite_pages
  8. innodb_flush_method
  9. innodb_io_capacity
  10. innodb_io_capacity_max
  11. innodb_log_buffer_size
  12. innodb_numa_interleave
  13. innodb_page_cleaners
  14. innodb_parallel_read_threads
  15. innodb_purge_threads
  16. innodb_read_io_threads
  17. innodb_use_fdatasync
  18. temptable_max_ram
  19. temptable_max_mmap
  20. temptable_use_mmap

意外と知らない変数ばかりだったので一つづつ見ていきます。

innodb_buffer_pool_in_core_file

  • プログラム異常時に出力される「coreファイル」にバッファー プール ページを含めるかいなかを表す変数

変更点

  • 新しいデフォルト値 (MySQL 8.4):MADV_DONTDUMPがサポートされている場合はOFF、そうでない場合はON
  • 以前のデフォルト値 (MySQL 8.0):ON

調べたこと

innodb_buffer_pool_instances

変更点

  • 新しいデフォルト値 (MySQL 8.4)
    • innodb_buffer_pool_size <= 1 GiB の場合、 innodb_buffer_pool_instances=1
    • innodb_buffer_pool_size > 1 GiB の場合、1-64 の範囲で計算された以下の 2 つのヒントの最小値
      • バッファプールヒント： (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) の 1/2 として計算される。
      • CPU ヒント: 利用可能な論理プロセッサ数の 1/4 として計算される。
  • 以前のデフォルト値 (MySQL 8.0)

調べたこと

  • 各バッファープールインスタンスが少なくとも 1GB になるようすると最高の効率になるらしい

innodb_change_buffering

  • バッファリングするDMLを決定する

変更点

  • 新しいデフォルト値 (MySQL 8.4):none
  • 以前のデフォルト値 (MySQL 8.0):all

調べたこと

innodb_dedicated_server

  • システム変数の自動設定

変更点

  • 新しいデフォルト値 (MySQL 8.4)
    • ON[a] の場合、innodb_flush_method の値は MySQL 8.0 のように変更されなくなるが、innodb_redo_log_capacity の計算はメモリベースから CPU ベースに変更される。
  • 以前のデフォルト値 (MySQL 8.0)
    • OFF

調べたこと

  • InnoDB次の変数が自動的に設定される
  • 利用可能なすべてのシステム リソースを使用できる専用サーバー上に存在する場合にのみ有効化を検討

innodb_adaptive_hash_index

変更点

  • 新しいデフォルト値 (MySQL 8.4):OFF
  • 以前のデフォルト値 (MySQL 8.0):ON

調べたこと

innodb_doublewrite_files

  • 二重書き込みファイルの数を定義
  • バッファープールインスタンスごとに 2 つの二重書き込みファイルが作成

変更点

調べたこと

innodb_doublewrite_pages

  • バッチ書き込みのスレッドごとの二重書き込みページの最大数を定義

変更点

調べたこと

innodb_flush_method

変更点

  • 新しいデフォルト値 (MySQL 8.4):サポートされていれば O_DIRECT、そうでなければ fsync
  • 以前のデフォルト値 (MySQL 8.0):fsync

調べたこと

innodb_io_capacity

  • innodb_io_capacity 変数は、バッファ・プールからのページのフラッシュや変更バッファからのデータのマージなどの InnoDB バックグラウンド・タスクで利用可能な 1 秒あたりの I/O 操作数 (IOPS) を定義します。

変更点

  • 新しいデフォルト値 (MySQL 8.4):10000
  • 以前のデフォルト値 (MySQL 8.0):200

調べたこと

  • innodb_io_capacity 変数は、InnoDB で使用可能な全体的な I/O 容量を定義

  • 設定は可能な限り低くするのが理想的ですが、バックグラウンド アクティビティが遅れるほど低くしてはいけません。

  • 値が高すぎると、バッファ プールからデータが削除され、バッファの変更が速すぎて、キャッシュによる大きなメリットが得られなくなります。

innodb_io_capacity_max

  • フラッシング活動が遅れた場合、InnoDBは、innodb_io_capacity変数で定義されたよりも高いI/Oオペレーション/秒（IOPS）で、より積極的にフラッシュすることができます。
  • innodb_io_capacity_max 変数は、このような状況で InnoDB バックグラウンドタスクが実行する IOPS の最大数を定義します。
  • このオプションはinnodb_flush_syncの動作を制御しない。
  • デフォルト値は、innodb_io_capacityの値の2倍です。

変更点

innodb_log_buffer_size

  • InnoDB がディスク上のログ ファイルに書き込むために使用するバッファのバイト サイズ。
  • デフォルトは64MBです。
  • 大きなログバッファを使用すると、トランザクションがコミットする前にディスクにログを書き込む必要なく、大きなトランザクションを実行することができます。
  • したがって、多くの行を更新、挿入、削除するトランザクションがある場合、ログバッファを大きくすることでディスクI/Oを節約することができます。
  • 関連情報については、メモリ構成、および項10.5.4. 「InnoDB Redo Loggingの最適化」を参照してください。一般的なI/Oチューニングのアドバイスについては、項10.5.8. 「InnoDBディスクI/Oの最適化」を参照してください。

変更点

  • 新しいデフォルト値 (MySQL 8.4):67108864 (64 MiB)
  • 以前のデフォルト値 (MySQL 8.0):16777216 (16 MiB)

innodb_numa_interleave

  • InnoDB バッファプールの割り当てのための NUMA インターリーブメモリポリシーを有効にします。
  • innodb_numa_interleave が有効になると、NUMA メモリ・ポリシーが mysqld プロセスの MPOL_INTERLEAVE に設定されます。
  • InnoDB バッファ・プールが割り当てられた後、NUMA メモリ・ポリシーは MPOL_DEFAULT に戻されます。
  • innodb_numa_interleave オプションを使用するには、MySQL を NUMA 対応 Linux システムでコンパイルする必要があります。
  • デフォルト値は、システムがサポートしていれば ON、そうでなければ OFF です。

変更点

  • 新しいデフォルト値 (MySQL 8.4):ON
  • 以前のデフォルト値 (MySQL 8.0):OFF

調べたこと

innodb_page_cleaners

  • バッファプールインスタンスからダーティページをフラッシュするページクリーナースレッドの数。
  • ページクリーナースレッドはフラッシュリストとLRUフラッシュを実行する。
  • 複数のページクリーナースレッドがある場合、各バッファプールインスタンスのバッファプールフラッシングタスクはアイドル状態のページクリーナースレッドにディスパッチされます。
  • innodb_page_cleaners のデフォルト値は innodb_buffer_pool_instances と同じ値に設定されています。
  • 指定されたページクリーナースレッドの数がバッファプールインスタンスの数を超える場合、 innodb_page_cleaners は自動的にinnodb_buffer_pool_instances と同じ値に設定されます。
  • バッファプールインスタンスからデータファイルへダーティページをフラッシュする際に、ワークロードがライトIOバインドされ、システムハードウェアに利用可能な容量がある場合、ページクリーナースレッドの数を増やすと、ライトIOスループットの改善に役立つかもしれません。
  • マルチスレッド・ページ・クリーナーのサポートは、シャットダウンおよびリカバリ・フェーズにまで拡張されます。
  • setpriority()システムコールは、それがサポートされ、mysqld実行ユーザが他のMySQLスレッドやInnoDBスレッドよりもpage_cleanerスレッドに優先順位を与えることを許可されているLinuxプラットフォームで使用され、ページフラッシングが現在のワークロードに追いつくのを助けます。

変更点

調べたこと

innodb_parallel_read_threads

  • クラスタ化されたインデックスの並列読み取りに使用できるスレッド数を定義します。
  • パーティションの並列スキャンもサポートされます。
  • 並列読み取りスレッドはCHECK TABLEのパフォーマンスを向上させます。
  • InnoDBは、CHECK TABLE操作中にクラスタ化インデックスを2回読み取ります。
  • 2回目の読み取りは並列で実行することができます。
  • この機能はセカンダリインデックススキャンには適用されません。
  • 並列クラスタ化インデックス読み取りを行うには、innodb_parallel_read_threadsセッション変数を1より大きい値に設定しなければなりません。
  • 並列クラスタ化インデックス読み取りを実行するために使用される実際のスレッド数は、innodb_parallel_read_threads設定か、スキャンするインデックスサブツリーの数のどちらか小さい方で決まります。
  • スキャン中にバッファプールに読み込まれたページは、バッファプールLRUリストの末尾に保持され、空きバッファプールページが必要になった時に素早く破棄できるようにします。
  • 並列読み取りスレッドの最大数（256）は、すべてのクライアント接続のスレッド数の合計である。
  • スレッドの上限に達した場合、接続はシングルスレッドの使用にフォールバックする。
  • デフォルト値は、システムで使用可能な論理プロセッサの数を 8 で割った値で計算され、デフォルトの最小値は 4 です。
  • MySQL 8.4 以前では、デフォルト値は常に 4 でした。

変更点

  • 新しいデフォルト値 (MySQL 8.4):available logical processors / 8, with a minimum default value of 4
  • 以前のデフォルト値 (MySQL 8.0):4

innodb_purge_threads

  • InnoDBのパージ操作に割かれるバックグラウンドスレッドの数。
  • この値を増やすと追加のパージ スレッドが作成され、複数のテーブルでDML操作が実行されるシステムで効率が向上します。

変更点

  • 新しいデフォルト値 (MySQL 8.4):1 if available logical processors is <= 16, otherwise 4
  • 以前のデフォルト値 (MySQL 8.0):4

調べたこと

  • パージ

    • InnoDBSQL 文で行を削除しても、行はデータベースからすぐに物理的に削除されるわけではありません。
    • 行とそのインデックス レコードは、 InnoDB削除のために書き込まれた UNDO ログ レコードを破棄した場合にのみ物理的に削除されます。
    • この削除操作は、行がマルチバージョン同時実行制御 (MVCC) またはロールバックに不要になった後にのみ実行

  • 1 つ以上のパージ スレッドによってバックグラウンドで実行されます。

  • MySQL :: MySQL 8.4 リファレンスマニュアル :: 17.8.9 パージ設定

innodb_read_io_threads

  • InnoDB の読み取り操作のための I/O スレッド数。
  • 書き込みスレッドに対する対応は innodb_write_io_threads です。
  • デフォルト値は、システムで利用可能な論理プロセッサ数を 2 で割った値で、最小デフォルト値は 4 です。
  • MySQL 8.4 以前では、デフォルト値は常に 4 でした。

変更点

  • 新しいデフォルト値 (MySQL 8.4):available logical processors / 2, with a minimum default value of 4
  • 以前のデフォルト値 (MySQL 8.0):4

調べたこと

innodb_use_fdatasync

  • fdatasync()システムコールをサポートしているプラットフォームでは、 innodb_use_fdatasync を有効にすると、オペレーティングシステムのフラッシュに fsync()システムコールの代わりに fdatasync()を使うことができます。
  • fdatasync() 呼び出しは、その後のデータ検索に必要でない限り、ファイルメタデータの変更をフラッシュしないので、潜在的なパフォーマンス上の利点があります。
  • fsync、O_DSYNC、O_DIRECT などの innodb_flush_method 設定のサブセットは fsync() システムコールを使用します。innodb_use_fdatasync 変数はこれらの設定を使用する時に適用されます。
  • MySQL 8.4 以前では、このオプションはデフォルトで無効になっていました。

変更点

  • 新しいデフォルト値 (MySQL 8.4):ON
  • 以前のデフォルト値 (MySQL 8.0):OFF

調べたこと

temptable_max_ram

  • TempTable ストレージ エンジンがディスクへのデータ保存を開始するまでに占有できるメモリの最大量を定義します。
  • デフォルト値はサーバで使用可能な総メモリの 3% で、デフォルトの最小および最大範囲は 1-4 GiB です。
  • MySQL 8.4 以前では、デフォルト値は常に 1 GiB でした。

変更点

  • 新しいデフォルト値 (MySQL 8.4): 3% of total memory, with a default value within a range of 1-4 GiB
  • 以前のデフォルト値 (MySQL 8.0):1073741824 (1 GiB)

調べたこと

temptable_max_mmap

  • TempTable ストレージ エンジンがディスク上の InnoDB 内部テンポラリ テーブルへのデータ格納を開始する前に、メモリ マップされたテンポラリ ファイルから割り当てることを許可されるメモリの最大量 (バイト単位) を定義します。
  • 0 の設定（デフォルト）は、メモリ マップされた一時ファイルからのメモリの割り当てを無効にします。
  • MySQL 8.4 以前では、このオプションは 0 ではなく 1 GiB に設定されていました。

変更点

  • 新しいデフォルト値 (MySQL 8.4):0, which means OFF
  • 以前のデフォルト値 (MySQL 8.0):1073741824 (1 GiB)

調べたこと

  • TempTable ストレージエンジンは、VARCHAR および VARBINARY カラム、および MySQL 8.0.13 の時点でのその他のバイナリラージオブジェクト型の効率的なストレージを提供します。

temptable_use_mmap

  • TempTable ストレージ エンジンが占有するメモリ量が temptable_max_ram 変数で定義された制限を超えた場合、メモリ マップされたテンポラリ ファイルとして内部メモリ内テンポラリ テーブルの領域を割り当てるかどうかを定義します。
  • temptable_use_mmapが無効な場合（デフォルト）、TempTableストレージエンジンは、代わりにInnoDBオンディスク内部テンポラリテーブルを使用します。

変更点

  • 新しいデフォルト値 (MySQL 8.4):OFF
  • 以前のデフォルト値 (MySQL 8.0):ON

〆

知らない変数ばかりだなってなりました。