Postgres: 肥大化、pg_repack、遅延制約

テーブルとインデックスに対する肥大化の影響は広く知られており、Postgres にだけ存在するわけではありません。 VACUUM FULL や CLUSTER など、すぐに使用できる対処方法がありますが、操作中にテーブルをロックするため、常に使用できるわけではありません。

この記事には、肥大化がどのように発生するか、それに対処する方法、遅延制約とそれが pg_repack 拡張機能の使用にもたらす問題についてのちょっとした理論が含まれます。

この記事は以下をもとに書かれています 私のスピーチ PgConf.Russia 2020にて。

むくみはなぜ起こるのでしょうか？

Postgres はマルチバージョン モデル (MVCC）。 その本質は、テーブル内の各行が複数のバージョンを持つことができる一方、トランザクションではこれらのバージョンのうち XNUMX つしか参照せず、必ずしも同じバージョンである必要はないということです。 これにより、複数のトランザクションが同時に動作し、実質的に相互に影響を与えることがなくなります。

明らかに、これらすべてのバージョンを保存する必要があります。 Postgres はメモリをページごとに処理し、ページはディスクから読み取ったり書き込んだりできる最小のデータ量です。 これがどのように起こるかを理解するために、小さな例を見てみましょう。

いくつかのレコードを追加したテーブルがあるとします。 新しいデータは、テーブルが保存されているファイルの最初のページに表示されます。 これらは、コミット後に他のトランザクションで使用できる行のライブ バージョンです (簡単にするために、分離レベルはコミット読み取りであると仮定します)。

次に、エントリの XNUMX つを更新し、古いバージョンを関連性がなくなったとマークしました。

段階的に行バージョンを更新および削除していくうちに、データの約半分が「ゴミ」になったページが完成しました。 このデータはどのトランザクションにも表示されません。

Postgresには仕組みがある VACUUM、古いバージョンを削除し、新しいデータ用のスペースを確保します。 しかし、十分に積極的に構成されていない場合、または他のテーブルでの作業が忙しい場合は、「ガベージ データ」が残り、新しいデータのために追加のページを使用する必要があります。

したがって、この例では、ある時点でテーブルは XNUMX ページで構成されますが、ライブ データが含まれるのは半分だけです。 その結果、テーブルにアクセスするときに、必要以上に多くのデータを読み取ることになります。

VACUUM が無関係な行バージョンをすべて削除したとしても、状況が劇的に改善されるわけではありません。 新しい行用にページ単位またはページ全体の空き領域が確保されますが、それでも必要以上に多くのデータを読み取ることになります。
ちなみに、完全に空白のページ (この例では XNUMX 番目) がファイルの最後にある場合、VACUUM はそれをトリミングできます。 しかし今、彼女は真ん中にいるので、彼女に何もすることはできません。

このような空のページまたは非常にまばらなページの数が増えると (肥大化と呼ばれます)、パフォーマンスに影響を及ぼし始めます。

上記で説明したことはすべて、テーブルの肥大化が発生するメカニズムです。 インデックスでもこれはほぼ同じように起こります。

むくみはありますか？

むくみがあるかどうかを判断する方法はいくつかあります。 XNUMX つ目のアイデアは、テーブル内の行数、「ライブ」行数などに関するおおよその情報を含む Postgres の内部統計を使用することです。インターネット上では、既製のスクリプトのさまざまなバリエーションが見つかります。 私たちは基礎としてとりました 脚本 PostgreSQL Experts から提供されており、トーストおよび肥大化した btree インデックスとともに肥大化テーブルを評価できます。 私たちの経験では、その誤差は 10 ～ 20% です。

別の方法は拡張機能を使用することです pgstattupleこれにより、ページ内を調べて、推定値と正確な膨張値の両方を取得できます。 ただし、XNUMX 番目のケースでは、テーブル全体をスキャンする必要があります。

20% までの小さな膨張値は許容できると考えられます。 これは、fillfactor の類似物と考えることができます。 テーブル и インデックス。 50% 以上になると、パフォーマンスの問題が発生する可能性があります。

むくみに対抗する方法

Postgres には、すぐに使用できる肥大化に対処する方法がいくつかありますが、必ずしもすべての人に適しているわけではありません。

肥大化が発生しないように AUTOVACUUM を設定する。 もっと正確に言えば、自分が許容できるレベルに保つことです。 これは「船長」のアドバイスのように思えますが、実際には、これを達成するのは必ずしも簡単ではありません。 たとえば、データ スキーマを定期的に変更する活発な開発が行われている場合や、何らかのデータ移行が行われている場合などです。 その結果、負荷プロファイルは頻繁に変更される可能性があり、通常はテーブルごとに異なります。 これは、常に少し先の作業を行い、各テーブルの変化するプロファイルに合わせて AUTOVACUUM を調整する必要があることを意味します。 しかし、明らかにこれを行うのは簡単ではありません。

AUTOVACUUM がテーブルを処理できないもう XNUMX つの一般的な理由は、長時間実行されるトランザクションがあり、それらのトランザクションで使用できるデータをクリーンアップできないことです。 ここでの推奨事項も明らかです。「ダングリング」トランザクションを削除し、アクティブなトランザクションの時間を最小限に抑えます。 ただし、アプリケーションの負荷が OLAP と OLTP のハイブリッドである場合は、レポートの作成などの長期的な操作だけでなく、頻繁な更新や短いクエリも同時に実行される可能性があります。 このような状況では、負荷を異なる拠点に分散することを検討する価値があります。そうすることで、それぞれの拠点をより微調整できるようになります。

別の例として、プロファイルが均一であっても、データベースに非常に高い負荷がかかっている場合、最も積極的な AUTOVACUUM でも対応できず、肥大化が発生する可能性があります。 スケーリング (垂直または水平) が唯一の解決策です。

AUTOVACUUM を設定したにもかかわらず、肥大化が続く状況で何をすべきか。

チーム バキュームフル テーブルとインデックスの内容を再構築し、関連するデータのみをそれらに残します。 肥大化を解消するために、この機能は完全に機能しますが、実行中にテーブルに対する排他ロックが取得され (AccessExclusiveLock)、このテーブルに対するクエリの実行は許可されず、選択さえできなくなります。 サービスまたはその一部をしばらく停止してもよい場合 (データベースとハードウェアのサイズに応じて数十分から数時間)、このオプションが最適です。 残念ながら、定期メンテナンス中は VACUUM FULL を実行する時間がないため、この方法は適していません。

チーム CLUSTER VACUUM FULL と同じ方法でテーブルの内容を再構築しますが、ディスク上でのデータの物理的な順序に基づいてインデックスを指定できます (ただし、将来的には新しい行の順序は保証されません)。 特定の状況では、これはインデックスによる複数のレコードの読み取りなど、多数のクエリにとって優れた最適化になります。 このコマンドの欠点は VACUUM FULL の欠点と同じで、操作中にテーブルがロックされます。

チーム 再索引 前の 12 つと似ていますが、テーブルの特定のインデックスまたはすべてのインデックスを再構築します。 ロックは少し弱くなります。テーブルに対する ShareLock (変更は禁止されますが、選択は許可されます)、および再構築されるインデックスに対する AccessExclusiveLock (このインデックスを使用するクエリをブロックします)。 ただし、Postgres の XNUMX バージョンではパラメータが登場しました。 同時にこれにより、レコードの同時追加、変更、削除をブロックすることなくインデックスを再構築できます。

Postgres の以前のバージョンでは、次を使用して REINDEX CONCURRENTLY と同様の結果を達成できます。 インデックスを同時に作成する。 これにより、厳密なロック (並列クエリを妨げない ShareUpdateExclusiveLock) を使用せずにインデックスを作成し、古いインデックスを新しいインデックスに置き換えて、古いインデックスを削除できます。 これにより、アプリケーションを妨げることなく、インデックスの肥大化を解消できます。 インデックスを再構築するときは、ディスク サブシステムに追加の負荷がかかることを考慮することが重要です。

したがって、インデックスについては肥大化を「その場で」解消する方法があるとしても、テーブルについては存在しません。 ここで、さまざまな外部拡張機能が活躍します。 pg_repack (以前の pg_reorg)、 pgコンパクト, pgコンパクトテーブル その他。 この記事では、それらを比較することはせず、いくつかの変更を加えて私たち自身が使用している pg_repack についてのみ説明します。

pg_repack の仕組み

インデックスや制限があり、残念ながら肥大化してしまった、まったく普通のテーブルがあるとします。 pg_repack の最初のステップは、実行中のすべての変更に関するデータを保存するログ テーブルを作成することです。 トリガーは、挿入、更新、削除のたびにこれらの変更を複製します。 次に、構造的には元のテーブルと似たテーブルが作成されますが、データの挿入プロセスが遅くならないように、インデックスや制限はありません。

次に、pg_repack は古いテーブルから新しいテーブルにデータを転送し、無関係な行をすべて自動的に除外してから、新しいテーブルのインデックスを作成します。 これらすべての操作の実行中に、変更がログ テーブルに蓄積されます。

次のステップでは、変更を新しいテーブルに転送します。 移行は数回繰り返して実行され、ログ テーブルに残っているエントリが 20 未満になると、pg_repack は強力なロックを取得し、最新のデータを移行し、Postgres システム テーブル内の古いテーブルを新しいテーブルに置き換えます。 これは、テーブルで作業できない唯一かつ非常に短い時間です。 この後、古いテーブルとログのあるテーブルが削除され、ファイル システム内のスペースが解放されます。 プロセスは完了です。

理論的にはすべてが素晴らしく見えますが、実際にはどうなるでしょうか? pg_repack を負荷なしと負荷下でテストし、途中で停止した場合 (つまり、Ctrl+C を使用した場合) の動作を確認しました。 すべての検査結果は陽性でした。

私たちは食料品店に行きましたが、すべてが期待通りにはいきませんでした。

初のパンケーキ発売

最初のクラスターで、一意制約の違反に関するエラーを受け取りました。

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

この制限には自動生成された名前、index_16508 が付けられました。これは pg_repack によって作成されました。 その構成に含まれる属性に基づいて、それに対応する「私たちの」制約を決定しました。 問題は、これが完全に通常の制限ではなく、延期された制限であることが判明しました (遅延制約)、つまりその検証は SQL コマンドよりも後で実行されるため、予期しない結果が生じます。

遅延制約: 遅延制約が必要な理由とその仕組み

延期された制限に関するちょっとした理論。
簡単な例を考えてみましょう。ディレクトリ内の車の名前と順序という XNUMX つの属性を持つ車のテーブル リファレンス ブックがあります。

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

XNUMX 台目と XNUMX 台目の車を交換する必要があるとします。 簡単な解決策は、最初の値を XNUMX 番目の値に更新し、XNUMX 番目の値を最初の値に更新することです。

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

しかし、このコードを実行すると、テーブル内の値の順序が一意であるため、制約違反が予想されます。

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

どうすれば別の方法で実行できますか? オプション 1: テーブルに存在しないことが保証されている注文に、「-XNUMX」などの追加の値置換を追加します。 プログラミングでは、これを「XNUMX つの変数の値を XNUMX 番目の変数を介して交換する」と呼びます。 この方法の唯一の欠点は、追加の更新が必要なことです。

オプション 1: 注文値に整数ではなく浮動小数点データ型を使用するようにテーブルを再設計します。 次に、値を 2.5 から XNUMX に更新すると、最初のエントリは自動的に XNUMX 番目と XNUMX 番目のエントリの間に「立ちます」。 この解決策は機能しますが、XNUMX つの制限があります。 まず、値がインターフェイスのどこかで使用されている場合は機能しません。 次に、データ型の精度に応じて、すべてのレコードの値を再計算する前に可能な挿入の数が制限されます。

オプション XNUMX: 制約を延期して、コミット時にのみチェックされるようにします。

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

最初のリクエストのロジックにより、コミット時にすべての値が一意であることが保証されるため、リクエストは成功します。

もちろん、上で説明した例は非常に合成的なものですが、アイデアを明らかにしています。 私たちのアプリケーションでは、遅延制約を使用して、ユーザーがボード上の共有ウィジェット オブジェクトを同時に操作するときに競合を解決するロジックを実装します。 このような制限を使用すると、アプリケーション コードを少し単純にすることができます。

一般に、制約のタイプに応じて、Postgres には制約をチェックするための XNUMX つの粒度レベル (行レベル、トランザクション レベル、式レベル) があります。

出所： 嘆く

CHECK と NOT NULL は常に行レベルでチェックされます。表からわかるように、その他の制限については、さまざまなオプションがあります。 もっと読むことができます ここで.

簡単に要約すると、多くの状況で遅延制約を使用すると、コードが読みやすくなり、コマンドが減ります。 ただし、エラーが発生した瞬間とそれを発見した瞬間が時間的に分離されているため、デバッグ プロセスが複雑になるという代償を払わなければなりません。 もう XNUMX つの考えられる問題は、リクエストに遅延制約が含まれる場合、スケジューラが常に最適な計画を構築できるとは限らないことです。

pg_repackの改善

遅延制約とは何かについて説明しましたが、それらは私たちの問題とどのように関係するのでしょうか? 先ほど受け取ったエラーを思い出してください。

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

これは、データがログ テーブルから新しいテーブルにコピーされるときに発生します。 これは奇妙に見えるので... ログ テーブルのデータは、ソース テーブルのデータとともにコミットされます。 元のテーブルの制約を満たしている場合、新しいテーブルで同じ制約に違反する可能性はありますか?

結局のところ、問題の根本は pg_repack の前のステップにあり、インデックスのみを作成しますが、制約は作成しません。古いテーブルには一意の制約があり、新しいテーブルは代わりに一意のインデックスを作成しました。

ここで、制約が通常で遅延されていない場合、代わりに作成される一意のインデックスはこの制約と同等であることに注意することが重要です。 Postgres の一意の制約は、一意のインデックスを作成することで実装されます。 ただし、遅延制約の場合、インデックスは遅延できず、SQL コマンドの実行時に常にチェックされるため、動作は同じではありません。

したがって、問題の本質はチェックの「遅延」にあります。元のテーブルではコミット時に発生し、新しいテーブルでは SQL コマンドの実行時に発生します。 これは、チェックが両方のケースで同じように実行されること、つまり常に遅延されるか、常に即時にチェックが実行されることを確認する必要があることを意味します。

それで、私たちはどんなアイデアを持っていたのでしょうか？

deferred と同様のインデックスを作成します

最初のアイデアは、両方のチェックを即時モードで実行することです。 これにより、いくつかの誤検出制限が生成される可能性がありますが、そのような制限がほとんどない場合、そのような競合はユーザーにとって通常の状況であるため、ユーザーの作業には影響しません。 たとえば、XNUMX 人のユーザーが同時に同じウィジェットの編集を開始し、XNUMX 番目のユーザーのクライアントが、そのウィジェットが最初のユーザーによって編集のためにすでにブロックされているという情報を受け取る時間がない場合に発生します。 このような状況では、サーバーは XNUMX 番目のユーザーを拒否し、クライアントは変更をロールバックしてウィジェットをブロックします。 少し後、最初のユーザーが編集を完了すると、XNUMX 番目のユーザーはウィジェットがブロックされなくなったという情報を受け取り、アクションを繰り返すことができるようになります。

チェックが常に非遅延モードであることを保証するために、元の遅延制約と同様の新しいインデックスを作成しました。

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

テスト環境では、予想されるエラーがわずか数件だけ発生しました。 成功！ 本番環境で pg_repack を再度実行したところ、5 時間の作業で最初のクラスターで XNUMX つのエラーが発生しました。 これは許容できる結果です。 ただし、すでに XNUMX 番目のクラスターではエラーの数が大幅に増加しており、pg_repack を停止する必要がありました。

なぜそうなったのでしょうか? エラーが発生する可能性は、同時に同じウィジェットを使用しているユーザーの数によって異なります。 どうやら、その時点では、最初のクラスターに保存されているデータの競合変更は、他のクラスターに比べてはるかに少なかったようです。 私たちはただ「幸運だった」だけです。

その考えはうまくいきませんでした。 その時点で、他の XNUMX つの解決策が見つかりました。それは、アプリケーション コードを書き直して遅延制約をなくすか、pg_repack に遅延制約を処理するように「教える」ことです。 私たちはXNUMX番目のものを選びました。

新しいテーブルのインデックスを元のテーブルの遅延制約に置き換えます。

改訂の目的は明らかです。元のテーブルに遅延制約がある場合、新しいテーブルにはインデックスではなくそのような制約を作成する必要があります。

変更をテストするために、簡単なテストを作成しました。

• 遅延制約と XNUMX つのレコードを持つテーブル。
• 既存のレコードと競合するデータをループに挿入します。
• 更新を実行します。データは競合しなくなりました。
• 変更をコミットします。

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

pg_repack の元のバージョンは最初の挿入時に常にクラッシュしましたが、変更されたバージョンはエラーなしで動作しました。 素晴らしい。

運用環境に移行すると、ログ テーブルから新しいテーブルにデータをコピーする同じフェーズで再びエラーが発生します。

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

典型的な状況: テスト環境ではすべてが動作するのに、本番環境では動作しない?!

APPLY_COUNT と XNUMX つのバッチの結合点

コードを文字通り XNUMX 行ずつ分析し始め、重要な点を発見しました。データはログ テーブルから新しいテーブルにバッチで転送され、APPLY_COUNT 定数はバッチのサイズを示していました。

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

問題は、元のトランザクションのデータ (複数の操作が制約に違反する可能性がある) が転送されると、最終的に XNUMX つのバッチの結合点に到達する可能性があることです。コマンドの半分は最初のバッチでコミットされ、残りの半分はコミットされます。 XNUMX番目に。 ここで、運次第ですが、チームが最初のバッチで何も違反しなければ、すべて問題ありませんが、違反した場合は、エラーが発生します。

APPLY_COUNT は 1000 レコードに等しいため、テストが成功した理由が説明されています。テストでは「バッチ ジャンクション」のケースがカバーされていませんでした。 挿入と更新という 500 つのコマンドを使用したため、XNUMX つのコマンドの正確に XNUMX トランザクションが常にバッチに配置され、問題は発生しませんでした。 XNUMX 番目の更新を追加した後、編集が機能しなくなりました。

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

したがって、次のタスクは、XNUMX つのトランザクションで変更された元のテーブルのデータが、XNUMX つのトランザクション内で新しいテーブルに配置されることを確認することです。

バッチ処理の拒否

そして、やはり解決策は XNUMX つありました。 まず、バッチへの分割を完全に放棄し、XNUMX つのトランザクションでデータを転送しましょう。 このソリューションの利点はそのシンプルさです。必要なコードの変更は最小限で済みました (ちなみに、古いバージョンでは、pg_reorg はまったく同じように動作しました)。 しかし、問題があります。私たちは長期にわたるトランザクションを作成しています。これは、前述したように、新たな肥大化の出現に対する脅威です。

XNUMX 番目の解決策はより複雑ですが、おそらくより正確です。テーブルにデータを追加したトランザクションの識別子を使用してログ テーブルに列を作成します。 その後、データをコピーするときに、この属性によってデータをグループ化し、関連する変更が一緒に転送されるようにすることができます。 バッチは複数のトランザクション (または XNUMX つの大きなトランザクション) から形成され、そのサイズはこれらのトランザクションで変更されたデータの量によって異なります。 異なるトランザクションからのデータはランダムな順序でログ テーブルに入力されるため、以前のように順番に読み取ることはできなくなることに注意することが重要です。 tx_id によるフィルタリングを使用した各リクエストの seqscan はコストがかかりすぎるため、インデックスが必要ですが、更新のオーバーヘッドによりメソッドの速度も低下します。 一般に、いつものように、何かを犠牲にする必要があります。

そこで、より簡単な最初のオプションから始めることにしました。 まず、長時間のトランザクションが実際に問題となるかどうかを理解する必要がありました。 古いテーブルから新しいテーブルへの主なデータ転送も 1 つの長いトランザクションで発生するため、質問は「このトランザクションをどれくらい増やすか?」というものに変わりました。 最初のトランザクションの継続時間は、主にテーブルのサイズによって決まります。 新しいものの持続時間は、データ転送中にテーブルに蓄積される変更の数によって異なります。 負荷の強さについて。 pg_repack の実行はサービス負荷が最小限のときに行われ、変更の量はテーブルの元のサイズに比べて不釣り合いに小さかったです。 新しいトランザクションの時間は無視できると判断しました (比較のために、平均して 2 時間 3 ～ XNUMX 分です)。

実験結果は肯定的でした。 本番環境でも起動します。 わかりやすくするために、実行後のデータベースの XNUMX つのサイズを示す図を次に示します。

私たちはこの解決策に完全に満足していたので、XNUMX 番目の解決策を実装しようとはしませんでしたが、拡張機能の開発者と話し合う可能性を検討しています。 残念ながら、現在のリビジョンはまだ公開の準備ができていません。これは、独自の遅延制限に関する問題を解決しただけであり、本格的なパッチの場合は他のタイプのサポートを提供する必要があるためです。 将来的にはこれができるようになることを期待しています。

おそらく、なぜ私たちは pg_repack の修正というこの話に関与し、たとえばその類似物を使用しなかったのかと疑問に思われるかもしれません。 私たちもある時点でこれについて考えましたが、遅延制約のないテーブルで以前にこれを使用したという前向きな経験が、問題の本質を理解して修正しようとする動機になりました。 さらに、他のソリューションを使用する場合もテストの実施に時間がかかるため、まずそのソリューションの問題を解決することに努め、妥当な時間内に解決できないことがわかったら、類似のソリューションを検討し始めることにしました。 。

所見

私たち自身の経験に基づいて推奨できること:

1. お腹の張りを観察してください。 監視データに基づいて、自動バキュームがどの程度適切に構成されているかを理解できます。
2. AUTOVACUUM を調整して膨張を許容レベルに保ちます。
3. 肥大化が依然として進行しており、すぐに使用できるツールを使用してそれを克服できない場合は、外部拡張機能を使用することを恐れないでください。 重要なのは、すべてを適切にテストすることです。
4. ニーズに合わせて外部ソリューションを変更することを恐れないでください。場合によっては、独自のコードを変更するよりも効果的で、さらに簡単な場合もあります。

出所： habr.com