前書き

DigitalOcean Managed Databasesでは、いくつかの方法を使用してPostgreSQLデータベースをスケーリングできます。 そのような方法の1つは、多数のクライアント接続を効率的に処理し、これらの開いている接続のCPUとメモリのフットプリントを削減できるビルトイン接続プーラーです。 接続プールを使用し、固定された一連のリサイクル可能な接続を共有することにより、同時クライアント接続を大幅に増やし、PostgreSQLデータベースのパフォーマンスをさらに高めることができます。

このチュートリアルでは、PostgreSQLの組み込みベンチマークツールである `+ pgbench `を使用して、DigitalOcean Managed PostgreSQL Databaseで負荷テストを実行します。 接続プールに飛び込み、それらがどのように機能するかを説明し、Cloud Controlパネルを使用して接続プールを作成する方法を示します。 最後に、「 pgbench +」テストの結果を使用して、接続プールの使用がデータベースのスループットを向上させる安価な方法になることを示します。

前提条件

このチュートリアルを完了するには、次のものが必要です。

  • DigitalOceanマネージPostgreSQLデータベースクラスター。 DigitalOcean PostgreSQLクラスターをプロビジョニングおよび構成する方法については、Managed Database https://www.digitalocean.com/docs/databases [製品ドキュメント]を参照してください。

  • PostgreSQLがインストールされたクライアントマシン。 デフォルトでは、PostgreSQLインストールには、このガイドで使用する `+ pgbench `ベンチマークユーティリティと ` psql +`クライアントが含まれます。 インストール方法については、https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04 [Ubuntu 18.04にPostgreSQLをインストールして使用する方法]を参照してください。 PostgreSQL。 クライアントマシンでUbuntuを実行していない場合は、バージョンファインダーを使用して適切なチュートリアルを見つけることができます。

DigitalOcean PostgreSQLクラスターを起動して実行し、 `+ pgbench +`がインストールされたクライアントマシンを作成したら、このガイドから始める準備ができました。

ステップ1-`+ benchmark +`データベースの作成と初期化

データベースの接続プールを作成する前に、まずPostgreSQLクラスターに + benchmark +`データベースを作成し、 `+ pgbench +`がテストを実行するダミーデータを追加します。 `+ pgbench +`ユーティリティは、複数のスレッドとクライアントを使用して、トランザクションで一連の5つのSQLコマンド( `+ SELECT ++ UPDATE +、および `+ INSERT `クエリで構成される)を繰り返し実行し、有用なパフォーマンスメトリックを計算します** T **トランザクション** p ** er ** S ** econd(TPS)と呼ばれます。 TPSはデータベースのスループットの測定値であり、データベースによって1秒間に処理されたアトミックトランザクションの数をカウントします。 ` pgbench `によって実行される特定のコマンドの詳細については、https://www.postgresql.org/docs/10/pgbench.html#id-1.9.4.10.7.2 [実際に実行される「トランザクション」とはpgbench?]公式の ` pgbench +`ドキュメントから。

PostgreSQLクラスターに接続して、 `+ benchmark +`データベースを作成することから始めましょう。

まず、* Databases に移動し、PostgreSQLクラスターを見つけて、クラスターの Connection Details *を取得します。 クラスターをクリックします。 次の[接続の詳細]ボックスを含むクラスター概要ページが表示されます。

image:https://assets.digitalocean.com/articles/managed_db_pools/conn_details.png [PostgreSQL Cluster Connection Details]

これから、次の構成変数を解析できます。

  • 管理ユーザー: + doadmin +

  • 管理者のパスワード:

  • クラスターエンドポイント: + dbaas-test-do-user-3587522-0.db.ondigitalocean.com +

  • 接続ポート: + 25060 +

  • 接続するデータベース: + defaultdb +

  • SSLモード: + require +(セキュリティを強化するにはSSL暗号化接続を使用します)

`+ psql `クライアントと ` pgbench +`ツールの両方を使用するときに必要になるため、これらのパラメーターに注意してください。

このボックスの上にあるドロップダウンをクリックして、*接続文字列*を選択します。 この文字列をコピーし、 `+ psql +`に渡して、このPostgreSQLノードに接続します。

`+ psql +`とコピーした接続文字列を使用してクラスターに接続します。

psql postgresql://doadmin:@:25060/defaultdb?sslmode=require

次のPostgreSQLクライアントプロンプトが表示され、PostgreSQLクラスターに正常に接続されたことを示します。

Outputpsql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

defaultdb=>

ここから、 `+ benchmark +`データベースを作成します:

CREATE DATABASE benchmark;

次のような出力が表示されるはずです。

OutputCREATE DATABASE

次に、クラスターから切断します。

\q

`+ pgbench `テストを実行する前に、テストの実行に必要ないくつかのテーブルとダミーデータをこの ` benchmark +`データベースに追加する必要があります。

これを行うには、次のフラグを指定して `+ pgbench +`を実行します。

  • + -h +:PostgreSQLクラスターエンドポイント

  • + -p +:PostgreSQLクラスター接続ポート

  • + -U +:データベースのユーザー名

  • + -i +:ベンチマークテーブルとそのダミーデータで `+ benchmark +`データベースを初期化することを示します。

  • + -s +:150のスケールファクターを設定します。これにより、テーブルサイズが150倍されます。 デフォルトのスケール係数「1」では、次のサイズのテーブルが作成されます。

    table                   # of rows
    ---------------------------------
    pgbench_branches        1
    pgbench_tellers         10
    pgbench_accounts        100000
    pgbench_history         0

    + 150のスケール係数を使用すると、 `+ pgbench_accounts +`テーブルには15,000,000行が含まれます。

完全な `+ pgbench +`コマンドを実行します。

pgbench -h  -p 25060 -U doadmin -i -s 150 benchmark

このコマンドを実行すると、指定したデータベースユーザーのパスワードを入力するよう求められます。 パスワードを入力し、「+ ENTER」を押します。

次のような出力が表示されるはずです。

Outputdropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 15000000 tuples (0%) done (elapsed 0.19 s, remaining 27.93 s)
200000 of 15000000 tuples (1%) done (elapsed 0.85 s, remaining 62.62 s)
300000 of 15000000 tuples (2%) done (elapsed 1.21 s, remaining 59.23 s)
400000 of 15000000 tuples (2%) done (elapsed 1.63 s, remaining 59.44 s)
500000 of 15000000 tuples (3%) done (elapsed 2.05 s, remaining 59.51 s)
. . .
14700000 of 15000000 tuples (98%) done (elapsed 70.87 s, remaining 1.45 s)
14800000 of 15000000 tuples (98%) done (elapsed 71.39 s, remaining 0.96 s)
14900000 of 15000000 tuples (99%) done (elapsed 71.91 s, remaining 0.48 s)
15000000 of 15000000 tuples (100%) done (elapsed 72.42 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

この時点で、 `+ pgbench +`テストの実行に必要なテーブルとデータが入力されたベンチマークデータベースを作成しました。 接続プーリングを有効にする前後のパフォーマンスを比較するために使用するベースラインテストの実行に進むことができます。

ステップ2-ベースライン `+ pgbench +`テストの実行

最初のベンチマークを実行する前に、接続プールで最適化しようとしているものに飛び込む価値があります。

通常、クライアントがPostgreSQLデータベースに接続すると、メインのPostgreSQL OSプロセスは、この新しい接続に対応する子プロセスに分岐します。 少数の接続しかない場合、これにより問題が発生することはほとんどありません。 ただし、クライアントと接続の規模が拡大すると、特に問題のアプリケーションがデータベース接続を効率的に使用していない場合、これらの接続の作成と保守のCPUとメモリのオーバーヘッドが増加し始めます。 さらに、PostgreSQLの「+ max_connections +」設定により、許可されるクライアント接続の数が制限され、追加の接続が拒否またはドロップされる可能性があります。

image:https://assets.digitalocean.com/articles/managed_db_pools/no_connection_pool.png [プーリングなしのデータベース接続]

接続プールは、一定数のデータベース接続(* pool size *)を開いたままにし、それを使用してクライアント要求を配布および実行します。 つまり、はるかに多くの同時接続に対応し、アイドル状態のクライアントまたは停滞しているクライアントを効率的に処理し、トラフィックの急増時にクライアント要求を拒否するのではなくキューに入れることができます。 接続をリサイクルすることにより、接続ボリュームが重い環境でマシンのリソースをより効率的に使用し、データベースから余分なパフォーマンスを引き出すことができます。

image:https://assets.digitalocean.com/articles/managed_db_pools/with_connection_pool.png [プーリングによるデータベース接続]

接続プールは、アプリケーション側で、またはデータベースとアプリケーション間のミドルウェアとして実装できます。 Managed Databases接続プーラーは、https://pgbouncer.github.io/ [pgBouncer]の上に構築されます。これは、PostgreSQL用の軽量でオープンソースのミドルウェア接続プーラーです。 そのインターフェイスは、クラウドコントロールパネルUIを介して利用できます。

コントロールパネルの[データベース]に移動し、PostgreSQLクラスターをクリックします。 ここから、*接続プール*をクリックします。 次に、[接続プールの作成]をクリックします。 次の構成ウィンドウが表示されます。

image:https://assets.digitalocean.com/articles/managed_db_pools/connection_pool_blank.png [接続プール設定ウィンドウ]

ここでは、次のフィールドを構成できます。

  • プール名:接続プールの一意の名前

  • データベース:接続をプールするデータベース

  • ユーザー:接続プールが認証するPostgreSQLユーザー

  • モードセッショントランザクション、または*ステートメント*のいずれか。 このオプションは、プールがクライアントにバックエンド接続を割り当てる時間を制御します。

  • セッション:クライアントは、明示的に切断するまで接続を保持します。

  • トランザクション:クライアントは、トランザクションが完了するまで接続を取得し、その後、接続はプールに返されます。

  • ステートメント:各クライアントステートメントの後に、プールは接続を積極的にリサイクルします。 ステートメントモードでは、複数ステートメントのトランザクションは許可されません。 詳細については、接続プールhttps://www.digitalocean.com/docs/databases/how-to/postgresql/manage-connection-pools [製品ドキュメント]を参照してください。

  • プールサイズ:接続プールがそれ自体とデータベースの間で開いたままにする接続の数。

接続プールを作成する前に、データベースのパフォーマンスと接続プールを比較できるベースラインテストを実行します。

このチュートリアルでは、4 GB RAM、2 vCPU、80 GBディスク、プライマリノードのみの管理されたデータベースのセットアップを使用します。 PostgreSQLクラスターの仕様に従って、このセクションのベンチマークテストパラメーターをスケーリングできます。

DigitalOcean Managed Databaseクラスターには、PostgreSQLの `+ max_connections `パラメーターが1 GB RAMあたり25接続にプリセットされています。 したがって、4 GB RAMのPostgreSQLノードには、「 max_connections +」が100に設定されています。 さらに、すべてのクラスターで、3つの接続がメンテナンス用に予約されています。 したがって、この4 GB RAMのPostgreSQLクラスターでは、97の接続が接続プーリングに使用できます。

これを念頭に置いて、最初のベースラインの「+ pgbench +」テストを実行しましょう。

クライアントマシンにログインします。 通常どおり、データベースのエンドポイント、ポート、ユーザーを指定して、「+ pgbench +」を実行します。 さらに、次のフラグを提供します。

  • + -c +:シミュレートする同時クライアントまたはデータベースセッションの数。 これを50に設定して、PostgreSQLクラスターの `+ max_connections +`パラメーターよりも少ない同時接続数をシミュレートします。

  • + -j +: `+ pgbench `がベンチマークの実行に使用するワーカースレッドの数。 マルチCPUマシンを使用している場合、これを上方に調整して、クライアントをスレッドに分散できます。 2コアマシンでは、これを「+2」に設定します。

  • + -P +:進行状況とメトリックを `+ 60 +`秒ごとに表示します。

  • + -T +:ベンチマークを `+ 600 +`秒(10分)実行します。 一貫性のある再現可能な結果を​​生成するには、ベンチマークを数分間、または1つのチェックポイントサイクルで実行することが重要です。

また、以前に作成してデータを入力した「+ benchmark +」データベースに対してベンチマークを実行することを指定します。

次の完全な `+ pgbench +`コマンドを実行します。

pgbench -h  -p 25060 -U doadmin -c 50 -j 2 -P 60 -T 600 benchmark

`+ ENTER `を押し、 ` doadmin +`ユーザーのパスワードを入力してテストの実行を開始します。 次のような出力が表示されるはずです(結果はPostgreSQLクラスターの仕様によって異なります)。

Outputstarting vacuum...end.
progress: 60.0 s, 157.4 tps, lat 282.988 ms stddev 40.261
progress: 120.0 s, 176.2 tps, lat 283.726 ms stddev 38.722
progress: 180.0 s, 167.4 tps, lat 298.663 ms stddev 238.124
progress: 240.0 s, 178.9 tps, lat 279.564 ms stddev 43.619
progress: 300.0 s, 178.5 tps, lat 280.016 ms stddev 43.235
progress: 360.0 s, 178.8 tps, lat 279.737 ms stddev 43.307
progress: 420.0 s, 179.3 tps, lat 278.837 ms stddev 43.783
progress: 480.0 s, 178.5 tps, lat 280.203 ms stddev 43.921
progress: 540.0 s, 180.0 tps, lat 277.816 ms stddev 43.742
progress: 600.0 s, 178.5 tps, lat 280.044 ms stddev 43.705
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 105256
latency average = 282.039 ms
latency stddev = 84.244 ms
tps = 175.329321 (including connections establishing)
tps = 175.404174 (excluding connections establishing)

ここでは、50の同時セッションで10分間実行すると、105,256トランザクションを処理し、1秒あたり約175トランザクションのスループットを実現しました。

次に、同じテストを実行します。今回は150の同時クライアントを使用し、このデータベースの `+ max_connections +`よりも高い値を使用して、クライアント接続の大量流入を総合的にシミュレートします。

pgbench -h  -p 25060 -U doadmin -c 150 -j 2 -P 60 -T 600 benchmark

次のような出力が表示されます。

Outputstarting vacuum...end.
connection to database "pgbench" failed:
FATAL:  remaining connection slots are reserved for non-replication superuser connections
progress: 60.0 s, 182.6 tps, lat 280.069 ms stddev 42.009
progress: 120.0 s, 253.8 tps, lat 295.612 ms stddev 237.448
progress: 180.0 s, 271.3 tps, lat 276.411 ms stddev 40.643
progress: 240.0 s, 273.0 tps, lat 274.653 ms stddev 40.942
progress: 300.0 s, 272.8 tps, lat 274.977 ms stddev 41.660
progress: 360.0 s, 250.0 tps, lat 300.033 ms stddev 282.712
progress: 420.0 s, 272.1 tps, lat 275.614 ms stddev 42.901
progress: 480.0 s, 261.1 tps, lat 287.226 ms stddev 112.499
progress: 540.0 s, 272.5 tps, lat 275.309 ms stddev 41.740
progress: 600.0 s, 271.2 tps, lat 276.585 ms stddev 41.221
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 150
number of threads: 2
duration: 600 s
number of transactions actually processed: 154892
latency average = 281.421 ms
latency stddev = 125.929 ms
tps = 257.994941 (including connections establishing)
tps = 258.049251 (excluding connections establishing)

「+ FATAL 」エラーに注意してください。これは、「 pgbench 」が「 max_connections +」で設定された100の接続制限しきい値に達し、接続が拒否されたことを示します。 テストは約257のTPSでまだ完了できました。

この時点で、接続プールがデータベースのスループットをどのように改善できるかを調査できます。

手順3-接続プールの作成とテスト

このステップでは、接続プールを作成し、以前の「+ pgbench +」テストを再実行して、データベースのスループットを改善できるかどうかを確認します。

一般的に、 `+ max_connections `設定と接続プールのパラメーターは、データベースの負荷を最大化するために連携して調整されます。 ただし、 ` max_connections +`はDigitalOcean Managed Databasesのユーザーから抽象化されているため、ここでの主なレバーは接続プールの* Mode および Size *設定です。

まず、利用可能なすべてのバックエンド接続を開いたままにする* Transaction *モードで接続プールを作成しましょう。

コントロールパネルの[データベース]に移動し、PostgreSQLクラスターをクリックします。 ここから、*接続プール*をクリックします。 次に、[接続プールの作成]をクリックします。

表示される構成ウィンドウで、次の値を入力します。

image:https://assets.digitalocean.com/articles/managed_db_pools/connection_pool_full.png [接続プールの構成値]

ここでは、接続プールに* test-pool という名前を付け、 benchmark データベースで使用します。 データベースユーザーは doadmin であり、接続プールを Transaction *モードに設定します。 4GBのRAMを備えたマネージドデータベースクラスターには、97の利用可能なデータベース接続があることを思い出してください。 したがって、97個のデータベース接続が開いたままになるようにプールを構成します。

完了したら、[プールの作成]をクリックします。

コントロールパネルにこのプールが表示されます。

image:https://assets.digitalocean.com/articles/managed_db_pools/connection_pool_ui.png [コントロールパネルの接続プール]

[接続の詳細]をクリックしてURIを取得します。 次のようになります。

postgres://doadmin:@:/test-pool?sslmode=require

ここで異なるポートに気付くはずです。また、プール名 `+ test-pool +`に対応する、異なるエンドポイントとデータベース名に気付くはずです。

+ test-pool`接続プールを作成したので、上で実行した + pgbench`テストを再実行できます。

`+ pgbench +`を再実行します

クライアントマシンから、次の `+ pgbench +`コマンド(150の同時クライアントを使用)を実行し、強調表示された値を接続プールURIの値に置き換えてください:

pgbench -h  -p  -U doadmin -c 150 -j 2 -P 60 -T 600 test-pool

ここでも、150の同時クライアントを使用し、2つのスレッドでテストを実行し、60秒ごとに進行状況を出力し、600秒間テストを実行します。 データベース名を接続プールの名前である「+ test-pool +」に設定します。

テストが完了すると、次のような出力が表示されます(これらの結果は、データベースノードの仕様によって異なることに注意してください)。

Outputstarting vacuum...end.
progress: 60.0 s, 240.0 tps, lat 425.251 ms stddev 59.773
progress: 120.0 s, 350.0 tps, lat 428.647 ms stddev 57.084
progress: 180.0 s, 340.3 tps, lat 440.680 ms stddev 313.631
progress: 240.0 s, 364.9 tps, lat 411.083 ms stddev 61.106
progress: 300.0 s, 366.5 tps, lat 409.367 ms stddev 60.165
progress: 360.0 s, 362.5 tps, lat 413.750 ms stddev 59.005
progress: 420.0 s, 359.5 tps, lat 417.292 ms stddev 60.395
progress: 480.0 s, 363.8 tps, lat 412.130 ms stddev 60.361
progress: 540.0 s, 351.6 tps, lat 426.661 ms stddev 62.960
progress: 600.0 s, 344.5 tps, lat 435.516 ms stddev 65.182
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 150
number of threads: 2
duration: 600 s
number of transactions actually processed: 206768
latency average = 421.719 ms
latency stddev = 114.676 ms
tps = 344.240797 (including connections establishing)
tps = 344.385646 (excluding connections establishing)

ここで、150の同時接続でデータベースのスループットを257 TPSから344 TPSに増やすことができ(33%の増加)、接続プールなしで以前にヒットした「+ max_connections +」の制限に達していないことに注意してください。 接続プールをデータベースの前に配置することにより、接続のドロップを回避し、同時接続が多数ある環境でデータベースのスループットを大幅に向上させることができます。

この同じテストを実行し、50の(+ -c +)値(より少ない数のクライアントを指定)を使用すると、接続プールを使用することによるメリットはそれほど明白ではなくなります。

Outputstarting vacuum...end.
progress: 60.0 s, 154.0 tps, lat 290.592 ms stddev 35.530
progress: 120.0 s, 162.7 tps, lat 307.168 ms stddev 241.003
progress: 180.0 s, 172.0 tps, lat 290.678 ms stddev 36.225
progress: 240.0 s, 172.4 tps, lat 290.169 ms stddev 37.603
progress: 300.0 s, 177.8 tps, lat 281.214 ms stddev 35.365
progress: 360.0 s, 177.7 tps, lat 281.402 ms stddev 35.227
progress: 420.0 s, 174.5 tps, lat 286.404 ms stddev 34.797
progress: 480.0 s, 176.1 tps, lat 284.107 ms stddev 36.540
progress: 540.0 s, 173.1 tps, lat 288.771 ms stddev 38.059
progress: 600.0 s, 174.5 tps, lat 286.508 ms stddev 59.941
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 102938
latency average = 288.509 ms
latency stddev = 83.503 ms
tps = 171.482966 (including connections establishing)
tps = 171.553434 (excluding connections establishing)

ここでは、接続プールを使用してスループットを向上させることができなかったことがわかります。 スループットは175 TPSから171 TPSに低下しました。

このガイドでは、組み込みのベンチマークデータセットで「+ pgbench +」を使用しますが、接続プールを使用するかどうかを判断するための最適なテストは、データベースの運用負荷を運用データに対して正確に表すベンチマーク負荷です。 カスタムベンチマークスクリプトとデータの作成はこのガイドの範囲外ですが、詳細については公式のhttps://www.postgresql.org/docs/10/pgbench.html[pgbench documentation]を参照してください。

結論

DigitalOcean Managed Databases接続プーリングは、データベースから余分なパフォーマンスを迅速に引き出すことができる強力な機能です。 レプリケーション、キャッシング、シャーディングなどの他の手法に加えて、接続プーリングは、データベースレイヤーをスケーリングして、さらに多くのリクエストを処理するのに役立ちます。

このガイドでは、PostgreSQLの組み込みの「+ pgbench +」ベンチマークツールとそのデフォルトのベンチマークテストを使用した、単純化された総合的なテストシナリオに焦点を当てました。 生産シナリオでは、生産負荷をシミュレートしながら、実際の生産データに対してベンチマークを実行する必要があります。 これにより、特定の使用パターンに合わせてデータベースを調整できます。

`+ pgbench +`の他に、データベースのベンチマークとロードを行う他のツールがあります。 Perconaが開発したこのようなツールの1つがhttps://github.com/Percona-Lab/sysbench-tpcc[sysbench-tpcc]です。 もう1つは、Apacheのhttps://jmeter.apache.org/[JMeter]です。これは、テストデータベースとWebアプリケーションをロードできます。

DigitalOcean Managed Databasesの詳細については、Managed Databases https://www.digitalocean.com/docs/databases [製品ドキュメント]をご覧ください。 別の有用なスケーリング手法であるシャーディングの詳細については、https://www.digitalocean.com/community/tutorials/understanding-database-sharding [データベースシャーディングの理解]を参照してください。