PrometheusによるPOWAのようなPostgreSQLモニタリング

背景

PostgreSQLの動作に関するデータ（サーバー全体のパフォーマンス、最も遅いクエリ、最も頻繁なクエリ）を収集して表示するために、私たちは長い間優れたPOWAユーティリティを使用してきました。 しかし、このソリューションは理想とはほど遠いものであり、メインの監視システムと完全に統合された、より良いオプションを見つけることができました。

POWAはほとんど私たちのニーズのほとんどを満たしました：

• インストールと操作が簡単です（Postgresの拡張機能をインストールし、Webフェイスをインストールして使用します）。
• Webインターフェイスがあります（ここの開発者は運用サーバーにアクセスできませんが、データベースの動作を確認したいです）。
• 最低限必要なメトリックを収集します。

ただし、不快な短所もありました。

• 複数のサーバーからのデータを監視するのは不便です。
• インターフェイスはカスタマイズしません。
• 一元化された承認を（苦痛なく）固定する方法はありません。
• 独自のメトリックを追加する方法はありません。
• 監視システムとの統合はありません（Prometheusを使用してメトリックを収集し、Grafanaを使用してそれらを視覚化します）。

たとえば、いくつかのPOWAスクリーンショット：

この点で、Prometheusのpostgres_exporterを使用して、PostgreSQLでメトリックを収集しようとすることが決定されました。

設置

TL; DR：これはAnsibleの役割で、CentOS 7で作成されましたが、最小限の変更（firewalldをiptablesに置き換え）後はsystemd systemdで動作するはずです-https ://github.com/UnitedTraders/ansible-postgresql-exporter

ソリューションのアーキテクチャ

プロメテウス自体については詳しく説明しませんが、それについては十分な資料がありますが、一般的なアーキテクチャとエクスポーターについて説明します。

Prometheusはプルメトリックコレクションモデルを使用します。エクスポーターのリストがあり、HTTPを介してそれらをポーリングし、それらからメトリックのリストを収集し、ストレージに格納します。

エクスポーターは、監視が必要なエンティティ（サーバー全体、または特定のアプリケーション）からメトリックを直接収集するエージェントです。 プロメテウスには計装の豊富な機会があるため、ほとんどの一般的なアプリケーションでエクスポーターを利用できます。必要に応じて独自に作成することは難しくありません。

postgres_exporterは次のように機能します。PostgreSQLに接続し、サービステーブルへのクエリを実行し、Prometheusによる収集のために内部HTTPサーバーを使用して特別な形式で結果を公開します。 重要な点：多数のデフォルトクエリセットに加えて、独自のクエリを定義し、SQLを使用して取得できるデータ（ビジネスメトリックなど）を収集できます。

したがって、postgres_exporterをセットアップすると、次の3つのアクションになります。

• 監視するサーバーにpostgres_exporterをインストールします。
• パラメータを監視するために、必要に応じてリクエストを作成します。
• メトリックの取得元のPrometheusサーバーを表示します。

エクスポーターのインストール

エクスポーターはGoで記述されているため、すべてがつまらないものです。

• https://github.com/wrouesnel/postgres_exporter/releasesから目的のバイナリをダウンロードします
• 環境変数をenvファイルで定義しますDATA_SOURCE_NAME="postgresql://postgres@localhost:5432/?sslmode=disable"
  
  
  
  
• 次のようなsystemdユニットを作成します。

 [Unit] Description=Prometheus exporter for Postgresql (https://github.com/wrouesnel/postgres_exporter) [Service] WorkingDirectory=/opt/postgres_exporter EnvironmentFile=/opt/postgres_exporter/env ExecStart=/opt/postgres_exporter/postgres_exporter_v0.4.1_linux-amd64/postgres_exporter --extend.query-path=/opt/postgres_exporter/metrics.yaml --web.listen-address=:9187 User=pg_exporter [Install] WantedBy=multi-user.target
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

• メトリックのカスタムリクエストを含むファイルを作成します（以下を参照）。
• サービスを開始します。

メトリックをカスタマイズする

デフォルトでは、postgres_exporterはリクエストに関するデータを収集できません。 しかし、PostgreSQLには非常に便利なpg_stat_statements



拡張機能がpg_stat_statements



まさにそれを行います。 pg_stat_statementsの設定は、次の3つの簡単な手順になります。

• PostgreSQL用のcontribモジュールをインストールします。
• shared_preload_libraries = 'pg_stat_statements'
  
  
  
  パラメーターをpostgresql.confに追加します。
• postgres自体に拡張機能を作成します： CREATE EXTENSION pg_stat_statements
  
  
  
  。

なぜなら 最初にクエリの実行時間を収集することが重要でしたが、メトリックファイルは次のようになりました。

 pg_database: query: " SELECT pg_database.datname, pg_database_size(pg_database.datname) as size FROM pg_database" metrics: - datname: usage: "LABEL" description: "Name of the database" - size: usage: "GAUGE" description: "Disk space used by the database" pg_stat_statements: query: "SELECT queryid, datname, left(query, 100) as short_query, sum(calls) as calls, sum(total_time) as total_time, min(min_time) as min_time, max(max_time) as max_time, sum(mean_time*calls)/sum(calls) as mean_time FROM pg_stat_statements JOIN pg_database ON pg_stat_statements.dbid = pg_database.oid group by queryid, short_query, datname" metrics: - queryid: usage: "LABEL" description: "Query ID" - datname: usage: "LABEL" description: "Database name" - short_query: usage: "LABEL" description: "Query limited to 100 symbols" - calls: usage: "COUNTER" description: "Number of times executed" - total_time: usage: "COUNTER" description: "Total time spent in the statement, in milliseconds" - min_time: usage: "GAUGE" description: "Minimum time spent in the statement, in milliseconds" - max_time: usage: "GAUGE" description: "Maximum time spent in the statement, in milliseconds" - mean_time: usage: "GAUGE" description: "Mean time spent in the statement, in milliseconds"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

落とし穴が1つあります。pg_stat_statementsのレコードを集約する必要があるためです。 このテーブルには、クエリID、データベースID、およびクエリの同じ組み合わせを持つ複数のレコードが存在する場合があります。 そのような記録が存在すると、postgres_exporterが低下します。 これらのデータに基づいてメトリックの名前を形成し、それらは一意でなければなりません。

簡単にするために、読み取り/書き込みメトリックを追加しませんでした（shared_blks_writtenなど、類推により追加されます）。 また、繰り返しますが、このようなクエリはpg_stat_statementsだけでなく、任意のテーブルに対して行うことができます。

プロメテウスクエリの例

上記の構成では、エクスポーターはかなりの数のメトリックを生成します-リクエストのタイプごとに5個。 プロメテウスの側でそれらを集約します。

クエリの例（テンプレート用のグラファン変数をすぐに使用）：

• sum(rate(pg_stat_statements_mean_time{datname!~"template.*", datname!~"postgres", instance=~"$server"}[1m])) by (instance)
  
  
  
  -サーバー全体の最後の1分間の平均リクエスト時間
• sum(rate(pg_stat_statements_mean_time{datname!~"template.*", datname!~"postgres", instance=~"$server", datname=~"$database"}[1m])) by (datname)
  
  
  
  -平均時間選択されたデータベースの最後のリクエスト
• sum(increase(pg_stat_statements_calls{datname!~"template.*", datname!~"postgres", instance=~"$server", datname=~"$database"}[1m])) by (datname)
  
  
  
  -クエリ数ベースまで1分あたり
• topk(20, increase(pg_stat_statements_calls{instance=~"$server", datname=~"$database"}[10m]))
  
  
  
  -過去10分間で最も頻度の高いデータベースクエリの上位20件
• topk(20, pg_stat_statements_mean_time{instance=~"$server", datname=~"$database"})
  
  
  
  -最も長いデータベースクエリのトップ20

グラファンでは、次のようになります（jsonへのリンク ）：