mita2 database life

主にMySQLに関するメモです

最近の MySQL の Internal Temporary Table ステータスとチューニング観点まとめ (version 8.0.28 版)

以下のエントリーでは、最近の MySQL の Internal Temporary Table の動作についてまとめました。

mita2db.hateblo.jp

条件によっては、領域が ディスク上に確保される場合があり、その場合はパフォーマンスに影響がある可能性があります。 今回はMySQLのステータスから Internal Temporary Table の状況を観測し、チューニングする方法をまとめます。

performance_schema.global_status / SHOW GLOBAL STATUS

mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Created_tmp_tables', 'Created_tmp_disk_tables') ORDER BY VARIABLE_NAME \G

*************************** 1. row ***************************
 VARIABLE_NAME: Created_tmp_disk_tables
VARIABLE_VALUE: 0
*************************** 2. row ***************************
 VARIABLE_NAME: Created_tmp_tables
VARIABLE_VALUE: 10
2 rows in set (0.00 sec)

Created_tmp_tables

  • Internal Temporary Table が作成された回数
  • Internal Temporary Table のストレージエンジンや、その領域が確保される場所（メモリ or ディスク）に関係なく、Internal Temporary Table が使われるたびにインクリメントされる
  • Internal Temporary Table の利用が避けられないケースや、Internal Temporary Table を使う実行計画を選択したほうが高速な場合もある
    • Created_tmp_tables の多寡だけではチューニングの余地があるかどうかは判断できない

Created_tmp_disk_tables

  • InnoDB ストレージエンジン で Internal Temporary Table が作成された回数
  • Temp Table エンジンが利用されたケースでは、インクリメントされない
  • (メモリではなく) ディスク上に作成されているため、このステータス値がどんどん増加している場合は、チューニングの余地があるかもしれない
    • InnoDB に落ちないよう、tmp_table_size や temptable_max_ram を調整するとパフォーマンス改善するかも

performance_schema.memory_summary_global_by_event_name

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/physical%' \G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_disk
                 COUNT_ALLOC: 0
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 0
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 0
             HIGH_COUNT_USED: 0
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
   HIGH_NUMBER_OF_BYTES_USED: 0
*************************** 2. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 1048608
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048608
   HIGH_NUMBER_OF_BYTES_USED: 1048608
2 rows in set (0.00 sec)

Temp Table エンジンに関するステータス。

Created_tmp_tables や Created_tmp_disk_tables はシンプルに「作成された Internal Temporary Table の数」を示しますが、 memory/temptable/{physical_disk,physical_ram} はテーブル数ではなく、メモリやディスク上に領域が確保された回数やサイズを示します。単位が異なるので注意。

各ステータスの意味はオフィシャルドキュメント参照。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 27.12.18.10 メモリーサマリーテーブル

  • memory/temptable/physical_ram

    • Temp Table エンジンがメモリ上に確保した領域のステータス
    • HIGH_NUMBER_OF_BYTES_USED が temptable_max_ram に近い場合、割り当てメモリが足りず、ファイル上に領域を確保しているかもしれない
      • memory/temptable/physical_disk と合わせて判断
      • ただし、一時的に足りなかっただけかもしれないので、CURRENT_NUMBER_OF_BYTES_USED を継続的に観測しておくと良い

  • memory/temptable/physical_disk

    • Temp Table エンジンがファイル（ディスク）上に確保した領域のステータス

    • HIGH_NUMBER_OF_BYTES_USED が 0 より大きい場合、割り当てメモリが足りずファイルに溢れていたことを示す

      • temptable_max_ram を引き上げるとパフォーマンスが改善するかもしれない

    • HIGH_NUMBER_OF_BYTES_USED が temptable_max_mmap に近い場合、ファイル（mmap）領域も足りず、InnoDB が使われている可能性がある

      • Created_tmp_disk_tables と合わせて判断
    • こちらも、CURRENT_NUMBER_OF_BYTES_USED を継続的に観測しておくと良い

Temp Table エンジンの領域は 2N MB で拡張されていく

Temp Table エンジンの領域は、1,2,4,8,16...MB と 2の階乗単位で追加されていきます。 このため、追加する領域のサイズに必要な十分な空きメモリがない場合、たとえ、メモリ領域が空いていても、ファイルに領域が確保される場合があります（memory/temptable/physical_ram の HIGH_NUMBER_OF_BYTES_USED が temptable_max_ram に達していなくても、ファイルが利用される可能性がある）

/* Concrete implementation of Block_size_policy, a type which controls how big
 * next Block of memory is going to be allocated by TempTable allocator.
 *
 * In particular, this policy will make TempTable allocator to grow the
 * block-size at exponential rate with upper limit of ALLOCATOR_MAX_BLOCK_BYTES,
 * which is 2 ^ ALLOCATOR_MAX_BLOCK_MB_EXP.
 *
 * E.g. allocation pattern may look like the following:
 *  1 MiB,
 *  2 MiB,
 *  4 MiB,
 *  8 MiB,
 *  16 MiB,
 *  32 MiB,
 *  ...,
 *  ALLOCATOR_MAX_BLOCK_BYTES,
 *  ALLOCATOR_MAX_BLOCK_BYTES
 *
 * In cases when block size that is being requested is bigger than the one which
 * is calculated by this policy, requested block size will be returned (even if
 * it grows beyond ALLOCATOR_MAX_BLOCK_BYTES).
 * */
struct Exponential_policy {
  /** Given the current number of allocated blocks by the allocator, and number
   * of bytes actually requested by the client code, calculate the new block
   * size.
   *
   * [in] Current number of allocated blocks.
   * [in] Number of bytes requested by the client code.
   * @return New block size. */
  static size_t block_size(size_t number_of_blocks, size_t n_bytes_requested) {
    size_t block_size_hint;
    if (number_of_blocks < ALLOCATOR_MAX_BLOCK_MB_EXP) {
      block_size_hint = (1ULL << number_of_blocks) * 1_MiB;
    } else {
      block_size_hint = ALLOCATOR_MAX_BLOCK_BYTES;
    }
    return std::max(block_size_hint, Block::size_hint(n_bytes_requested));
  }
};