序章

DigitalOcean Managed Databasesを使用すると、いくつかの方法を使用してPostgreSQLデータベースを拡張できます。 そのような方法の1つは、多数のクライアント接続を効率的に処理し、これらの開いている接続のCPUとメモリのフットプリントを削減できる組み込みの接続プールです。 接続プールを使用し、リサイクル可能な接続の固定セットを共有することで、より多くの同時クライアント接続を処理し、PostgreSQLデータベースから余分なパフォーマンスを引き出すことができます。

このチュートリアルでは、 pgbench、PostgreSQLの組み込みベンチマークツール。DigitalOceanマネージドPostgreSQLデータベースで負荷テストを実行します。 接続プールについて詳しく説明し、それらがどのように機能するかを説明し、クラウドコントロールパネルを使用して接続プールを作成する方法を示します。 最後に、 pgbench テストでは、接続プールの使用がデータベーススループットを向上させる安価な方法になる方法を示します。

前提条件

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

  • DigitalOceanマネージドPostgreSQLデータベースクラスター。 DigitalOcean PostgreSQLクラスターをプロビジョニングおよび構成する方法については、マネージドデータベース製品ドキュメントを参照してください。
  • PostgreSQLがインストールされているクライアントマシン。 デフォルトでは、PostgreSQLインストールには pgbench ベンチマークユーティリティと psql クライアント。どちらもこのガイドで使用します。 PostgreSQLのインストール方法については、 Ubuntu18.04にPostgreSQLをインストールして使用する方法を参照してください。 クライアントマシンでUbuntuを実行していない場合は、バージョンファインダーを使用して適切なチュートリアルを見つけることができます。

DigitalOcean PostgreSQLクラスターを稼働させ、クライアントマシンを pgbench インストールすると、このガイドを開始する準備が整います。

ステップ1—作成と初期化 benchmark データベース

データベースの接続プールを作成する前に、まず benchmark PostgreSQLクラスター上のデータベースにダミーデータを入力します。 pgbench テストを実行します。 The pgbench ユーティリティは、一連の5つのSQLコマンドを繰り返し実行します（ SELECT, UPDATE、 と INSERT クエリ）をトランザクションで実行し、複数のスレッドとクライアントを使用して、 T ransactions p er S econd（TPS）と呼ばれる有用なパフォーマンスメトリックを計算します。 TPSはデータベースのスループットの尺度であり、データベースによって1秒間に処理されたアトミックトランザクションの数をカウントします。 によって実行される特定のコマンドの詳細については、 pgbench、 pgbenchで実際に実行される「トランザクション」とは何ですか？公式から pgbench ドキュメンテーション。

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

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

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

  • 管理者ユーザー： doadmin
  • 管理者パスワード： your_password
  • クラスターエンドポイント： dbaas-test-do-user-3587522-0.db.ondigitalocean.com
  • 接続ポート： 25060
  • 接続するデータベース： defaultdb
  • SSLモード： require （セキュリティを強化するためにSSL暗号化接続を使用してください）

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

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

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

  1. psql postgresql://doadmin:your_password@your_cluster_endpoint:25060/defaultdb?sslmode=require

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

Output
psql (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 データベース：

  1. CREATE DATABASE benchmark;

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

Output
CREATE DATABASE

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

  1. \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行が含まれます。

    注：過度にブロックされたトランザクションを回避するために、スケール係数を少なくともテストする同時クライアントの数と同じ値に設定してください。 このチュートリアルでは、最大150のクライアントでテストするため、次のように設定します。 -s ここで150に。 詳細については、公式からこれらの推奨プラクティスを参照してください pgbench ドキュメンテーション。

完全に実行します pgbench 指図：

  1. pgbench -h your_cluster_endpoint -p 25060 -U doadmin -i -s 150 benchmark

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

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

Output
dropping 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とメモリのオーバーヘッドが増加し始めます。 加えて max_connections PostgreSQLの設定により、許可されるクライアント接続の数が制限され、追加の接続が拒否またはドロップされる場合があります。

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

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

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

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

  • プール名：接続プールの一意の名前
  • データベース：接続をプールするデータベース
  • User ：接続プールが認証するPostgreSQLユーザー
  • モード：セッション、トランザクション、またはステートメントのいずれか。 このオプションは、プールがクライアントにバックエンド接続を割り当てる時間を制御します。
    • セッション：クライアントは、明示的に切断されるまで接続を保持します。
    • Transaction ：クライアントは、トランザクションが完了するまで接続を取得します。完了すると、接続はプールに戻されます。
    • ステートメント：プールは、各クライアントステートメントの後に接続を積極的にリサイクルします。 ステートメントモードでは、マルチステートメントトランザクションは許可されません。 詳細については、接続プール製品ドキュメントを参照してください。
  • プールサイズ：接続プールがそれ自体とデータベースの間で開いたままにする接続の数。

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

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

DigitalOceanマネージドデータベースクラスターにはPostgreSQLがあります max_connections 1GBRAMあたり25接続にプリセットされたパラメータ。 したがって、4GBのRAMPostgreSQLノードには max_connections 100に設定します。 さらに、すべてのクラスターで、3つの接続が保守用に予約されています。 したがって、この4 GB RAM PostgreSQLクラスターでは、97の接続を接続プールに使用できます。

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

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

  • -c：シミュレートする同時クライアントまたはデータベースセッションの数。 これを50に設定して、より少ない同時接続の数をシミュレートします。 max_connections PostgreSQLクラスターのパラメーター。
  • -j：ワーカースレッドの数 pgbench ベンチマークの実行に使用します。 マルチCPUマシンを使用している場合は、これを上方に調整して、クライアントをスレッド間で分散させることができます。 2コアマシンでは、これを次のように設定します。 2.
  • -P：進捗状況と指標を毎回表示 60 秒。
  • -T：のベンチマークを実行します 600 秒（10分）。 一貫性のある再現可能な結果を生成するには、ベンチマークを数分間、または1つのチェックポイントサイクルで実行することが重要です。

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

次の完了を実行します pgbench 指図：

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

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

Output
starting 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分間実行すると、毎秒約175トランザクションのスループットで105,256トランザクションを処理したことがわかりました。

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

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

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

Output
starting 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 によって設定された100接続制限しきい値に達する max_connections、接続が拒否されます。 TPSは約257で、テストはまだ完了できました。

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

ステップ3—接続プールの作成とテスト

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

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

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

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

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

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

完了したら、プールの作成を押します。

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

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

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

ここでは、プール名に対応する別のポートと、場合によっては別のエンドポイントとデータベース名に気付くはずです。 test-pool.

これで、 test-pool 接続プール、再実行できます pgbench 上で実行したテスト。

再実行 pgbench

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

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

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

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

Output
starting 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の同時接続でデータベースのスループットを257TPSから344TPSに上げることができ（33％の増加）、 max_connections 以前に接続プールなしでヒットした制限。 データベースの前に接続プールを配置することで、接続の切断を回避し、同時接続が多数ある環境でデータベースのスループットを大幅に向上させることができます。

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

Output
starting 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)

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

このガイドでは使用しますが pgbench 組み込みのベンチマークデータセットを使用すると、接続プールを使用するかどうかを判断するための最良のテストは、本番データに対してデータベースの本番負荷を正確に表すベンチマーク負荷です。 カスタムベンチマークスクリプトとデータの作成はこのガイドの範囲を超えていますが、詳細については、公式のpgbenchドキュメントを参照してください。

注：プールサイズの設定はワークロード固有です。 このガイドでは、使用可能なすべてのバックエンドデータベース接続を使用するように接続プールを構成しました。 これは、ベンチマーク全体を通じて、データベースが完全に使用されることはめったにないためです（クラウドコントロールパネルのメトリックタブからデータベースの負荷を監視できます）。 データベースの負荷によっては、これが最適な設定ではない場合があります。 データベースが常に完全に飽和していることに気付いた場合、接続プールを縮小すると、すでにロードされているサーバーですべてを同時に実行しようとするのではなく、追加の要求をキューに入れることでスループットが向上し、パフォーマンスが向上する可能性があります。

結論

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

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

一緒に pgbench、データベースのベンチマークとロードを行うための他のツールがあります。 Perconaによって開発されたそのようなツールの1つは、sysbench-tpccです。 もう1つは、Apacheの JMeter で、テストデータベースとWebアプリケーションをロードできます。

DigitalOceanマネージドデータベースの詳細については、マネージドデータベース製品ドキュメントを参照してください。 もう1つの便利なスケーリング手法であるシャーディングの詳細については、データベースシャーディングについてを参照してください。

参考文献