postgresql の肥大化を引き起こすアプリケーションの典型的なエラー。 アンドレイ・サルニコフ

Andrey Salnikov による 2016 年初頭のレポートの書き起こし「postgresql の肥大化につながるアプリケーションの典型的なエラー」を読むことをお勧めします。

このレポートでは、アプリケーション コードを設計および作成する段階で発生するアプリケーションの主なエラーを分析します。 Postgresql の肥大化につながるエラーのみを取り上げます。 原則として、これはシステム全体のパフォーマンスの終わりの始まりですが、当初はこのための前提条件が見えませんでした。

皆さんを歓迎します! このレポートは、私の同僚による以前のレポートほど技術的なものではありません。 私たちはかなりの数のクライアントを抱えているため、このレポートは主にバックエンド システム開発者を対象としています。 そして、彼らは皆同じ​​間違いを犯します。 それらについてお話します。 これらの間違いがどのような致命的で悪いことを引き起こすかを説明します。

なぜ間違いが起こるのでしょうか? これらが行われる理由は XNUMX つあります。XNUMX つは、おそらくうまくいくだろうというランダムな理由、もう XNUMX つは、データベースとアプリケーションの間のレベル、およびデータベース自体で発生するいくつかのメカニズムを理解していないためです。

事態がどれほど悪化したかについて、ひどい写真とともに XNUMX つの例を紹介します。 そこで起こるメカニズムについて簡単に説明します。 そして、それらにどのように対処し、いつ起こったのか、そして間違いを防ぐためにどのような予防法を使用すればよいのか。 補助ツールについて説明し、役立つリンクを提供します。

XNUMX つのテーブルがあるテスト データベースを使用しました。 XNUMX つのプレートには顧客の口座が記載され、もう XNUMX つのプレートにはこれらの口座での取引が記載されます。 また、これらの口座の残高は一定の頻度で更新されます。

プレートの初期データ: 2 MB と非常に小さいです。 データベース、特にサインの応答時間も非常に優れています。 そしてかなり良い負荷 - プレートによると2秒あたり000回の操作。

このレポートでは、何が起こっているかを明確に理解できるようにグラフを示します。 グラフを含むスライドは常に 2 枚あります。 最初のスライドは、サーバー上で一般的に何が起こるかを示しています。

そしてこの状況では、本当に小さな兆候があることがわかります。 インデックスは 2 MB と小さいです。 これは左側の最初のグラフです。

サーバーの平均応答時間も安定して短くなります。 右上のグラフです。

左下のグラフは最長のトランザクションを示しています。 トランザクションが迅速に完了していることがわかります。 また、これは開始テストだったので、ここでは自動バキュームはまだ機能しません。 それは今後も機能し、私たちにとって役立つでしょう。

XNUMX 番目のスライドは常にテストされるプレート専用になります。 この場合、当社はクライアントの口座残高を常に更新します。 また、更新操作の平均応答時間は非常に良好で、XNUMX ミリ秒未満であることがわかります。 プロセッサ リソース (これは右上のグラフ) も均等に消費され、非常に少ないことがわかります。

右下のグラフは、更新する前に目的の行を検索するためにどれだけの動作メモリとディスクメモリを通過するかを示しています。 そして、最初に述べたように、符号に応じた演算数は 2 秒あたり 000 回です。

そして今、悲劇が起きています。 何らかの理由で、長い間忘れられていたトランザクションがあります。 通常、その理由はすべてありきたりなものです。

• 最も一般的なものの XNUMX つは、アプリケーション コードで外部サービスへのアクセスを開始したことです。 そして、このサービスは私たちに答えません。 つまり、トランザクションを開き、データベースに変更を加え、アプリケーションからメールを読んだり、インフラストラクチャ内の別のサービスに移動したりしましたが、何らかの理由で応答しません。 そして、セッションはいつ解決されるか不明な状態でスタックしています。
• XNUMX 番目の状況は、何らかの理由でコード内で例外が発生した場合です。 そして例外として、トランザクションの終了処理を行いませんでした。 そして、オープンなトランザクションでハングセッションが発生してしまいました。
• 最後のケースもかなり一般的なケースです。 これは低品質のコードです。 一部のフレームワークはトランザクションを開きます。 ハングしていますが、アプリケーションではハングしていることに気づかない可能性があります。

そのようなことはどこにつながるのでしょうか？

テーブルとインデックスが劇的に膨張し始めるまでに。 これはまさに同じ膨張効果です。 データベースの場合、これはデータベースの応答時間が大幅に増加し、データベース サーバーの負荷が増加することを意味します。 その結果、アプリケーションに問題が発生します。 なぜなら、データベースへのリクエストにコードで 10 ミリ秒、ロジックで 10 ミリ秒を費やした場合、関数が完了するまでに 20 ミリ秒かかるからです。 そして今、あなたの状況は完全に悲しいものになるでしょう。

そして何が起こるか見てみましょう。 左下のグラフは、長い長いトランザクションがあることを示しています。 左上のグラフを見ると、テーブルのサイズが 300 メガバイトから XNUMX メガバイトに突然増加していることがわかります。 同時に、テーブル内のデータ量は変化していません。つまり、かなり大量のゴミがそこにあります。

サーバーの平均応答時間に関する一般的な状況も、数桁変化しました。 つまり、サーバー上のすべてのリクエストが完全に低下し始めました。 そして同時に、内部 Postgres プロセスが自動バキュームの形で起動され、何かを行おうとしてリソースを消費します。

私たちの看板に何が起こっているのでしょうか？ 同じ。 兆候によると、平均応答時間は数桁跳ね上がりました。 特に消費されたリソースの観点から見ると、プロセッサの負荷が大幅に増加していることがわかります。 右上のグラフです。 そして、プロセッサが必要な行を探すために大量の無駄な行を分類する必要があるため、この量は増加しました。 右下のグラフです。 その結果、データベースが同じ数のリクエストを処理する時間がなくなったため、XNUMX 秒あたりの呼び出し数が大幅に減少し始めました。

私たちは日常に戻らなければなりません。 オンラインにアクセスすると、長時間の取引が問題につながることがわかりました。 このトランザクションを見つけて強制終了します。 そして、私たちにとってすべてが普通になりつつあります。 すべてが正常に機能します。

私たちは落ち着きましたが、しばらくすると、アプリケーションが緊急事態前と同じように機能しないことに気づき始めました。 リクエストの処理は依然として遅くなり、大幅に遅くなります。 私の例では、具体的には XNUMX 倍半から XNUMX 倍遅くなります。 サーバーの負荷も事故前よりも高くなっています。

そして、「今この瞬間、基地に何が起こっているのか？」という質問です。 そして、ベースでは次のような状況が発生します。 取引チャートでは、取引が停止しており、実際には長期取引がないことがわかります。 しかし、事故によって標識のサイズが致命的に大きくなった。 そしてそれ以来、彼らは減っていません。 平均出塁時間が安定してきました。 そしてその答えは、私たちが許容できる速度で適切に提供されているようです。 自動バキュームはよりアクティブになり、より多くのデータを選別する必要があるため、サインを使って何かをし始めました。

具体的には、残高を変更するアカウントのテスト プレートによると、リクエストの応答時間が通常に戻っているようです。 しかし実際にはXNUMX倍も高いのです。

また、プロセッサの負荷から、プロセッサの負荷がクラッシュ前の必要な値に戻っていないことがわかります。 そして、その理由はまさに右下のグラフにあります。 そこではある程度のメモリが検索されていることがわかります。 つまり、必要な行を見つけるために、無駄なデータを選別しながらデータベース サーバーのリソースを無駄に消費することになります。 XNUMX 秒あたりのトランザクション数が安定しました。

全体的には良好ですが、状況は以前よりも悪化しています。 このデータベースを操作するアプリケーションの結果として、明らかにデータベースが低下します。

前回のレポートをご覧になっていない方のために、そこで何が起こっているのかを理解するために、ここで少し理論を説明しましょう。 内部プロセスに関する理論。 車に掃除機をかける理由とその効果は何ですか?

文字通り、理解のために簡単に説明します。 ある時点でテーブルができました。 テーブルには行があります。 これらの線は、アクティブであり、生き生きとしており、今私たちが必要としているものになります。 写真では緑色でマークされています。 そして、すでに解決され、更新され、新しいエントリが追加されたデッドラインがあります。 そして、それらはデータベースにとってもはや興味深いものではないというマークが付けられます。 ただし、これらは Postgres の機能により表に含まれています。

車の掃除機が必要な理由は何ですか? ある時点で、自動バキュームが来てデータベースにアクセスし、「現在データベースで開いている最も古いトランザクションの ID を教えてください」と尋ねます。 データベースはこの ID を返します。 そして、自動バキュームは、これに依存して、テーブル内の行を並べ替えます。 そして、一部の行がはるかに古いトランザクションによって変更されたことを確認した場合、そこに新しいデータを書き込むことで、将来再利用できる行としてマークを付ける権利があります。 これはバックグラウンドプロセスです。

現時点では、データベースの操作を継続し、テーブルにいくつかの変更を加え続けます。 そして、再利用できるこれらの行に、新しいデータを書き込みます。 したがって、サイクルが発生します。つまり、常に古い行がそこに表示され、その代わりに必要な新しい行を書き留めます。 これは PostgreSQL が動作する通常の状態です。

事故中に何が起こったのですか? そこでこのプロセスはどのようにして起こったのでしょうか?

サインには何らかの状態があり、生きているサインもあれば、期限切れのサインもありました。 車用掃除機が到着しました。 彼はデータベースに、最も古いトランザクションとその ID を尋ねました。 私がこの ID を受け取ったのは何時間も前、あるいは XNUMX 分前かもしれません。 それはデータベースにかかる負荷がどれだけ大きいかによって異なります。 そして、再利用としてマークできる行を探しました。 そして、私たちのテーブルにはそのような行は見つかりませんでした。

ただし、現時点ではテーブルでの作業を続けます。 その中で何かを行い、更新し、データを変更します。 このときデータベースは何をすべきでしょうか? 彼女には既存の表の末尾に新しい行を追加する以外に選択肢はありません。 したがって、テーブルのサイズは膨張し始めます。

実際には、緑色の線が機能する必要があります。 しかし、このような問題が発生すると、テーブル全体で緑の線の割合が非常に低いことがわかります。

そして、クエリを実行するとき、データベースは目的の行を見つけるために、赤と緑の両方の行をすべて調べなければなりません。 また、無駄なデータでテーブルが肥大化する影響は「肥大化」と呼ばれ、ディスク容量も消費します。 2 MB だったものが 300 MB になったのを覚えていますか? ここでメガバイトをギガバイトに変更すると、すぐにすべてのディスク リソースが失われます。

私たちにどのような影響が及ぶ可能性があるでしょうか?

• 私の例では、テーブルとインデックスは 150 倍に増加しました。 私たちのクライアントの中には、単にディスク容量が不足し始めたときに、より致命的なケースに遭遇した人もいます。
• テーブル自体のサイズは決して減少しません。 デッドラインしかない場合、自動バキュームによってテーブルの末尾が切り取られる場合があります。 ただし、常にローテーションが行われるため、XNUMX つの緑の線が最後にフリーズして更新されない場合がありますが、他のすべての線はプレートの先頭のどこかに書き込まれます。 ただし、テーブル自体のサイズが縮小するということは非常にまれな出来事であるため、期待する必要はありません。
• データベースは、大量の無駄な行を分類する必要があります。 そして、ディスクリソース、プロセッサリソースと電力を浪費します。
• そして、これはアプリケーションに直接影響します。なぜなら、最初にリクエストに 10 ミリ秒、コードに 10 ミリ秒を費やした場合、クラッシュ中にリクエストに 10 秒、コードに 20 ミリ秒を費やし始めるからです。アプリケーションのパフォーマンスが大幅に低下しました。 そして事故が解決すると、リクエストに 10 ミリ秒、コードに XNUMX ミリ秒を費やすようになりました。 これは、依然として生産性が XNUMX 倍低下していることを意味します。 そしてこれはすべて、おそらく私たちのせいで凍結した XNUMX つのトランザクションのせいです。
• そして、「どうすればすべてを取り戻すことができるでしょうか？」という質問です。そうすれば、すべてがうまくいき、事故前と同じくらい早くリクエストが届くようになります。

この目的のために、一定の作業サイクルが実行されます。

まず、肥大化している問題のあるテーブルを見つける必要があります。 一部のテーブルでは記録がより活発になり、他のテーブルではそれほど活発ではないことがわかります。 このために、拡張機能を使用します pgstattuple。 この拡張機能をインストールすると、非常に肥大化したテーブルを検索するのに役立つクエリを作成できます。

これらのテーブルを見つけたら、それらを圧縮する必要があります。 このためのツールはすでにあります。 当社ではXNUMXつのツールを使用しています。 XNUMX つ目は、内蔵の VACUUM FULL です。 彼は残酷で厳しく、無慈悲ですが、時には非常に役に立つこともあります。 Pg_repack и pgコンパクトテーブル - これらはテーブルを圧縮するためのサードパーティ ユーティリティです。 そして、データベースをより慎重に扱います。

どちらが便利かに応じて使用されます。 しかし、これについては最後にお話します。 重要なのは、ツールが XNUMX つあるということです。 選択肢はたくさんあります。

すべてを修正し、すべてが正常であることを確認したら、今後この状況を防ぐ方法を知る必要があります。

• それは非常に簡単に防ぐことができます。 マスターサーバー上のセッションの継続時間を監視する必要があります。 トランザクション状態でアイドル状態にあるセッションは特に危険です。 これらは、トランザクションを開いて何かをしただけで終了したり、単にハングしたり、コードの中で迷ってしまった人たちです。
• 開発者にとって、このような状況が発生したときにコードをテストすることが重要です。 難しいことではありません。 これは便利なチェックになります。 長時間のトランザクションに伴う多くの「幼稚な」問題を回避できます。

これらのグラフでは、この場合、VACUUM FULL のサインを通過した後に、サインとデータベースの動作がどのように変化したかを示したかったのです。 これは私にとって制作ではありません。

テーブルのサイズはすぐに数メガバイトの通常の動作状態に戻りました。 これはサーバーの平均応答時間に大きな影響を与えませんでした。

しかし、特に口座残高を更新したテスト サインの場合、サイン内のデータ更新リクエストの平均応答時間が緊急前のレベルまで短縮されたことがわかります。 このリクエストを完了するためにプロセッサが消費したリソースも、クラッシュ前のレベルまで低下しました。 右下のグラフは、テーブルが圧縮される前に存在していたデッドラインの山を通過することなく、必要な行をすぐに正確に見つけることができることを示しています。 また、平均リクエスト時間はほぼ同じレベルのままでした。 しかし、ここではむしろハードウェアにエラーがあります。

ここで第一話は終了です。 それが最も一般的です。 そして、クライアントの経験やプログラマーの資格の有無に関係なく、それは誰にでも起こります。 遅かれ早かれこれは起こります。

負荷分散とサーバーリソースの最適化の第 XNUMX 話

• 私たちはもう成長して、真面目な人間になっています。 そして、レプリカがあり、マスターに書き込み、レプリカから読み取るという負荷のバランスを取ることが良いことを理解しています。 そして通常、この状況は、レポートや ETL を準備したいときに発生します。 ビジネス側もこれに非常に満足しています。 彼は、多くの複雑な分析を含むさまざまなレポートを本当に望んでいます。
• 複雑な分析はミリ秒単位で計算できないため、レポートには何時間もかかります。 私たちは勇敢な人のようにコードを書きます。 挿入アプリケーションでは、マスター上で記録を作成し、レプリカ上でレポートを実行します。
• 負荷を分散します。
• すべてが完璧に機能します。 私たちは素晴らしいです。

そして、この状況はどのようなものでしょうか? 特にこれらのグラフでは、トランザクション期間に対するレプリカからのトランザクションの期間も追加しました。 他のすべてのグラフはマスター サーバーのみを参照します。

この時点で、私のレポートボードは大きくなっていました。 もっとたくさんあります。 サーバーの平均応答時間が安定していることがわかります。 レプリカには、2 時間実行される長時間実行トランザクションがあることがわかります。 デッドラインを処理する自動バキュームが静かに動作していることがわかります。 そして、私たちにとってはすべてが順調です。

具体的には、テストされたプレートに従って、アカウント残高の更新を継続します。 また、リクエストに対する応答時間も安定しており、リソース消費も安定しています。 私たちには何も問題ありません。

レプリケーションとの競合によりこれらのレポートが反撃され始めるまでは、すべてが正常です。 そして彼らは一定の間隔で反撃します。

私たちはオンラインにアクセスして、なぜこれが起こっているのかを読み始めます。 そして私たちは解決策を見つけます。

3 つ目の解決策は、レプリケーションの遅延を増やすことです。 レポートの実行時間は 3 時間であることがわかっています。 レプリケーション遅延を XNUMX 時間に設定します。 すべてを開始していますが、レポートが時々キャンセルされるなど、依然として問題が発生しています。

私たちはすべてが完璧であることを望んでいます。 さらに登っていきます。 そして、インターネット上で素晴らしい設定、hot_standby_フィードバックを見つけました。 オンにしてみましょう。 Hot_standby_フィードバックを使用すると、マスターでの自動バキュームを抑制できます。 したがって、レプリケーションの競合を完全に排除します。 レポートに関してはすべてがうまく機能します。

そして現時点でマスターサーバーで何が起こっているのでしょうか? そして、マスターサーバーで完全に問題が発生しています。 これらの設定の両方を有効にすると、グラフが表示されるようになりました。 そして、レプリカ上のセッションが何らかの形でマスター サーバー上の状況に影響を与え始めていることがわかります。 彼女はデッドラインをクリアする自動バキュームを一時停止したため、効果があります。 テーブルのサイズが再び急増しました。 データベース全体の平均クエリ実行時間も急増しました。 自動バキュームが少し強化されました。

具体的には、プレートから、そのデータの更新も空に飛んでいることがわかります。 CPU 消費量も同様に大幅に増加しました。 私たちは再び、大量の無駄な無駄なラインを通過しています。 そして、このサインの応答時間とトランザクション数が減少しました。

私が前に話していたことを知らなかったら、どうなるでしょうか?

• 私たちは問題を探し始めます。 最初の部分で問題が発生した場合は、長いトランザクションが原因である可能性があることがわかり、マスターに問い合わせます。 マスターに問題があります。 彼にソーセージを。 加熱すると、負荷平均値は約 XNUMX になります。
• そこのリクエストは遅いですが、長時間実行されるトランザクションは見られません。 そして私たちは何が問題なのか分かりません。 どこを見ればよいのかわかりません。
• サーバー機器の点検を行っております。 おそらく私たちの襲撃は失敗したのでしょう。 もしかしたらメモリースティックが焼き切れたのかもしれません。 そう、何でも起こり得るのです。 しかし、いいえ、サーバーは新しいので、すべてが正常に動作します。
• 管理者、開発者、ディレクターなど、全員が実行しています。 何も役に立ちません。
• そしてある時点で、突然すべてが自動的に修正され始めます。

この時点で、レプリカに対するリクエストは処理され、残されました。 報告書を受け取りました。 ビジネスは依然として幸せです。 ご覧のとおり、私たちのサインは再び成長し、縮小するつもりはありません。 セッションを含むグラフでは、状況が安定するまでにかかる時間を見積もることができるように、レプリカからこの長いトランザクションの一部を残しました。

セッションは終了しました。 そして、しばらくしてから、サーバーは多かれ少なかれ順番に来るようになります。 そして、マスターサーバー上のリクエストの平均応答時間は通常に戻ります。 なぜなら、最終的に、自動バキュームはこれらのデッドラインを消去してマークする機会を得るからです。 そして彼は自分の仕事を始めました。 そして彼はとても素早くそれを行うので、私たちはすぐに秩序を取り戻すでしょう。

口座残高を更新するテスト済みのタブレットによると、まったく同じ画像が表示されます。 アカウントの平均更新時間も徐々に正常化しています。 プロセッサーによって消費されるリソースも削減されます。 そして、XNUMX 秒あたりのトランザクション数は通常に戻ります。 しかし、私たちは再び通常の状態に戻っており、事故前と同じではありません。

いずれにせよ、最初のケースと同様に、XNUMX ～ XNUMX 倍、場合によってはそれ以上のパフォーマンスの低下が生じます。

私たちはすべて正しくやったようです。 負荷を分散します。 機器はアイドル状態ではありません。 私たちは自分たちの考えに従ってリクエストを分割しましたが、それでもすべてがうまくいきませんでした。

• hot_standby_フィードバックを有効にしないでください? はい、特に強い理由がない限り、これをオンにすることはお勧めできません。 これは、この変更がマスター サーバーに直接影響し、そこでの自動バキュームの動作が一時停止されるためです。 一部のレプリカでこれを有効にして忘れると、マスターが強制終了され、アプリケーションに大きな問題が発生する可能性があります。
• max_standby_streaming_delay を増やしますか? はい、レポートの場合はこれが当てはまります。 XNUMX 時間のレポートがあり、レプリケーションの競合によってレポートがクラッシュしたくない場合は、単純に遅延を増やします。 長期レポートには、現時点でデータベースに到着しているデータは必要ありません。 XNUMX 時間使用している場合は、古いデータ期間にわたって実行していることになります。 そして、あなたにとっては、XNUMX 時間の遅れであろうと XNUMX 時間の遅れであろうと何の違いもありませんが、レポートは一貫して受け取りますし、レポートが落ちても何の問題もありません。
• 当然のことながら、特にレプリカで hot_standby_フィードバックを有効にする場合は、レプリカでの長時間セッションを制御する必要があります。 何でも起こり得るからです。 開発者がリクエストをテストできるように、このレプリカを開発者に渡しました。 彼はクレイジーなリクエストを書いた。 彼はそれを立ち上げてお茶を飲みに行きました、そして私たちは確立されたマスターを獲得しました。 あるいは、間違ったアプリケーションを入れてしまったのかもしれません。 状況はさまざまです。 レプリカ上のセッションは、マスター上のセッションと同様に注意深く監視する必要があります。
• また、レプリカに対して高速で長いクエリがある場合は、それらを分割して負荷を分散することをお勧めします。 これはストリーミング_遅延へのリンクです。 高速なレプリカの場合は、レプリケーション遅延が小さいレプリカを 6 つ用意します。 長時間実行されるレポート リクエストの場合は、XNUMX 時間または XNUMX 日の遅延が発生する可能性があるレプリカを用意します。 これはまったく正常な状況です。

同じ方法で結果を排除します。

• 肥大化したテーブルが見つかります。
• そして、私たちに合った最も便利なツールを使用してそれを圧縮します。

第二話はここで終わりです。 ３つ目の話に移ります。

これは、移行を行う私たちにとっても非常に一般的なことです。

• どのソフトウェア製品も成長しています。 それに求められる要件は変化しています。 とにかく発展していきたいと思っています。 そして、テーブル内のデータを更新する必要がある場合があります。つまり、開発の一環として導入する新機能の移行に関して更新を実行する必要があります。
• 古いデータ形式は満足のいくものではありません。 ここで、これらの口座のトランザクションがある XNUMX 番目のテーブルに目を向けるとします。 そして、ルーブル単位だったとします。精度を高めてコペイカ単位で行うことにしました。 このためには、更新を行う必要があります。トランザクション金額のフィールドに XNUMX を掛けます。
• 今日の世界では、自動化されたデータベース バージョン管理ツールが使用されています。 まあ言ってみれば リキベース。 そこで移行を登録します。 弊社のテストベースでテストします。 すべて順調。 アップデートは進行中です。 しばらく作業がブロックされますが、更新されたデータが得られます。 そして、これに基づいて新しい機能を立ち上げることができます。 すべてがテストされ、チェックされました。 すべてが確認されました。
• 計画的な作業を実行し、移行を実行しました。

ここでは、目の前に提示された更新を伴う移行を示します。 これらは私のアカウントのトランザクションなので、プレートは 15 GB でした。 そして、すべての行を更新するので、すべての行を書き換えるため、更新によってテーブルのサイズが XNUMX 倍になります。

移行中は、このプレートに対するすべてのリクエストがキューに入れられ、この更新が完了するまで待機されたため、このプレートに対して何も行うことができませんでした。 しかし、ここで注目していただきたいのは、縦軸の数字です。 つまり、移行前の平均リクエスト時間は約 5 ミリ秒で、プロセッサの負荷、ディスク メモリの読み取りのブロック操作の数は 7,5 未満です。

移行を実行しましたが、再び問題が発生しました。

移行は成功しましたが、次のことが起こります。

• 古い機能が完了するまでに時間がかかるようになりました。
• テーブルがまた大きくなりました。
• 再びサーバーの負荷が以前よりも大きくなりました。
• そしてもちろん、うまく機能した機能についてはまだ調整中であり、少し改良しました。

そしてこれがまた肥大化し、再び私たちの生活を台無しにしてしまいます。

ここでは、前の XNUMX つのケースと同様に、テーブルが以前のサイズに戻らないことを示します。 平均的なサーバー負荷は十分であるようです。

そして、アカウントを含むテーブルに目を向けると、このテーブルの平均リクエスト時間が 7,5 倍になっていることがわかります。 プロセッサの負荷とメモリ内で整理された行数は 2 を超えましたが、それよりは低くなりました。 そして、プロセッサの場合は 1,5 倍、ブロック操作の場合は XNUMX 倍に跳ね上がりました。つまり、サーバーのパフォーマンスが低下しました。 その結果、アプリケーションのパフォーマンスが低下します。 同時に、通話数はほぼ同じレベルを維持しました。

ここで重要なことは、そのような移行を正しく行う方法を理解することです。 そしてそれらは実行される必要があります。 私たちはこれらの移行をかなり一貫して行っています。

• このような大規模な移行は自動的には行われません。 それらは常に制御下に置かれていなければなりません。
• 知識のある人の監督が必要です。 チームに DBA がいる場合は、DBA に任せてください。 それは彼の仕事だ。 そうでない場合は、データベースの操作方法を知っている最も経験豊富な人にやってもらいます。
• 新しいデータベース スキーマは、XNUMX つの列を更新する場合でも、常に段階的に準備します。つまり、アプリケーションの新しいバージョンがロールアウトされる前に、事前に準備します。
• 新しいフィールドが追加され、そこに更新されたデータが記録されます。
• 古いフィールドから新しいフィールドにデータを少しずつ転送します。 なぜこれを行うのでしょうか? まず、このプロセスのプロセスを常に管理します。 すでに非常に多くのバッチを転送しており、非常に多くのバッチが残っていることがわかっています。
• XNUMX 番目のプラスの効果は、そのような各バッチ間でトランザクションを閉じ、新しいトランザクションを開くことです。これにより、自動バキュームがプレートに従って機能し、再利用のために期限をマークできるようになります。
• アプリケーションの実行中に表示される行 (古いアプリケーションがまだ実行されています) については、新しいフィールドに新しい値を書き込むトリガーを追加します。 この場合、これは古い値の XNUMX 倍になります。
• 完全に頑固で同じフィールドが必要な場合は、すべての移行が完了し、アプリケーションの新しいバージョンをロールアウトする前に、単にフィールドの名前を変更します。 古いフィールドには独自の名前が付けられ、新しいフィールドの名前は古いフィールドに変更されます。
• その後、アプリケーションの新しいバージョンを起動します。

同時に、肥大化したり、パフォーマンスが低下したりすることもありません。

ここで第三話は終了です。

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat_approx.sql

ここで、最初の話で述べたツールについてもう少し詳しく説明します。

肥大化を検索する前に、拡張機能をインストールする必要があります pgstattuple.

クエリを考え出す必要がないように、これらのクエリは作業中にすでに記述されています。 それらを使用できます。 ここで二つのお願いがあります。

• 最初のものは作業にかなり長い時間がかかりますが、表から正確な膨張値が表示されます。
• XNUMX 番目の方法はより高速に動作し、表に従って膨満感があるかどうかを迅速に評価する必要がある場合に非常に効果的です。 また、Postgres テーブルには肥大化が常に存在することも理解する必要があります。 これは MVCC モデルの機能です。
• また、ほとんどの場合、テーブルの 20% の肥大化は正常です。 つまり、心配してこのテーブルを圧縮する必要はありません。

私たちは、無駄なデータで肥大化したテーブルを特定する方法を考え出しました。

次に、むくみを修正する方法について説明します。

• 小型のタブレットと良好なディスク、つまりギガバイトまでのタブレットがある場合は、VACUUM FULL を使用することがかなり可能です。 彼は数秒間テーブル上であなたから排他的ロックを取得しますが、大丈夫ですが、彼はすべてを迅速かつ厳しく行います。 バキュームフルは何をするのですか? テーブルに排他ロックをかけて、古いテーブルのライブ行を新しいテーブルに書き換えます。 そして最後に彼は彼らを置き換えます。 古いファイルを削除し、古いファイルを新しいファイルに置き換えます。 ただし、作業中はテーブルに対して排他ロックを取得します。 これは、このテーブルに対しては何もできないこと、つまり書き込みも読み取りも変更もできないことを意味します。 また、VACUUM FULL では、データを書き込むために追加のディスク容量が必要です。
• 次のツール pg_repack。 原理的には、古いファイルから新しいファイルにデータを再書き込みし、テーブル内のデータを置き換えるという点で、VACUUM FULL と非常によく似ています。 しかし同時に、作業の開始時にテーブルに対して排他ロックを取得するのではなく、ファイルを置き換えるための準備ができたデータがすでにある時点でのみ排他ロックを取得します。 ディスク リソース要件は VACUUM FULL の要件と似ています。 追加のディスク領域が必要ですが、テラバイトのテーブルがある場合、これが重要になる場合があります。 また、I/O を積極的に処理するため、非常にプロセッサを大量に消費します。
• XNUMX 番目のユーティリティは、 pgコンパクトテーブル。 わずかに異なる原則に従って動作するため、リソースの管理にはより注意が必要です。 pgcompacttable の主なアイデアは、テーブル内の更新を使用して、すべてのライブ行をテーブルの先頭に移動することです。 次に、このテーブルに対してバキュームを実行します。これは、最初に生きている行があり、最後に死んだ行があることがわかっているためです。 そして、バキューム自体がこの尾部を切り取ります。つまり、追加のディスク領域をあまり必要としません。 そして同時に、リソースの面では依然として圧迫される可能性があります。

すべて道具付き。

内部をさらに深く掘り下げるという観点から、この肥大化したトピックが興味深いと思われる場合は、いくつかの役立つリンクを以下に示します。

• https://www.slideshare.net/alexius2Mb/where-is-the-space-postgres - これは私の同僚からの報告です。 これは、Postgres の作業中および存続中にそのスペースがどこに行くかについての一般的なものです。 また、データベース管理者向けに、肥大化に関する非常に大規模で詳細な技術文書があります。
• https://github.com/dataegret/pg-utils – これは私たちのリポジトリへのリンクです。ここには、データベースの状態をチェックするための便利なスクリプトが多数保存されています。 そこには肥大化を検索するスクリプトがあります。
• Третья и 第四 標識を縮小するのに役立つツールへのリンク。
• http://blog.dataegret.com/2Mb018/03/postgresql-bloatbusters.html – これは私の同僚からの投稿です。 そこでは、管理者に近いレベルでかなり真剣かつ技術的に肥大化を詳細に分析しています。

開発者はデータベースの直接のクライアントであり、そのアクションが何をもたらすのかを理解する必要があるため、私は開発者向けに恐ろしい話を見せるように努めました。 成功したといいのですが。 ご清聴ありがとうございました！

質問

ご報告ありがとうございます！ 問題を特定する方法について話しました。 どうすれば彼らに警告できるでしょうか？ つまり、リクエストが一部の外部サービスにアクセスしたためだけでなく、リクエストがハングする状況が発生しました。 これらは単なるワイルドな結合でした。 いくつかの小さくて無害なリクエストが XNUMX 日滞留し、その後、いくつかのナンセンスな作業が始まりました。 つまり、あなたが説明したものと非常に似ています。 これを追跡するにはどうすればよいですか? どのリクエストが滞っているかを座って常に監視していますか? これを防ぐにはどうすればよいでしょうか?

この場合、これは会社の管理者のタスクであり、必ずしも DBA のタスクではありません。

私は管理者です。

PostgreSQL には、未解決のクエリを表示する pg_stat_activity というビューがあります。 そして、それがどれだけ長くそこにぶら下がっているかがわかります。

5分ごとに見に来なければなりませんか？

cronを設定して確認してください。 長期的なリクエストがある場合は、手紙を書いてください。 つまり、目で見る必要はなく、自動化できます。 あなたは手紙を受け取り、それに反応します。 あるいは自動で撮影することもできます。

これが起こる明確な理由はありますか?

いくつか挙げてみました。 その他のより複雑な例。 そして長時間の会話も可能です。

ご報告ありがとうございます！ pg_repack ユーティリティについて明確にしたいと思いました。 排他ロックをしなければ...

彼女は排他的ロックを行います。

... データが失われる可能性があります。 この間、アプリケーションは何も記録すべきではありませんか?

いいえ、テーブルではスムーズに動作します。つまり、pg_repack は最初に存在するすべてのライブ行を転送します。 当然のことながら、テーブルへの何らかのエントリがそこで発生します。 彼はちょうどこのポニーテールを捨てているところです。

つまり、彼は最終的にそれを実際に行うのでしょうか？

最終的に、彼はこれらのファイルを交換するために排他的ロックを取得します。

VACUUM FULLよりも早くなりますか？

VACUUM FULL は、開始するとすぐに排他ロックを取得しました。 そして、すべてを成し遂げるまで、彼は彼女を手放しません。 また、pg_repackはファイル置換時のみ排他ロックをとります。 現時点ではそこに書き込みませんが、データは失われず、すべて問題ありません。

こんにちは！ 車の掃除機の操作について話しました。 赤、黄、緑の記録セルを含むグラフがありました。 つまり、黄色のもの - 彼はそれらを削除済みとしてマークしました。 その結果、何か新しいものを書き込むことができるのでしょうか？

はい。 Postgres は行を削除しません。 彼にはそのような特異性があります。 行を更新した場合は、古い行を削除済みとしてマークしました。 この行を変更したトランザクションの ID がそこに表示され、新しい行を書き込みます。 そして、それらを読み取る可能性のあるセッションがあります。 ある時点で、彼らはかなりの年齢になります。 自動バキュームの仕組みの本質は、これらの行を通過して不要なものとしてマークを付けることです。 そして、そこでデータを上書きすることができます。

わかりました。 しかし、質問の内容はそこではありません。 終わらなかった。 テーブルがあると仮定しましょう。 可変サイズのフィールドがあります。 また、新しいものを挿入しようとしても、古いセルに収まらない可能性があります。

いいえ、いずれにしても、行全体がそこで更新されます。 Postgres には XNUMX つのデータ ストレージ モデルがあります。 データ型から選択します。 テーブルに直接格納されるデータもあれば、tos データもあります。 これらは大量のデータです: テキスト、json。 それらは別々のプレートに保管されます。 そして、これらの錠剤によると、膨満感についても同じことが起こります、つまり、すべてが同じです。 それらは個別にリストされているだけです。

ご報告ありがとうございます！ 期間を制限するためにステートメント タイムアウト クエリを使用することは許容されますか?

非常に許容範囲です。 私たちはこれをどこでも使用します。 また、当社は独自のサービスを持っていないため、リモート サポートを提供しており、さまざまなクライアントを抱えています。 そして誰もがこれに完全に満足しています。 つまり、チェックする cron ジョブがあります。 セッションの期間は単にクライアントと合意されるものであり、それ以前に私たちが同意するものではありません。 10分かもしれないし、XNUMX分かもしれない。 それはベースにかかる負荷とその目的によって異なります。 しかし、私たちは皆 pg_stat_activity を使用します。

ご報告ありがとうございます！ あなたのレポートを私のアプリケーションに適用しようとしています。 そして、私たちはどこでもトランザクションを開始し、どこでも明らかに完了しているように見えます。 何らかの例外がある場合でも、ロールバックは発生します。 そして、私は考え始めました。 結局のところ、トランザクションは明示的に開始できない可能性があります。 これはおそらく少女へのヒントです。 レコードを更新するだけの場合、トランザクションは PostgreSQL で開始され、接続が切断されたときにのみ完了しますか?

ここでアプリケーション レベルについて話している場合、それは使用しているドライバーや ORM によって異なります。 そこにはたくさんの設定があります。 自動コミットが有効になっている場合、トランザクションはそこで開始され、すぐに終了します。

つまり、アップデート後すぐに閉じてしまうのでしょうか？

設定により異なります。 設定の XNUMX つに名前を付けました。 これは自動コミットオンです。 それはよくあることです。 有効になっている場合、トランザクションは開始および終了しています。 「トランザクションの開始」と「トランザクションの終了」を明示的に指定した場合を除き、単にセッションにリクエストを開始しただけです。

こんにちは！ ご報告ありがとうございます！ データベースがどんどん肥大化して、サーバー上のスペースが足りなくなったと想像してみましょう。 この状況を解決するツールはありますか?

サーバー上のスペースを適切に監視する必要があります。

たとえば、DBA はお茶をしに行ったり、リゾートに行ったりしました。

ファイル システムが作成されると、データが書き込まれない少なくともある種のバックアップ スペースが作成されます。

完全に氷点下になったらどうなるでしょうか？

そこでは予約スペースと呼ばれます。つまり、それを解放することができ、作成されたサイズに応じて空きスペースが得られます。 デフォルトでは何個あるかわかりません。 また、別のケースでは、再構築操作を実行する余地を残すためにディスクを納品します。 不要であることが確実なテーブルは削除できます。

他にツールはありますか?

いつも手作りです。 そして、一部のデータは重要であり、一部のデータは重要ではないため、局所的にはそこで何をするのが最善であるかが明確になります。 また、各データベースとそれと連携するアプリケーションは、ビジネスによって異なります。 必ず現地で決めます。

ご報告ありがとうございます！ 質問がXNUMXつあります。 まず、トランザクションがスタックすると、テーブルスペースのサイズとインデックスのサイズの両方が増加することを示すスライドを示しました。 さらにレポートには、タブレットをパッケージ化する多数のユーティリティが含まれていました。 インデックスはどうですか？

彼らも梱包します。

しかし、真空はインデックスに影響を与えないのでしょうか?

インデックスを使用して動作するものもあります。 たとえば、pg_rapack、pgcompacttable。 バキュームによりインデックスが再作成され、インデックスに影響を与えます。 VACUUM FULL の考え方は、すべてを上書きすることです。つまり、誰でも機能します。

そしてXNUMXつ目の質問。 なぜレプリカに関するレポートがレプリケーション自体に大きく依存するのか理解できません。 レポートは読み取り、レプリケーションは書き込みであるように思えました。

レプリケーションの競合の原因は何ですか? 私たちにはプロセスが行われるマスターがあります。 車の掃除機をかけています。 自動バキュームは実際に何をするのでしょうか? 彼は古いセリフをいくつか切り取っています。 この時点で、これらの古い行を読み取るリクエストがレプリカ上にあり、マスター上で自動バキュームがこれらの行を上書き可能としてマークする状況が発生した場合、それらを上書きします。 そして、データ パケットを受信しました。リクエストに必要な行をレプリカ上で書き換える必要がある場合、レプリケーション プロセスは、設定したタイムアウトになるまで待機します。 そして、PostgreSQL にとって何がより重要かを判断します。 そして、彼にとってはリクエストよりもレプリケーションの方が重要であり、レプリカにこれらの変更を加えるためにリクエストを実行します。

アンドレイ、質問があります。 プレゼンテーション中に示されたこれらの素晴らしいグラフは、あなたが何らかの用途で取り組んだ結果なのでしょうか？ グラフはどのように作成されましたか?

これはサービスです オクメーター.

これは市販品ですか？

はい。 これは市販品です。

出所： habr.com