Posted inデータベース

PrometheusとGrafanaによるPostgreSQLパフォーマンス監視：完全セットアップガイド

Database tutorial - IT technology blog
Database tutorial - IT technology blog

背景と目的：本番データベースに潜む死角

リアルタイムのテレメトリなしで本番環境のPostgreSQLを運用するのは、いずれ破綻する危険な賭けです。インデックスのないJOINによって注文テーブルがロックされたり、朝9時に接続数が上限に達したり、autovacuumの処理が書き込み量に対して50 GBも遅延するまでは、すべてが順調に動いているように見えます。エラーログや定期的なpg_topの確認だけに頼っていると、障害の発覚は常にユーザーからの指摘後になってしまいます。

データベースがいきなり前触れなくダウンすることは稀です。多くの場合、数日かけて徐々にパフォーマンスが低下していきます。バッファキャッシュヒット率が99.2%から87%に低下する、ディスクI/O待機時間が2 msから45 msに急増する、レプリケーション遅延が10ミリ秒から8秒に延びる、といった兆候が現れます。継続的なメトリクスパイプラインがあれば、これらが大規模なシステム障害へ連鎖する前に検知できます。

このパイプラインは、互いを補完し合う以下の3つのツールでシンプルに構築できます。

  • postgres_exporter: 最小限の権限を持つユーザーでPostgreSQLに接続し、システムカタログビュー（pg_stat_databaseやpg_stat_activityなど）の情報をPrometheusのGaugeおよびCounterフォーマットに変換します。
  • Prometheus: exporterのエンドポイントを15秒間隔でスクレイピングし、時系列データをインデックス化して、アラートの閾値を継続的に評価します。
  • Grafana: 収集したデータをリアルタイムのダッシュボードに可視化し、クエリがSLAを超過した際にはSlack、PagerDuty、DiscordへアラートのWebhookを送信します。

デプロイ前のベンチマークやステージング環境での検証には、本番に近いデータスキーマが欠かせません。シードデータを手早く加工したい場合、toolcraft.app/ja/tools/data/csv-to-json を使えば、プライベートデータを外部サーバーに送信することなく、ブラウザ上だけでCSVの生データをクリーンなJSONペイロードに変換できます。テストテーブルにデータを投入して疑似トランザクションを実行すれば、各クエリパターンの正確なCPU、ロック、I/O負荷を監視スタック上で把握できるようになります。

インストール：監視スタックのデプロイ

Docker Composeを使用することで、テレメトリスタック全体を分離し、ステージング環境や本番環境のホスト間で容易に再現可能な状態を維持できます。コンテナ間のネットワークレイテンシが懸念される場合は、データベースノード上にpostgres_exporterをネイティブのsystemdユニットとして直接実行してください。

1. 専用データベースユーザーの作成

サードパーティのサービスにpostgresスーパーユーザーの認証情報を設定してはいけません。PostgreSQL 10以降には組み込みのpg_monitorロールが用意されており、テーブルデータ自体の読み取り権限を与えずに、内部診断テーブルへの完全なアクセスを許可できます。

-- postgresスーパーユーザーとして実行
CREATE USER postgres_exporter WITH PASSWORD 'YourSecureExporterPasswordHere!';
GRANT pg_monitor TO postgres_exporter;
GRANT CONNECT ON DATABASE production_db TO postgres_exporter;

このロールにより、サーバーメトリクス、アクティブなクエリ状態、ロックテーブルを安全に読み取ることができます。機密データを含む行の閲覧や、実行時設定の変更はできません。

2. Docker Composeによるデプロイ

pg-monitoringという名前のディレクトリを作成し、以下のdocker-compose.ymlファイルを保存します。

version: '3.8'

services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    container_name: postgres_exporter
    restart: unless-stopped
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres_exporter:[email protected]:5432/production_db?sslmode=disable"
    ports:
      - "9187:9187"

  prometheus:
    image: prom/prometheus:v2.52.0
    container_name: prometheus
    restart: unless-stopped
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml:ro
      - ./alert_rules.yml:/etc/prometheus/alert_rules.yml:ro
      - prometheus_data:/prometheus
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.path=/prometheus'
      - '--storage.tsdb.retention.time=15d'
    ports:
      - "9090:9090"

  grafana:
    image: grafana/grafana:11.0.0
    container_name: grafana
    restart: unless-stopped
    ports:
      - "3000:3000"
    volumes:
      - grafana_data:/var/lib/grafana
    environment:
      - GF_SECURITY_ADMIN_USER=admin
      - GF_SECURITY_ADMIN_PASSWORD=AdminSecurePassword123

volumes:
  prometheus_data:
  grafana_data:

設定：メトリクス、ダッシュボード、アラートの連携

1. PostgreSQLでpg_stat_statementsを有効化

ハードウェアリソースの消費量と合わせてクエリの実行時間を分析できるように、postgresql.confファイル内でpg_stat_statementsを有効化します。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on
track_activity_query_size = 2048

PostgreSQLを再起動して共有ライブラリを読み込みます。その後、対象のデータベース内で拡張機能を登録します。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2. Prometheusのスクレイピングとアラートルールの設定

デプロイディレクトリに以下の基本設定となるprometheus.ymlファイルを保存します。

global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - "alert_rules.yml"

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['postgres_exporter:9187']
        labels:
          environment: 'production'
          cluster: 'main-postgres'

次に、alert_rules.ymlで実践的なアラート条件を定義します。アラート疲れを防ぐため、閾値は適切に調整してください。

groups:
  - name: postgresql_alerts
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQLインスタンスに接続できません"
          description: "postgres_exporterが対象のPostgreSQLインスタンスに1分以上接続できていません。"

      - alert: HighConnectionUsage
        expr: (sum(pg_stat_database_numbackends) by (instance) / pg_settings_max_connections) * 100 > 85
        for: 3m
        labels:
          severity: warning
        annotations:
          summary: "コネクションプールの使用率が85%を超過"
          description: "アクティブなデータベースバックエンド接続数がmax_connections制限の85%を超えています。"

      - alert: LowCacheHitRatio
        expr: (rate(pg_stat_database_blks_hit[5m]) / (rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m]))) * 100 < 90
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "バッファキャッシュヒット率が90%未満に低下"
          description: "バッファキャッシュヒット率が10分間90%を下回っています。不足しているインデックスがないか確認してください。"

      - alert: DeadlockDetected
        expr: increase(pg_stat_database_deadlocks[5m]) > 0
        labels:
          severity: critical
        annotations:
          summary: "トランザクションのデッドロックを検知"
          description: "過去5分間にPostgreSQLで1件以上のデッドロックが記録されました。"

3. サービスの起動とGrafanaの設定

デタッチモードでコンテナを起動します。

docker compose up -d

ブラウザでhttp://<your-server-ip>:3000を開き、管理者アカウントでログインします。次の手順でGrafanaをPrometheusに接続します。

  1. Connections > Data Sources > Add data sourceへ進み、Prometheusを選択します。
  2. Prometheus server URLフィールドにhttp://prometheus:9090と入力します。
  3. Save & Testをクリックし、接続成功を示す緑色のメッセージが表示されることを確認します。
  4. Dashboards > New > Importへ移動し、コミュニティダッシュボードID 9628（PostgreSQL Database）を入力して、設定したPrometheusデータソースを選択します。

検証とモニタリング：テレメトリの検証と重要シグナルの読み解き方

1. Exporterエンドポイントの疎通確認

生のHTTPエンドポイントにアクセスし、postgres_exporterが内部メトリクスを正常に取得できているか確認します。

curl -s http://localhost:9187/metrics | grep -E "^(pg_up|pg_stat_database_xact_commit)"

想定される出力結果は以下のようになります。

pg_stat_database_xact_commit{datname="production_db"} 482910
pg_up 1

pg_up 1が出力されていれば、データベースの認証情報が有効で、ネットワーク接続も正常に機能していることが確認できます。

2. 注視すべき重要ダッシュボードシグナル

日々の運用レビューでは、特に以下の5つの指標を重点的に確認してください。

  • コミット／ロールバック比率（Commit vs. Rollback Ratio）: 健全なアプリケーションであれば、コミット比率は99.5%以上を維持します。ロールバックが急増している場合は、アプリケーション層の例外、外部キー制約違反、または直列化（シリアライゼーション）エラーの発生を示唆しています。
  • 状態別のアクティブ接続数（Active Connections by State）: アクティブな実行状態、アイドル状態のクライアントソケット、そしてidle in transaction（トランザクション内アイドル）状態を区別して監視します。idle in transactionが30秒以上継続している接続は、ロックツリーを保持し続け、autovacuumによるクリーンアップを妨害します。
  • バッファキャッシュヒット率（Buffer Cache Hit Ratio）: OLTPワークロードでは、常に98%以上を維持するのが理想です。ヒット率が90%を下回る場合は、shared_buffersのメモリ不足か、インデックスのないテーブルフルスキャン（シーケンシャルスキャン）によってキャッシュブロックが溢れていないか調査してください。
  • バイト単位のレプリケーション遅延（Replication Lag in Bytes）: ストリーミング物理レプリケーションの場合、pg_stat_replicationを監視します。遅延が50 MBを超えている場合は、レプリカ側のディスク書き込みボトルネックや、プライマリ側での過大なバッチトランザクションが発生している警告です。
  • トランザクションID周回年齢（Transaction ID Wraparound Age）: pg_database_datfrozenxid_ageを注意深く追跡してください。autovacuum freezeが実行されずに年齢が2億トランザクションを超えた場合、20億に達した時点でデータベースが強制停止（フェイルセーフシャットダウン）に追い込まれる危険があります。

3. アラート発砲テスト

アラートは実際に発砲（トリガー）させるまで正常に機能していると過信してはいけません。アプリケーション用のアカウント（読み取り専用の監視ユーザーではなく）を使用し、ステージング環境でpgbenchを実行して疑似的な並行負荷を生成します。

# アプリケーション書き込みユーザーでスケールファクター20（約2,000,000行）を初期化
pgbench -i -s 20 -U app_user -h localhost -d production_db

# 4つのワーカースレッドで80の並行接続を2分間実行
pgbench -c 80 -j 4 -T 120 -U app_user -h localhost production_db

http://localhost:9090/alertsにアクセスします。接続数が3分の閾値を超えて持続すると、HighConnectionUsageアラートがPendingからFiringへ切り替わります。アラートが発砲されれば、通知パイプラインが正しく機能していることが確認できます。

Share:
Tags: