PostgreSQL と JDBC はすべての機能を絞り出します。 ウラジミール・シトニコフ

Vladimir Sitnikov の 2016 年初頭のレポート「PostgreSQL と JDBC がすべての機能を絞り出している」の転写を読むことをお勧めします。

こんにちは私の名前はウラジミール・シトニコフです。 私は NetCracker で 10 年間働いています。 そして私は主に生産性を重視しています。 Java に関連するものすべて、SQL に関連するものすべてが私は大好きです。

そして今日は、PostgreSQL をデータベース サーバーとして使い始めたときに社内で遭遇した出来事について話します。 そして私たちは主に Java を使って作業します。 しかし、今日私がお話しすることは Java に限った話ではありません。 実際にやってみるとわかるように、これは他の言語でも発生します。

次のことについて話します。

• データサンプリングについて。
• データの保存について。
• そしてパフォーマンスについても。
• そしてそこに埋められている水中熊手について。

簡単な質問から始めましょう。 主キーに基づいてテーブルから XNUMX つの行を選択します。

データベースは同じホスト上にあります。 このすべてのファーミングには 20 ミリ秒かかります。

この 20 ミリ秒は長いです。 このようなリクエストが 100 件ある場合、これらのリクエストをスクロールするのに XNUMX 秒あたりの時間を費やすことになり、時間の無駄になります。

私たちはこれを行うのが好きではなく、このために基地が私たちに何を提供するかを検討します。 データベースでは、クエリを実行するための XNUMX つのオプションが提供されます。

最初のオプションは単純なリクエストです。 それの何が良いのですか？ 私たちがそれを受け取って送信するという事実だけで、それ以上のものは何もありません。

https://github.com/pgjdbc/pgjdbc/pull/478

データベースには、より複雑ではありますが、より機能的な高度なクエリもあります。 解析、実行、変数バインディングなどのリクエストを個別に送信できます。

超拡張クエリについては、現在のレポートでは取り上げません。 おそらく、私たちはデータベースから何かを望んでおり、何らかの形で作成されたウィッシュリストがあります。つまり、これが私たちが望んでいることですが、現在も来年もそれは不可能です。 それで、ちょうどそれを録音して、主要な人々を揺さぶりながら回ります。

そしてできることは、単純なクエリと拡張クエリです。

それぞれのアプローチの特徴は何ですか?

単純なクエリは XNUMX 回限りの実行に適しています。 一度終わったら忘れてしまいます。 そして問題は、バイナリデータ形式をサポートしていないことです。つまり、一部の高性能システムには適していません。

拡張クエリ – 解析時間を節約できます。 これが私たちが実行し、使用し始めたものです。 これは本当に本当に助かりました。 節約できるのは解析だけではありません。 データ転送の節約になります。 バイナリ形式でデータを転送する方がはるかに効率的です。

練習に移りましょう。 典型的なアプリケーションは次のようになります。 Javaなどかもしれません。

ステートメントを作成しました。 コマンドを実行しました。 近くで作成しました。 ここのどこが間違いなのでしょうか？ 何が問題ですか？ 問題ない。 これはどの本にも書いてあることです。 このように書くべきです。 最大のパフォーマンスが必要な場合は、次のように記述します。

しかし、実際にやってみると、これではうまくいかないことがわかっています。 なぜ？ 「閉じる」メソッドがあるからです。 これを行うと、データベースの観点から見ると、喫煙者がデータベースを操作しているようなものであることがわかります。 「PARSE EXECUTE DEALLOCATE」と言いました。

なぜこのような余分なステートメントの作成とアンロードが行われるのでしょうか? 誰も必要としていません。 ただし、PreparedStatement で通常起こることは、PreparedStatement を閉じると、データベース上のすべてが閉じられることです。 これは私たちが望んでいることではありません。

私たちは健康な人と同じように、基地で働きたいと思っています。 私たちはステートメントを一度作成して準備し、それを何度も実行します。 実際、アプリケーションは何度も (これはアプリケーションの一生のうちに一度だけですが) 解析されています。 そして、異なる REST で同じステートメント ID を使用します。 これが私たちの目標です。

どうすればこれを達成できるでしょうか?

とても簡単です。ステートメントを閉じる必要はありません。 「準備」「実行」のように書きます。

こんなものを立ち上げたら、どこかに何かが溢れ出すのは明らかだ。 不明な場合は、試着してみることもできます。 この簡単な方法を使用したベンチマークを作成してみましょう。 ステートメントを作成します。 あるバージョンのドライバーでこのドライバーを起動すると、ドライバーが持っていたメモリがすべて失われ、すぐにクラッシュすることがわかりました。

このようなエラーは簡単に修正できることは明らかです。 それらについては話しません。 ただし、新しいバージョンの方がはるかに高速に動作すると言えます。 方法は愚かですが、それでもです。

正しく働くにはどうすればよいでしょうか? そのためには何をする必要があるでしょうか?

実際には、アプリケーションは常にステートメントを閉じます。 どの本にも、閉じないとメモリがリークすると言っています。

また、PostgreSQL はクエリをキャッシュする方法を知りません。 各セッションがそれ自体のためにこのキャッシュを作成する必要があります。

また、解析に時間を無駄にしたくありません。

そしていつものように、選択肢は XNUMX つあります。

最初のオプションは、これを受け入れて、すべてを PgSQL でラップしましょうということです。 そこにキャッシュがあります。 すべてをキャッシュします。 それは素晴らしい結果になるでしょう。 私たちはこれを見ました。 100500件のリクエストがあります。 動作しません。 私たちは、リクエストを手動で手順に変換することに同意しません。 いやいや。

XNUMX 番目のオプションがあります。それを自分でカットしてください。 ソースを開いてカットを開始します。 私たちは何度も見ました。 それはそれほど難しいことではないことがわかりました。

https://github.com/pgjdbc/pgjdbc/pull/319

こちらは2015年XNUMX月に登場しました。 現在は、より現代的なバージョンがあります。 そしてすべてが素晴らしいです。 非常にうまく機能するので、アプリケーションには何も変更しません。 そして私たちは PgSQL の方向で考えることさえやめました。つまり、すべてのオーバーヘッド コストをほぼゼロに削減するにはこれで十分でした。

したがって、サーバーが準備したステートメントは、5 回限りのリクエストごとにデータベース内のメモリが無駄に消費されるのを避けるために、XNUMX 回目の実行時にアクティブ化されます。

「数字はどこにあるの?」と疑問に思うかもしれません。 何を得るのですか？ それぞれのリクエストには独自のものがあるため、ここでは数字は示しません。

私たちのクエリは、OLTP クエリの解析に約 20 ミリ秒を費やしたものでした。 実行には 0,5 ミリ秒、解析には 20 ミリ秒かかりました。 リクエスト – テキスト 10 KiB、計画 170 行。 これは OLTP リクエストです。 1、5、10 行、場合によってはそれ以上の行を要求します。

しかし、20 ミリ秒も無駄にしたくありませんでした。 それを0に減らしました。 すべてが素晴らしいです。

ここから何を得ることができますか? Java を使用している場合は、最新バージョンのドライバーを入手して喜ぶでしょう。

別の言語を話す場合は、考えてみてください。もしかしたら、これも必要なのでしょうか? なぜなら、最終言語の観点から見ると、たとえば PL 8 や LibPQ を使用している場合、実行や解析に時間を費やしていることが明らかではないため、これは確認する価値があります。 どうやって？ すべて無料です。

間違いやいくつかの特殊性があることを除いて。 そして、それらについて今すぐ話します。 そのほとんどは産業考古学に関するもので、私たちが発見したもの、出会ったものについてのものになります。

リクエストが動的に生成される場合。 それは起こります。 誰かが文字列をつなぎ合わせて、SQL クエリを作成します。

なぜ彼が悪いのでしょうか？ 毎回違う文字列になってしまうので、それは問題です。

そして、この異なる文字列の hashCode を再度読み取る必要があります。 これは実際には CPU のタスクです。既存のハッシュであっても長いリクエスト テキストを見つけるのはそれほど簡単ではありません。 したがって、結論は単純です。リクエストを生成しないことです。 それらを XNUMX つの変数に格納します。 そして喜ぶ。

次の問題。 データ型は重要です。 ORM の中には、どのような種類の NULL があるかは問題ではなく、何らかの種類の NULL があるようにするというものがあります。 Int の場合は、setInt と言います。 NULL の場合は、常に VARCHAR にしてください。 そして、NULL が存在することで、最終的にはどのような違いが生じるのでしょうか? データベース自体がすべてを理解します。 そして、この写真は機能しません。

実際には、データベースはまったく気にしません。 最初にこれは数値であると言い、XNUMX 回目には VARCHAR であると言った場合、サーバーで準備されたステートメントを再利用することは不可能です。 この場合、ステートメントを再作成する必要があります。

同じクエリを実行している場合は、列のデータ型が混同されていないことを確認してください。 NULL に注意する必要があります。 これは、PreparedStatements を使用し始めた後に発生する一般的なエラーです。

はい、オンになりました。 おそらく彼らは運転手を連れて行ったのでしょう。 そして生産性も低下しました。 事態は悪化した。

これはどうして起こるのでしょうか? これはバグですか、それとも機能ですか? 残念ながら、これがバグなのか機能なのかを理解することはできませんでした。 ただし、この問題を再現するための非常に簡単なシナリオがあります。 彼女はまったく予想外に私たちを待ち伏せしてきた。 そして、それは文字通り XNUMX つのテーブルからのサンプリングで構成されます。 もちろん、そのような要望はもっとありました。 原則として、XNUMX つまたは XNUMX つのテーブルが含まれていましたが、そのような再生シナリオもあります。 データベースから任意のバージョンを取得して再生します。

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

重要なのは、列が 20 つあり、それぞれにインデックスが付いているということです。 XNUMX つの NULL 列には XNUMX 万行があります。 そして、XNUMX 列目には XNUMX 行しか含まれていません。 バインドされた変数を使用せずに実行すると、すべてがうまく動作します。

バインドされた変数を使用して実行を開始すると、つまり「?」を実行します。 リクエストに対して「$1」を要求した場合、最終的に何が得られるのでしょうか?

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

最初の実行は予想通りです。 XNUMX 番目の方が少し速いです。 何かがキャッシュされました。 XNUMX番目、XNUMX番目、XNUMX番目。 それからバン、そしてそのようなこと。 そして最悪なのは、これが XNUMX 回目の実行で起こることです。 実際の実行計画が何であるかを理解するには、正確に XNUMX 回の実行が必要であることを誰が知っていたでしょうか。

誰が有罪ですか？ どうしたの？ データベースには最適化が含まれています。 そして、一般的なケースに最適化されているようです。 したがって、ある時点から、彼女は一般的なプランに切り替えることになりますが、残念ながら、それは異なることが判明する可能性があります。 同じになるかもしれないし、違うかもしれない。 そして、この動作につながるある種のしきい値が存在します。

それについて何ができるでしょうか？ もちろん、ここで何かを仮定することはさらに困難です。 私たちが使用する簡単な解決策があります。 これは +0、OFFSET 0 です。きっとあなたはそのような解決策を知っています。 それを受け取ってリクエストに「+0」を追加するだけで、すべて問題ありません。 後で紹介します。

そして、別のオプションがあります - 計画をより注意深く見てください。 開発者はリクエストを書くだけでなく、「分析して説明してください」と 6 回言わなければなりません。 5だと機能しません。

そして XNUMX 番目のオプションがあります - pgsql-hackers に手紙を書きます。 と書きましたが、これがバグなのか仕様なのかはまだ不明です。

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

これがバグなのか機能なのかを考えながら、修正してみましょう。 リクエストを受け入れて「+0」を追加しましょう。 すべて順調。 シンボルが 0 つあれば、それがどのようなものであるか、それが何であるかについて考える必要さえありません。 とてもシンプルです。 データベースがこの列のインデックスを使用することを禁止しただけです。 「+XNUMX」列にはインデックスがありません。データベースはインデックスを使用しないので、すべて問題ありません。

これが6つの説明の法則です。 現在のバージョンでは、バインドされた変数がある場合、これを 6 回実行する必要があります。 バインドされた変数がない場合は、これが行われます。 そして最終的には、まさにこのリクエストが失敗します。 それは難しいことではありません。

どこまで可能ですか？ ここにもバグ、あそこにもバグ。 実際、バグはどこにでも存在します。

詳しく見てみましょう。 たとえば、XNUMX つのスキーマがあります。 スキーム A とテーブル S、図 B とテーブル S。 クエリ - テーブルからデータを選択します。 この場合、何が得られるでしょうか？ エラーが発生します。 上記のすべてをご用意いたします。 ルールは、バグはどこにでもありますが、上記のすべてを解決するということです。

ここで問題となるのは、「なぜですか?」ということです。 スキーマがある場合、テーブルを探す場所を示す「search_path」変数があるというドキュメントがあるようです。 変数があるようです。

何が問題ですか？ 問題は、サーバーで準備されたステートメントが、search_path が誰かによって変更される可能性があることを疑わないことです。 この値は、いわばデータベースに対して一定のままです。 また、一部の部分は新しい意味を捉えられない可能性があります。

もちろん、これはテストしているバージョンによって異なります。 テーブルの違いがどれほど深刻かによって異なります。 また、バージョン 9.1 では古いクエリが実行されるだけです。 新しいバージョンではバグが検出され、バグがあることが通知される場合があります。

search_path + サーバーが準備したステートメント = を設定します。
キャッシュされたプランは結果のタイプを変更してはなりません

治療方法は？ 簡単なレシピがありますが、それはやめてください。 アプリケーションの実行中に search_path を変更する必要はありません。 変更する場合は、新しい接続を作成することをお勧めします。

議論することができます。つまり、開く、議論する、追加することができます。 おそらく、誰かが値を変更したとき、データベースはそのことをクライアントに伝えるべきであるとデータベース開発者を説得できるかもしれません。 おそらくステートメントをリセットして再作成する必要があるでしょうか?」 現在、データベースは秘密裏に動作し、ステートメントが内部のどこかで変更されたことを一切報告しません。

もう一度強調しておきますが、これは Java では一般的ではないことです。 PL/pgSQL でも同じことが XNUMX 対 XNUMX で見られます。 しかし、そこでも再現されてしまうのです。

さらにデータ選択を試してみましょう。 私たちは選択し、選択します。 128万行のテーブルがあります。 各行は XNUMX キロバイトです。 約ギガバイトのデータ。 Java マシンには XNUMX メガバイトの作業メモリがあります。

すべての書籍で推奨されているように、私たちはストリーム処理を使用します。 つまり、resultSet を開いて、そこからデータを少しずつ読み込みます。 うまくいきますか？ 記憶から消えてしまうのでしょうか？ 少し読んでみますか？ データベースを信頼しましょう、Postgres を信頼しましょう。 私たちはそれを信じません。 メモリから落ちてしまうのでしょうか？ OutOfMemory を経験したのは誰ですか? その後誰がそれを修正できたでしょうか？ 誰かがそれをなんとか修正してくれました。

XNUMX 万行ある場合、ただ選択することはできません。 OFFSET/LIMIT は必須です。 このオプションに賛成する人は誰ですか? autoCommit を使用することに賛成しているのは誰でしょうか?

ここでは、いつものように、最も予想外の選択肢が正しいことが判明します。 また、突然 autoCommit をオフにした場合にも役立ちます。 何故ですか？ 科学はこれについて知りません。

ただし、デフォルトでは、Postgres データベースに接続しているすべてのクライアントがデータ全体を取得します。 この点では PgJDBC も例外ではなく、すべての行を選択します。

FetchSize テーマにはバリエーションがあります。つまり、別のステートメントのレベルで、ここではデータを 10、50 ずつ選択してくださいと言うことができます。ただし、これは autoCommit をオフにするまで機能しません。 autoCommit をオフにすると、動作が開始されます。

しかし、コードを調べてあらゆる場所に setFetchSize を設定するのは不便です。 したがって、接続全体のデフォルト値を示す設定を作成しました。

それが私たちが言ったことです。 パラメータが設定されました。 そして何が得られたでしょうか？ 少量を選択する場合、たとえば一度に 10 行を選択すると、非常に大きなオーバーヘッド コストが発生します。 したがって、この値は約 XNUMX に設定する必要があります。

もちろん、理想的には、バイト単位で制限する方法を学ぶ必要がありますが、そのレシピは次のとおりです。defaultRowFetchSize を XNUMX を超える値に設定すれば満足です。

データの挿入に進みましょう。 挿入は簡単で、さまざまなオプションがあります。 たとえば、INSERT、VALUES などです。 これは良い選択肢です。 「INSERT SELECT」と言うことができます。 実際には同じことです。 性能に違いはありません。

本には Batch ステートメントを実行する必要があると書かれていますが、本にはいくつかの括弧を使用してより複雑なコマンドを実行できると書かれています。 Postgres には素晴らしい機能があります。COPY を実行できる、つまり、より高速に実行できます。

測定してみると、また面白い発見ができます。 これをどのように機能させたいでしょうか? 不要なコマンドを解析したり実行したりしたくないのです。

実際には、TCP ではこれを行うことはできません。 クライアントがリクエストの送信で忙しい場合、データベースは応答を送信しようとしてリクエストを読み取りません。 最終的に、クライアントはデータベースがリクエストを読み取るのを待ち、データベースはクライアントが応答を読み取るのを待ちます。

したがって、クライアントは定期的に同期パケットを送信する必要があります。 余分なネットワーク インタラクションが発生し、余分な時間の無駄が発生します。

そして、それらを追加すればするほど、状況は悪化します。 ドライバーは非常に悲観的で、行のサイズなどに応じて、約 200 行に XNUMX 回など、非常に頻繁に追加します。

https://github.com/pgjdbc/pgjdbc/pull/380

たった 10 行修正するだけで、すべてが 128 倍高速化されることがあります。 それは起こります。 なぜ？ いつものように、このような定数はすでにどこかで使用されています。 また、値「XNUMX」はバッチ処理を使用しないことを意味します。

Java マイクロベンチマーク ハーネス

これが正式版に含まれていないのは良いことです。 発売開始前に発見されました。 私が与える意味はすべて現代版に基づいています。

試着してみましょう。 InsertBatch を簡単に測定します。 InsertBatch を複数回測定します。つまり、同じことですが、多くの値があります。 トリッキーな動き。 誰もがこれを実行できるわけではありませんが、非常に単純な操作であり、COPY よりもはるかに簡単です。

COPYができます。

そして、これを構造体に対して行うことができます。 ユーザーのデフォルトの型を宣言し、配列と INSERT をテーブルに直接渡します。

リンク pgjdbc/ubenchmsrk/InsertBatch.java を開くと、このコードが GitHub にあります。 そこで生成されたリクエストを具体的に確認できます。 それは問題ではありません。

立ち上げました。 そして私たちが最初に気づいたのは、バッチを使用しないということは単純に不可能であるということでした。 すべてのバッチ処理オプションはゼロです。つまり、XNUMX 回限りの実行と比較して実行時間は実質的にゼロです。

データを挿入していきます。 とてもシンプルなテーブルです。 XNUMX つの列。 ここで何が見えるでしょうか？ これら XNUMX つのオプションはすべてほぼ同等であることがわかります。 もちろん COPY の方が優れています。

ピースを挿入するときです。 10 つの VALUES 値、1 つの VALUES 値、2 つの VALUES 値、またはカンマで区切られた 4 個の VALUES 値を示した場合。 これでちょうど水平になりました。 128、XNUMX、XNUMX、XNUMX。青で描かれた一括挿入により、彼の気分がかなり良くなっていることがわかります。 つまり、一度に XNUMX つずつ挿入した場合でも、一度に XNUMX つ挿入した場合でも、VALUES にもう少し詰め込んだだけで、効果は XNUMX 倍になります。 EXECUTE 操作が少なくなります。

小規模なボリュームで COPY を使用することは非常に期待できません。 最初のXNUMX枚は描いてませんでした。 彼らは天国に行きます、つまり、コピー用のこれらの緑色の数字です。

COPY は、少なくとも XNUMX 行のデータがある場合に使用する必要があります。 この接続を開く際のオーバーヘッドは大きくなります。 正直に言うと、私はこの方向に進んでいませんでした。 Batch は最適化しましたが、COPY は最適化しませんでした。

次は何をするの？ 試着してみました。 私たちは、構造か、複数の意味を組み合わせた賢い方法のいずれかを使用する必要があることを理解しています。

今日のレポートから何を学べばよいでしょうか?

• PreparedStatement がすべてです。 これにより、生産性が大幅に向上します。 軟膏に大きなフロップが生じます。
• そしてEXPLAIN ANALYZEを6回行う必要があります。
• そして、問題のあるクエリの残りの割合を修正するには、OFFSET 0 を薄め、+0 などのトリックを行う必要があります。

出所： habr.com