MySQLのユーザ接続の使用メモリの上限を設定する

MySQL 8.0
この記事は最終更新から2年以上経過しています。内容が古くなっている可能性があります。

はじめに

先月、MySQLのバージョン 8.0.28 がリリースされました。
MySQL8.0 からは、マイナーバージョンアップであっても積極的な機能追加が行われている傾向が見られる為、MySQLをご使用の方は、リリースノートを是非、確認してみて下さい。
リリースノートは以下となります。

今回のリリースでもいくつかの新機能が追加されておりますが、今回は、ユーザ接続のメモリの監視、及びメモリ使用量の上限設定が可能になったという機能を取り上げたいと思います。

This release introduces monitoring and limiting of memory allocation on a global and per-user basis. You can now observe the total memory consumed by all user connections by checking the value of the Global_connection_memory status variable, which must be enabled by setting global_connection_memory_tracking = 1.
This total does not include memory used by system processes, or by the MySQL root user. It also does not take into account memory used by the InnoDB buffer pool.
You can control indirectly how often the status variable is updated by adjusting connection_memory_chunk_size; Global_connection_memory is updated only when the total memory usage varies by more than this amount.
You can specify limits on resource consumption per user connection by setting connection_memory_limit; any user whose memory usage exceeds this amount cannot issue additional queries. You can also impose a global memory limit by setting global_connection_memory_limit. Whenever Global_connection_memory exceeds the global limit, no regular users can issue new queries requiring memory usage. System users such as MySQL root are not bound by these limits.

目次

ユーザ接続の合計メモリ使用量の監視

ユーザ接続が使用しているメモリの合計使用量を確認する方法を見ていきます。
このメモリ使用量の中には、ユーザの接続スレッドが使用しているメモリ量のみが含まれますので、InnoDBバッファプールや、システムスレッドが使用するメモリは含まれません。

まずは、ユーザ接続のメモリ使用量の監視をするにあたり、以下2つのパラメータの調整が必要となります。

  • global_connection_memory_tracking
    使用メモリの算出を行うかどうかを設定するパラメータとなります。
    デフォルトでは、 無効となる為、ユーザ接続が使用しているメモリの合計使用量を監視したい場合には、明示的に有効にする必要があります。
    また、この変数は動的に設定する事が可能ですので、MySQLを再起動せずに、設定の変更を行うことができます。

  • connection_memory_chunk_size
    合計メモリ使用量がこの設定値を超えて変動した場合に、メモリの合計使用量をあらわすステータス変数の値が更新されます。
    デフォルト値は 8192 バイトとなりますが、小さな変動でもステータス変数に反映させたい場合には、この設定値を小さくする事で対応が可能となります。

また、上述しておりますメモリの合計使用量をあらわすステータス変数は Global_connection_memory となります。
ユーザの接続スレッドが使用しているメモリの合計サイズを確認したい場合には、この変数を監視するようにしましょう。

ユーザ接続のメモリ使用量の上限設定

次は、ユーザ接続のメモリ使用量の上限設定について見ていきます。
上限設定については、2つのパラメータで制御することができます。

上記どちらの上限設定でも root アカウントに関しては適用されませんので注意が必要です。

また、上限設定をする際は、前提としまして前述しております global_connection_memory_tracking を有効にしておく必要があります。
それでは、connection_memory_limit で上限設定の確認をしてみましょう。
手っ取り早く MySQL Test Framework に含まれる以下の内容を流用して確認します。

https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/mysql-test/include/mem_cnt_common.inc

まずは、検証用のユーザ(user1)を作成し、blog.t1 テーブルを作成してデータを登録します。

その後、 global_connection_memory_tracking を有効にして、connection_memory_limit の値を小さめに設定しておきます。

この状態で、検証用のユーザ user1 でログインし、以下のようにSQLを実行します。

connection_memory_limit の値を超過してメモリを確保しようとした為にエラーとなり、接続が切断されたことが分かります。
エラーログにも以下のように出力されています。

次は全てのユーザ接続の合計メモリの上限を設定してみます。
connection_memory_limit の設定をデフォルト値に戻しglobal_connection_memory_limit の設定を小さめに設定します。

次に以下のような簡単なシェルスクリプトを用意します。
検証用ユーザ user1 で、バックグラウンドで2接続で同時にSQLを実行し、2つ目の接続で合計メモリ使用量の上限に達する想定です。

それでは実行してみます。

1接続ではSQLの実行が正常にできるのが、想定通り同時に2接続で実行すると、global_connection_memory_limit の制限により、エラーとなる事が分かります。

まとめ

MySQLのメモリ関連のトラブルもたまに聞かれますが、ユーザ接続で使用するメモリに関しては上限設定ができるようになった事で、活用できる場面もあるのではないかと思います。
冒頭で案内していますリリースノートを見ると他にも新機能や、廃止になった機能もありますので、バージョンアップを検討される際は、リリースノードの確認を怠らないようにしましょう。

スマートスタイルTECHブログについて

スマートスタイルTECHブログでは、日頃MySQLのサポート業務に従事している有資格者で構成された技術サポートチームがMySQLに関する技術情報を発信しています。データベースのお困りごとはお気軽にご相談下さい。

よかったらシェアしてね!
  • URLをコピーしました!
目次