親愛なるDELETE様。 ニコライ・サモクバロフ (Postgres.ai)

遠い将来、不要なデータを自動的に削除することが DBMS の重要なタスクの 1 つになるでしょう [1]。 それまでの間、私たち自身が不要なデータを削除したり、より安価なストレージ システムに移動したりする必要があります。 数百万行を削除することにしたとします。 特に条件がわかっており、適切なインデックスがある場合は、非常に単純なタスクです。 「DELETE FROM table1 WHEREcolXNUMX = :value」 - もっと簡単なことはないでしょうか?

ビデオ：

• 私は初年度、つまり 2007 年から Highload プログラム委員会に参加しています。

• 私は 2005 年から Postgres を使用しています。 多くのプロジェクトで使用されました。

• 2007 年からは RuPostges ともグループ化。

• Meetup の参加者は 2100 名を超えるまでに成長しました。 長らくサンフランシスコに抜かれ、ニューヨークに次ぐ世界第２位だった。

• 私はカリフォルニアに数年間住んでいます。 私は大企業を含むアメリカ企業と取引することが多いです。 彼らは Postgres のアクティブ ユーザーです。 そして、さまざまな興味深いものがあります。

https://postgres.ai/ 私の会社です。 私たちは、開発の遅延を解消するタスクを自動化するビジネスに取り組んでいます。

何かをしている場合、Postgres の周囲に何らかのプラグが存在することがあります。 管理者がテスト スタンドをセットアップするまで待つ必要がある場合、または DBA が応答するまで待つ必要があるとします。 そして、私たちは開発、テスト、管理プロセスにおけるそのようなボトルネックを発見し、自動化と新しいアプローチの助けを借りてそれらを排除しようとします。

https://www.seagate.com/files/www-content/our-story/trends/files/idc-seagate-dataage-whitepaper.pdf

私は最近ロサンゼルスの VLDB にいました。 これはデータベースに関する最大のカンファレンスです。 また、将来的には DBMS がデータを保存するだけでなく、自動的に削除するようになるという報告もありました。 これは新しいトピックです。

世界ではゼタバイト、つまり 1 ペタバイトのデータがますます増えています。 そして現在、世界中には 000 ゼタバイトを超えるデータが保存されているとすでに推定されています。 そして、その数はますます増えています。

https://vldb2019.github.io/files/VLDB19-keynote-2-slides.pdf

そしてそれをどうすればよいでしょうか？ 明らかにそれを取り除く必要があります。 この興味深いレポートへのリンクは次のとおりです。 しかし、これまでのところ、これは DBMS に実装されていません。

お金を数えられる人は XNUMX つのことを望んでいます。 彼らは私たちに削除を求めているので、技術的には削除できるはずです。

次に私が話すのは、実際の状況を多数含む抽象的な状況です。つまり、私と周囲のデータベースに実際に何年も何度も起こったことをまとめたものです。 熊手はどこにでもあり、誰もがいつもそれを踏んでいます。

成長中の XNUMX つまたは複数の拠点があるとします。 そして、いくつかのレコードは明らかにゴミです。 たとえば、ユーザーはそこで何かを始めましたが、それを完了しませんでした。 そしてしばらくして、この未完成のものはもう保存できないことがわかります。 つまり、スペースを節約したり、パフォーマンスを向上させたりするために、不要なものをいくつかクリーンアップしたいと考えています。

一般に、タスクは、テーブル内の特定のもの、特定の行の削除を自動化することです。

そして、今日お話しするのは、ゴミの撤去についてです。

経験豊富な開発者に依頼しました。 彼はこのリクエストを受け取り、自分で確認しました。すべてがうまくいきました。 ステージングでテストしました - すべて問題ありません。 ロールアウト - すべてが機能します。 XNUMX 日 XNUMX 回実行しますが、すべて問題ありません。

データベースはどんどん成長していきます。 毎日の DELETE の動作が少し遅くなります。

その後、マーケティング会社を設立し、トラフィックが数倍になることがわかったので、不要なものを一時的に停止することにしました。 そして返すのを忘れます。

数か月後、彼らは思い出した。 そして、その開発者は辞めたか、別のことで忙しいため、別の開発者に返却するように指示しました。

彼は開発とステージングをチェックしましたが、すべて問題ありませんでした。 当然のことながら、蓄積されたものをクリーンアップする必要があります。 彼はすべてが動作することを確認しました。

次は何が起こる？ そうなると、私たちにとってすべてが崩壊してしまいます。 それは落ちるので、ある時点ですべてが倒れます。 誰もがショックを受けており、何が起こっているのか誰も理解していません。 そして、問題はこのDELETEにあったことが判明しました。

何か問題が発生しましたか? 何が問題だったのかを以下にリストします。 これらのうちどれが最も重要ですか?

• たとえば、レビューはありませんでした。つまり、DBA 専門家はそれを確認しませんでした。 彼は経験豊富な目で問題をすぐに見つけます。さらに、数百万行が蓄積されている本番環境にアクセスできます。

• おそらく彼らは何か間違ったことをチェックしたのでしょう。

• おそらくハードウェアが古いため、このベースをアップグレードする必要があります。

• または、データベース自体に問題があり、Postgres から MySQL に移行する必要があります。

• あるいは操作に何か問題があるのか​​もしれません。

• もしかしたら、仕事の組織化に何らかの間違いがあり、誰かを解雇して最も優秀な人材を雇う必要があるかもしれません?

DBA チェックはありませんでした。 もし DBA がいたら、これらの数百万行を見て、実験をしなくても「彼らはそんなことはしない」と言うでしょう。 このコードが GitLab や GitHub にあり、コード レビュー プロセスがあり、DBA の承認なしにこの操作が本番環境で行われるというようなことはなかったと仮定します。その場合、明らかに DBA はこう言うでしょう。 」

そして彼は、ディスク IO に問題が発生し、すべてのプロセスがおかしくなり、ロックが発生する可能性があり、さらに自動バキュームが数分間ブロックされることになるため、これは良くないと言います。

http://bit.ly/nancy-hl2018-2

1番目の間違いは、間違った場所でチェックしたことです。 後になってから、大量のジャンク データが本番環境に蓄積されていることを確認しましたが、開発者はこのデータベースにデータを蓄積しておらず、ステージング中にこのジャンク データを作成した人はいませんでした。 したがって、すぐに解決できる行が 000 行ありました。

私たちは、テストが弱い、つまり構築されたプロセスが問題を検出できないことを理解しています。 適切な DB 実験が行われていませんでした。

理想的な実験は同じ装置で実行することが望ましいです。 これを同じ機器で常に実行できるとは限りませんが、データベースのフルサイズのコピーであることが非常に重要です。 これは私がここ数年説教してきたことです。 XNUMX 年前に私はこのことについて話しましたが、すべて YouTube で見ることができます。

もしかしてうちの設備が悪いのか？ 見てみると、レイテンシーが跳ね上がりました。 使用率が 100% であることがわかりました。 もちろん、これらが最新の NVMe ドライブであれば、おそらくはるかに簡単になるでしょう。 そしておそらく私たちはそこから横たわらないでしょう。

クラウドを使用している場合、アップグレードはそこで簡単に実行できます。 新しいハードウェアで新しいレプリカを作成しました。 切り替える。 そしてすべて順調です。 とても簡単。

どうにかして小さなディスクに触れることができますか? ここでは、DBA の助けを借りて、チェックポイント チューニングと呼ばれる特定のトピックについて詳しく説明します。 チェックポイント調整を行っていないことが判明しました。

チェックポイントとは何ですか? どの DBMS にもあります。 メモリ内に変更されたデータがある場合、そのデータはすぐにはディスクに書き込まれません。 データが変更されたという情報は、まず先行書き込みログに書き込まれます。 そしてある時点で、DBMS は実際のページをディスクにダンプする時期が来たと判断します。これにより、障害が発生した場合に REDO を減らすことができます。 まるでおもちゃのようだ。 死亡した場合は、最後のチェックポイントからゲームが開始されます。 そしてすべての DBMS がそれを実装します。

Postgresの設定が遅れています。 これらは、10 ～ 15 年前のボリュームのデータとトランザクション向けに設計されています。 チェックポイントも例外ではありません。

以下は、Postgres チェックアップ レポート、つまり自動ヘルス チェックからの情報です。 ここに数テラバイトのデータベースがあります。 そして、ほぼ90％のケースで検問所が強制されたことがよくわかります。

それはどういう意味ですか？ そこには10つの設定があります。 チェックポイントは、たとえば XNUMX 分のタイムアウトによって到達することができます。 あるいは、かなり多くのデータが埋め込まれたときに発生する可能性があります。

デフォルトでは、max_wal_saze は 1 ギガバイトに設定されます。 実際、これは Postgres で 300 ～ 400 メガバイト以降で実際に発生します。 大量のデータを変更すると、チェックポイントが発生します。

そして、誰も調整せず、サービスが成長し、会社が多額の収益を上げ、トランザクション数が多ければ、チェックポイントは 30 分に XNUMX 回、場合によっては XNUMX 秒ごとに、場合によっては重複することもあります。 これはかなりひどいことです。

そして、それが起こる頻度を減らす必要があります。 つまり、max_wal_size を上げることができます。 そして来る頻度も減ります。

しかし、私たちはそれをより正確に行う方法、つまり、明確に特定のデータに基づいて設定の選択に関する意思決定を行う方法に関する全体的な方法論を開発しました。

したがって、データベースに関して XNUMX つの一連の実験を行っています。

最初のシリーズ - max_wal_size を変更します。 そして私たちは大規模な作戦を行っています。 まず、デフォルト設定の 1 ギガバイトで実行します。 そして、何百万行もの大規模な DELETE を実行します。

私たちにとってそれがどれほど難しいかわかります。 ディスク IO が非常に悪いことがわかります。 これは非常に重要なので、生成した WAL の数を確認します。 チェックポイントが何回発生したかを見てみましょう。 そして、それが良くないこともわかります。

次に、max_wal_size を増やします。 繰り返します。 私たちは増加し、繰り返します。 そして何度も。 原則として、10、1、2、4 ギガバイトの場合は 8 ポイントが適切です。 そして、特定のシステムの動作を調べます。 ここでの機器が本番環境と同じである必要があることは明らかです。 同じディスク、同じ量のメモリ、同じ Postgres 設定が必要です。

このようにしてシステムを交換すると、不正な一括削除の場合に DBMS がどのように動作するか、チェックポイントがどのように行われるかがわかります。

チェックポイントはロシア語で検問所。

例: インデックスによって数百万行を削除すると、行はページ全体に「分散」します。

ここに一例を示します。 これはある基地です。 また、max_wal_size のデフォルト設定が 1 GB であるため、ディスクが記録用に棚に置かれていることは明らかです。 この写真は非常に病気の患者の典型的な症状です。つまり、彼は本当に気分が悪くなっています。 操作は XNUMX つだけで、数百万行の DELETE だけでした。

このような操作が prod で許可されている場合は、XNUMX 回の DELETE がシェルフ内で私たちを殺すことは明らかなので、ただ横になることになります。

さらに、16 ギガバイトの場合、すでに歯がなくなっていることは明らかです。 歯はすでに良くなりました。つまり、天井を叩いていますが、それほど悪くはありません。 そこにはある程度の自由がありました。 右側が記録です。 そして操作数 - 16番目のグラフ。 そして、XNUMX ギガバイトになると、すでに呼吸が少し楽になっているのは明らかです。

そして 64 ギガバイトを見ると、完全に改善されていることがわかります。 すでに歯が目立っているため、他の手術を乗り越えてディスクで何かをする機会が増えています。

これはなぜですか？

もう少し詳しく説明しますが、このトピック、つまりチェックポイント チューニングの実施方法については、レポート全体が作成される可能性があるため、あまり読み込まずに、どのような困難があるのか​​について少し概要を説明します。

チェックポイントが頻繁に発生し、行を順番に更新せずにインデックスで検索する場合、テーブル全体を削除しないため、これは良いことですが、最初に最初のページにアクセスし、次に XNUMX 番目のページにアクセスし、そして最初に戻りました。 そして、最初のページへのこれらの訪問の間に、チェックポイントがそのページをすでにディスクに保存していた場合、XNUMX 度目にページを汚したため、チェックポイントはそれを再度保存します。

そしてチェックポイントを何度も強制的に保存します。 彼にとって余分な手術がどのように行われるでしょうか。

しかし、それだけではありません。 ページは Postgres で 8 キロバイト、Linux で 4 キロバイトです。 そして、full_page_writes 設定があります。 デフォルトでは有効になっています。 これは正しいです。これをオフにすると、クラッシュした場合にページの半分しか保存されない危険性があるからです。

フォワード ログの WAL への書き込み動作は、チェックポイントがあり、初めてページを変更すると、変更したのは 8 キロバイトすべてであるにもかかわらず、ページ全体、つまり 100 キロバイトすべてがフォワード ログに書き込まれます。行、重さは XNUMX バイトです。 そしてページ全体を書き留めなければなりません。

その後の変更では特定のタプルのみが存在しますが、初めてすべてを書き留めます。

したがって、チェックポイントが再び発生した場合は、すべてを最初からやり直して、ページ全体をプッシュする必要があります。 チェックポイントが頻繁にある場合、同じページを通過すると、full_page_writes = on が想定以上に多くなります。つまり、より多くの WAL が生成されます。 さらに多くのデータがレプリカ、アーカイブ、ディスクに送信されます。

したがって、XNUMX つの余剰人員が存在します。

max_wal_size を増やすと、チェックポイントと wal ライターの両方が容易になることがわかります。 それは素晴らしいことです。

テラバイトを投入して、それで暮らしてみましょう。 それの何が悪いことですか？ これは悪いことです。チェックポイントはずっと前にあり、すでに多くのことが変わっているため、失敗した場合は何時間も登ることになります。 そして、これらすべてをやり直しする必要があります。 そこで私たちは XNUMX 番目の一連の実験を行います。

操作を実行し、チェックポイントが完了するタイミングを確認し、-9 Postgres を意図的に強制終了します。

その後、再度起動して、この装置でどのくらいの時間上昇するか、つまり、この悪い状況でどれだけ REDO するかを確認します。

状況が悪いことに二度注意します。 まず、チェックポイントが終わる直前にクラッシュしてしまったので、失うものがたくさんあります。 そして第二に、大規模な手術が行われました。 また、チェックポイントがタイムアウトになった場合、最後のチェックポイント以降に生成される WAL はおそらく少なくなります。 つまり二重の敗者です。

このような状況をさまざまな max_wal_size サイズで測定し、max_wal_size が 64 ギガバイトの場合、10 倍の最悪のケースでは 3 分間上昇することがわかりました。 そしてそれが自分に合うか合わないかを考えます。 これはビジネス上の質問です。 私たちはこの写真を経営上の意思決定の責任者に見せて、こう尋ねる必要があります。 最悪の状況で5〜XNUMX分間横になることができますか？ そしてあなたは決断を下します。

そしてここに興味深い点があります。 カンファレンスでのパトローニに関するレポートがいくつかあります。 そしてもしかしたらあなたもそれを使っているかもしれません。 これは Postgres の自動フェイルオーバーです。 GitLab と Data Egret がこれについて話しました。

30 秒以内に自動フェイルオーバーが行われる場合は、10 分間横になってもよいでしょうか? この時点までにレプリカに切り替えると、すべてがうまくいくためです。 これは議論の余地があります。 明確な答えはわかりません。 このトピックはクラッシュリカバリに関するものだけではないと感じています。

失敗からの回復に時間がかかると、他の多くの状況でも不快になります。 たとえば、同じ実験で、何かをするときに 10 分間待たなければならない場合があります。

たとえ自動フェイルオーバーがあったとしても、私はそこまでやりすぎるつもりはありません。 一般に、64、100 ギガバイトなどの値が適切な値です。 場合によっては、選択を減らすことさえ価値があります。 一般に、これは微妙な科学です。

たとえば、max_wal_size =1, 8 などの反復を実行するには、一括操作を何度も繰り返す必要があります。 やった。 そして、同じベースで、もう一度やりたいと思っていますが、すでにすべてを削除しています。 何をするか？

私たちの解決策、そのような状況で反復するために何をするかについては後ほど説明します。 そしてこれが最も正しいアプローチです。

しかし、この場合、私たちは幸運でした。 ここで「BEGIN、DELETE、ROLLBACK」と書かれている場合は、DELETE を繰り返すことができます。 つまり、自分でキャンセルした場合は、繰り返すことができます。 そして物理的には、データは同じ場所に置かれます。 むくみもありません。 このような DELETE を反復処理できます。

この ROLLBACK を使用した DELETE は、適切にデプロイされたデータベース ラボがない場合でも、チェックポイントのチューニングに最適です。

「i」がXNUMX列のプレートを作りました。 Postgres にはユーティリティ列があります。 特に要求されない限り、それらは表示されません。 これらは、ctid、xmid、xmax です。

ctid は物理アドレスです。 ゼロページ、ページ内の最初のタプル。

ROOLBACK 後もタプルは同じ場所に残っていることがわかります。 つまり、再試行しても同じように動作します。 これが主なものです。

Xmax はタプルの消滅時刻です。 スタンプされていますが、Postgres はトランザクションがロールバックされたことを認識しているため、それが 0 であるかロールバックされたトランザクションであるかは関係ありません。 これは、DELETE を反復してシステム動作の一括操作を確認できることを示唆しています。 貧しい人々のためのデータベースラボを作ることができます。

プログラマーについての話です。 DBAについても、プログラマーに対して「なぜそんなに長くて難しい操作をするのですか？」といつも叱られます。 これはまったく異なる垂直のトピックです。 かつては管理が行われていましたが、これからは開発が行われます。

明らかに、私たちはばらばらにはなっていません。 それは明らかだ。 このような DELETE を何百万行もの部分に分割しないことは不可能です。 それは20分間行われ、すべてが横になります。 しかし、残念ながら、たとえ大企業であっても、経験豊富な開発者でもミスをすることがあります。

なぜ壊すことが重要なのでしょうか？

• ディスクが硬いことがわかったら、速度を下げましょう。 そして、壊れた場合は、一時停止を追加したり、スロットリングを遅くしたりすることができます。

• そして、私たちは他の人を長期間ブロックしません。 場合によっては問題ありませんが、誰も作業していない実際のガベージを削除する場合は、トランザクションが完了するまで待機するため、自動バキューム作業以外はブロックされない可能性が高くなります。 しかし、他の人がリクエストできるものを削除すると、その人はブロックされ、ある種の連鎖反応が発生します。 Web サイトやモバイル アプリケーションでは長時間のトランザクションを避ける必要があります。

https://postgres.ai/products/joe/

これは面白い。 開発者が「どのパック サイズを選択すればよいですか?」と尋ねるのをよく見かけます。

バンドル サイズが大きくなるほど、トランザクション オーバーヘッド、つまりトランザクションによる追加オーバーヘッドが小さくなるのは明らかです。 しかし同時に、このトランザクションにかかる時間も増加します。

私には非常に単純なルールがあります。それは、できる限り多くのデータを取得しますが、XNUMX 秒あたりの実行可能ファイルの数を超えないようにすることです。

なぜちょっと? 説明は非常にシンプルで、専門知識のない人でも理解できます。 反応が見られます。 50 ミリ秒を考えてみましょう。 何かが変化すると、私たちの目は反応します。 それ以下の場合は、さらに困難になります。 たとえば、マウスをクリックして 100 ミリ秒後に何かが応答した場合、すでにこのわずかな遅延を感じています。 100番目はすでにブレーキとして認識されています。

したがって、大量の操作を 10 秒のバーストに分割すると、誰かをブロックしてしまうリスクがあります。 そして、それは数秒間効果があり、人々はすでにそれに気づきます。 したがって、私はXNUMX秒以上はやりたくないのです。 ただし同時に、トランザクションのオーバーヘッドが顕著になるため、あまり細かく分割しないでください。 ベースが硬くなり、別の問題が発生する可能性があります。

パックのサイズを選択します。 それぞれの場合において、異なる方法で実行できます。 自動化できる。 そしてXNUMXパックの処理効率にも自信があります。 つまり、XNUMXつのパックのDELETEまたはUPDATEを行います。

ちなみに、私が話しているのは DELETE だけではありません。 ご想像のとおり、これらはデータに対する一括操作です。

そして、その計画が素晴らしいことが分かりました。 インデックス スキャンが確認できますが、インデックスのみのスキャンはさらに優れています。 そして、少量のデータが関係しています。 そして、それは XNUMX 秒もかからずに完了します。 素晴らしい。

そして、劣化がないことを確認する必要があります。 最初のパックはすぐにうまくいきますが、その後はどんどん悪化していきます。 このプロセスでは多くのテストを行う必要があります。 データベース ラボはまさにこれに当てはまります。

そして、本番環境でこれに正しく従うことができるように、何かを準備する必要があります。 たとえば、ログに時刻を書き込んだり、今どこにいるのか、誰を削除したのかを書き込んだりできます。 そしてこれにより、後で何が起こっているかを理解できるようになります。 そして、何か問題が発生した場合には、すぐに問題を見つけます。

リクエストの効率をチェックする必要があり、何度も繰り返す必要がある場合は、フェロー ボットのようなものがあります。 彼はすでに準備ができています。 毎日数十人の開発者によって使用されています。 そして彼は、要求に応じて 30 秒で巨大なテラバイトのデータベースを自分のコピーとして提供する方法を知っています。 そこで何かを削除して「リセット」と言い、再度削除することができます。 このように実験してみることができます。 私はこのことには未来があると思っています。 そして私たちはすでにそれを実行しています。

https://docs.gitlab.com/ee/development/background_migrations.html

パーティショニング戦略とは何ですか? このパックの開発者が使用している 3 つの異なるパーティショニング戦略がわかります。

100つ目はとてもシンプルです。 数値IDを持っています。 そして、それをさまざまな間隔に分割して作業してみましょう。 マイナス面は明らかです。 最初のセグメントには実際のゴミが 5 行あるかもしれませんが、1 番目の 000 行にはゴミが含まれているか、まったく含まれていない場合、XNUMX 行すべてがゴミであることが判明します。 非常に不均一な作業ですが、壊れやすいです。 彼らは最大のIDを奪い、それを粉砕しました。 これは素朴なアプローチです。

10 番目の戦略はバランスのとれたアプローチです。 Gitlabで使用されています。 彼らはテーブルを手に取ってスキャンしました。 各パックにちょうど 000 レコードが含まれるように、ID パックの境界を見つけました。 そして彼らを列に並べます。 そして、処理していきます。 これは複数のスレッドで実行できます。

ちなみに、最初の戦略でも、これを複数のスレッドで実行できます。 難しくない。

https://medium.com/@samokhvalov/how-partial-indexes-affect-update-performance-in-postgres-d05e0052abc

しかし、もっとクールで優れたアプローチがあります。 これが XNUMX つ目の戦略です。 そして可能であればそれを選択する方が良いでしょう。 これは特別なインデックスに基づいて行われます。 この場合、ガベージ条件と ID に応じたインデックスになる可能性が高くなります。 ヒープに行かないように、インデックスのみのスキャンになるように ID を含めます。

一般に、インデックスのみのスキャンはインデックス スキャンよりも高速です。

そして、削除したい ID がすぐに見つかります。 BATCH_SIZE は事前に選択します。 そして、私たちはそれらを入手するだけでなく、特別な方法で入手し、すぐにハッキングします。 ただし、ロックしているため、すでにロックされている場合はロックせず、先に進んで次のロックを取得します。 更新スキップロック用です。 Postgres のこの優れた機能により、必要に応じて複数のスレッドで作業できるようになります。 XNUMXつのストリームで可能です。 そしてここに CTE があります - これは XNUMX つの要求です。 そして、この CTE の XNUMX 階では実際の削除が行われています - returning *。 IDを返すこともできますが、その方が良いです *各行にあまりデータがない場合。

なぜそれが必要なのでしょうか? これは私たちが報告する必要があるものです。 実際、非常に多くの行を削除しました。 そして、このようにIDまたはcreated_atごとに境界線を設定します。 最小値、最大値を実行できます。 他にも何かできるはずです。 ここにはたくさん詰めることができます。 モニタリングにも非常に便利です。

インデックスについてはもう XNUMX つ注意点があります。 このタスクに特別なインデックスが必要であると判断した場合は、それによってヒープのみのタプルの更新が損なわれないようにする必要があります。 つまり、Postgres にはそのような統計があります。 これは、テーブルの pg_stat_user_tables で確認できます。 ホットアップデートが使用されているかどうかを確認できます。

新しいインデックスによってそれらが単純に切り取られる場合があります。 他のアップデートもすべてすでに機能しているので、速度を落としてください。 インデックスが表示されたからだけではなく（各インデックスの更新が少し遅くなりますが、少しずつ）、ここではまだそれが台無しになっています。 また、このテーブルに対して特別な最適化を行うことはできません。 これは時々起こります。 これは非常に微妙な点なので、覚えている人はほとんどいません。 そしてこの熊手は踏みやすいです。 場合によっては、反対側からのアプローチを見つけて、それでもこの新しいインデックスを使用しない、別のインデックスを作成する、または他の方法 (たとえば XNUMX 番目の方法を使用する) が必要になる場合があります。

ただし、これが最も最適な戦略であり、バッチに分割して XNUMX つのリクエストでバッチを撮影したり、少し削除したりする方法です。

ロングトランザクション https://gitlab.com/snippets/1890447

自動バキュームのブロック - https://gitlab.com/snippets/1889668

ブロックの問題 - https://gitlab.com/snippets/1890428

間違い #5 は大きな間違いです。 Okmeter の Nikolai が Postgres の監視について話しました。 残念ながら、理想的な Postgres 監視は存在しません。 近いものもあれば、遠いものもあります。 OKmeter はほぼ完璧に近づいていますが、多くのものが欠けており、追加する必要があります。 これに備えて準備をしておく必要があります。

たとえば、無効なタプルは監視するのが最適です。 テーブルに死んだものがたくさんある場合は、何かが間違っています。 今すぐ対応した方が良いです。そうしないと劣化が起こる可能性があるので、横になっても大丈夫です。 それは起こります。

IO が大きい場合、これが良くないことは明らかです。

長い取引も。 OLTP では長いトランザクションを許可しないでください。 そして、ここにあるスニペットへのリンクを参照すると、このスニペットを使用して、すでに長いトランザクションの追跡を行うことができます。

なぜ長時間のトランザクションは良くないのでしょうか? すべてのロックが解除されるのは最後だけだからです。 そして私たちは皆を台無しにします。 さらに、すべてのテーブルの自動バキュームをブロックします。 全然良くないよ。 レプリカでホット スタンバイが有効になっている場合でも、それでも問題は発生します。 一般に、長時間のトランザクションを回避するのが最適な場所はありません。

バキュームされていないテーブルが多数ある場合は、アラートが必要です。 ここではそのような状況が考えられます。 自動バキュームの動作に間接的に影響を与える可能性があります。 これは Avito の抜粋で、私が少し改良したものです。 そして、これは自動バキュームで何ができるかを知るための興味深いツールであることがわかりました。 たとえば、いくつかのテーブルはそこで待機していて、順番を待ちません。 また、監視対象にしてアラートを発する必要もあります。

そしてブロックを発行します。 ブロックの木の森。 私は誰かから何かを取り入れて改良するのが好きです。 ここでは、ロック ツリーの森を示す Data Egret からクールな再帰 CTE を取得しました。 これは優れた診断ツールです。 そして、それに基づいて監視を構築することもできます。 しかし、これは慎重に行う必要があります。 自分用に小さなstatement_timeoutを作成する必要があります。 そして、lock_timeout が望ましいです。

場合によっては、これらすべてのエラーが合計して発生することがあります。

私の意見では、ここでの主な間違いは組織的なものです。 テクニックが引っ張らないので組織的です。 これがその 2 です。彼らは間違った場所にチェックを入れました。

簡単にチェックできる実稼働クローンがなかったため、間違った場所でチェックしてしまいました。 開発者は本番環境にまったくアクセスできない場合があります。

そして、私たちはそこにいないことを確認しました。 もし私たちがそこを確認していたら、私たち自身もそれを見ていたでしょう。 開発者は、同じ量のデータと同じ場所がある良好な環境でチェックすれば、DBA がなくてもすべてを確認できました。 彼はこのすべての劣化を見たら、恥じ入るだろう。

自動バキュームについてさらに詳しく。 数百万行の大規模なスイープを行った後も、REPACK を行う必要があります。 これはインデックスにとって特に重要です。 私たちがそこにあるものをすべて掃除した後、彼らは気分が悪くなるでしょう。

そして、毎日の掃除作業を復活させたい場合は、より頻繁に、ただし規模は小さくすることをお勧めします。 XNUMX 分に XNUMX 回、あるいはそれよりも少し頻繁に行うこともあります。 そして、XNUMX つのことを監視する必要があります。それは、これにエラーがないことと、遅れがないことです。 私が示したトリックはこれを解決するだけです。

私たちがやっていることはオープンソースです。 GitLab に投稿されています。 また、DBA がなくてもチェックできるようにしています。 私たちはデータベース ラボを行っています。つまり、Joe が現在作業しているベース コンポーネントを呼び出します。 そして、本番環境のコピーを入手することもできます。 現在、Slack 用の Joe の実装があり、そこで「これこれのリクエストを説明する」と言うと、データベースのコピーの結果をすぐに取得できます。 そこで削除することもできますが、誰もそれに気づきません。

10 テラバイトがあるとします。データベース ラボも 10 テラバイトにします。 また、同時に 10 テラバイトのデータベースを使用できるため、10 人の開発者が同時に作業できます。 誰もが自分のやりたいことをすることができます。 削除したりドロップしたりできるなど、それはとても幻想です。 これについては明日話します。

これはシン プロビジョニングと呼ばれます。 これは微妙なプロビジョニングです。 これは、開発やテストの遅れを大幅に解消し、この点で世界をより良い場所にするある種のファンタジーです。 つまり、一括操作の問題を回避できるだけです。

例: 5 テラバイトのデータベース、30 秒以内にコピーを取得します。 そして、それはサイズにも依存しません、つまり、何テラバイトかは関係ありません。

今日あなたが行けるのは、 postgres.ai そして私たちのツールを掘り下げてみましょう。 登録すると、そこにあるものを確認できます。 このボットをインストールできます。 それは無料です。 書く。

質問

実際の状況では、テーブルに残すべきデータが、削除する必要があるデータよりもはるかに少ないことが判明することがよくあります。 つまり、そのような状況では、新しいオブジェクトを作成し、必要なデータのみをそこにコピーし、古いテーブルをトランクする方が簡単なため、そのようなアプローチを実装する方が簡単であることがよくあります。 切り替え中の現時点では、プログラムによるアプローチが必要であることは明らかです。 このアプローチはどうですか？

これは非常に優れたアプローチであり、非常に優れたタスクです。 これは pg_repack が行うことと非常に似ており、ID を 4 バイトにするときに行う必要があることと非常に似ています。 多くのフレームワークは数年前にこれを行っていましたが、プレートだけが大きくなり、8 バイトに変換する必要がありました。

この作業はかなり難しいです。 やった。 そして、非常に注意しなければなりません。 鍵などはありますがやられています。 つまり、標準的なアプローチは pg_repack を使用することです。 このようなラベルを宣言します。 また、スナップショット データのアップロードを開始する前に、すべての変更を追跡する XNUMX つのプレートも宣言します。 一部の変更を追跡できない可能性があるというトリックがあります。 微妙な点があります。 そして、ローリング変更によって切り替えます。 全員をシャットダウンするときは少し時間がかかりますが、通常はこれが行われています。

GitHub の pg_repack を見ると、ID を int 4 から int 8 に変換するタスクがあったときに、pg_repack 自体を使用するというアイデアがありました。 これも可能ですが、ちょっとしたハックですが、これでも機能します。 pg_repack が使用するトリガーに介入して、「このデータは必要ありません」と言うことができます。つまり、必要なものだけを転送します。 そして彼はただ切り替えて、それで終わりです。

このアプローチでも、テーブルの XNUMX 番目のコピーを取得できます。このコピーでは、データがすでにインデックス付けされており、美しいインデックスで非常に均等に積み重ねられています。

膨満感はなく、良いアプローチです。 しかし、これを自動化する、つまり普遍的なソリューションを開発する試みがあることは知っています。 この自動化についてご連絡させていただきます。 Python で書かれているので、それは良いことです。

私は MySQL の世界から少しだけ離れているので、話を聞きに来ました。 そして私たちはこのアプローチを採用しています。

ただし、それは 90% を達成した場合に限ります。 5% ある場合、それを使用するのはあまり良くありません。

ご報告ありがとうございます！ prod の完全なコピーを作成するリソースがない場合、負荷やサイズを計算するためのアルゴリズムや計算式はありますか?

良い質問。 これまでのところ、数テラバイトのデータベースを見つけることができています。 たとえハードウェアが同じでなくても、たとえば、メモリやプロセッサが少なく、ディスクがまったく同じではありませんが、それでも私たちは同じことをします。 まったくどこにもない場合は、考える必要があります。 明日まで考えさせてください、あなたが来ました、私たちは話します、これは良い質問です。

ご報告ありがとうございます！ あなたは最初に、さまざまな制限があるクールな Postgres が存在するという事実から始めましたが、それは開発中です。 そして、これは全体的に見て松葉杖です。 これはすべて、Postgres 自体の開発と矛盾しているのではありませんか。Postgres 自体の開発では、何らかの DELETE 遅延関数や、ここで私たちが奇妙な手段で汚そうとしているものを低レベルに保つべき何かが現れるでしょう。

SQL で XNUMX つのトランザクションで多くのレコードを削除または更新すると言ったら、Postgres はどのようにしてそれをそこに分散できるのでしょうか? 物理的に業務に限界がございます。 これからも長くやっていきます。 そしてこの時点でロックします、など。

インデックスの作成は完了です。

同じチェックポイントのチューニングを自動化できると考えられます。 いつかそうなるかもしれない。 しかし、それでは質問がよくわかりません。

問題は、あちらに行ったりこちらに行ったり、こちらでは並行して進むような開発のベクトルがあるのか​​ということです。 それらの。 彼らはまだそれについて考えていないのですか？

今から使える原則についてお話しました。 別のボットがあります ナンシー, これを使用すると、自動チェックポイント調整を行うことができます。 いつかPostgresにも登場するのでしょうか？ わかりません、まだ議論されていません。 私たちはまだそこからは程遠いです。 しかし、新しいシステムを作る科学者がいます。 そして彼らは私たちを自動インデックスに押し込みます。 発展もある。 たとえば、自動チューニングを見てみましょう。 パラメータを自動的に選択します。 しかし、彼はまだチェックポイント調整を行うつもりはありません。 つまり、パフォーマンスやシェルバッファーなどが向上します。

チェックポイントの調整については、これを行うことができます。クラウド上に XNUMX 個のクラスターと異なるハードウェア、異なる仮想マシンがある場合、当社のボットを使用できます。 ナンシー 自動化を行います。 そして、max_wal_size はターゲット設定に従って自動的に選択されます。 しかし、残念ながら今のところ、これは核心に迫るものではありません。

こんにちは長期取引の危険性について話しました。 削除の場合には自動バキュームがブロックされると言いました。 他にどのように私たちに害を及ぼすのでしょうか？ なぜなら、私たちはスペースを解放し、それを使用できるようにすることについてもっと話しているからです。 他に何が足りないでしょうか？

ここでの最大の問題は自動バキュームではないかもしれません。 そして、長いトランザクションが他のトランザクションをロックする可能性があるという事実、この可能性はさらに危険です。 彼女は会うかもしれないし、会わないかもしれない。 もし彼女が会ったら、それは非常に悪いことになる可能性があります。 そして自動バキュームに関しても、これは問題です。 OLTP の長いトランザクションには、ロックと自動バキュームという XNUMX つの問題があります。 また、レプリカ上でホット スタンバイ フィードバックが有効になっている場合でも、マスター上で自動バキューム ロックを受け取ります。ロックはレプリカから届きます。 しかし、少なくともロックは存在しないでしょう。 そしてロックもあるでしょう。 データの変更について話しているので、ここではロックが重要なポイントになります。 そして、これがずっと長い間続くと、ロックされるトランザクションがますます増えます。 彼らは他人を盗むことができます。 そしてロックの木が現れます。 スニペットへのリンクを提供しました。 そして、この問題は、蓄積されるだけの自動バキュームの問題よりも早く顕著になります。

ご報告ありがとうございます！ あなたはテストが間違っていたと言ってレポートを始めました。 私たちは、同じ装備、同じ方法でベースを使用する必要があるという考えを続けました。 開発者にベースを提供したとしましょう。 そして彼はその要求に応じた。 そして彼は元気のようだ。 しかし、彼はライブの場合はチェックしませんが、ライブの場合、たとえば、60〜70％の負荷があります。 そしてこのチューニングをしてもあまりうまくいきません。

チームに専門家を配置し、実際のバックグラウンド負荷で何が起こるかを予測できる DBA 専門家を活用することが重要です。 クリーンな変更を実行すると、次のような画像が表示されます。 しかし、より高度なアプローチでは、本番環境でシミュレートされた負荷を使用して、同じことを再度実行しました。 とてもクールです。 それまでは成長しなければなりません。 大人っぽいですね。 私たちは自分たちが持っているものを確認し、十分なリソースがあるかどうかも確認しました。 それは良い質問です。

すでにガベージ セレクトを実行していて、たとえば削除済みフラグがある場合

これは、Postgres で自動バキュームが自動的に行うことです。

ああ、彼はやりますか？

Autovacuum はガベージ コレクターです。

ありがとうございます！

ご報告ありがとうございます！ すべてのゴミがメインテーブルのどこかから汚れてしまうような方法でパーティション分割を使用してデータベースをすぐに設計するオプションはありますか?

もちろんあります。

では、使用すべきではないテーブルをロックした場合に自分自身を守ることはできるのでしょうか?

もちろんあります。 しかし、それは鶏が先か卵が先かというような問題です。 将来何が起こるかを私たち全員が知っているなら、もちろん、私たちはすべてを冷静に行うでしょう。 しかし、ビジネスは変化しており、新しいコラムや新しい要求があります。 そして、おっと、それを削除したいと思います。 しかし、この理想的な状況は人生において起こりますが、常に起こるわけではありません。 しかし、全体的には良いアイデアです。 切り詰めるだけで終わりです。

出所： habr.com