著者は、 Write for DOnations プログラムの一環として、 Free and Open SourceFundを選択して寄付を受け取りました。

序章

データベースの監視は、データベースのパフォーマンスを示すさまざまなメトリックを体系的に追跡する継続的なプロセスです。 パフォーマンスデータを観察することで、貴重な洞察を得て、考えられるボトルネックを特定し、データベースのパフォーマンスを向上させる追加の方法を見つけることができます。 このようなシステムは、多くの場合、問題が発生したときに管理者に通知するアラートを実装しています。 収集された統計は、データベースの構成とワークフローだけでなく、クライアントアプリケーションの構成とワークフローを改善するためにも使用できます。

Elastic Stack (ELKスタック)を使用して管理対象データベースを監視する利点は、検索の優れたサポートと、新しいデータを非常に迅速に取り込む機能です。 データの更新には優れていませんが、このトレードオフは、過去のデータがほとんど変更されない監視とログの目的には受け入れられます。 Elasticsearch は、データをクエリする強力な手段を提供します。これを Kibana で使用すると、データベースがさまざまな期間でどのように処理されるかをよりよく理解できます。 これにより、データベースの負荷を実際のイベントと相関させて、データベースがどのように使用されているかについての洞察を得ることができます。

このチュートリアルでは、PostgreSQL統計コレクターによって生成されたデータベースメトリックをLogstashを介してElasticsearchにインポートします。 これには、 PostgreSQL JDBCコネクタを使用してデータベースからデータをプルし、直後にインデックスを作成するためにElasticsearchに送信するようにLogstashを構成する必要があります。 インポートされたデータは、後でKibanaで分析および視覚化できます。 次に、データベースがまったく新しい場合は、PostgreSQLベンチマークツールである pgbench を使用して、より興味深い視覚化を作成します。 最終的には、後で分析するためにPostgreSQL統計を取得する自動システムができます。

前提条件

  • 少なくとも8GBのRAM、root権限、およびセカンダリの非rootアカウントを持つUbuntu18.04サーバー。 これは、この初期サーバーセットアップガイドに従ってセットアップできます。 このチュートリアルでは、root以外のユーザーは sammy.

  • サーバーにインストールされているJava8。 インストール手順については、 Ubuntu18.04にaptを使用してJavaをインストールする方法をご覧ください。

  • Nginxがサーバーにインストールされています。 その方法のガイドについては、 Ubuntu18.04にNginxをインストールする方法を参照してください。

  • サーバーにElasticsearchとKibanaがインストールされています。 Ubuntu 18.04 チュートリアルにElasticsearch、Logstash、およびKibana(Elastic Stack)をインストールする方法の最初の2つのステップを完了します。

  • DigitalOceanからプロビジョニングされたPostgreSQL13マネージドデータベースで、接続情報が利用可能です。 サーバーのIPアドレスがホワイトリストに含まれていることを確認してください。 DigitalOceanコントロールパネルを使用してPostgreSQL管理データベースを作成するためのガイドについては、PostgreSQLクイックスタートガイドにアクセスしてください。

ステップ1—LogstashとPostgreSQLJDBCドライバーのセットアップ

このセクションでは、Logstashをインストールし、 PostgreSQL JDBCドライバーをダウンロードして、Logstashが管理対象データベースに接続できるようにします。

次のコマンドを使用してLogstashをインストールすることから始めます。

  1. sudo apt install logstash -y

Logstashをインストールしたら、起動時にサービスを自動的に開始できるようにします。

  1. sudo systemctl enable logstash

LogstashはJavaで記述されているため、PostgreSQLに接続するには、実行しているシステムでPostgreSQL JDBC(Java Database Connectivity)ライブラリが使用可能である必要があります。 内部制限のため、Logstashは、ライブラリが次の場所にある場合にのみ、ライブラリを適切にロードします。 /usr/share/logstash/logstash-core/lib/jars 使用するサードパーティライブラリを格納するディレクトリ。

JDBCライブラリのダウンロードページに移動し、リンクを最新バージョンにコピーします。 次に、を使用してダウンロードします curl 次のコマンドを実行します。

  1. sudo curl https://jdbc.postgresql.org/download/postgresql-42.3.3.jar -o /usr/share/logstash/logstash-core/lib/jars/postgresql-jdbc.jar

これを書いている時点では、ライブラリの最新バージョンは 42.3.3、サポートされているランタイムバージョンとしてJava8を使用。 最新バージョンをダウンロードしてください。 JDBCとLogstashの両方がサポートする正しいJavaバージョンとペアリングします。 Logstashは、構成ファイルを下に保存します /etc/logstash/conf.d、およびそれ自体が下に保存されます /usr/share/logstash/bin.

Logstashを使用してインストールしました apt Logstashが管理対象データベースに接続するために使用できるようにPostgreSQLJDBCライブラリをダウンロードしました。 次のステップでは、Logstashから統計データを取得するようにLogstashを構成します。

ステップ2—統計をプルするためのLogstashの構成

このセクションでは、管理対象のPostgreSQLデータベースからメトリックをプルするようにLogstashを構成します。

PostgreSQLの3つのシステムデータベースを監視するようにLogstashを構成します。

  • pg_stat_database:名前、接続数、トランザクション、ロールバック、データベースのクエリによって返される行、デッドロックなど、各データベースに関する統計を提供します。 それは stats_reset フィールド。統計が最後にリセットされた日時を指定します。
  • pg_stat_user_tables:挿入、削除、更新された行の数など、ユーザーが作成した各テーブルに関する統計を提供します。
  • pg_stat_user_indexes:特定のインデックスがスキャンされた回数など、ユーザーが作成したテーブル内のすべてのインデックスに関するデータを収集します。

ElasticsearchでPostgreSQL統計のインデックスを作成するための構成を次の名前のファイルに保存します postgresql.conf/etc/logstash/conf.d Logstashが構成ファイルを保存するディレクトリ。 サービスとして開始すると、バックグラウンドで自動的に実行されます。

作成 postgresql.conf お気に入りのエディター(たとえば、nano)を使用する:

  1. sudo nano /etc/logstash/conf.d/postgresql.conf

次の行を追加します。

/etc/logstash/conf.d/postgresql.conf
input {
        # pg_stat_database
        jdbc {
                jdbc_driver_library => ""
                jdbc_driver_class => "org.postgresql.Driver"
                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
                jdbc_user => "username"
                jdbc_password => "password"
                statement => "SELECT * FROM pg_stat_database"
                schedule => "* * * * *"
                type => "pg_stat_database"
        }

        # pg_stat_user_tables
        jdbc {
                jdbc_driver_library => ""
                jdbc_driver_class => "org.postgresql.Driver"
                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
                jdbc_user => "username"
                jdbc_password => "password"
                statement => "SELECT * FROM pg_stat_user_tables"
                schedule => "* * * * *"
                type => "pg_stat_user_tables"
        }

        # pg_stat_user_indexes
        jdbc {
                jdbc_driver_library => ""
                jdbc_driver_class => "org.postgresql.Driver"
                jdbc_connection_string => "jdbc:postgresql://host:port/defaultdb"
                jdbc_user => "username"
                jdbc_password => "password"
                statement => "SELECT * FROM pg_stat_user_indexes"
                schedule => "* * * * *"
                type => "pg_stat_user_indexes"
        }
}

output {
        elasticsearch {
                hosts => "http://localhost:9200"
                index => "%{type}"
        }
}

交換することを忘れないでください host あなたのホストアドレスで、 port データベースに接続できるポートを使用して、 username データベースユーザーのユーザー名、および password そのパスワードで。 これらの値はすべて、管理対象データベースのコントロールパネルにあります。

この構成では、3つのJDBC入力と1つのElasticsearch出力を定義します。 3つの入力は、からデータをプルします pg_stat_database, pg_stat_user_tables、 と pg_stat_user_indexes それぞれデータベース。 それらはすべて設定します jdbc_driver_library PostgreSQL JDBCライブラリは、Logstashが自動的にロードするフォルダにあるため、パラメータを空の文字列に設定します。

次に、彼らは設定します jdbc_driver_class、その値はJDBCライブラリに固有であり、 jdbc_connection_string、データベースへの接続方法の詳細。 The jdbc: 一部は、それがJDBC接続であることを意味しますが、 postgres:// ターゲットデータベースがPostgreSQLであることを示します。 次に、データベースのホストとポートが表示されます。スラッシュの後に、接続するデータベースも指定します。 これは、PostgreSQLではクエリを発行できるようにデータベースに接続する必要があるためです。 ここでは、常に存在し、削除できないデフォルトのデータベースに設定されており、適切な名前が付けられています defaultdb.

次に、データベースにアクセスするためのユーザーのユーザー名とパスワードを設定します。 The statement パラメータには、処理するデータを返すSQLクエリが含まれています。この構成では、適切なデータベースからすべての行を選択します。

The schedule パラメータは、Logstashがこの入力を実行するタイミングを定義するcron構文の文字列を受け入れます。 完全に省略すると、Logstashは1回だけ実行します。 指定する * * * * *、ここで行ったように、Logstashに毎分実行するように指示します。 異なる間隔でデータを収集する場合は、独自のcron文字列を指定できます。

3つの入力からのデータを受け入れる出力は1つだけです。 それらはすべて、ローカルで実行され、で到達可能なElasticsearchにデータを送信します。 http://localhost:9200. The index パラメータは、データを送信するElasticsearchインデックスを定義し、その値は type 入力のフィールド。

編集が終わったら、ファイルを保存して閉じます。

さまざまなPostgreSQL統計テーブルからデータを収集してElasticsearchに送信し、ストレージとインデックスを作成するようにLogstashを構成しました。 次に、Logstashを実行して構成をテストします。

ステップ3—Logstash構成のテスト

このセクションでは、Logstashを実行して構成をテストし、データが適切にプルされることを確認します。 次に、Logstashパイプラインとして構成することにより、この構成をバックグラウンドで実行します。

Logstashは、ファイルパスをに渡すことにより、特定の構成の実行をサポートします。 -f パラメータ。 次のコマンドを実行して、最後の手順からの新しい構成をテストします。

  1. sudo /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/postgresql.conf

出力が表示されるまでに時間がかかる場合があります。これは次のようになります。

Output
Using bundled JDK: /usr/share/logstash/jdk OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release. WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console [INFO ] 2022-02-24 08:49:36.664 [main] runner - Starting Logstash {"logstash.version"=>"7.17.0", "jruby.version"=>"jruby 9.2.20.1 (2.5.8) 2021-11-30 2a2962fbd1 OpenJDK 64-Bit Server VM 11.0.13+8 on 11.0.13+8 +indy +jit [linux-x86_64]"} [INFO ] 2022-02-24 08:49:36.671 [main] runner - JVM bootstrap flags: [-Xms1g, -Xmx1g, -XX:+UseConcMarkSweepGC, -XX:CMSInitiatingOccupancyFraction=75, -XX:+UseCMSInitiatingOccupancyOnly, -Djava.awt.headless=true, -Dfile.encoding=UTF-8, -Djruby.compile.invokedynamic=true, -Djruby.jit.threshold=0, -Djruby.regexp.interruptible=true, -XX:+HeapDumpOnOutOfMemoryError, -Djava.security.egd=file:/dev/urandom, -Dlog4j2.isThreadContextMapInheritable=true] [INFO ] 2022-02-24 08:49:36.700 [main] settings - Creating directory {:setting=>"path.queue", :path=>"/usr/share/logstash/data/queue"} [INFO ] 2022-02-24 08:49:36.710 [main] settings - Creating directory {:setting=>"path.dead_letter_queue", :path=>"/usr/share/logstash/data/dead_letter_queue"} [WARN ] 2022-02-24 08:49:36.992 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified [INFO ] 2022-02-24 08:49:37.018 [LogStash::Runner] agent - No persistent UUID file found. Generating new UUID {:uuid=>"bfd27cc5-f2d0-4b19-8870-a125586135ed", :path=>"/usr/share/logstash/data/uuid"} [INFO ] 2022-02-24 08:49:38.085 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9600, :ssl_enabled=>false} [INFO ] 2022-02-24 08:49:39.284 [Converge PipelineAction::Create<main>] Reflections - Reflections took 68 ms to scan 1 urls, producing 119 keys and 417 values ... [INFO ] 2022-02-24 08:50:03.102 [[email protected][d39f109727b9e1a2b881639e708f21ce1d65378257869071cbed233a3946468d]<jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/scheduler.rb:77] jdbc - (0.194969s) SELECT * FROM pg_stat_user_tables [INFO ] 2022-02-24 08:50:03.104 [[email protected][bd7d166b46e4ae8c53b4d498eaec7d53de881ea0f8a9bdfb08f574f9cbd3a4f6]<jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:284] jdbc - (0.192893s) SELECT * FROM pg_stat_database [INFO ] 2022-02-24 08:50:03.104 [[email protected][fc2c0b0065c00ee9f942e75f35edf001a9e285c77ba7cf4ae127886e43c140fc]<jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:284] jdbc - (0.197744s) SELECT * FROM pg_stat_user_indexes ...

Logstashにエラーが表示されず、正常にログに記録された場合 SELECT3つのデータベースの行を編集すると、データベースメトリックがElasticsearchに送信されます。 エラーが発生した場合は、構成ファイルのすべての値を再確認して、Logstashを実行しているマシンが管理対象データベースに接続できることを確認してください。

Logstashは、指定された時間にデータのインポートを続行します。 を押すと安全に停止できます CTRL+C.

このチュートリアルのためだけに新しいデータベースを作成した場合、1人のユーザーのみ(doadmin)が存在する必要があります。 これは、 pg_stat_user_tablespg_stat_user_indexes テーブルは空になり(他のユーザーが存在せず、テーブルを作成していないため)、Elasticsearchに表示されません。

前述のように、サービスとして開始すると、Logstashは下にあるすべての構成ファイルを自動的に実行します /etc/logstash/conf.d バックグラウンドで。 次のコマンドを実行して、サービスとして開始します。

  1. sudo systemctl start logstash

このステップでは、Logstashを実行して、データベースに接続してデータを収集できるかどうかを確認しました。 次に、Kibanaの統計データのいくつかを視覚化して調査します。

ステップ4—Kibanaでインポートされたデータを探索する

このセクションでは、Kibanaでのデータベースのパフォーマンスを説明する統計データを調べて視覚化します。

Webブラウザーで、前提条件の一部としてKibanaを公開したドメインに移動します。 デフォルトのウェルカムページが表示されます。

LogstashがElasticsearchに送信するデータを調べる前に、まず pg_stat_database Kibanaのインデックス。 これを行うには、最初に自分で探索を押してから、左上隅にあるハンバーガーメニューを開きます。 Analytics の下で、Discoverをクリックします。

次に、Kibanaは新しいインデックスパターンを作成するように促します。

インデックスパターンの作成を押します。 新しいインデックスパターンを作成するためのフォームが表示されます。 Kibanaのインデックスパターンは、複数のElasticsearchインデックスから一度にデータを取得する方法を提供し、1つのインデックスのみを探索するために使用できます。

右側にリストされているのは、Logstashが統計を送信している3つのインデックスです。 入力します pg_stat_database Name テキストフィールドで、 @timestamp ドロップダウンからタイムスタンプフィールドとして。 完了したら、下のインデックスパターンの作成ボタンを押します。

既存のビジュアライゼーションを作成して表示するには、ハンバーガーメニューを開きます。 Analytics で、Dashboardを選択します。 ロードされたら、新しいダッシュボードの作成をクリックし、視覚化の作成を押して新しいダッシュボードの作成を開始します。

左側のパネルには、Kibanaが視覚化を描画するために使用できる値のリストが表示されます。これは画面の中央部分に表示されます。 画面の右上には、日付範囲ピッカーがあります。 の場合 @timestamp フィールドが視覚化で使用されている場合、Kibanaは範囲ピッカーで指定された時間間隔に属するデータのみを表示します。

これで、データタプルの平均数を視覚化できます INSERT指定された間隔で分単位で編集されます。 ページのメイン部分にあるドロップダウンから、 Line andareaセクションの下のLineを選択します。 次に、 tup_inserted 左側のリストからフィールドを移動し、中央部分にドラッグします。 中央値の量の線の視覚化がすぐに表示されます INSERT 時間の経過とともにクエリを実行します。 データベースが新しく、使用されていない場合、まだ何も表示されません。 ただし、すべての場合において、データベースの使用状況が正確に描写されています。

右側では、水平軸と垂直軸の処理方法を構成できます。 そこで、表示された軸を押すことにより、中央値の代わりに平均値を表示するように垂直軸を設定できます。

別の機能を選択するか、独自の機能を提供することができます。

グラフは、更新された値ですぐに更新されます。

このステップでは、Kibanaを使用してPostgreSQL統計データの一部を視覚化する方法を学習しました。

ステップ5—(オプション)pgbenchを使用したベンチマーク

このチュートリアル以外でデータベースをまだ使用していない場合は、pgbenchを使用してデータベースのベンチマークを行うことにより、この手順を完了して、より興味深い視覚化を作成できます。 pgbenchは、同じSQLコマンドを繰り返し実行し、実際のクライアントによる実際のデータベースの使用をシミュレートします。

最初に、次のコマンドを実行してpgbenchをインストールする必要があります。

  1. sudo apt install postgresql-contrib -y

pgbenchはテストデータを挿入および更新するため、そのために別のデータベースを作成する必要があります。 これを行うには、 ユーザーとデータベース管理対象データベースのコントロールパネルのタブをクリックし、下にスクロールしてデータベースセクション。 入力します pgbench 新しいデータベースの名前として、保存を押します。 この名前、ホスト、ポート、およびユーザー名の情報をpgbenchに渡します。

実際に実行する前に pgbench、で実行する必要があります -i データベースを初期化するためのフラグ:

  1. pgbench -h host -p port -U username -i pgbench

交換する必要があります host あなたのホストアドレスで、 port データベースに接続できるポートを使用して、 username データベースユーザーのユーザー名を使用します。 これらの値はすべて、管理対象データベースのコントロールパネルにあります。

注意してください pgbench パスワード引数はありません。 代わりに、実行するたびに要求されます。

出力は次のようになります。

Output
NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping creating tables... 100000 of 100000 tuples (100%) done (elapsed 0.16 s, remaining 0.00 s) vacuum... set primary keys... done.

pgbench ベンチマークに使用する4つのテーブルを作成し、それらにいくつかのサンプル行を入力しました。 これで、ベンチマークを実行できるようになります。

ベンチマークの実行時間を制限する2つの最も重要な引数は次のとおりです。 -t、完了するトランザクションの数を指定し、および -T、ベンチマークを実行する秒数を定義します。 これらの2つのオプションは相互に排他的です。 各ベンチマークの最後に、1秒あたりのトランザクション数などの統計を受け取ります(tps).

次に、次のコマンドを実行して、30秒間続くベンチマークを開始します。

  1. pgbench -h host -p port -U username pgbench -T 30

出力は次のようになります。

Output
starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 30 s number of transactions actually processed: 11991 latency average = 2.502 ms tps = 399.664353 (including connections establishing) tps = 399.987202 (excluding connections establishing)

この出力には、実行されたトランザクションの総数など、ベンチマークに関する一般的な情報が表示されます。 これらのベンチマークの効果は、LogstashがElasticsearchに送信する統計がその数値を反映することです。これにより、Kibanaでの視覚化がより面白くなり、実際のグラフに近くなります。 上記のコマンドをさらに数回実行し、場合によっては期間を変更できます。

完了したら、Kibanaに移動し、右上隅にあるRefreshを押します。 以前とは異なる行が表示されます。これは、 INSERTs。 更新ボタンの上にあるピッカーの値を変更して、表示されるデータの時間範囲を自由に変更してください。 さまざまな期間の複数のベンチマークの後にグラフがどのように表示されるかを次に示します。

pgbenchを使用してデータベースのベンチマークを行い、結果のグラフをKibanaで評価しました。

結論

これで、Elasticスタックがサーバーにインストールされ、管理対象のPostgreSQLデータベースから統計データを定期的にプルするように構成されました。 Kibanaまたはその他の適切なソフトウェアを使用してデータを分析および視覚化できます。これにより、データベースのパフォーマンスに関する貴重な洞察と実際の相関関係を収集できます。

PostgreSQLマネージドデータベースでできることの詳細については、 productdocsにアクセスしてください。