MySQLでは、プロセス全体で使用するためのメモリ領域
グローバルバッファには、InnoDBのデータとインデックスをメモリにキャッシュするためのInnoDBバッファプール
MySQL 8.
スレッドバッファについて
スレッドバッファは主にクエリ処理の一時的なメモリ領域として使用されます。たとえば、以下のようなパラメータがあります。
- sort_
buffer_ size …インデックスのないカラムでORDER BYするときに使用されるソート用バッファ - join_
buffer_ size …ハッシュジョインで使用されるバッファ - select_
into_ buffer_ size …SELECT INTO OUTFILEやSELECT INTO DUMPFILEで使用されるバッファ - tmp_
table_ size …内部一時テーブルで使用されるバッファ
スレッドのメモリ使用量を制御する
MySQL 8.
まずは、global_
をON
に設定します。デフォルトはOFF
なので変更する必要があります。
mysql> SET GLOBAL global_connection_memory_tracking=ON;
最大メモリサイズを設定するパラメータは2つあります。どちらのパラメータもデフォルトは大きな数字で、普通に使っていればメモリを超過することないと思います。
connection_
…1つのスレッドに対する最大メモリサイズを設定memory_ limit global_
…すべてのスレッドに対する総量のメモリサイズを設定connection_ memory_ limit
それでは、connection_
を最小値に設定して試してみましょう。
大きな内部テンポラリテーブルを作成するクエリを実行し、connection_
の値を超過するとエラーになります。
mysql> SET GLOBAL connection_memory_limit=2097152; mysql> SELECT col1,col2 FROM db1.t1 GROUP BY col1,col2; ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 2143392 bytes.
rootユーザやSUPER権限を持ったユーザは制御に含まれません。
スレッドやステートメントのメモリ使用量を見る
MySQL 8.
大きく分けて4種類のテーブルタイプに対して、スレッドごと、ステートメントごとのメモリ使用量が確認できます。
No | テーブル名 | カラム名 |
---|---|---|
1 | threads | CONTROLLED_ MAX_ TOTAL_ MAX_ |
2 | events_ events_ events_ events_ |
MAX_ MAX_ |
3 | prepared_ |
MAX_ MAX_ |
4 | accounts hosts users |
MAX_ MAX_ |
カラム名にはTOTAL
とCONTROLLED
とついた2種類のタイプがあります。TOTAL
とついたカラムはすべてのスレッドバッファのメモリ使用量が表示されます。CONTROLLED
とついたカラムは前述のスレッドのメモリ使用量を制御する
で説明した制御対象のスレッドバッファのメモリ使用量が表示されます。
よって、スレッドのメモリ量の制御はCONTROLLED
のついたカラムのメモリ量が対象です。制御対象かどうかはsetup_
テーブルのFLAGSカラムがcontrolled
になっているものが対象です。
MySQL 8.PROPERTIES='controlled_
となっている以下のスレッドバッファがデフォルトで制御対象になっています。
mysql> SELECT NAME,PROPERTIES,FLAGS FROM performance_schema.setup_instruments WHERE PROPERTIES='controlled_by_default'; +-------------------------------------------------+-----------------------+------------+ | NAME | PROPERTIES | FLAGS | +-------------------------------------------------+-----------------------+------------+ | memory/sql/THD::main_mem_root | controlled_by_default | controlled | | memory/sql/Prepared_statement::infrastructure | controlled_by_default | controlled | | memory/sql/Prepared_statement::main_mem_root | controlled_by_default | controlled | | memory/sql/THD::sp_cache | controlled_by_default | controlled | | memory/sql/sp_head::execute_mem_root | controlled_by_default | controlled | | memory/sql/sp_head::call_mem_root | controlled_by_default | controlled | | memory/sql/test_quick_select | controlled_by_default | controlled | | memory/sql/Partition::prune_exec | controlled_by_default | controlled | | memory/sql/Blob_mem_storage::storage | controlled_by_default | controlled | | memory/sql/Filesort_info::merge | controlled_by_default | controlled | | memory/sql/Filesort_info::record_pointers | controlled_by_default | controlled | | memory/sql/Filesort_buffer::sort_keys | controlled_by_default | controlled | | memory/sql/IndexRangeScanIterator::mrr_buf_desc | controlled_by_default | controlled | | memory/sql/READ_INFO | controlled_by_default | controlled | | memory/sql/TABLE::sort_io_cache | controlled_by_default | controlled | | memory/sql/Unique::sort_buffer | controlled_by_default | controlled | | memory/sql/Unique::merge_buffer | controlled_by_default | controlled | | memory/sql/bison_stack | controlled_by_default | controlled | | memory/sql/hash_join | controlled_by_default | controlled | | memory/temptable/physical_disk | controlled_by_default | controlled | | memory/temptable/physical_ram | controlled_by_default | controlled | +-------------------------------------------------+-----------------------+------------+
たとえば、memory/
はソート用のバッファを表しています。
また、以下のようにsetup_
テーブルを更新することで、明示的に制御対象のスレッドバッファを増やしたり減らしたりできます。
mysql> UPDATE performance_schema.setup_instruments SET FLAGS="controlled" WHERE NAME='memory/sql/NET::buff';
しかし、どの処理がNAME列の値に該当するのかは、ソースコードに精通していないとわからないのが現状です。
ちなみに、FLAGSをnullに設定すると制御対象から外れます。よって、デフォルト設定されているすべてをnullにすると制御対象はなくなり、先ほどのクエリも通るようになります。
mysql> SELECT f1,f2 FROM db1.t1 GROUP BY f2,f1; ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 3184848 bytes. mysql> UPDATE performance_schema.setup_instruments SET FLAGS=null WHERE PROPERTIES='controlled_by_default'; Query OK, 21 rows affected (0.01 sec) Rows matched: 21 Changed: 21 Warnings: 0 mysql> SELECT f1,f2 FROM db1.t1 GROUP BY f2,f1; 43008 rows in set (0.40 sec)
それぞれのカラムの用途について説明します。
- TOTAL_
MEMORY …現在のメモリ使用量 - MAX_
TOTAL_ MEMORY …最大メモリ使用量 - CONTROLLED_
MEMORY …現在の制御対象のメモリ使用量 - MAX_
CONTROLLED_ MEMORY …制御対象の最大メモリ使用量 - MAX_
SESSION_ TOTAL_ MEMORY …属するスレッドの最大メモリ使用量 - MAX_
SESSION_ CONTROLLED_ MEMORY …属するスレッドの制御対象の最大メモリ使用量
では、見てみましょう。
1.のthreadsテーブルでは、現在接続中のスレッドのメモリ使用量が確認できます。threadsテーブルについては、第38回 performance_
mysql> SELECT THREAD_ID,PROCESSLIST_ID,CONTROLLED_MEMORY,MAX_CONTROLLED_MEMORY,TOTAL_MEMORY,MAX_TOTAL_MEMORY,format_bytes(TOTAL_MEMORY) FROM performance_schema.threads where PROCESSLIST_ID=12; +-----------+----------------+-------------------+-----------------------+--------------+------------------+----------------------------+ | THREAD_ID | PROCESSLIST_ID | CONTROLLED_MEMORY | MAX_CONTROLLED_MEMORY | TOTAL_MEMORY | MAX_TOTAL_MEMORY | format_bytes(TOTAL_MEMORY) | +-----------+----------------+-------------------+-----------------------+--------------+------------------+----------------------------+ | 53 | 12 | 39056 | 50320 | 56752 | 89472 | 55.42 KiB | +-----------+----------------+-------------------+-----------------------+--------------+------------------+----------------------------+
このように確認できます。format_
関数を挟むことで、より把握しやすくなります。
2.のevents_
mysql> SELECT SQL_TEXT,MAX_TOTAL_MEMORY from performance_schema.events_statements_history WHERE SQL_TEXT LIKE 'SELECT * FROM t0 ORDER BY id2'; +-------------------------------+------------------+ | SQL_TEXT | MAX_TOTAL_MEMORY | +-------------------------------+------------------+ | SELECT * FROM t0 ORDER BY id2 | 1893196 | +-------------------------------+------------------+
3.のprepared_
4.のaccounts,hosts,usersテーブルでは、MAX_
やMAX_
からメモリ使用量が確認できます。以下の例では、過去のユーザー名 user1
の接続において使用されたことのある最大メモリ使用量を表示しています。
mysql> SELECT * from performance_schema.users WHERE USER='user1'; +----------+---------------------+-------------------+-------------------------------+--------------------------+ | USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS | MAX_SESSION_CONTROLLED_MEMORY | MAX_SESSION_TOTAL_MEMORY | +----------+---------------------+-------------------+-------------------------------+--------------------------+ | user1 | 2 | 5 | 8428032 | 9056057 | +----------+---------------------+-------------------+-------------------------------+--------------------------+
稼働中のMySQLに対してスレッドのメモリ使用量を制御したい場合は、各テーブルのCONTROLLED
カラムの値を参考にすると良いでしょう。
まとめ
今回は、スレッドのメモリ使用量の制御と確認方法について紹介しました。筆者としては設定したメモリサイズを超過するとエラーになってしまうため、導入は難しいと感じます。
しかし、MySQL 8.