🥇当 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 再次快速运行。

来源： habr.com

当 VACUUM 失败时，我们手动清理表