VACUUM が失敗した場合、テーブルを手動でクリーンアップします

VACUUM PostgreSQL のテーブルから「クリーンアップ」できるのは、 誰も見ることができません - つまり、これらのレコードが変更される前に開始されたアクティブなリクエストは XNUMX つもありません。

しかし、このような不快なタイプ (OLTP データベースに対する長期的な OLAP 負荷) がまだ存在している場合はどうなるでしょうか? どうやって 積極的におむつ交換台をきれいにする 長いクエリに囲まれて熊手を踏んでいませんか?

熊手を展開する

まず、解決したい問題が何であり、それがどのように発生するかを判断しましょう。

通常このような状況が起こります 比較的小さなテーブルの上に、しかしそれが起こる場所 たくさんの変化。 通常はこれか違う メートル/総計/定格UPDATE が頻繁に実行される、または バッファキュー 常に進行中のイベントのストリームを処理し、そのレコードは常に INSERT/DELETE されます。

評価を付けてオプションを再現してみましょう。

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- по этому индексу будем строить рейтинг

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

そして並行して、別の接続で長い長いリクエストが開始され、複雑な統計が収集されますが、 私たちのテーブルには影響しない:

SELECT pg_sleep(10000);

ここで、カウンターの XNUMX つの値を何度も更新します。 実験を純粋にするために、これをやってみましょう dblink を使用した別のトランザクションで実際にはどうなるか：

DO $$
DECLARE
  i integer;
  tsb timestamp;
  tse timestamp;
  d double precision;
BEGIN
  PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
  FOR i IN 1..10000 LOOP
    tsb = clock_timestamp();
    PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$);
    tse = clock_timestamp();
    IF i % 1000 = 0 THEN
      d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5);
    END IF;
  END LOOP;
  PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

NOTICE:  i =  1000, exectime = 0.524
NOTICE:  i =  2000, exectime = 0.739
NOTICE:  i =  3000, exectime = 1.188
NOTICE:  i =  4000, exectime = 2.508
NOTICE:  i =  5000, exectime = 1.791
NOTICE:  i =  6000, exectime = 2.658
NOTICE:  i =  7000, exectime = 2.318
NOTICE:  i =  8000, exectime = 2.572
NOTICE:  i =  9000, exectime = 2.929
NOTICE:  i = 10000, exectime = 3.808

どうしたの？ 単一レコードの最も単純な UPDATE であってもなぜ 実行時間が7倍に低下 — 0.524ミリ秒から3.808ミリ秒へ？ そして、私たちの評価はますますゆっくりと高まっています。

それはすべてMVCCのせいです。

それはすべてについてです MVCCの仕組みこれにより、クエリはエントリの以前のバージョンをすべて調べます。 それでは、テーブルから「死んだ」バージョンを削除しましょう。

VACUUM VERBOSE tbl;

INFO:  vacuuming "public.tbl"
INFO:  "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages
DETAIL:  10000 dead row versions cannot be removed yet, oldest xmin: 597439602

ああ、掃除するものが何もない！ 平行 実行中のリクエストが邪魔をしています - 結局のところ、彼はいつかこれらのバージョンを使用したいと思うかもしれません (もしそうなったら?)。それらは彼にとって利用可能であるはずです。 したがって、VACUUM FULL でも役に立ちません。

テーブルを「折りたたむ」

しかし、そのクエリにはテーブルが必要ないことは確かです。 したがって、VACUUM が屈するため、少なくとも「手動で」テーブルから不要なものをすべて削除することで、システムのパフォーマンスを適切な限界に戻そうと試みます。

より明確にするために、バッファー テーブルの例を見てみましょう。 つまり、INSERT/DELETE の大量のフローが発生し、テーブルが完全に空になる場合があります。 しかし、それが空でない場合は、 現在の内容を保存する.

#0: 状況の評価

各操作の後でもテーブルに対して何かを実行できることは明らかですが、これにはあまり意味がありません。メンテナンスのオーバーヘッドがターゲット クエリのスループットよりも明らかに大きくなります。

基準を明確にしてみましょう。次の場合は「行動を起こす時です」。

• VACUUMはかなり前に発売されました
  重い負荷がかかることが予想されるので、そのままにしておきます 60秒 前回の[自動]VACUUM以降。
• 物理テーブルのサイズがターゲットより大きい
  最小サイズに対するページ数 (8KB ブロック) の XNUMX 倍として定義しましょう - ヒープに 1 ブロック + 各インデックスに 1 ブロック - 空の可能性があるテーブルの場合。 一定量のデータが常に「通常」バッファーに残ると予想される場合は、この式を調整するのが合理的です。

検証リクエスト

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

#1: 真空のまま

並列クエリが重大な妨げになっているかどうか、つまり並列クエリの開始以来、正確に何件のレコードが「古くなった」のかを事前に知ることはできません。 したがって、何らかの方法でテーブルを処理することにした場合は、いずれの場合でも、最初にテーブルに対して実行する必要があります。 VACUUM - VACUUM FULL とは異なり、読み取り/書き込みデータを処理する並列プロセスを妨げません。

同時に、削除したいもののほとんどをすぐに削除できます。 はい。このテーブルに対する後続のクエリは私たちに送られます。 「ホットキャッシュ」によるこれにより、その期間が短縮され、サービス トランザクションによって他のユーザーをブロックする合計時間が短縮されます。

#2: 誰か家にいますか?

テーブルに何かがあるかどうかを確認してみましょう。

TABLE tbl LIMIT 1;

レコードが XNUMX つも残っていない場合は、次のようにするだけで処理を大幅に節約できます。 切り捨てる:

これは各テーブルに対する無条件の DELETE コマンドと同じように動作しますが、実際にテーブルをスキャンしないため、はるかに高速です。 さらに、すぐにディスク領域が解放されるため、後で VACUUM 操作を実行する必要はありません。

テーブル シーケンス カウンター (RESTART IDENTITY) をリセットする必要があるかどうかは、ユーザーが決定します。

#3: 全員で交代してください!

私たちは非常に競争の激しい環境で働いているため、テーブルにエントリがないことを確認している間に、誰かがすでにそこに何かを書き込んでいる可能性があります。 この情報を失うべきではありません。それではどうでしょうか? そうです、誰もそれを確実に書き留めることができないようにする必要があります。

これを行うには、有効にする必要があります シリアル化可能-トランザクションを分離し（はい、ここでトランザクションを開始します）、テーブルを「しっかりと」ロックします。

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

このブロックのレベルは、ブロックに対して実行する操作によって決まります。

#4: 利益相反

私たちはここに来て、標識を「ロック」したいと考えています。たとえば、その瞬間に誰かが標識を読んでいる場合はどうなるでしょうか? 私たちはこのブロックが解放されるのを「ハングアップ」して待っていると、読みたい他の人が私たちに遭遇するでしょう...

これが起こらないようにするために、私たちは「自分自身を犠牲にする」ことになります。特定の（許容できる短い）時間内にロックを取得できなかった場合、ベースから例外を受け取りますが、少なくともあまり干渉しません。その他。

これを行うには、セッション変数を設定します。 ロックタイムアウト (バージョン 9.3 以降の場合) または/および ステートメントタイムアウト。 覚えておくべき主な点は、statement_timeout 値は次のステートメントからのみ適用されるということです。 つまり、接着するとこのようになります - 機能しません:

SET statement_timeout = ...;LOCK TABLE ...;

後で変数の「古い」値を復元する必要がないように、次の形式を使用します。 ローカルに設定、設定の範囲を現在のトランザクションに制限します。

テーブルに大量のデータがある場合にトランザクションが許容できない値にまで拡張できないように、statement_timeout は後続のすべてのリクエストに適用されることを思い出してください。

#5: データをコピーする

テーブルが完全に空でない場合は、補助一時テーブルを使用してデータを再保存する必要があります。

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

サイン コミットドロップ時 これは、トランザクションが終了した時点で一時テーブルが存在しなくなるため、接続コンテキストで手動でテーブルを削除する必要がないことを意味します。

「ライブ」データはそれほど多くないと想定しているため、この操作は非常に迅速に実行されるはずです。

さて、それだけです！ 取引完了後も忘れずに 分析を実行します 必要に応じてテーブル統計を正規化します。

最終的なスクリプトをまとめる

この「疑似 Python」を使用します。

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

データを再度コピーしないようにすることはできますか?原則として、テーブル自体の oid が BL 側の他のアクティビティや DB 側の FK に関連付けられていない場合は可能です。

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

ソーステーブルでスクリプトを実行し、メトリクスを確認してみましょう。

VACUUM tbl;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
  LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
  CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
  TRUNCATE TABLE tbl;
  INSERT INTO tbl TABLE _tmp_swap;
COMMIT;

relpages | size_norm | size   | vaclag
-------------------------------------------
       0 |     24576 |  49152 | 32.705771

すべてうまくいきました！ テーブルは 50 分の XNUMX に縮小され、すべての UPDATE が再び高速に実行されるようになりました。

出所： habr.com