著者は、 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に保存します。

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

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

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

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

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

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

お気に入りのエディター(nanoなど)を使用してpostgresql.confを作成します。

  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_databasepg_stat_user_tables、およびpg_stat_user_indexesデータベースからデータをプルします。 PostgreSQL JDBCライブラリはLogstashが自動的にロードするフォルダにあるため、これらはすべてjdbc_driver_libraryパラメータを空の文字列に設定します。

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

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

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

3つの入力からのデータを受け入れる出力は1つだけです。 これらはすべて、ローカルで実行され、http://localhost:9200で到達可能なElasticsearchにデータを送信します。 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 [Ruby-0-Thread-34@[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 [Ruby-0-Thread-32@[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 [Ruby-0-Thread-33@[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にエラーが表示されず、3つのデータベースからの行が正常にSELECTされたことをログに記録した場合、データベースメトリックはElasticsearchに送信されます。 エラーが発生した場合は、構成ファイルのすべての値を再確認して、Logstashを実行しているマシンが管理対象データベースに接続できることを確認してください。

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

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

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

  1. sudo systemctl start logstash

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

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

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

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

Kibana - Welcome Page

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

Kibana - Discover under Analytics

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

Kibana - Index Pattern Creation

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

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

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

Kibana - New visualization

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

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

Kibana Median of INSERTs visualization

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

Kibana - Vertical axis values

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

Kibana - Axis functions

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

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

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

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

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

  1. sudo apt install postgresql-contrib -y

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

Accessing Databases section in DO control panel

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を押します。 INSERTの数を示す以前とは異なる行が表示されます。 更新ボタンの上にあるピッカーの値を変更して、表示されるデータの時間範囲を自由に変更してください。 さまざまな期間の複数のベンチマークの後でグラフがどのように表示されるかを次に示します。

Kibana - Visualization After Benchmarks

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

結論

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

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