Posted inMySQL

MySQL 8 Resource Groups：重いクエリで本番システム全体をダウンさせないために

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

クイックスタート：5分で重いクエリのCPUを制限する方法

レポート用のSQLクエリが突然暴走し、CPU使用率が100%に跳ね上がってウェブサイト全体がフリーズしてしまったことはありませんか？急ぎの場合は、次の3つのステップでそれらを隔離できます。サーバーに4つのコア（0, 1, 2, 3）がある場合、重いタスクをコア3に閉じ込め、コア0, 1, 2をユーザーへのサービス提供用に残しておきます。

ステップ1：Resource Groupの作成

CREATE RESOURCE GROUP batch_processing
TYPE = USER
VCPU = 3
THREAD_PRIORITY = 19;

このコマンドは、コア3でのみ実行されるbatch_processingというグループを作成します。優先度19は最低レベルであり、他の重要なプロセスとリソースを奪い合わないようにします。

ステップ2：グループが有効であることを確認する

SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS;

ステップ3：SQL文に直接適用する

SELECTキーワードの直後に Optimizer Hint を挿入します：

SELECT /*+ RESOURCE_GROUP(batch_processing) */ 
COUNT(*), category_id 
FROM heavy_sales_data 
GROUP BY category_id;

わずか数行のコードで、このクエリは「制限エリア」に閉じ込められ、他のユーザーのリソースを侵害する機会を失います。

なぜ Resource Groups がデータベースの救世主となるのか？

バージョン8.0以前は、MySQLの管理者はMySQLパフォーマンスチューニングにおいてほぼ無力でした。MySQLはマルチスレッド（multi-threaded）メカニズムを使用しているため、各接続はスレッドであり、OSがそのスレッドをどこで実行するかを完全に決定します。実際、数百万件のレコードをスキャンする不適切なクエリが1つあるだけで、ユーザー情報の取得のような単純なクエリが数十秒待ちになることがよくあります。

Resource Groupsは、処理スレッドを論理グループにまとめることで、この状況を一変させます。以下の2つの鉄則を強制する権利が得られます：

  • VCPU (CPU Affinity): スレッドが実行を許可されるCPUコアを正確に指定します（例：コア1と2のみ）。
  • Thread Priority: Linuxのniceメカズムに基づいて優先度を設定します。重要なスレッドが優先的に処理され、補助的なスレッドは後回しになります。

MySQLはグループを2つのタイプに分けます：SYSTEM（システムバックグラウンドプロセス用、極めて高い優先度）とUSER（ユーザー接続用、優先度0から19まで）。

この機能を動作させるための2つの必須条件

設定したのに効果がないというケースが多いのは、以下の2つの技術的詳細を見落としているためです：

1. Linuxプラットフォームが最適： MySQLは多くのOSで動作しますが、Resource Groupsがその実力を最大限に発揮するのはLinux上です。WindowsやmacOSは現在、理論上はグループ定義をサポートしていますが、VCPUやThread Priorityを完全に分離することはまだできていません。

2. CAP_SYS_NICE権限： MySQLはシステムレベルでスレッドの優先度を変更する権限を必要とします。この権限がない場合、実行時にエラーが発生します。以下のコマンドで実行ファイルを確認し、権限を付与してください：

# 現在の権限を確認
getcap /usr/sbin/mysqld

# スレッド優先度変更の権限を付与
setcap 'cap_sys_nice=ep' /usr/sbin/mysqld

プロフェッショナルな Resource Group のライフサイクル管理

グループを無計画に作成しないでください。サーバーの実際のコア数に基づいて割り当て計画を立てましょう。データベースを再起動することなく、いつでもグループ設定を変更できます。

グループの編集と削除

処理速度を上げるために、グループがコア0からコア2まで使用できるように拡張したい場合：

ALTER RESOURCE GROUP batch_processing
VCPU = 0-2
THREAD_PRIORITY = 5;

必要がなくなったら、リソースを解放するためにクリーンアップしましょう：

DROP RESOURCE GROUP batch_processing;

接続全体をグループに割り当てる

各文にヒントを追加する代わりに、特定のセッション（例：データクローリングボットのセッション）を特定のグループに固定して割り当てることができます。まず、接続IDを取得します：

SELECT CONNECTION_ID(); -- 例：105が返される場合

その後、そのセッション全体に制限を適用します：

SET RESOURCE GROUP batch_processing FOR 105;

100万件の注文を処理する私のシステムでの実例

以前、約50GBのECサイトのデータベースを管理していました。最大の悩みは、マーケティング部門が午後2時にプロモーション計算のためのデータスキャン用スクリプトを実行するたびに、サーバーのCPUが98%に達し、購入者が504 Gateway Timeoutエラーに遭遇し続けることでした。

「リソース隔離」戦略を適用した後、状況は完全に一変しました：

  1. 優先グループ (Web): コア0, 1, 2。最高優先度。
  2. 低優先グループ (マーケティング): コア3のみ。最低優先度。

結果は驚くべきものでした。マーケティングスクリプトによってコア3が限界まで使われていても、残りの3つのコアは余裕を持って注文を処理し続けました。ウェブサイトのレスポンスタイム（Latency）は、ピーク時でも2.5秒から150msの安定した数値にまで短縮されました。

導入時の「極めて重要な」注意点

Resource Groupsは非常に強力ですが、インデックスの最適化に代わる魔法ではありません。不適切なクエリは依然として遅いままですが、Resource Groupsはそれが他のクエリまで巻き込んで遅くするのを防ぐだけです。

  • 過度な制限を避ける： あまりにも多くのスレッドを単一のコアに押し込むと、スレッド間でのリソース争奪（context switching）が発生し、全体のパフォーマンスが急激に低下します。
  • Performance Schemaで監視する： スレッドが実際にどこにあるかを常に確認してください：
SELECT THREAD_ID, RESOURCE_GROUP_NAME 
FROM performance_schema.threads 
WHERE RESOURCE_GROUP_NAME IS NOT NULL;
  • RAMとI/Oের制限： 現在のResource GroupsはCPUのみを管理できることに注意してください。8GBのサーバーでクエリが16GBのRAMを消費する場合、依然として通常通りOOM（Out Of Memory）エラーが発生します。

MySQL 8+を運用しているなら、今すぐResource Groupsを導入しましょう。これは、混乱したデータベースサーバーを規律あるシステムに変え、バックグラウンドでの重いタスクがどれほどあっても、ユーザー体験を常にスムーズに保つための最もプロフェッショナルな方法です。

Share:
Tags: