🥇當 VACUUM 失敗時，我們手動清理表

真空只能從 PostgreSQL 的表中「清理」什麼 沒有人能看到 - 也就是說，在這些記錄變更之前沒有啟動任何活動請求。

但是，如果這種令人不快的類型（OLTP 資料庫上的長期 OLAP 負載）仍然存在怎麼辦？如何 清潔主動換尿布台 被長長的疑問包圍而不踩耙子？

放置耙子

首先，讓我們確定我們想要解決的問題是什麼以及它是如何出現的。

通常會出現這種情況在一張相對較小的桌子上，但它發生在其中 很多變化。通常是這個或不同的米/總量/評級，經常執行 UPDATE，或者緩衝佇列處理一些持續不斷的事件流，其記錄不斷插入/刪除。

讓我們嘗試重現帶有評級的選項：

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);

現在我們多次更新其中一個計數器的值。為了實驗的純潔性，我們這樣做使用 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

發生了什麼事？為什麼即使是最簡單的單一記錄更新 執行時間降低了 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秒 自上次 [auto]VACUUM 以來。
物理表大小大於目標
讓我們將其定義為相對於最小大小的頁面數（8KB 區塊）的兩倍 - 堆 1 個 blk + 每個索引 1 個 blk - 對於可能為空的表。如果我們預期一定數量的資料將始終「正常」保留在緩衝區中，則調整此公式是合理的。

驗證請求

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 FULL 不同，它不會幹擾處理讀寫資料的平行程序。

同時，它可以立即清除大部分我們想要清除的內容。是的，此表的後續查詢將由我們處理 透過“熱緩存”，這將減少它們的持續時間，從而減少我們的服務交易阻止其他人的總時間。

#2：有人在家嗎？

讓我們檢查一下表中是否有任何內容：

TABLE tbl LIMIT 1;

如果沒有留下任何記錄，那麼我們可以透過簡單地執行以下操作來節省大量處理時間截短:

它的作用與每個表的無條件 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 倍，所有 UPDATE 再次快速運行。

來源： www.habr.com

當 VACUUM 失敗時，我們手動清理表