ProHoster > блог > Администрација > Кога VACUUM не успее, рачно ја чистиме масата

Кога VACUUM не успее, рачно ја чистиме масата

ВАКУУМ може да „исчисти“ од табела во PostgreSQL само што никој не може да види - односно, нема ниту едно активно барање кое започнало пред да се сменат овие записи.

Но, што ако таков непријатен тип (долгорочно оптоварување OLAP на OLTP база на податоци) сè уште постои? Како чиста маса за активно менување опкружен со долги прашања и не гази на гребло?

Кога VACUUM не успее, рачно ја чистиме масата

Поставување на гребло

Прво, да утврдиме кој е проблемот што сакаме да го решиме и како може да се појави.

Обично оваа ситуација се случува на релативно мала маса, но во која се јавува многу промени. Обично ова или различно метри/агрегати/оценки, на кој често се врши АЖУРИРАЊЕ, или тампон-редица за обработка на некои постојано тековни текови на настани, чии записи постојано се Вметнуваат/БРИШИ.

Ајде да се обидеме да ја репродуцираме опцијата со оценки:

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 ms до 3.808 ms? И нашиот рејтинг се гради сè побавно.

Се е виновен 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 секунди од последниот [авто]ВАКУУМ.
  • големината на физичката маса е поголема од целта
    Ајде да го дефинираме како двојно поголем број на страници (8KB блокови) во однос на минималната големина - 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 FULL, тој не се меша во паралелните процеси кои работат со податоци за читање-запишување.

Во исто време, може веднаш да го исчисти најголемиот дел од она што би сакале да го отстраниме. Да, и следните прашања на оваа табела ќе одат кај нас од „жешка кеш“, што ќе го намали нивното времетраење - и, според тоа, вкупното време на блокирање други од нашата трансакција за сервисирање.

#2: Дали некој е дома?

Ајде да провериме дали воопшто има нешто во табелата:

TABLE tbl LIMIT 1;

Ако не остана ниту еден запис, тогаш можеме да заштедиме многу при обработката со едноставно правење ТРАНКУТИРАЈ:

Дејствува исто како безусловна команда DELETE за секоја табела, но е многу побрза бидејќи всушност не ги скенира табелите. Покрај тоа, веднаш ослободува простор на дискот, така што нема потреба да се врши операција ВАКУУМ потоа.

Дали треба да го ресетирате бројачот на секвенца на табелата (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;

Потпис НА ЗАВРШЕНИ ОПАЃАЊЕ значи дека во моментот кога трансакцијата ќе заврши, привремената табела ќе престане да постои и нема потреба рачно да се брише во контекст на конекцијата.

Бидејќи претпоставуваме дека нема многу „живи“ податоци, оваа операција треба да се одвива доста брзо.

Па, тоа е сè! Не заборавајте по завршувањето на трансакцијата работи ANALYZE за нормализирање на статистиката на табелата доколку е потребно.

Составување на последното сценарио

Го користиме овој „псевдо-пајтон“:

# собираем статистику с таблицы
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;

Дали е можно да не се копираат податоците по втор пат?Во принцип, можно е ако идот на самата табела не е врзан за други активности од страната BL или FK од страната DB:

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 пати и сите Ажурирања повторно работат брзо.

Извор: www.habr.com

Додадете коментар