GrafanaでMySQL Telemetryを可視化する

1回目の記事では、MySQLのOpenTelemetryメトリクスをPrometheusへ直接エクスポートしました。 2回目の記事では、DBAが運用中に抱く疑問をもとに、注目すべきメトリクスを選びました。今回は、それらの疑問に答えるGrafanaダッシュボードを作成してみます。

目的は、取得できるメトリクスをすべて表示することではありません。次に何を調査すべきかを判断できるようにすることです。ワークロードが変化したとき、接続、キャッシュの動作、書き込み経路、ロック競合、ホストのどこに負荷がかかっているのかをDBAが判断できる構成にします。

6つの運用に関する問いを軸にダッシュボードを作る

この例では、MySQL Telemetry Overviewという名前のダッシュボードを1つ作成します。ダッシュボードを6つの問いに沿って構成し、それぞれに1つ以上のGrafanaパネルを作成します。

#	運用中の疑問	代表的なパネル
1	接続数が増えているか、接続が拒否されているか	Connections Connection rejections
2	ワークロードが変化したか	Workload
3	バッファプールが読み取り処理を効率よく処理しているか	Buffer Pool Hit Ratio Physical Reads
4	書き込み経路に負荷がかかっているか	Redo Write Throughput Redo Log Waits Pending Redo Fsyncs
5	トランザクションが行ロック待ちになっているか	Current Row Lock Waits Row Lock Wait Rate
6	ホストが問題に関係しているか	CPU Utilization Available Memory Average Disk Read and Write Time Network Traffic

パネル数は6つを超えてもかまいません。 6つの問いは設計の軸であり、パネルはそれをGrafanaで表現するための要素です。以下の図のように1つのダッシュボードの関連部分を、読みやすい大きさに分けて示します。

MySQL Telemetryダッシュボードの接続、ワークロード、バッファプール効率のパネル — 図1：接続、ワークロード、バッファプール効率を関連付けて確認する画面例

パネルを作成する前の準備

1回目の記事では、MySQLのネイティブなテレメトリをOTLP経由でPrometheusのOTLPレシーバーへエクスポートするように設定しました。 Grafanaのパネルを作成する前に、ダッシュボードで必要となるメーターグループが有効になっていることを確認します。

SELECT NAME, ENABLED, FREQUENCY
FROM performance_schema.setup_meters
WHERE NAME IN (
  'mysql.stats',
  'mysql.stats.connection',
  'mysql.stats.com',
  'mysql.inno',
  'mysql.inno.buffer_pool'
);

検証環境では、MySQLがこれらのメーターを10秒ごとにエクスポートします。 Prometheus側の正確なメトリクス名は、OTLPレシーバーと、MySQLからPrometheusに届くまでに適用される変換によって異なります。クエリーをコピーする前に、GrafanaのMetrics browserまたはPrometheusの式ブラウザーで、対象環境のメトリクス名を確認してください。

以下の例では、MySQLからPrometheusのOTLPレシーバーへ直接エクスポートした環境で確認した名前を使います。
処理回数や書き込みバイト数などの累積値を表すカウンター型メトリクスには _totalが付きます。
複数のMySQLサーバーを監視する場合は、instanceというテンプレート変数を作成し、以下のパネルで対象となるインスタンスを1つ選択します。

job="mysql/mysqld", instance="$instance"

これにより、各パネルの対象を1台のMySQLサーバーに限定し、複数インスタンスの時系列を意図せず混在させることを防ぎます。

接続とワークロード

ダッシュボードの上部には、クライアントからの要求量と接続の状態を配置します。

接続数

Connectionsという名前のTime seriesパネルを作成し、次のクエリーを追加します。

threads_connected{
  job="mysql/mysqld",
  instance="$instance"
}

凡例をCurrent connectionsに設定します。

max_used_connections{
  job="mysql/mysqld",
  instance="$instance"
}

こちらの凡例はPeak connections since restart、単位はshortに設定します。 max_used_connectionsは再起動後の最大同時接続数であり、設定値max_connectionsが表す接続上限ではありません。キャパシティ計画に役立ちますが、この値だけでは接続数の余裕はわかりません。

接続拒否

Connection rejectionsという名前のStatパネルを作成します。

increase(
  errors_max_connections_total{
    job="mysql/mysqld",
    instance="$instance"
  }[5m]
)

ValueをLast not null、Unitをshortに設定します。

値が0なら、接続上限に達したことによる拒否は発生していません。 0以外の値は、設定された接続上限に達し、MySQLがすでにクライアントの接続を拒否したことを示します。

ワークロード

Workloadという名前のTime seriesパネルを作成します。

rate(
  questions_total{
    job="mysql/mysqld",
    instance="$instance"
  }[5m]
)

凡例をCommands per second、単位をThroughput / ops/sec (ops)に設定します。 questions_totalは、クライアントから送信されたステートメントを数えるMySQLのコマンドカウンターです。レートに変換すると、クライアントからの要求量が時間とともにどう変化したかを確認できます。

トラフィックの増加だけでは、問題が起きているとは判断できません。接続の状態、アプリケーションのレイテンシー、後述するキャッシュ、書き込み経路、競合のパネルと合わせて確認します。

バッファプールの動作

バッファプールの状態は2つのパネルで確認します。一方でキャッシュ効率を把握し、もう一方で物理ディスク読み取りを確認します。

バッファプールのヒット率

Buffer Pool Hit Ratio Since Restartという名前のTime seriesパネルを作成します。

1 - (
  reads_total{
    job="mysql/mysqld",
    instance="$instance"
  }
  /
  read_requests_total{
    job="mysql/mysqld",
    instance="$instance"
  }
)

凡例をBuffer pool hit ratio、単位をPercent (0.0-1.0)に設定します。 MySQL側の元の値はInnodb_buffer_pool_readsとInnodb_buffer_pool_read_requestsです。

2回目の記事では、一定期間の読み取り状況の変化を捉えるため、カウンターの増加率からヒット率を計算しました。一方、今回のように負荷が低い検証環境では、短い集計期間に読み取りが数回発生しただけで、ヒット率が大きく変動することがあります。
そのため、今回は再起動後の累積読み取り回数からヒット率を計算しています。

本番環境では、キャッシュの動作が問題の原因だと判断する前に、通常時のヒット率と、物理読み取りのレート、アプリケーションの読み取りレイテンシーを比較してください。

物理読み取り

Physical Readsという名前のTime seriesパネルを作成します。

rate(
  reads_total{
    job="mysql/mysqld",
    instance="$instance"
  }[5m]
)

凡例をPhysical reads per second、単位をThroughput / ops/sec (ops)に設定します。
物理読み取りの増加は、ヒット率が通常時の水準から低下し、読み取りレイテンシーも上昇している場合に、より意味のある兆候となります。

書き込み経路と競合

書き込み経路は、複数の指標を組み合わせて読み取ります。 Redoのスループットは処理量を表します。 Redoの待機と保留中のfsyncは、その処理が継続的な負荷を生んでいるかを示します。

Redo書き込みスループット

Redo Write Throughputという名前のTime seriesパネルを作成します。

rate(
  os_log_written_total{
    job="mysql/mysqld",
    instance="$instance"
  }[5m]
)

凡例をRedo bytes written per second、単位をData rate / bytes/sec (SI)に設定します。 os_log_writtenを使うと、Redoの書き込み量を時系列として把握できます。

Redoログ待機

Redo Log Waitsという名前のTime seriesパネルを作成します。

rate(
  log_waits_total{
    job="mysql/mysqld",
    instance="$instance"
  }[5m]
)

凡例をRedo log waits per second、単位をThroughput / ops/sec (ops)に設定します。 log_waitsの元の値はInnodb_log_waitsで、ログバッファーの容量不足による待機を記録します。

保留中のRedo fsync

Pending Redo Fsyncsという名前のStatパネルを作成します。

os_log_pending_fsyncs{
  job="mysql/mysqld",
  instance="$instance"
}

ValueをLast not null、Unitをshortに設定します。スパークラインは有効にしておきます。大きく表示される値は現在の待ち行列の長さを表し、スパークラインは、それ以前に発生した一時的な増加が速やかに解消したか、継続していたかを示します。

ロック競合

Current Row Lock Waitsという名前のStatパネルを作成します。

row_lock_current_waits{
  job="mysql/mysqld",
  instance="$instance"
}

ValueをLast not null、Unitをshortに設定します。続いて、Row Lock Wait Rateという名前のTime seriesパネルを作成します。

rate(
  row_lock_waits_total{
    job="mysql/mysqld",
    instance="$instance"
  }[5m]
)

凡例をRow lock waits per second、単位をThroughput / ops/sec (ops)に設定します。
これらのパネルで、一時的な待機と、競合が積み重なっている状態を区別します。いずれかの指標がアプリケーションのレイテンシーとともに上昇する場合は、Performance Schemaを使い、関連するセッション、ステートメント、トランザクション、オブジェクトを特定します。

node_exporterでホストの状態を確認する

MySQL Telemetryが示すのは、データベースサーバーの状態です。ホストのCPU、メモリー、ディスク、ネットワークの状態を把握するには、ホスト側のテレメトリが必要です。この例では、MySQLホストでnode_exporterを実行し、Prometheusが別のnode-mysqlジョブでスクレイプします。

- job_name: 'node-mysql'
  static_configs:
    - targets: ['MYSQL_PRIVATE_IP:9100']

このスクレイプジョブは、MySQLからのOTLPエクスポートを補完するものです。 MySQLとPrometheusの間にOpenTelemetry Collectorを挿入する構成ではありません。

MySQLサーバーのホストに関するシステム指標 — 図3：CPU、利用可能メモリー、ディスクの動作、ネットワークトラフィックから、MySQLテレメトリを解釈するためのホスト側の情報を確認します。

CPU使用率

CPU Utilizationという名前のTime seriesパネルを作成します。

100 * (
  1 - avg by (instance) (
    rate(node_cpu_seconds_total{
      job="node-mysql",
      mode="idle"
    }[5m])
  )
)

凡例をCPU utilization、単位をPercent (0-100)に設定します。

利用可能メモリー

Available Memoryという名前のTime seriesパネルを作成します。

node_memory_MemAvailable_bytes{job="node-mysql"}

凡例をAvailable memory、単位をData / bytes (IEC)に設定します。 MemAvailableには、回収可能なページキャッシュなど、Linuxがスワップせずに利用可能にできるメモリーが含まれます。この目的では、単純な空きメモリー量よりも有用です。

ディスクの平均読み取り時間と平均書き込み時間

Average Disk Read and Write Timeという名前のTime seriesパネルを作成し、次の2つのクエリーを追加します。

クエリーA：読み取り時間

1000 * (
  sum by (instance) (
    rate(node_disk_read_time_seconds_total{
      job="node-mysql",
      device!~"loop.*|ram.*|fd.*|sr.*"
    }[5m])
  )
  /
  sum by (instance) (
    rate(node_disk_reads_completed_total{
      job="node-mysql",
      device!~"loop.*|ram.*|fd.*|sr.*"
    }[5m])
  )
)

凡例をAverage disk read timeに設定します。

クエリーB：書き込み時間

1000 * (
  sum by (instance) (
    rate(node_disk_write_time_seconds_total{
      job="node-mysql",
      device!~"loop.*|ram.*|fd.*|sr.*"
    }[5m])
  )
  /
  sum by (instance) (
    rate(node_disk_writes_completed_total{
      job="node-mysql",
      device!~"loop.*|ram.*|fd.*|sr.*"
    }[5m])
  )
)

凡例をAverage disk write time、単位をTime / milliseconds (ms)に設定します。

Linuxのディスクカウンターは、読み取りと書き込みに費やした累積時間をそれぞれ記録します。時間の増加率を完了した操作数の増加率で割ると、選択したデバイス全体について、I/O数で重み付けした、完了した読み取りまたは書き込み1回あたりの平均時間を求められます。

これらはホストレベルの処理時間を示す指標であり、エンドツーエンドのストレージレイテンシーを測るものではありません。待ち行列や並行I/Oによって解釈が変わるため、MySQLのRedo、ロック、アプリケーションのレイテンシーと合わせて状況を判断します。

ネットワークトラフィック

Network Trafficという名前のTime seriesパネルを作成し、受信用のクエリーを追加します。

sum(
  rate(node_network_receive_bytes_total{
    job="node-mysql",
    device!~"lo|docker.*|veth.*|br-.*"
  }[5m])
)

凡例をReceive bytes per secondに設定します。続いて、送信用のクエリーを追加します。

sum(
  rate(node_network_transmit_bytes_total{
    job="node-mysql",
    device!~"lo|docker.*|veth.*|br-.*"
  }[5m])
)

凡例をTransmit bytes per secondに設定します。パネルの単位はData rate / bytes/sec (SI)に設定します。

ダッシュボードを順に読み取る

アプリケーションの応答が遅くなったら、次の判断につながるポイントから確認します。

図の流れに沿って、要求量や接続の状態が変化したか、キャッシュ、書き込み経路、ロックに関する指標が上昇しているかを確認します。ホスト側のCPU、メモリー、ディスクI/O、ネットワークの情報も同じ説明を裏付けるかを確かめ、MySQL側とホストまたはプラットフォーム側のどちらを次に調査するかを判断します。

ダッシュボードは次の調査の手がかりを導くもので、根本原因の分析と置き換えられるものではありません。次回は、同じ指標を使って、ダッシュボードに現れた兆候からMySQLのトラブルシューティングに必要な具体的な証拠収集を試みます。

GrafanaでMySQL Telemetryを可視化する

6つの運用に関する問いを軸にダッシュボードを作る

パネルを作成する前の準備

接続とワークロード

接続数

接続拒否

ワークロード

バッファプールの動作

バッファプールのヒット率

物理読み取り

書き込み経路と競合

Redo書き込みスループット

Redoログ待機

保留中のRedo fsync

ロック競合

node_exporterでホストの状態を確認する

CPU使用率

利用可能メモリー

ディスクの平均読み取り時間と平均書き込み時間

ネットワークトラフィック

ダッシュボードを順に読み取る

参考資料

Machiko Ikoma

MySQL Principal Solution Engineer

MySQL HeatWaveでOCI Log Analyticsが利用可能になりました

MySQLのSELECT *：すべての列を取得する前に考えたいこと

GrafanaでMySQL Telemetryを可視化する

6つの運用に関する問いを軸にダッシュボードを作る

パネルを作成する前の準備

接続とワークロード

接続数

接続拒否

ワークロード

バッファプールの動作

バッファプールのヒット率

物理読み取り

書き込み経路と競合

Redo書き込みスループット

Redoログ待機

保留中のRedo fsync

ロック競合

node_exporterでホストの状態を確認する

CPU使用率

利用可能メモリー

ディスクの平均読み取り時間と平均書き込み時間

ネットワークトラフィック

ダッシュボードを順に読み取る

参考資料

Authors

Machiko Ikoma

MySQL Principal Solution Engineer

MySQL HeatWaveでOCI Log Analyticsが利用可能になりました

MySQLのSELECT *：すべての列を取得する前に考えたいこと