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

VACUUM կարող է «մաքրել» PostgreSQL-ի աղյուսակից միայն այն, ինչ ոչ ոք չի կարող տեսնել - այսինքն՝ չկա մեկ ակտիվ հարցում, որը սկսվել է մինչ այս գրառումների փոփոխությունը։

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

Փոցխի բացում

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

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

Ինչ է պատահել? Ինչու նույնիսկ մեկ ձայնագրության ամենապարզ ԹԱՐՄԱՑՄԱՆ համար կատարման ժամանակը նվազել է 7 անգամ — 0.524 մս-ից մինչև 3.808 մս: Իսկ մեր վարկանիշն ավելի ու ավելի դանդաղ է կառուցվում։

Ամեն ինչ 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 վայրկյան վերջին [ավտո]ՎԱԿՈՒՈՒՄից սկսած։
• ֆիզիկական սեղանի չափը ավելի մեծ է, քան թիրախը
  Եկեք այն սահմանենք որպես էջերի երկու անգամ (8 ԿԲ բլոկներ) նվազագույն չափի համեմատ. 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 - ի տարբերություն 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. Հիմնական բանը, որ պետք է հիշել, այն է, որ statement_timeout արժեքը կիրառվում է միայն հաջորդ հայտարարությունից: Այսինքն, սոսնձման մեջ այսպես - չի աշխատի:

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

Որպեսզի հետագայում չզբաղվենք փոփոխականի «հին» արժեքի վերականգնման հետ, մենք օգտագործում ենք ձևը ՍՏԵՂԾԵԼ ՏԵՂԱԿԱՆ, որը սահմանափակում է պարամետրի շրջանակը ընթացիկ գործարքով:

Մենք հիշում ենք, որ statement_timeout-ը վերաբերում է բոլոր հետագա հարցումներին, որպեսզի գործարքը չհասնի անընդունելի արժեքների, եթե աղյուսակում շատ տվյալներ կան:

# 5. պատճենել տվյալները

Եթե ​​աղյուսակն ամբողջությամբ դատարկ չէ, տվյալները պետք է նորից պահպանվեն՝ օգտագործելով օժանդակ ժամանակավոր աղյուսակը.

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Ստորագրություն ON COMMIT DOP նշանակում է, որ գործարքի ավարտի պահին ժամանակավոր աղյուսակը կդադարի գոյություն ունենալ, և կարիք չկա այն ձեռքով ջնջել կապի համատեքստում:

Քանի որ մենք ենթադրում ենք, որ շատ «կենդանի» տվյալներ չկան, այս գործողությունը պետք է տեղի ունենա բավականին արագ։

Դե, այսքանը: Գործարքն ավարտելուց հետո մի մոռացեք գործարկել 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 անգամ, և բոլոր թարմացումները նորից արագ են աշխատում:

Source: www.habr.com