MySQL 8.0からリソースグループが導入されましたので、検証してみたいと思います。
リソースグループ
https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html
リソースグループとは
MySQLスレッドが使用できるサーバリソースに制限をかける仕組みとなります。
リソースグループは長時間実行されるトランザクションのリソースを制限したり、異なる処理をCPUのコアに割当ててスループットを安定させるといった意図で利用します。
今のところ制御できるのは個別のCPUコアへのスレッドの割当、スレッドごとのCPU利用優先度の指定です。
リソースグループの作成
まずは、リソースグループを作成します。
CREATE RESOURCE GROUP
https://dev.mysql.com/doc/refman/8.0/en/create-resource-group.html
実行のためには、RESOURCE_GROUP_ADMIN 権限が必要です。
|
1 |
MySQL> GRANT RESOURCE_GROUP_ADMIN ON *.* TO username@`hostname`; |
CREATE RESOURCE GROUPの構文は以下のとおりです。
|
1 2 3 4 5 |
CREATE RESOURCE GROUP group_name TYPE = {SYSTEM|USER} [VCPU [=] vcpu_spec [, vcpu_spec] ...] [THREAD_PRIORITY [=] N] [ENABLE|DISABLE] |
以下は作成例となります。
Batchというリソースグループを作成しています。
|
1 2 3 4 |
CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 0 THREAD_PRIORITY = 10; |
TYPEとは、適用できるTHREAD_PRIORITYの違いです。
THREAD_PRIORITYとは、その名の通り適用するスレッドの処理の優先度を決める値です。
-20から19までの値を取ることができ、マイナス方向に優先度が高くなります(Linuxのnice値と同じです)
TYPE=USERは
0から19 までの優先度を割り当てる事ができ、TYPE=SYSTEMは
-20から0 までの優先度を割り当てる事ができます。
THREAD_PRIORITYのデフォルトはUSER/SYSTEMかかわらず0です。
なおTHREAD_PRIORITYを設定するためには、事前にCAP_SYS_NICEケイパビリティの設定が必須です。
|
1 2 3 |
$ sudo setcap cap_sys_nice+ep /sbin/mysqld $ getcap /sbin/mysqld /sbin/mysqld = cap_sys_nice+ep |
その他の制限については、以下のURLをご確認ください。
Limitations
https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html
弊社検証時には、ケイパビリティを設定しない状態でTHREAD_PRIORITYを0以外に設定するとWarningが出力されるようでした。
|
1 2 3 4 5 6 7 8 |
MySQL> CREATE RESOURCE GROUP TEST_RG TYPE = USER THREAD_PRIORITY = 19; Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL > show warnings; +---------+------+-------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------+ | Warning | 3659 | Attribute thread_priority is ignored (using default value). | +---------+------+-------------------------------------------------------------+ |
VCPUは使用するCPUコアのスレッド番号です。
0-2のように範囲で指定することも可能ですし、1,3-5のように、CSV形式で個別に指定も可能です。
ENABLE、DISABLEは、RESOURCE GROUPの作成時点でグループを有効にするかどうかです(デフォルトはENABLE)。
ENABLEにすると、スレッドへのグループ割当が可能となり、DISABLEにすると、スレッドへのグループ割当が出来ない状態となります。
リソースグループ の確認
INFORMATION_SCHEMA.RESOURCE_GROUPSで登録済みのリソースグループを確認します。
すでに2つのデフォルトのリソースグループが登録されています。
先程作成したBatchも確認する事ができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
MySQL> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS\G *************************** 1. row *************************** RESOURCE_GROUP_NAME: USR_default RESOURCE_GROUP_TYPE: USER RESOURCE_GROUP_ENABLED: 1 VCPU_IDS: 0-1 THREAD_PRIORITY: 0 *************************** 2. row *************************** RESOURCE_GROUP_NAME: SYS_default RESOURCE_GROUP_TYPE: SYSTEM RESOURCE_GROUP_ENABLED: 1 VCPU_IDS: 0-1 THREAD_PRIORITY: 0 *************************** 3. row *************************** RESOURCE_GROUP_NAME: Batch RESOURCE_GROUP_TYPE: USER RESOURCE_GROUP_ENABLED: 1 VCPU_IDS: 0 THREAD_PRIORITY: 10 |
リソースグループの割当
作成し、有効化されたリソースグループは、それだけでは効果を発揮しません。
実行されたユーザースレッドへの割当が必要です。
SET RESOURCE GROUP
https://dev.mysql.com/doc/refman/8.0/en/set-resource-group.html
SET RESOURCE GROUPの実行には RESOURCE_GROUP_ADMIN/RESOURCE_GROUP_USER 動的権限のいずれかが必要です。
以下では、現在の接続スレッドにリソースグループを適用します。
|
1 |
MySQL> SET RESOURCE GROUP Batch; |
以下ではThread Idを指定して適用しています。
|
1 |
MySQL> SET RESOURCE GROUP Batch for 93; |
注意点として混同しやすいのが、Thread IdはConnection Idとは異なるものである点です。
例えば、以下で確認できるのはConnection Idです。
|
1 2 3 |
MySQL> status : Connection id: 49 |
Thread IdはConnection Idを基に検索できます。
|
1 2 3 |
MySQL> SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 49\G *************************** 1. row *************************** THREAD_ID: 93 |
検証
sysbenchのoltp_common.luaをカスタムして、簡単な検証を行いました。
/usr/share/sysbench/oltp_common.lua
|
1 2 3 4 5 6 7 8 |
77 , 78 resgroup = 79 {"Name of resource group to use by all threads", ""} : 366 if (sysbench.opt.resgroup ~= "") then 367 con:query('SET RESOURCE GROUP ' .. sysbench.opt.resgroup) 368 end |
上記では、resgroupパラメータを追加し、sysbenchの接続時に有効化する処理を追加しました。
sysbenchのパラメータは以下の通りです。
bench.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox --mysql-port=8011 --mysql-host=127.0.0.1 --mysql_storage_engine=innodb --table_size=10000 --tables=10 --mysql-db=test --time=10000 --report-interval=1 --threads=10 |
sysbenchは以下のように実行します。
|
1 |
$ sysbench $(cat bench.cnf) --resgroup=Batch oltp_read_only run |
Batchリソースグループは
VCPU=0としていますので、0番目のコアしか使用しないはずです。
sysbenchを実行すると、その様子が確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ mpstat 1 -P ALL : 10時34分40秒 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle 10時34分41秒 all 41.33 0.00 11.22 0.00 0.00 3.06 0.51 0.00 0.00 43.88 10時34分41秒 0 76.77 0.00 18.18 0.00 0.00 4.04 1.01 0.00 0.00 0.00 10時34分41秒 1 4.17 0.00 5.21 0.00 0.00 1.04 0.00 0.00 0.00 89.58 10時34分41秒 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle 10時34分42秒 all 41.75 0.00 11.86 0.00 0.00 2.58 0.00 0.00 0.00 43.81 10時34分42秒 0 77.23 0.00 17.82 0.00 0.00 4.95 0.00 0.00 0.00 0.00 10時34分42秒 1 4.21 0.00 5.26 0.00 0.00 1.05 0.00 0.00 0.00 89.47 |
意図したとおりに動作しました。
リソースグループの定義変更
リソースグループは、割当中であっても定義を変更する事ができます。
定義変更はリソースグループに所属している接続中のスレッドに即時反映されます。
ALTER RESOURCE GROUP
https://dev.mysql.com/doc/refman/8.0/en/alter-resource-group.html
定義変更には、 RESOURCE_GROUP_ADMIN 動的権限が必要です。
今度は、VCPU = 1に定義を変更してみます。
|
1 |
ALTER RESOURCE GROUP Batch VCPU = 1; |
sysbenchを実行すると、1のコアに負荷が偏っている事が確認できました。
|
1 2 3 4 5 6 7 8 9 |
10時39分11秒 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle 10時39分12秒 all 41.03 0.00 11.28 0.00 0.00 3.08 0.00 0.00 0.00 44.62 10時39分12秒 0 4.17 0.00 4.17 0.00 0.00 1.04 0.00 0.00 0.00 90.62 10時39分12秒 1 76.77 0.00 18.18 0.00 0.00 5.05 0.00 0.00 0.00 0.00 10時39分12秒 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle 10時39分13秒 all 40.00 0.00 12.31 0.00 0.00 3.59 0.51 0.00 0.00 43.59 10時39分13秒 0 4.12 0.00 5.15 0.00 0.00 1.03 1.03 0.00 0.00 88.66 10時39分13秒 1 74.26 0.00 19.80 0.00 0.00 5.94 0.00 0.00 0.00 0.00 |
すでにユーザーに割り当てられているリソースグループを無効化するには以下のコマンドを実行します。
|
1 |
MySQL> ALTER RESOURCE GROUP Batch DISABLE FORCE; |
無効化したリソースグループに所属していたスレッドは、デフォルトのリソースグループに変更されます。
無効化したリソースグループを再度SETしようとするとエラーが発生しますので、ご注意ください。
|
1 2 |
MySQL> SET RESOURCE GROUP Batch; ERROR 3657 (HY000): Resource group Batch is disabled. |
またリソースグループを有効化する事は可能ですが、一度リソースグループが変更されたスレッドは、再度SETコマンドを実行しないと、以前割り当てていたリソースグループに移動しません。
|
1 |
ALTER RESOURCE GROUP Batch ENABLE; |
リソースグループの削除
不要なリソースグループは削除します。
DROP RESOURCE GROUP
https://dev.mysql.com/doc/refman/8.0/en/drop-resource-group.html
スレッドによって利用中のリソースグループは削除や無効化をすることができません。
|
1 2 |
MySQL> DROP RESOURCE GROUP Batch; ERROR 3656 (HY000): Resource group Batch is busy. |
その場合、FORCE句をつけて実行することで削除が可能です。
|
1 2 |
MySQL> DROP RESOURCE GROUP Batch FORCE; Query OK, 0 rows affected (0.03 sec) |
無効化の際と同様に、利用中のスレッドはデフォルトリソースグループに移動します。
THREAD_PRIORITYの検証
THREAD_PRIORITYが異なる2つのリソースグループを作成し、それぞれのリソースグループを適用したsysbenchを並列で実行した時に
どのような影響があるか検証しました。
|
1 2 3 4 5 6 7 8 9 |
MySQL> CREATE RESOURCE GROUP HIGH_PRIO TYPE = USER VCPU = 0 THREAD_PRIORITY = 0; MySQL> CREATE RESOURCE GROUP LOW_PRIO TYPE = USER VCPU = 0 THREAD_PRIORITY = 19; |
以下のsysbenchはほぼ同時に実行しています。
HIGH_PRIOグループの処理は通常時のTPS/QPSとなりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$ sysbench $(cat bench.cnf) --resgroup=HIGH_PRIO --time=120 oltp_read_only run : [ 1s ] thds: 10 tps: 217.12 qps: 3614.43 (r/w/o: 3160.26/0.00/454.17) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00 [ 2s ] thds: 10 tps: 256.17 qps: 4062.76 (r/w/o: 3550.41/0.00/512.35) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00 [ 3s ] thds: 10 tps: 277.01 qps: 4466.12 (r/w/o: 3912.11/0.00/554.02) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 10 tps: 277.02 qps: 4383.29 (r/w/o: 3829.25/0.00/554.04) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 [ 5s ] thds: 10 tps: 258.96 qps: 4175.35 (r/w/o: 3657.43/0.00/517.92) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00 [ 6s ] thds: 10 tps: 274.95 qps: 4405.21 (r/w/o: 3855.31/0.00/549.90) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 7s ] thds: 10 tps: 277.10 qps: 4413.60 (r/w/o: 3859.40/0.00/554.20) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 8s ] thds: 10 tps: 269.88 qps: 4349.09 (r/w/o: 3809.33/0.00/539.76) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 [ 9s ] thds: 10 tps: 280.09 qps: 4450.41 (r/w/o: 3890.24/0.00/560.18) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 10s ] thds: 10 tps: 272.02 qps: 4351.29 (r/w/o: 3808.25/0.00/543.04) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00 |
一方、LOW_PRIOグループの処理は、非常に遅くなりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$ sysbench $(cat bench.cnf) --resgroup=LOW_PRIO --time=120 oltp_read_only run : [ 1s ] thds: 10 tps: 4.98 qps: 191.18 (r/w/o: 161.30/0.00/29.87) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00 [ 2s ] thds: 10 tps: 8.01 qps: 149.12 (r/w/o: 133.11/0.00/16.01) lat (ms,95%): 1708.63 err/s: 0.00 reconn/s: 0.00 [ 3s ] thds: 10 tps: 19.99 qps: 282.89 (r/w/o: 242.90/0.00/39.98) lat (ms,95%): 2585.31 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 10 tps: 9.01 qps: 160.11 (r/w/o: 142.10/0.00/18.01) lat (ms,95%): 3208.88 err/s: 0.00 reconn/s: 0.00 [ 5s ] thds: 10 tps: 0.00 qps: 0.97 (r/w/o: 0.97/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00 [ 6s ] thds: 10 tps: 3.08 qps: 48.30 (r/w/o: 42.13/0.00/6.17) lat (ms,95%): 3095.38 err/s: 0.00 reconn/s: 0.00 [ 7s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00 [ 8s ] thds: 10 tps: 1.00 qps: 16.00 (r/w/o: 14.00/0.00/2.00) lat (ms,95%): 5409.26 err/s: 0.00 reconn/s: 0.00 [ 9s ] thds: 10 tps: 1.00 qps: 19.00 (r/w/o: 17.00/0.00/2.00) lat (ms,95%): 6360.91 err/s: 0.00 reconn/s: 0.00 [ 10s ] thds: 10 tps: 0.00 qps: 16.00 (r/w/o: 16.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00 : |
LOW_PRIOの処理は接続時の処理も、HIGH_PRIOグループの影響でなかなか完了しなかったために、HIGH_PRIOグループよりも、その後のメインの処理完了が遅くなりましたが、HIGH_PRIOグループの処理が完了した時点で通常時のパフォーマンスに戻っている様子が確認できました。
|
1 2 3 4 5 6 7 8 9 10 |
[ 108s ] thds: 10 tps: 0.00 qps: 12.00 (r/w/o: 12.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00 [ 109s ] thds: 10 tps: 5.00 qps: 73.99 (r/w/o: 63.99/0.00/10.00) lat (ms,95%): 2449.36 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 10 tps: 3.00 qps: 56.01 (r/w/o: 50.01/0.00/6.00) lat (ms,95%): 5033.35 err/s: 0.00 reconn/s: 0.00 [ 111s ] thds: 10 tps: 6.00 qps: 89.99 (r/w/o: 77.99/0.00/12.00) lat (ms,95%): 6360.91 err/s: 0.00 reconn/s: 0.00 [ 112s ] thds: 10 tps: 272.06 qps: 4285.99 (r/w/o: 3741.86/0.00/544.13) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00 [ 113s ] thds: 10 tps: 299.97 qps: 4813.51 (r/w/o: 4213.57/0.00/599.94) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00 [ 114s ] thds: 10 tps: 298.00 qps: 4780.07 (r/w/o: 4184.06/0.00/596.01) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00 [ 115s ] thds: 10 tps: 295.79 qps: 4739.63 (r/w/o: 4148.05/0.00/591.58) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00 [ 116s ] thds: 10 tps: 299.23 qps: 4782.65 (r/w/o: 4184.19/0.00/598.46) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00 [ 117s ] thds: 10 tps: 301.00 qps: 4828.93 (r/w/o: 4226.94/0.00/601.99) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00 |
まとめ
スレッドが使用するリソースをMySQLのレベルで細かく制御できることは、安定稼働のために役立つものと思います。
現在は比較的シンプルな制御のみですが、今後機能が拡充されていくことを期待したいと思います。
長時間バッチが実行されてリソースが専有されてしまうといった点でお困りの方はぜひMySQL 8.0を導入して、リソースグループを使ってみてはいかがでしょうか。
