TempTable in MySQL8.0のメモ

temp_table

MySQL8.0.16のリリースノートに以下の様にTempTableの処理に変更が加わっていたので仕様を把握する為に、追加されたパラメータと挙動に関して確認してみました。見ていると、8.0.23での変更や、8.0.26におけるtemptable_use_mmapのdeprecate notice,8.0.28における変更もあるようです。なので、最新の挙動は8.0.31以降で再確認します。結論としては、8.0.28以降ではTempTable, MEMORYストレージエンジン共にtmp_table_sizeを適切に設定する必要あり。

InnoDB: When the amount of memory occupied by the TempTable storage engine exceeds the limit defined by the temptable_max_ram variable, the TempTable storage engine allocates space for internal in-memory temporary tables as memory-mapped temporary files. This behavior is now controlled by the temptable_use_mmap variable, which can be disabled to have the TempTable storage engine use InnoDB on-disk internal temporary tables instead. For more information, see Internal Temporary Table Use in MySQL. (Bug #28944457)

Changes in MySQL 8.0.16 (2019-04-25, General Availability)

こちらの設定で、Temp Tableの処理をコントロールする様な仕様に変わっているとの事。

mysql> show variables like 'temptable_use_mmap';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| temptable_use_mmap | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'temptable_max_ram';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| temptable_max_ram | 1073741824 |
+-------------------+------------+
1 row in set (0.00 sec)

mysql> select 1073741824/1024/1024;
+----------------------+
| 1073741824/1024/1024 |
+----------------------+
|        1024.00000000 |
+----------------------+
1 row in set (0.00 sec)
  • temptable_use_mmapは既にDeprecatedになっているので、将来Removeされる予定
temptable_use_mmap

MySQL8.0.23では更にパラメータが追加されているとの事。

temptable_max_ram 変数は、TempTable ストレージエンジンがメモリーマップ一時ファイルまたは InnoDB ディスク上の内部一時テーブルの形式でディスクから領域の割り当てを開始する前に占有できる RAM の最大量を定義します。 デフォルトの temptable_max_ram 設定は 1GiB です。 temptable_use_mmap 変数 (MySQL 8.0.16 で導入) は、temptable_max_ram の制限を超えた場合に、TempTable ストレージエンジンがメモリーマップされたファイルまたは InnoDB ディスク上の内部一時テーブルを使用するかどうかを制御します。 デフォルト設定は temptable_use_mmap=ON です。 MySQL 8.0.23 で導入された temptable_max_mmap 変数は、TempTable ストレージエンジンが内部一時テーブルデータの InnoDB ディスク上の内部一時テーブルへの格納を開始する前に、メモリーマップされたファイルから割り当てることができるメモリーの最大量を定義します。 temptable_max_mmap=0 設定では、メモリーマップファイルからの割当てが無効化され、temptable_use_mmap 設定に関係なく、使用が効率的に無効化されます。

8.4.4 MySQL での内部一時テーブルの使用

上記マニュアルにあるように、MySQL8.0.23で更にコントロールオプションが追加されています。

mysql> show variables like 'temptable_max_mmap';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)
temptable_* オプション全般

このパラメータの追加による処理方法の変化によって、Temp Tableの処理にどれだけ差異が出てくるのだろうか?これまでもメモリーで処理しきれない処理は、ディスクにて処理(現状:InnoDB)しているが… もう少し深堀してみる。

mysql> show status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 324   |
| Sort_scan         | 13    |
+-------------------+-------+
4 rows in set (0.00 sec)

復習:Temp Tableの利用条件 (マニュアル参照

一時テーブルはメモリー内に保持され、TempTable または MEMORY ストレージエンジンによって処理されるか、ディスク上においては、InnoDB ストレージエンジンによってディスク上に格納され処理されます。

1UNION ステートメントの評価
2UNION または集計を使用するビューなど一部のビューの評価
3導出テーブル(サブクエリー等)の評価
4共通テーブル式の評価 (WITH [CTE])
5サブクエリーまたは準結合の実体化用に作成されたテーブル
6ORDER BY 句と異なる GROUP BY 句を含むステートメント、または結合キューの最初のテーブル以外のテーブルのカラムが ORDER BY または GROUP BY に含まれるステートメントの評価。
7ORDER BY と組み合せた DISTINCT の評価には、一時テーブルが必要になる場合があります。
8SQL_SMALL_RESULT 修飾子を使用するクエリーの場合、ディスク上の記憶域を必要とする要素 (後述) もクエリーに含まれていないかぎり、MySQL はインメモリー一時テーブルを使用します。
9同じテーブルから選択して同じテーブルに挿入する INSERT … SELECT ステートメントを評価するために、MySQL は SELECT の行を保持する内部一時テーブルを作成し、それらの行をターゲットテーブルに挿入します。
10複数テーブルの UPDATE ステートメントの評価。
11GROUP_CONCAT() または COUNT(DISTINCT) 式の評価。
12ウィンドウ関数の評価 (Windows関数では、必要に応じて一時テーブルが使用されます。
13ディスク上のテーブルを使用
テーブル内の BLOB または TEXT カラムの存在 ただし、MySQL 8.0 のインメモリー内部一時テーブルのデフォルトのストレージエンジンである TempTable ストレージエンジンは、MySQL 8.0.13 の時点でバイナリラージオブジェクト型をサポートしています。
14ディスク上のテーブルを使用
UNION または UNION ALL が使用された場合に、SELECT リスト内の 512 (バイナリ文字列の場合はバイト数、非バイナリ文字列の場合は文字数) より大きい最大長を持つ文字列カラムの存在。
15ディスク上のテーブルを使用
SHOW COLUMNS および DESCRIBE ステートメントでは、一部のカラムのタイプとして BLOB が使用されるため、結果に使用される一時テーブルはディスク上のテーブルです。
16特定の条件を満たす UNION ステートメントに一時テーブルを使用しません。
共用体は、UNION や UNION DISTINCT ではなく、UNION ALL です。
17特定の条件を満たす UNION ステートメントに一時テーブルを使用しません。
グローバル ORDER BY 句がありません。
18特定の条件を満たす UNION ステートメントに一時テーブルを使用しません。
共用体は、{INSERT | REPLACE} … SELECT … ステートメントのトップレベルのクエリーブロックではありません。
19
20

internal_tmp_mem_storage_engine (TempTable, MEMORY)

TempTableストレージエンジンはSHOW ENGINESでは出てこないストレージエンジンなのですね。

internal_tmp_mem_storage_engine(インメモリー内部一時テーブルのストレージエンジン)

MySQL8.0.16, 8.0.23で追加されたパラメータの概要

Variable_name概要
temptable_max_mmap
( MySQL 8.0.23~)
TempTable ストレージエンジンが内部一時テーブルデータをInnoDB ディスク上の内部一時テーブルへ格納を開始する前に、メモリーマップされたファイルから割り当てることができるメモリーの最大量を定義。
temptable_max_mmap=0 設定では、メモリーマップファイルからの割当てが無効化され、temptable_use_mmap 設定に関係なく使用が効率的に無効化されます。
補足:MySQL 8.0.23 より前は、TempTable ストレージエンジンが temptable_max_ram の制限を超え、メモリーマップされたファイル用に一時ディレクトリ内の過剰な領域を使用することが多い場合、TempTable オーバーフローメカニズムとして InnoDB のディスク上の内部一時テーブルが推奨されていました。 MySQL 8.0.23 の時点で、temptable_max_mmap 変数はTempTable ストレージエンジンがメモリーマップされたファイルから割り当てるメモリー量の制限を定義するため、これらのファイルが使用する領域が多すぎるリスクに対処します。 通常、temptable_max_ram の制限を超えるのは、大規模な内部一時テーブルの使用または内部一時テーブルの広範囲な使用が原因との事。
temptable_max_ram
 (MySQL 8.0.16~) 
TempTable ストレージエンジンがメモリーマップ一時ファイルまたは InnoDB ディスク上の内部一時テーブルの形式でディスクから領域の割り当てを開始する前に占有できる RAM の最大量を定義。
補足:MySQL 8.0.16 で削除された internal_tmp_disk_storage_engine 設定は、TempTable ストレージエンジンオーバーフローメカニズムには影響しないとの事。
temptable_use_mmap
 (MySQL 8.0.16~) 
temptable_max_ram の制限を超えた場合に、TempTable ストレージエンジンがメモリーマップされたファイルまたは InnoDB ディスク上の内部一時テーブルを使用するかどうかを制御
temptable_* 概要

注記には以下の様に記載があるので、1MBがスレッドに割り当てられるメモリーの閾値となる様子。

temptable_max_ram 設定では、TempTable ストレージエンジンを使用する各スレッドに割り当てられたスレッドローカルメモリーブロックは考慮されません。 スレッドローカルメモリーブロックのサイズは、スレッドの最初のメモリー割当てリクエストのサイズによって異なります。 リクエストが 1MB 未満の場合 (ほとんどの場合)、スレッドローカルメモリーブロックサイズは 1MB です。 リクエストが 1MB を超える場合、スレッドローカルメモリーブロックは初期メモリーリクエストとほぼ同じサイズになります。 スレッドローカルメモリーブロックは、スレッドが終了するまでスレッドローカル記憶域に保持されます。

注記

ファイルパス:InnoDBセッション一時テーブルスペース

MySQL 8.0.16 以降、InnoDB ストレージエンジンを使用してディスク上の内部一時テーブルを管理しています。MySQL 8.0.15 以前で利用されていた、internal_tmp_disk_storage_engine 変数でストレージエンジン(MyISAM, InnoDB)を定義していましたがMySQL 8.0.16 で削除されました

mysql> show variables like 'innodb_temp_tablespaces_dir';
+-----------------------------+-----------------+
| Variable_name               | Value           |
+-----------------------------+-----------------+
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
+-----------------------------+-----------------+
1 row in set (0.01 sec)

internal_tmp_disk_storage_engineは5.7にP.18に記載した様にアップグレード時に問題があったが、MySQL8.0.16以降ではInnoDB一択になったので,今後何か対応が必要になるか?も含めて別途確認。

INFORMATION_SCHEMA:セッション一時テーブルスペースに関するメタデータの確認

INNODB_SESSION_TEMP_TABLESPACES:セッション一時テーブルスペースに関するメタデータ
INNODB_TEMP_TABLE_INFO:InnoDBインスタンスでアクティブなユーザー作成一時テーブルに関するメタデータ

mysql> select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE      | PATH                       | SIZE  | STATE    | PURPOSE   |
+----+------------+----------------------------+-------+----------+-----------+
|  8 | 4243767290 | ./#innodb_temp/temp_10.ibt | 98304 | ACTIVE   | INTRINSIC |
|  0 | 4243767281 | ./#innodb_temp/temp_1.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767282 | ./#innodb_temp/temp_2.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767283 | ./#innodb_temp/temp_3.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767284 | ./#innodb_temp/temp_4.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767285 | ./#innodb_temp/temp_5.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767286 | ./#innodb_temp/temp_6.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767287 | ./#innodb_temp/temp_7.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767288 | ./#innodb_temp/temp_8.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767289 | ./#innodb_temp/temp_9.ibt  | 81920 | INACTIVE | NONE      |
+----+------------+----------------------------+-------+----------+-----------+
10 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
Empty set (0.00 sec)

PERFORMANCE_SCHEMA: イベント発生状況の詳細確認

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE '%temptable%'\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: 8
                  COUNT_FREE: 7
   SUM_NUMBER_OF_BYTES_ALLOC: 8388608
    SUM_NUMBER_OF_BYTES_FREE: 7340032
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 2
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 2097152
2 rows in set (0.00 sec)

SHOW STATUS: ディスクに一時テーブルが作成された回数の確認

内部一時テーブルがディスク上に作成されると、 Created_tmp_disk_tables 値を増分していくので、 ディスク上に作成される内部一時テーブルが多すぎると判断される場合は、tmp_table_size および max_heap_table_size の設定を増やすことを検討。Created_tmp_disk_tables ではメモリーマップファイルに作成されたディスク上の一時テーブルはカウントされません

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 45    |
+-------------------------+-------+
3 rows in set (0.01 sec)

mysql> show global status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 48    |
+-------------------------+-------+
3 rows in set (0.00 sec)

備考:tmp_table_size, max_heap_table_sizeについては基本的にはこれまで通り

インメモリー一時テーブルに MEMORY ストレージエンジンを使用する場合、MySQL はインメモリー一時テーブルが大きすぎると、インメモリー一時テーブルをディスク上のテーブルに自動的に変換します。 インメモリー一時テーブルの最大サイズは、tmp_table_size または max_heap_table_size のいずれか小さい方の値によって定義されます。 これは、CREATE TABLE で明示的に作成される MEMORY テーブルとは異なります。 このようなテーブルの場合、max_heap_table_size 変数のみがテーブルの大きさを決定し、ディスク上の形式への変換はありません。

設定したことはないのですが、MEMORYストレージエンジンを利用していて、メモリーにデータが乗り切らない事が分かっている場合は、以下の設定をONにする事で変換をスキップし、ディスク上に最初から内部一時テーブルを作成して開始することで、パフォーマンスが向上する可能性があるとの事。

mysql> show variables like 'big_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| big_tables    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

その他参考:27.12.18.10 メモリーサマリーテーブル

確認:

sort_buffer_sizeサイズを最小まで落とし、temptable_max_mmapを”0″に設定してみたり、temptable_use_mmapを”OFF”にしてみましたが。データ量が少ないからかCreated_tmp_disk_tablesは発生しませんでした。tmp_table_sizeを変更しないでも、TempTableストレージエンジン+temptable_*パラメータでコントロール出来る認識でしたが、念の為に1024迄落として確認しています。上記のbig_tablesをONにすると、マニュアルにある通りにディスク上に内部テーブルを作成して処理しました。もう少し、データ量がある環境で近いうちバージョンを上げると同時に再確認してみようと思います。ただ仕様上、メモリーに余裕があるのであれば、TempTableを利用している場合は、temptable_max_ramもtemptable_max_mmapも必要に応じて、Defaultの1GB以上に設定してあげると、パフォーマンスは維持出来るのかと思います。

 MySQL 8.0.28以降では、TempTableでもMemoryのどちらでもtmp_table_sizeを参照する様です。

Defines the maximum size of internal in-memory temporary tables created by the MEMORY storage engine and, as of MySQL 8.0.28, the TempTable storage engine. If an internal in-memory temporary table exceeds this size, it is automatically converted to an on-disk internal temporary table.

Server System Valiables
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'internal%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
1 row in set (0.00 sec)

mysql>  show variables  like 'temptable_%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
| temptable_max_ram  | 1073741824 |
| temptable_use_mmap | ON         |
+--------------------+------------+
3 rows in set (0.01 sec)

mysql> show variables like 'sort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sort_buffer_size | 32768 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables  like 'tmp_%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| tmp_table_size | 1024  |
| tmpdir         | /tmp  |
+----------------+-------+
2 rows in set (0.00 sec)

mysql>  flush status;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/%'\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: 33
                  COUNT_FREE: 32
   SUM_NUMBER_OF_BYTES_ALLOC: 80740352
    SUM_NUMBER_OF_BYTES_FREE: 79691776
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 4
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 10485760
2 rows in set (0.00 sec)

mysql> explain with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: T_Dummy_no_Key
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9147520
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p11-10_12-jgd-g_busstop
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,gid
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (15.92 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/%'\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: 57
                  COUNT_FREE: 56
   SUM_NUMBER_OF_BYTES_ALLOC: 176160768
    SUM_NUMBER_OF_BYTES_FREE: 175112192
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 4
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 10485760
2 rows in set (0.01 sec)


mysql> set global temptable_max_mmap = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables  like 'temptable_%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 0          | /***  0 =メモリーマップされた一時ファイルからのメモリーの割り当て無効 ***/
| temptable_max_ram  | 1073741824 | /*** TempTableストレージエンジン占有メモリー ***/
| temptable_use_mmap | ON         |
+--------------------+------------+
3 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/%'\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: 57
                  COUNT_FREE: 56
   SUM_NUMBER_OF_BYTES_ALLOC: 176160768
    SUM_NUMBER_OF_BYTES_FREE: 175112192
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 4
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 10485760
2 rows in set (0.00 sec)

mysql> explain with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: T_Dummy_no_Key
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9147520
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p11-10_12-jgd-g_busstop
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,gid
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (16.15 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/%'\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: 81
                  COUNT_FREE: 80
   SUM_NUMBER_OF_BYTES_ALLOC: 271581184
    SUM_NUMBER_OF_BYTES_FREE: 270532608
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 4
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 10485760
2 rows in set (0.00 sec)

mysql> set global temptable_use_mmap=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1287 | 'temptable_use_mmap' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables  like 'temptable_%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 0          |
| temptable_max_ram  | 1073741824 |
| temptable_use_mmap | OFF        |
+--------------------+------------+
3 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/%'\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: 81
                  COUNT_FREE: 80
   SUM_NUMBER_OF_BYTES_ALLOC: 271581184
    SUM_NUMBER_OF_BYTES_FREE: 270532608
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 4
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 10485760
2 rows in set (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (15.56 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/%'\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: 105
                  COUNT_FREE: 104
   SUM_NUMBER_OF_BYTES_ALLOC: 367001600
    SUM_NUMBER_OF_BYTES_FREE: 365953024
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 4
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 10485760
2 rows in set (0.01 sec)

mysql> set big_tables = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (21.25 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.01 sec)

mysql> show status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 10    |
| Sort_scan         | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

mysql>
sort_buffer_sizeを最小まで落として、temptable_max_mmapを0に設定

今回のデータではメモリーから溢れる事も無かったので、設定をDefaultに戻しても大きくレスポンスに変更はありませんでした。big_tablesパラメータでディスクを利用する様にした場合にメモリーテーブルを利用しないので極端に遅くなった事だけが顕著でした。(≒やはりメモリーに出来るだけ載せるのが良いですね。)

mysql> set global temptable_max_mmap = 1073741824;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'temp%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
| temptable_max_ram  | 1073741824 |
| temptable_use_mmap | OFF        |
+--------------------+------------+
3 rows in set (0.00 sec)

mysql> set global temptable_use_mmap = ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (21.70 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 4     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 6     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show variables like  'big_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| big_tables    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set big_tables = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (15.46 sec)

mysql> show variables like 'tmp_table_size';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| tmp_table_size | 1024  |
+----------------+-------+
1 row in set (0.01 sec)

mysql> set tmp_table_size = 16777216;
Query OK, 0 rows affected (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (15.71 sec)

mysql> show variables like 'sort_buffer%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sort_buffer_size | 32768 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set sort_buffer_size = 262144;
Query OK, 0 rows affected (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (15.80 sec)

mysql>

ちなみに、internal_tmp_mem_storage_engineをMEMORYにしてtmp_table_sizeを1024迄下げた場合は、想定した通りの挙動になりました。今は設定がDepricateしたinternal_tmp_disk_storage_engine(InnoDB)が利用されている。

mysql> SET SESSION internal_tmp_mem_storage_engine = Memory;
Query OK, 0 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (16.20 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> set session tmp_table_size = 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (20.96 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql>

追記:MySQL8.0.31での再確認

temptable_use_mmapの値がON/OFFに限らず、tmp_table_size によってメモリー内で処理するかどうか判断している事が確認出来ました。

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)


mysql> show variables like 'internal_%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
1 row in set (0.00 sec)


mysql> show variables  like 'temptable_%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
| temptable_max_ram  | 1073741824 |
| temptable_use_mmap | ON         |
+--------------------+------------+
3 rows in set (0.01 sec)

mysql> show variables like 'sort%';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 2097152 |
+------------------+---------+
1 row in set (0.00 sec)

mysql> show variables  like 'tmp_%';
+----------------+---------+
| Variable_name  | Value   |
+----------------+---------+
| tmp_table_size | 8388608 |
| tmpdir         | /tmp    |
+----------------+---------+
2 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/%'\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: 2
                  COUNT_FREE: 1
   SUM_NUMBER_OF_BYTES_ALLOC: 2097216
    SUM_NUMBER_OF_BYTES_FREE: 1048608
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 2
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048608
   HIGH_NUMBER_OF_BYTES_USED: 2097216
2 rows in set (0.00 sec)

mysql> explain with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: T_Dummy_no_Key
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8847520
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p11-10_12-jgd-g_busstop
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,gid
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (23.19 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> set global temptable_use_mmap = 0;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show variables  like 'tmp_%';
+----------------+---------+
| Variable_name  | Value   |
+----------------+---------+
| tmp_table_size | 8388608 |
| tmpdir         | /tmp    |
+----------------+---------+
2 rows in set (0.00 sec)

mysql> show variables  like 'temptable_%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
| temptable_max_ram  | 1073741824 |
| temptable_use_mmap | OFF        |
+--------------------+------------+
3 rows in set (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (20.74 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 4     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> set tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (26.31 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 5     |
+-------------------------+-------+
3 rows in set (0.01 sec)

mysql> set global temptable_use_mmap=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables  like 'temptable_%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
| temptable_max_ram  | 1073741824 |
| temptable_use_mmap | ON         |
+--------------------+------------+
3 rows in set (0.00 sec)

mysql> with dummy as (select fk_id,dummy_id from T_Dummy_no_Key) select a.p11_003_1,count(*) total from (select gid,p11_001,p11_003_1,p11_004_1,geom from POC.`p11-10_12-jgd-g_busstop` inner join dummy on gid = dummy.fk_id ) a where a.gid >= 100 group by a.p11_003_1 order by total desc limit 10;
+----------------------------------------+-------+
| p11_003_1                              | total |
+----------------------------------------+-------+
| 千葉交通                               | 34122 |
| 小湊鉄道                               | 30174 |
| 小湊鉄道,小湊鉄道                      | 22748 |
| 京成バス(株)                         | 21902 |
| 成田市                                 | 17108 |
| 匝瑳市                                 | 16826 |
| 野田市                                 | 16450 |
| 千葉中央バス                           | 14006 |
| 小湊鉄道,小湊鉄道,小湊鉄道             | 13630 |
| 旭市                                   | 13442 |
+----------------------------------------+-------+
10 rows in set (26.69 sec)

mysql> show status like '%_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 2     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 7     |
+-------------------------+-------+
3 rows in set (0.01 sec)

mysql>
tmp_table_size=1024
tmp_table_size=8388608

グローバル一時テーブル

今回、追加された上記パラメータとは直性関係ないですが、一時ファイルとして認識しておく必要があるのでこちらも📝しておきます。

グローバル一時テーブルスペース

グローバル一時テーブルスペース (ibtmp1) には、ユーザーが作成した一時テーブルに対する変更のロールバックセグメントが格納され,
停止時または中断された初期化時に削除され、サーバーが起動するたびに再作成されます。

mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.01 sec)

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)

INFORMATION_SCHEMA.FILES :グローバル一時テーブルスペースに関するメタデータの確認

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
*************************** 1. row ***************************
             FILE_ID: 4294967293
           FILE_NAME: ./ibtmp1
           FILE_TYPE: TEMPORARY
     TABLESPACE_NAME: innodb_temporary
       TABLE_CATALOG:
        TABLE_SCHEMA: NULL
          TABLE_NAME: NULL
  LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
              ENGINE: InnoDB
       FULLTEXT_KEYS: NULL
        DELETED_ROWS: NULL
        UPDATE_COUNT: NULL
        FREE_EXTENTS: 2
       TOTAL_EXTENTS: 12
         EXTENT_SIZE: 1048576
        INITIAL_SIZE: 12582912
        MAXIMUM_SIZE: NULL
     AUTOEXTEND_SIZE: 67108864
       CREATION_TIME: NULL
    LAST_UPDATE_TIME: NULL
    LAST_ACCESS_TIME: NULL
        RECOVER_TIME: NULL
 TRANSACTION_COUNTER: NULL
             VERSION: NULL
          ROW_FORMAT: NULL
          TABLE_ROWS: NULL
      AVG_ROW_LENGTH: NULL
         DATA_LENGTH: NULL
     MAX_DATA_LENGTH: NULL
        INDEX_LENGTH: NULL
           DATA_FREE: 6291456
         CREATE_TIME: NULL
         UPDATE_TIME: NULL
          CHECK_TIME: NULL
            CHECKSUM: NULL
              STATUS: NORMAL
               EXTRA: NULL
1 row in set (0.00 sec)

SIZE: グローバル一時テーブルスペースデータファイルのサイズ

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912 /*** 一時テーブルスペースデータファイルの現在のサイズ ***/
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)
SIZE OF GLOBAL TEMP TABLESPACE

カテゴリー: