PostgreSQLベースのアプリケーションパフォーマンス：明示的および非表示の遅延

PostgreSQLベースのアプリケーションのパフォーマンスを最適化しようとしている場合は、おそらくEXPLAIN（BUFFERS、ANALYZE）、pg_stat_statements、auto_explain、log_statement_min_durationなどの基本ツールを使用します。

おそらく、 log_lock_waitsとのブロックの競合を探している、ブレークポイントの動作を監視している、など。

しかし、ネットワーク遅延について考えたことはありますか？ プレーヤーはそれを知っていますが、これはアプリケーションを使用するサーバーに関連していますか？

遅延の影響

一般的なクライアント/サーバーリングネットワークの遅延は、0.01ミリ秒（ローカル）からスイッチドネットワークで〜0.5ミリ秒、WiFiで5ミリ秒、ADSLで20ミリ秒、大陸間ルーティングで300ミリ秒、衛星やWWANなどでさらに変化しますリンク。

些細なSELECTは、サーバー側で実行するのに約0.1 msかかります。 自明な挿入 0.5ミリ秒。

アプリケーションが要求を実行するたびに、成功/エラー、場合によっては結果、要求メタデータなどを含むサーバーからの応答を待機する必要があります。 これには、ネットワーク上で少なくとも1つの往復遅延が伴います。

主に小規模で単純なクエリを使用する場合、データベースとアプリケーションが異なるサーバー上にあると、ネットワーク遅延が実行時間に大きく影響する可能性があります。

ほとんどのアプリケーション、特にORMは、非常に単純なクエリを多数実行する傾向があります。 たとえば、Hibernateアプリケーションが、遅延して抽出された1000個の子オブジェクトへの@OneToManyリレーションを介してエンティティを取得する場合、n + 1フェッチの問題により、おそらく1001リクエストを実行する予定です。 これは、待機中の往復パスでネットワークを数千回遅延させることを意味します。 これを回避するために左結合フェッチを使用できますが、その後、親要素を1000回JOINに渡し、重複排除する必要があります。

同様に、ORMを介してデータベースにデータを入力する場合、おそらく数十万の通常のINSERTを実行し、サーバーからの個々のINSERTの確認がすべて完了したことを待機します。

クエリの実行時間に集中して最適化を試みるのは十分簡単ですが、最も単純なINSERT INTO ... VALUES ...でできることには多数のオプションがあります。 いくつかのインデックスと制約を削除し、トランザクション内にあることを確認すれば、ほぼ完了です。

しかし、ネットワークのすべての期待を取り除くのはどうですか？ ローカルネットワーク上であっても、1000回のリクエストの後、成長し始めます。

コピー

遅延を回避する1つの方法は、 COPYを使用することです。 PostgreSQlのCOPYサポートを使用するには、アプリケーションまたはドライバーがCSVのような文字列のセットを生成し、連続したシーケンスでサーバーにブロードキャストする必要があります。 または、サーバーがアプリケーションにCSVのようなストリームを送信するように求められる場合があります。

いずれの場合でも、アプリケーションはCOPYを他のクエリと切り替えることはできず、コピーと貼り付けを宛先テーブルに直接ロードする必要があります。 一般的なアプローチは、COPYを一時テーブルに作成し、そのテーブルからすでにINSERT INTO ... SELECT ...、UPDATE ... FROM ....、DELETE FROM ... USING ...などを使用して、 1つの操作でメインテーブルを変更するためのコピーされたデータ。

これは、独自のSQLを直接記述する場合に便利ですが、フレームワークとORMはこれをサポートしていません。さらに、単純な挿入のみを直接置き換えることができます。 アプリケーション、フレームワーク、またはユーザードライバーは、COPYに必要な特別なプレゼンテーションを処理し、このために必要なメタデータを探す必要があります。

（COPYを実際にサポートするよく知られているドライバーには、libpq、PgJDBC、psycopg2、Pg gemなどがありますが、それらに基づいて構築されたフレームワークとORMSもCOPYをサポートする必要はありません。）

PgJDBC-グループ化モード

PostgreSQLのJDBCドライバーには、この問題に対する解決策があります。 JDBCドライバーAPIのグループ化の機能に基づいています。サーバーに要求のグループを送信し、その後、セット全体が成功したことを確認するのを1回だけ待機します。

少なくとも理論的には。 実際、いくつかの実装上の問題により、このすべてが一度にバンドルごとに数百のリクエストに制限されます。 また、ドライバーは、結果が時間の経過とともにどれだけ大きくなるかを判断できる場合に、結果をセットとして返すクエリのみを実行できます。 これらの制限にもかかわらず、 Statement.executeBatch（）を使用すると、リモートデータベースインスタンスからのデータのバルクロードなどのタスクを実行するアプリケーションのパフォーマンスが大幅に向上します。

これは標準APIであるため、複数のDBMSで実行されるアプリケーションで使用できます。 たとえば、HibernateはJDBCグループ化を使用できますが、デフォルトではこれを行いません。

libpqとグループ化

他のほとんどの（すべて？）PostgreSQLドライバーはグループ化をサポートしていません。 PgJDBCはPostgreSQLプロトコルを完全に独立して使用しますが、他のほとんどのドライバーはPostgreSQLの一部として提供されるC libpqライブラリを使用します。

libpqには非同期の非ブロッキングAPIがありますが、ユーザーは一度に1つのクエリのみを実行できます。 次のクエリを送信する前に、このクエリの結果が受信されるのを待つ必要があります。

PostgreSQLサーバーはグループ化をうまくサポートしており、PgJDBCはすでにそれを使用しています。 この点で、 私はlibpqのグループ化サポートを作成し 、PostgreSQLの次のバージョンで提案しました 。 確認された場合、クライアントを変更するだけなので、古いサーバーに接続するときのプロセスをさらに高速化します。

著者およびlibpqベースのユーザードライバーの上級ユーザーとlibpqベースのアプリケーションの開発者からのフィードバックに非常に興味があります。 パッチは、試してみたい場合にPostgreSQLバージョン9.6ベータ1に正常に適用されます。 詳細なドキュメントと包括的なサンプルプログラムが用意されています 。

性能

RDSまたはHeroku Postgresに基づいたDBMSサーバーは、説明されている機能が有用な好例です。 特に、自分のネットワークの外部からそれらにアクセスすると、遅延がどのように損傷する可能性があるかを完全に示しています。

〜320ミリ秒のネットワーク遅延：

• グループ化なしの500挿入：167.0秒
• グループ化による500の挿入：1.2秒

...これは約120倍高速です。

原則として、アプリケーションが配置されているサーバーとデータベースが配置されているサーバー間で大陸間接続を使用しないでください。これらの同じサーバーを使用して、遅延の影響を示します。 ローカルホスト上のUnixソケットを使用しても、10,000回の挿入でパフォーマンスが50％向上しました。

既存のアプリケーションのグループ化

残念ながら、既存のアプリケーションのグループ化を自動的に有効にすることはできません。 一連の要求を送信してから結果を要求する場合、わずかに異なるインターフェイスを使用する必要があります。

特に、非ブロッキングモードとselect（）/ poll（）/ epoll（）/ WaitForMultipleObjectsExループを使用する場合は、非同期libpqインターフェースを既に使用しているアプリケーションを適応させるのは簡単です 。 同期libpqインターフェイスを使用するアプリケーションでは、さらに変更が必要になります。

他のカスタムドライバーのグループ化

同様に、ユーザードライバー、フレームワーク、およびORMは通常、グループ化を可能にするためにインターフェイスと内部変更を必要とします。 既にイベントループとノンブロッキングI / Oを使用している場合は、かなり簡単に変更できます。

Python、Ruby、およびこの機能を使用できる他のユーザーに会えて嬉しいので、誰が興味を持っているかを知りたいです。 これができると想像してください：

import psycopg2 conn = psycopg2.connect(...) cur = conn.cursor() # this is just an idea, this code does not work with psycopg2: futures = [ cur.async_execute(sql) for sql in my_queries ] for future in futures: result = future.result # waits if result not ready yet ... process the result ... conn.commit() 

非同期のグループ化は、ユーザーが複雑にする必要はありません。

COPYは最速です

実用的な顧客はまだコピーを好む。 ここに私のラップトップからのいくつかの結果があります：

 inserting 1000000 rows batched, unbatched and with COPY batch insert elapsed: 23.715315s sequential insert elapsed: 36.150162s COPY elapsed: 1.743593s Done. 

作業をグループ化すると、ローカルUNIXソケットでも驚くほど大きなパフォーマンスが向上しますが、COPYでは、個々のINSERTアプローチの両方がはるかに遅れています。

COPYを使用します。

画像

この記事の画像は、西オーストラリアのパース近くのマンダリングウィアーから内部（砂漠）の金鉱山までのゴールドフィールズ給水スキームパイプラインです。 彼女はこの記事にアプローチします。なぜなら、その建設の長さと批判のせいで、その創始者であり主な創始者であるCY O'Connorは、パイプラインが委託される12ヶ月前に自殺したからです。 地元の人々は、パイプラインが開かれた後に彼が亡くなったとしばしば（間違って）言いますが、水は流れませんでした-パイプラインプロジェクトが失敗であると誰もが考えるほど時間がかかりました。 しかし、数週間後、水は行きました。