gihyo.jpサイトのロゴ

MySQL道普請便り

第183回MySQLスレッドのメモリ使用量の制御と確認方法

2022-11-01

MySQLでは、プロセス全体で使用するためのメモリ領域（グローバルバッファ）と、スレッドごとに確保されるメモリ領域（スレッドバッファ）があります。

グローバルバッファには、InnoDBのデータとインデックスをメモリにキャッシュするためのInnoDBバッファプール（innodb_buffer_pool_size パラメータ）や、TempTableストレージエンジンに割り当てるメモリ領域（temptable_max_ram パラメータ）などがあります。

MySQL 8.0.28からスレッドが使用できるスレッドバッファの合計サイズの制御が、そしてMySQL 8.0.31からはスレッドごとのスレッドバッファの合計サイズがperformance_schemaのテーブルから簡単に確認できるようになりました。

スレッドバッファについて

スレッドバッファは主にクエリ処理の一時的なメモリ領域として使用されます。たとえば、以下のようなパラメータがあります。

  • sort_buffer_size …インデックスのないカラムでORDER BYするときに使用されるソート用バッファ
  • join_buffer_size …ハッシュジョインで使用されるバッファ
  • select_into_buffer_size …SELECT INTO OUTFILEやSELECT INTO DUMPFILEで使用されるバッファ
  • tmp_table_size …内部一時テーブルで使用されるバッファ

スレッドのメモリ使用量を制御する

MySQL 8.0.28から、スレッドのメモリ使用量を制御できます。

まずは、global_connection_memory_trackingをONに設定します。デフォルトはOFFなので変更する必要があります。

mysql> SET GLOBAL global_connection_memory_tracking=ON;

最大メモリサイズを設定するパラメータは2つあります。どちらのパラメータもデフォルトは大きな数字で、普通に使っていればメモリを超過することないと思います。

  • connection_memory_limit …1つのスレッドに対する最大メモリサイズを設定
  • global_connection_memory_limit …すべてのスレッドに対する総量のメモリサイズを設定

それでは、connection_memory_limitを最小値に設定して試してみましょう。

大きな内部テンポラリテーブルを作成するクエリを実行し、connection_memory_limitの値を超過するとエラーになります。

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.0.31から、いくつかのperformance_schemaのテーブルにメモリ使用量を確認できるカラムが追加されました。

大きく分けて4種類のテーブルタイプに対して、スレッドごと、ステートメントごとのメモリ使用量が確認できます。

No テーブル名 カラム名
1 threads CONTROLLED_MEMORY
MAX_CONTROLLED_MEMORY
TOTAL_MEMORY
MAX_TOTAL_MEMORY
2 events_statements_current
events_statements_history
events_statements_history_long
events_statements_summary関連		 MAX_CONTROLLED_MEMORY
MAX_TOTAL_MEMORY
3 prepared_statements_instances MAX_CONTROLLED_MEMORY
MAX_TOTAL_MEMORY
4 accounts
hosts
users		 MAX_SESSION_CONTROLLED_MEMORY
MAX_SESSION_TOTAL_MEMORY

カラム名にはTOTALとCONTROLLEDとついた2種類のタイプがあります。TOTALとついたカラムはすべてのスレッドバッファのメモリ使用量が表示されます。CONTROLLEDとついたカラムは前述のスレッドのメモリ使用量を制御するで説明した制御対象のスレッドバッファのメモリ使用量が表示されます。

よって、スレッドのメモリ量の制御はCONTROLLEDのついたカラムのメモリ量が対象です。制御対象かどうかはsetup_instrumentsテーブルのFLAGSカラムがcontrolledになっているものが対象です。

MySQL 8.0.31では、PROPERTIES='controlled_by_default'となっている以下のスレッドバッファがデフォルトで制御対象になっています。

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/sql/Filesort_buffer::sort_keysはソート用のバッファを表しています。

また、以下のようにsetup_instrumentsテーブルを更新することで、明示的に制御対象のスレッドバッファを増やしたり減らしたりできます。

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_schemaのthreadsテーブルをご確認ください。

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_bytes()関数を挟むことで、より把握しやすくなります。

2.のevents_statements系テーブルではステートメント単位の最大メモリ使用量が確認できます。

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_statements_instancesテーブルは、プリペアステートメントごとに使用された最大メモリ使用量を確認することができます。

4.のaccounts,hosts,usersテーブルでは、MAX_SESSION_CONTROLLED_MEMORYやMAX_SESSION_TOTAL_MEMORYからメモリ使用量が確認できます。以下の例では、過去のユーザー名 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.0.31で追加されたperformance_schemaのカラムにより、スレッドのメモリ使用量の制限を目的としなくても、そのスレッドのメモリ使用量やステートメントのメモリ使用量を簡単に確認できるようになりました。パフォーマンスチューニングや、サーバのメモリ使用率が高いときなどに確認できるので、こちらは使いどころがあると思います。

おすすめ記事

記事・ニュース一覧