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を適切に設定する必要あり。グローバルで利用されるTempTableリソースは, temptable_max_ramで上限を設定され、Defaultは1GBになっています。
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される予定
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)
このパラメータの追加による処理方法の変化によって、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 ストレージエンジンによってディスク上に格納され処理されます。
| 1 | UNION ステートメントの評価 |
| 2 | UNION または集計を使用するビューなど一部のビューの評価 |
| 3 | 導出テーブル(サブクエリー等)の評価 |
| 4 | 共通テーブル式の評価 (WITH [CTE]) |
| 5 | サブクエリーまたは準結合の実体化用に作成されたテーブル |
| 6 | ORDER BY 句と異なる GROUP BY 句を含むステートメント、または結合キューの最初のテーブル以外のテーブルのカラムが ORDER BY または GROUP BY に含まれるステートメントの評価。 |
| 7 | ORDER BY と組み合せた DISTINCT の評価には、一時テーブルが必要になる場合があります。 |
| 8 | SQL_SMALL_RESULT 修飾子を使用するクエリーの場合、ディスク上の記憶域を必要とする要素 (後述) もクエリーに含まれていないかぎり、MySQL はインメモリー一時テーブルを使用します。 |
| 9 | 同じテーブルから選択して同じテーブルに挿入する INSERT … SELECT ステートメントを評価するために、MySQL は SELECT の行を保持する内部一時テーブルを作成し、それらの行をターゲットテーブルに挿入します。 |
| 10 | 複数テーブルの UPDATE ステートメントの評価。 |
| 11 | GROUP_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では出てこないストレージエンジンなのですね。
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 ディスク上の内部一時テーブルを使用するかどうかを制御 |
注記には以下の様に記載があるので、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の１GB以上に設定してあげると、パフォーマンスは維持出来るのかと思います。
MySQL 8.0.28以降では、TempTableでもMemoryのどちらでもセッション単位ではtmp_table_sizeを参照して、インスタンス全体でtemptable_max_ramの上限を超えた場合は同様にローカルのInnoDBのtemp_disk_tableに変換される様です。
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>
今回のデータではメモリーから溢れる事も無かったので、設定を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>
グローバル一時テーブル
今回、追加された上記パラメータとは直性関係ないですが、一時ファイルとして認識しておく必要があるのでこちらも📝しておきます。
グローバル一時テーブルスペース (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)