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.524ms에서 3.808ms로? 그리고 우리의 평가는 점점 더 느리게 구축되고 있습니다.

모두 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 블록)의 두 배로 정의해 보겠습니다. 힙에 대해 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: 이해 상충

우리는 여기에 와서 표지판을 "잠그고" 싶습니다. 예를 들어 그 순간 누군가가 표지판을 읽고 있었다면 어떨까요? 우리는 이 블록이 해제될 때까지 기다리며 “중지”할 것이며, 읽고 싶어하는 다른 사람들이 우리를 만날 것입니다...

이런 일이 발생하는 것을 방지하기 위해 우리는 "우리 자신을 희생"할 것입니다. 특정(허용 가능한 짧은) 시간 내에 잠금을 얻을 수 없는 경우 베이스에서 예외를 받게 되지만 최소한 너무 많이 간섭하지는 않습니다. 다른 사람.

이렇게 하려면 세션 변수를 설정하십시오. lock_timeout (버전 9.3+의 경우) 또는/및 문_시간 초과. 기억해야 할 가장 중요한 점은statement_timeout 값은 다음 명령문에서만 적용된다는 것입니다. 즉, 접착에서 이렇게- 작동하지 않습니다:

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

나중에 변수의 "이전" 값을 복원하는 작업을 처리할 필요가 없도록 다음 형식을 사용합니다. 로컬로 설정, 이는 설정 범위를 현재 트랜잭션으로 제한합니다.

테이블에 많은 데이터가 있는 경우 트랜잭션이 허용할 수 없는 값으로 확장되지 않도록 모든 후속 요청에 statement_timeout이 적용된다는 점을 기억합니다.

#5: 데이터 복사

테이블이 완전히 비어 있지 않으면 보조 임시 테이블을 사용하여 데이터를 다시 저장해야 합니다.

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

서명 커밋 삭제 시 이는 트랜잭션이 종료되는 순간 임시 테이블이 더 이상 존재하지 않으며 연결 컨텍스트에서 수동으로 삭제할 필요가 없음을 의미합니다.

"라이브" 데이터가 많지 않다고 가정하므로 이 작업은 매우 빠르게 수행되어야 합니다.

글쎄, 그게 다야! 거래 완료 후 잊지 마세요 분석 실행 필요한 경우 테이블 통계를 정규화합니다.

최종 스크립트 작성

우리는 이 "의사 파이썬"을 사용합니다:

# собираем статистику с таблицы
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배로 줄어들었고 모든 업데이트가 다시 빠르게 실행되고 있습니다.

출처 : habr.com