ProHoster > Блог > адміністрування > Коли пасує VACUUM – чистимо таблицю вручну

Коли пасує VACUUM – чистимо таблицю вручну

ВАКУУМ може «зачистити» з таблиці в PostgreSQL лише те, що ніхто не може побачити — тобто немає жодного активного запиту, який стартував раніше, ніж ці записи було змінено.

А якщо такий неприємний тип (тривале OLAP-навантаження на OLTP-базі) все ж таки є? Як почистити таблицю, що активно змінюється в оточенні довгих запитів і не наступити на граблі?

Коли пасує VACUUM – чистимо таблицю вручну

Розкладаємо граблі

Спочатку визначимо, у чому полягає і як взагалі може виникнути проблема, яку ми хочемо вирішити.

Зазвичай така ситуація трапляється на відносно невеликій таблиці, але в якій відбувається дуже багато змін. Зазвичай це чи різні лічильники/агрегати/рейтинги, на яких часто-густо виконується UPDATE, або буфер-черга для обробки якогось потоку подій, що постійно йде, записи про які весь час INSERT/DELETE.

Спробуємо відтворити варіант із рейтингами:

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

Що сталося? Чому навіть для найпростішого UPDATE єдиного запису час виконання деградував у 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 на heap + 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

Ми не можемо знати заздалегідь, чи нам заважає паралельний запит — скільки саме записів «застаріло» з моменту його початку. Тому, коли таки вирішимо таблицю якось обробити, по-любому спочатку варто виконати на ній ВАКУУМ — він, на відміну 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: Конфлікт інтересів

Ми тут приходимо та хочемо табличку «залочити» — а якщо на ній у цей момент хтось був активний, наприклад, читав із неї? Ми «повиснемо» в очікуванні звільнення цього блокування, а інші охочі почитати впруться вже в нас.

Щоб такого не сталося, «пожертвуємо собою» — якщо вже за певний (припустимо короткий) час блокування нам отримати все-таки не вдалося, то ми отримаємо від бази exception, але хоча б не завадимо іншим.

Для цього виставимо змінну сесії lock_timeout (для версій 9.3+) або/і оператор_тайм-аут. Головне пам'ятати, що значення statement_timeout застосовується лише з наступного statement. Тобто ось так у склейці. не запрацює: 

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

Щоб не займатися згодом відновленням «старого» значення змінної, використовуємо форму SET LOCAL, яка обмежує область дії налаштування поточною транзакцією.

Пам'ятаємо, що statement_timeout поширюється на всі наступні запити, щоб транзакція не могла у нас розтягтися до неприйнятних величин, якщо даних у таблиці виявиться багато.

#5: Копіюємо дані

Якщо таблиця виявилася не зовсім порожньою – дані доведеться перезберігати через допоміжну тимчасову табличку:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Сигнатура ON COMMIT DROP означає, що в момент закінчення транзакції тимчасова таблиця перестане існувати і займатися її ручним видаленням у контексті з'єднання не потрібно.

Оскільки ми припускаємо, що «живих» даних не дуже багато, то ця операція має пройти досить швидко.

Ну от як би і все! Не забувайте після завершення транзакції запустити 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;

А чи можна не копіювати дані вдруге?В принципі, можна, якщо на oid самої таблиці не зав'язані інші активності з боку БЛ або 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

Додати коментар або відгук