Երբ VACUUM-ը անջատվում է, մաքրում ենք աղյուսակը ձեռքով

ուղղությամբ և կվերացվեն: Մեծահնար է «մաքրել» PostgreSQL-ի աղյուսակից միայն այն, որ եթե ոչ ոք չի կարող տեսնել ՝ դա նշանակում է, որ չկա ոչ մի ակտիվ հարցում, որը սկսվել է ավելի վաղ, քան այս գրառումները փոփոխվել են:

Բայց ինչու, եթե նման անհարմար մարդ (երկարատև OLAP բեռ և OLTP բազայում) իսկապես կա? Ինչպես մաքրել ակտիվորեն փոխվող աղյուսակը երկարաշար հարցումների միջավայրում և չհանդիպել բախումների:

Եկեք մաքրենք բախումները

Նախ սահմանենք, որը բաղադրիչների և ինչպես կարող է առաջանալ խնդիրը, որը ցանկանում ենք լուծել:

Նման իրավիճակը սովորաբար տեղի է ունենում համեմատաբար փոքր աղյուսակում, սակայն որտեղ տեղի է ունենում շատ փոփոխություններ. Հusually, դա կամ տարբեր հաշվիչներ/հավաքագրեր/ռեյթինգներ, որոնց վրա հաճախ-հաճախ կատարվում է 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;

Եթե ոչ մի գրառում չի մնացել, ապա կարող ենք շատ խնայել մշակման վրա՝ պարզապես կատարելով TRUNCATE:

Այն գործում է, ինչպես պայմանական հրաման DELETE յուրաքանչյուր աղյուսակի համար, բայց շատ ավելի արագ, քանի որ այն փաստորեն չի սկանում աղյուսակները։ Եվ ավելին, այն անմիջապես ազատում է սկավառակի տարածությունը, այնպես որ VACUUM գործողությունը դրա հետևից անել պետք չէ.

Ի՞նչ պետք է անենք, եթե մենք ցանկանում ենք նորից սկսել տվյալների անվանել (RESTART IDENTITY)։ Վերջնական որոշումը թողնում ենք ձեզ։

#3: Все — по-очереди!

Չգնալով առաջ գործողությունների՝ մենք մինչ այս փորձենք ստուգել, թե таблице մտահոգվող գրառումներ կա, իսկ այս ընթացքում ինչ-որ մեկը կարող է արդեն ինչ-որ բան գրել։ Մենք չենք կարող կորցնել այդ տեղեկատվությունը, այդ պատճառով ինչ պիտի անենք։ Հավանաբար, պետք է անենք, որպեսզի ոչ ոք այլևս գրառել չկարողանա։

Այս նպատակին հասնելու համար մենք պետք է ակտիվացնենք Սերիալիզավորվող- մեր գործարքի համար մեկուսացումը (այո, մենք այստեղ սկսում ենք գործարք) և փակենք таблицу «մեռյալ» վիճակում։

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

Այսպիսով, այդ փակման մակարդակը պայմանավորված է այն գործողություններով, որոնք ցանկանում ենք կատարել դրա նկատմամբ։

#4: Конфликт интересов

Մենք այստեղ գալիս ենք և ցանկանում ենք таблицу «փակել» — և եթե այդ պահին ո alguien актив էր, օրինակ, կարդում էր նրանից։ Մենք «վաղաժամ կխափանվենք» այդ փակման ազատման սպասման մեջ, իսկ մյուսները, ովքեր ցանկանում են կարդալան մեզ դիմակայեն…

Այդպիսի բան տեղի չունենալու համար, մենք «ծրագրենք ինքներս մեզ» — եթե որոշակի (թույլատրելի փոքր) ժամանակահատվածի ընթացքում փակումը չստացվի, ապա մենք շտապողները ավելորդ բան չենք արգելակիչ ստացել։

Այս նպատակին հասնելու համար համակարգային փոփոխականը կտեղադրենք lock_timeout (9.3+ տարբերակների համար) կամ/և statement_timeout։ Միակ կարևոր բանն այն է, որ statement_timeout արժեքը կիրառվում է միայն հաջորդ նույն հայտարարության համար։ Այդպիսով, նման մի ժամանակի գործարքը — չի աշխատի:

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-ին կապված լինի ինչ-որ այլ գործողությունների կամ տեղեկատվական կողմի տվյալների բազայի կողմից.

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