はじめに

はじめまして。求人ボックスシステム部SREチームの伊東です。SREチームではありますが、業務では主にSolrをメインに各種の運用・保守を担当しています。
今回はSQLのパフォーマンスモニタリングツールであるPercona Monitoring and Management(PMM)を紹介いたします。

パフォーマンス劣化の原因を特定することへの課題

求人ボックスは2015年にサービス開始しました。
サービス成長に伴いサーバーおよびサービスの各指標の監視にPrometheus(Victoria Metrics) + Grafanaを導入して実施しています。
しかし、サーバー負荷の高騰やサイトパフォーマンスの劣化を把握した際に、MySQLに問題がありそうだと推測ができても実際にどのクエリが影響しているのかという特定に時間を要していました。
スロークエリログはElasticSearchに取り込んでKibana上で確認する仕組みにしていますが、パラメータを正規化できていないのでクエリごとの時間帯別の発行数や実行時間などを追うことができませんでした。

これらの情報を収集するソリューションとしてNewRelic、DatadogのようなSaaS型のモニタリングツールが有名だと思いますが、まずはオンプレでクエリを収集できるようなOSSは無いかと探していたときに見つけたのがPercona Monitoring and Management(PMM)でした。

Percona Monitoring and Management(PMM)とは

Percona Monitoring and Management(PMM) は、MySQL、PostgreSQL、MongoDB用のオープンソースデータベースの監視、管理、可観測性ソリューションです。
これによりデータベースシステムがオンプレミスまたはクラウドのどこに配置されていても、データベースシステムの健全性を観察し、その動作の新しいパターンを探索し、トラブルシューティングを行い、データベース管理操作を実行できます。
PMMは、データベースとそのホストからすぐに使用できる数千のパフォーマンスメトリックを収集します。
PMM Web UIは、ダッシュボード内のデータを視覚化します。
追加機能には、データベースの健全性評価のためのアドバイザーが含まれます。

(公式document welcomeの機械翻訳より)

アーキテクチャは後述しますが、Victoria Metrics + Grafanaベースで構成されています。
そのためnode exporter,mysqld exporterなどで収集されたメトリクスも閲覧可能です。

この製品の一番の特徴はスロークエリログ、もしくはPerformance Schema情報からクエリ情報を収集して監視などができる点です。
スロークエリログだけならfluentdを経由してElasticSearchに取り込んで検索可能ですが、パラメータの正規化はされていないためクエリ別の発行数などを可視化することが非常に難しいです。

PMMでは例えばMySQL5.6以上であればスロークエリに限らず、すべて¹のクエリでパラメータの正規化を行ない、グルーピングを行なってくれるので時間帯ごとのクエリ別発行数などを可視化できます。
そのためDBの負荷調査に非常に役立ちます。

1.Performance Schemaの設定やPMMの設定などによってすべてのクエリを必ず取り込めるわけではありません。

アーキテクチャ

PMM Server

各クライアントからのメトリクス等を収集し、保存しています。
SQLクエリ情報のみQAN APIを経由してClickHouseに格納、exporter系のメトリクスはVictoriaMetricsに格納されます。
GrafanaによるWEBUIが提供されています。

以下の公式ドキュメントより引用
https://docs.percona.com/percona-monitoring-and-management/details/architecture.html#pmm-server_1

PMM Client

監視対象のホスト上で実行されるエージェントとエクスポーターの集合です。
下図のように各exporterのメトリクスをvmagentがまとめてPMM Serverにpushするのがデフォルトの構成です。
PMM Serverから直接各exporterに対してスクレイピングしてメトリクスをpullする方式にも切り替えが可能です。（exporterごとにportを意識する必要あり）
基本的に各監視対象にインストールすることで情報を収集しますが、PMM Server上に直接エージェントとエクスポーターを設置して情報を取得可能です。（PMM Clientのインストールは不要となります）
ただし、その場合はnode_exporterによるCPU情報などの取得はできません。

以下の公式ドキュメントより引用
https://docs.percona.com/percona-monitoring-and-management/details/architecture.html#pmm-client_1

インストール

今回はMySQLからSQLクエリを収集するようにインストール・設定をしてみました。

PMM Server

Dockerを利用してPMM Serverのコンテナを立ち上げます。Docker以外にも複数のインストール方法がありますので公式ドキュメントをご確認ください。
Run Docker with volume
上記の公式ドキュメントを参考に以下を実行します。

# dockerイメージ pull
sudo docker pull percona/pmm-server:2

# volume作成
sudo docker volume create pmm-data

# pmm-serverコンテナ起動
sudo docker run --detach --restart always \
--publish 8443:443 \
-v pmm-data:/srv \
--name pmm-server \
percona/pmm-server:2

# adminユーザーのパスワード変更(今回は検証なので`admin`にしています)
sudo docker exec -t pmm-server change-admin-password admin

PMM Client

node_exporterのメトリクスを取得したい場合はPMM Clientのインストールが必須となりますが、今回はSQLクエリの情報を取得できれば良いのでインストールは行ないません。

インストール方法は以下の公式ドキュメントをご参考ください。
Set up PMM Client

サービス

PMMでモニタリングするために対象サーバーを登録します。モニタリングする際にPMMからMySQLに接続するため、MySQLに専用ユーザーの作成が推奨されています。

MySQL側の設定

ユーザ作成&権限付与

# MySQL8
CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';

権限付与

MySQL5.7以上の場合は「Performance Schema」から情報を取得することが推奨されていますので、my.cnfで以下の関連する設定の適用が必要です。

performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all

PMM側の設定

サービスの登録はコマンドラインもしくはGUIから行います。今回はGUIから登録してみます。

[Add Service]から[MySQL]を選択
Hostname,Username,Passwordが入力必須なので入力します。SQLクエリを取得する場合は[Use performance schema]にチェックを入れます。（デフォルトでチェック入っています）
[Add service]をクリック。登録したサービスの詳細が以下のようにQan mysql perfschema agentとMysqld exporterがRunningになっていればOKです。

PMM Query Analytics

インストール・設定が問題無く完了するとPMM Query Analyticsページでは以下のようにSQLクエリごとの情報が表示されます。
Database,Schema,Node Nameなどでフィルタリングも可能で、またQuery欄でクエリの検索も可能となっています。
ボカシを入れていない部分でwhere部分のパラメータは?で正規化されておりパラメータが異なっていても集約されていることがわかります。

カラム	内容
Load	指定した時間帯に対して当該クエリの合計実行時間が占める割合が表示されます。
Query Count	指定した時間帯のQPSが表示されます。カーソルを合わせるとクエリ数および全体に占める割合も表示されます。
Query Time	指定した時間帯の平均クエリ実行時間が表示されます。カーソルを合わせると合計クエリ実行時間および全体に占める割合も表示されます。