ProHoster > Блог > администрация > Когато VACUUM не успее, ние почистваме масата ръчно

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

ВАКУУМ може да „почисти“ от таблица в PostgreSQL само какво никой не може да види - тоест няма нито една активна заявка, която да е започнала преди тези записи да бъдат променени.

Но какво ще стане, ако такъв неприятен тип (дългосрочно OLAP натоварване на OLTP база данни) все още съществува? как чиста маса за активно повиване заобиколен от дълги запитвания и не стъпвате на рейк?

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

Разгъване на рейка

Първо, нека определим какъв е проблемът, който искаме да разрешим и как може да възникне.

Обикновено се случва тази ситуация на сравнително малка маса, но в който се среща много промени. Обикновено това или различно метри/агрегати/оценки, на който често се изпълнява 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 секунди от последния [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 след това.

Дали трябва да нулирате брояча на последователностите на таблицата (РЕСТАРТ ИДЕНТИЧНОСТ) зависи от вас да решите.

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

Подпис ON COMMIT DROP означава, че в края на транзакцията временната таблица ще престане да съществува и няма нужда да я изтривате ръчно в контекста на връзката.

Тъй като предполагаме, че няма много „живи“ данни, тази операция трябва да се извърши доста бързо.

Е, това е всичко! Не забравяйте след приключване на транзакцията стартирайте АНАЛИЗ за нормализиране на статистическите данни на таблицата, ако е необходимо.

Съставяне на окончателния сценарий

Използваме този „псевдо-питон“:

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

Добавяне на нов коментар