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

2022年2月17日

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

はじめに

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

Changes in MySQL 8.0.28（2022-01-18, General Availability）

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

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 となります。
ユーザの接続スレッドが使用しているメモリの合計サイズを確認したい場合には、この変数を監視するようにしましょう。

mysql> SHOW GLOBAL STATUS LIKE 'Global_connection_memory';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Global_connection_memory | 1122912 |
+--------------------------+---------+

mysql> SHOW GLOBAL STATUS LIKE 'Global_connection_memory';

+--------------------------+---------+

| Variable_name | Value |

+--------------------------+---------+

| Global_connection_memory | 1122912 |

+--------------------------+---------+

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

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

connection_memory_limit
1ユーザ接続が使用可能な最大メモリサイズ。
global_connection_memory_limit
全ユーザ接続で使用可能な最大合計メモリサイズ。

上記どちらの上限設定でも 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 テーブルを作成してデータを登録します。

mysql> CREATE USER 'user1'@localhost IDENTIFIED BY 'MySQL8.0';
mysql> GRANT ALL ON blog.* TO 'user1'@localhost;

mysql> CREATE DATABASE blog;
mysql> USE blog;

mysql> CREATE TABLE t1 (f1 LONGTEXT , f2  INTEGER);

mysql> INSERT INTO t1 VALUES 
> (REPEAT('a', 1024), 0), (REPEAT('b', 1024), 1),
> (REPEAT('c', 1024), 2), (REPEAT('d', 1024), 3),
> (REPEAT('e', 1024), 4), (REPEAT('f', 1024), 5);

mysql> INSERT INTO t1 SELECT f1, f2 + 6 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 12 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 24 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 48 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 96 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 192 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 384 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 768 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 1536 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 3072 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 6144 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 12288 FROM t1;

mysql> CREATE USER 'user1'@localhost IDENTIFIED BY 'MySQL8.0';

mysql> GRANT ALL ON blog.* TO 'user1'@localhost;

mysql> CREATE DATABASE blog;

mysql> USE blog;

mysql> CREATE TABLE t1 (f1 LONGTEXT , f2 INTEGER);

mysql> INSERT INTO t1 VALUES

> (REPEAT('a', 1024), 0), (REPEAT('b', 1024), 1),

> (REPEAT('c', 1024), 2), (REPEAT('d', 1024), 3),

> (REPEAT('e', 1024), 4), (REPEAT('f', 1024), 5);

mysql> INSERT INTO t1 SELECT f1, f2 + 6 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 12 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 24 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 48 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 96 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 192 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 384 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 768 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 1536 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 3072 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 6144 FROM t1;

mysql> INSERT INTO t1 SELECT f1, f2 + 12288 FROM t1;

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

mysql> SET GLOBAL global_connection_memory_tracking = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL connection_memory_limit = 1024 * 1024 * 2;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'connection_memory_limit';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| connection_memory_limit | 2097152 |
+-------------------------+---------+

mysql> SET GLOBAL global_connection_memory_tracking = ON;

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL connection_memory_limit = 1024 * 1024 * 2;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'connection_memory_limit';

+-------------------------+---------+

| Variable_name | Value |

+-------------------------+---------+

| connection_memory_limit | 2097152 |

+-------------------------+---------+

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

mysql> SELECT COUNT(*) FROM blog.t1 GROUP By CONCAT(f1,f2);
ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 2136272 bytes.

1 2	mysql> SELECT COUNT(*) FROM blog.t1 GROUP By CONCAT(f1,f2); ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 2136272 bytes.

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

略…) [ERROR] [MY-013833] [Server] Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 2136272 bytes.

1	略…) [ERROR] [MY-013833] [Server] Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 2136272 bytes.

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

mysql> SET GLOBAL connection_memory_limit = default;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL global_connection_memory_limit = 1024 * 1024 * 32;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'global_connection_memory_limit';
+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| global_connection_memory_limit | 33554432 |
+--------------------------------+----------+

mysql> SET GLOBAL connection_memory_limit = default;

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL global_connection_memory_limit = 1024 * 1024 * 32;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'global_connection_memory_limit';

+--------------------------------+----------+

| Variable_name | Value |

+--------------------------------+----------+

| global_connection_memory_limit | 33554432 |

+--------------------------------+----------+

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

# cat verification.sh
#!/bin/bash
for i in {1..2} ; do
  mysql -u user1 -pMySQL8.0 blog -e "SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1;" 1>/dev/null &
done

# cat verification.sh

#!/bin/bash

for i in {1..2} ; do

mysql -u user1 -pMySQL8.0 blog -e "SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1;" 1>/dev/null &

done

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

# ./verification.sh
mysql: mysql: [Warning] Using a password on the command line interface can be insecure.
[Warning] Using a password on the command line interface can be insecure.
ERROR 4081 (HY000) at line 1: Connection closed. Global connection memory limit 33554432 bytes exceeded. Consumed 35933184 bytes.

# ./verification.sh

mysql: mysql: [Warning] Using a password on the command line interface can be insecure.

[Warning] Using a password on the command line interface can be insecure.

ERROR 4081 (HY000) at line 1: Connection closed. Global connection memory limit 33554432 bytes exceeded. Consumed 35933184 bytes.

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

まとめ

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