Postgres: раздуване, pg_repack и отложени ограничения

Ефектът на раздуване на таблици и индекси (bloat) е широко известен и се среща не само в Postgres. Съществуват начини за справяне с него „из коробки“, като VACUUM FULL или CLUSTER, но те блокират таблиците по време на работа и затова не винаги могат да бъдат използвани.

В статията ще има малко теория относно това как възниква bloat, как можем да се борим с него, за отложените ограничения и за проблемите, които те внасят в използването на разширението pg_repack.

Тази статия е написана на базата на моята презентация на PgConf.Russia 2020.

Защо възниква bloat

В основата на Postgres стои многоверсионният модел (MVCC). Същността му е, че всяка редица в таблицата може да има няколко версии, като транзакциите виждат не повече от една от тези версии, но не е задължително една и съща. Това позволява на няколко транзакции да работят едновременно и почти не оказват влияние една на друга.

Очевидно е, че всички тези версии трябва да се съхраняват. Postgres работи с паметта на страници и страницата е минималният обем данни, който може да се прочете от диска или да бъде записан. Нека разгледаме един малък пример, за да разберем как това става.

Да предположим, че имаме таблица, в която сме добавили няколко записи. В първата страница на файла, където се съхранява таблицата, се появиха нови данни. Това са живите версии на редовете, които са достъпни за други транзакции след комит (за опростяване, нека приемем, че нивото на изолация е Read Committed).

След това обновихме един от записите и по този начин маркирахме старата версия като неактуална.

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

В Postgres съществува механизъм VACUUM, който изчиства неактуалните версии и освобождава място за нови данни. Но ако той не е настроен достатъчно агресивно или е зает с работа в други таблици, „боклучните данни“ остават и ни се налага да използваме допълнителни страници за новите данни.

Така в нашия пример в някакъв момент времето таблицата ще се състои от четири страници, но живите данни в нея ще бъдат само половината. В резултат, при достъпа до таблицата, ще четем много повече данни, отколкото е необходимо.

Дори ако VACUUM в момента изтрие всичките неактуални версии на редовете, ситуацията няма да се подобри драстично. Ще имаме свободно пространство на страниците или дори цели страници за нови редове, но все пак ще четем повече данни, отколкото е необходимо.
Между другото, ако напълно празната страница (втората в нашия пример) бъде накрая на файла, VACUUM би могъл да я подреже. Но в момента тя е разположена по средата, затова не може да се направи нищо с нея.

Когато броят на такива празни или силно разредени страници стане голям, наричано bloat, това започва да се отразява на производителността.

Всичко описано по-горе е механика на възникване на bloat в таблиците. В индексите това се случва приблизително по същия начин.

Имам ли bloat?

Има няколко начина да определите дали имате bloat. Идеята на първия метод е да се използва вътрешната статистика на Postgres, която съдържа приблизителна информация за броя редове в таблиците, броя на „живите“ редове и т.н. В интернет може да намерите много вариации на готови скриптове. Ние взехме за основа скрипт от PostgreSQL Experts, който може да оцени bloat на таблиците заедно с toast и bloat на btree индексите. Според нашия опит, неговата неточност е 10-20%.

Друг метод е да се използва разширението pgstattuple, което позволява да се загледате в страниците и да получите както оценъчно, така и точно значение на bloat. Но в втория случай трябва да сканирате цялата таблица.

Ниско значение на bloat, до 20%, считаме за приемливо. Може да го смятате за аналог на fillfactor за таблици и индекси. При 50% и нагоре могат да започнат проблеми с производителността.

Методи за справяне с bloat

В Postgres има няколко метода за справяне с bloat “извън кутията”, но те не са винаги и не на всички могат да отговорят.

Настройте AUTOVACUUM, за да не се появява bloat.. Ако да бъдем по-точни, за да остане на приемливо за вас ниво. Изглежда като „капитански“ съвет, но всъщност не винаги е лесно да се постигне. Например, ако имате активна разработка с редовни промени в схемата на данните или се извършва някаква миграция на данни. В резултат на това, вашият натоварен профил може често да се променя и обикновено е различен за различни таблици. Тоест, трябва постоянно да работите малко напред и да настройвате AUTOVACUUM спрямо променящия се профил на всяка таблица. Но е очевидно, че не е просто да се направи това.

Друга разпространена причина, поради която AUTOVACUUM не успява да обработи таблиците, е наличието на дълготрайни транзакции, които не му позволяват да изчиства данни, тъй като те са достъпни за тези транзакции. Препоръката тук също е очевидна – освободете се от „висящите“ транзакции и минимизирайте времето на активни транзакции. Но ако натоварването на вашето приложение е хибрид между OLAP и OLTP, то вие можете да имате едновременно множество чести актуализации и кратки заявки, както и дълготрайни операции – например, изграждане на определен отчет. В такава ситуация е добре да се помисли за разпределяне на натоварването на различни бази, което ще позволи по-фина настройка на всяка от тях.

Още един пример – дори ако профилът е хомогенен, но БД е под много високо натоварване, дори и най-агресивният AUTOVACUUM може да не успее и bloat може да възникне. Масштабиране (вертикално или хоризонтално) – единственото решение.

Какво да правим в ситуация, когато AUTOVACUUM е настроен, но bloat продължава да расте.

Команда VACUUM FULL преструктурира съдържанието на таблиците и индексите и оставя само актуалните данни в тях. За премахване на bloat тя работи идеално, но по време на нейното изпълнение се взема ексклузивна блокировка на таблицата (AccessExclusiveLock), която не позволява изпълнението на заявки към тази таблица, дори select-ове. Ако можете да си позволите спиране на вашия сервис или част от него за известно време (от десетки минути до няколко часа в зависимост от размера на БД и вашето оборудване), то този вариант е най-добрият. За съжаление, не успяваме да стартираме VACUUM FULL в рамките на планираната поддръжка, затова този метод не ни подхожда.

Команда CLUSTER също така реконструира съдържанието на таблици, както и VACUUM FULL, като при това позволява да се зададе индекс, според който данните ще бъдат физически подредени на диска (но в бъдеще за нови редове подредбата не е гарантирана). В определени ситуации това е добра оптимизация за редица запитвания – с четене на няколко записа по индекса. Недостатъкът на командата е същият, както при VACUUM FULL – тя блокира таблицата по време на работа.

Команда REINDEX е подобна на двете предишни, но изпълнява реконструкция на конкретен индекс или на всички индекси на таблицата. Блокировките са малко по-слаби: ShareLock на таблицата (пречи на модификации, но позволява изпълнение на select) и AccessExclusiveLock на реконструирания индекс (блокира запитвания, използващи този индекс). Въпреки това в 12-та версия на Postgres се появи параметър CONCURRENTLY, който позволява реконструкция на индекса, без да блокира паралелно добавяне, изменение или изтриване на записи.

В по-ранни версии на Postgres може да се постигне резултат, сходен с REINDEX CONCURRENTLY, с помощта на CREATE INDEX CONCURRENTLY. Той позволява създаване на индекс без строга блокировка (ShareUpdateExclusiveLock, който не пречи на паралелни запитвания), след което да се замени стария индекс с новия и да се изтрие старият индекс. Това позволява да се избегне bloat на индексите, без да се нарушава работата на вашето приложение. Важно е да се има предвид, че при реконструкция на индексите ще има допълнителна натовареност на дисковата подсистема.

Следователно, ако за индексите има начини за премахване на bloat „на горещо“, то за таблиците няма. Тук влизат в действие различни външни разширения: pg_repack (по-рано pg_reorg), pgcompact, pgcompacttable и други. В рамките на тази статия няма да ги сравнявам и ще разкажа само за pg_repack, което след известно подобрение ние използваме у нас.

Как работи pg_repack

Предположим, че имаме напълно обикновена таблица – с индекси, ограничения и, за съжаление, с bloat. Първата стъпка на pg_repack е да създаде лог-таблица, за да съхранява данни за всички промени по време на работа. Триггерът ще репликира тези промени на всеки insert, update и delete. След това се създава таблица, аналогична на първоначалната по структура, но без индекси и ограничения, за да не забавя процеса на вмъкване на данни.

Следва pg_repack да прехвърли данните от старата таблица в нова, автоматично филтрирайки всички неактуални редове, а след това създава индекси за новата таблица. През времето на изпълнение на всички тези операции в лог-таблицата се натрупват промени.

Следващата стъпка е да се прехвърлят промените в новата таблица. Прехвърлянето се извършва в няколко итерации, и когато в лог-таблицата останат по-малко от 20 записа, pg_repack хваща строга блокировка, прехвърля последните данни и заменя старата таблица с новата в системните таблици на Postgres. Това е единственият и много кратък момент, в който не можете да работите с таблицата. След това старата таблица и таблицата с логовете се изтриват и в файловата система се освобождава място. Процесът е завършен.

В теория всичко изглежда отлично, какво обаче е в практиката? Тествали сме pg_repack без натоварване и под натоварване, проверихме работата му в случай на предсрочно спиране (по-лесно казано, с Ctrl+C). Всички тестове бяха положителни.

Отправихме се към продукция – и тук всичко тръгна не както очаквахме.

Първият блин в продукция

На първия кластер получихме грешка за нарушаване на уникално ограничение:

$ ./pg_repack -t tablename -o id
INFO: репакетиране на таблицата "tablename"
ERROR: заявката не успя:
    ERROR: дублираща стойност на ключа нарушава уникалното ограничение "index_16508"
DETAIL: Ключ (id, index)=(100500, 42) вече съществува.

Това ограничение имаше автосгенерирано наименование index_16508 – създадено е от pg_repack. По атрибутите, входящи в него, определихме “нашето” ограничение, което му отговаря. Проблемът се състоеше в това, че това не е съвсем обикновено ограничение, а отложено (deferred constraint), т.е. проверката му се извършва по-късно, отколкото SQL командата, което води до неочаквани последствия.

Отложени ограничения: защо са необходими и как работят

Няколко теоретични разяснения за отложените ограничения.
Нека разгледаме прост пример: имаме справочна таблица с автомобили с два атрибута – наименование и ред на автомобила в справочника.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

Да предположим, че трябва да сменим първия и втория автомобил местата. Решението “в лоб” е да актуализираме първата стойност на втората, а втората на първата:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

Но при изпълнението на този код очаквано ще получим нарушение на ограничението, защото редът на стойностите в таблицата е уникален.

[23305] ГРЕШКА: дублираща се стойност на ключа нарушава уникалното ограничение “uk_cars”
Детайли: Ключ (ord)=(2) вече съществува.

Как да направим по различен начин? Първи вариант: добавете допълнителна стойност за замяна на реда, която гарантирано не съществува в таблицата, например “-1”. В програмирането това се нарича “размяна на стойности на две променливи чрез трета”. Единственият недостатък на този метод е допълнителен ъпдейт.

Втори вариант: преразгледайте таблицата, за да използвате тип данни с плаваща запетая за стойността на реда вместо цели числа. Тогава при актуализиране на стойността от 1, например, на 2.5, първият запис автоматично “ще се позиционира” между втория и третия. Това решение работи, но има две ограничения. Първо, не е подходящо, ако стойността се използва някъде в интерфейса. На второ място, в зависимост от точността на типа данни, ще имате ограничен брой възможни вставки преди извършване на преизчисление на стойностите на всички записи.

Трети вариант: направете ограничението отложено, за да се проверява само по време на комита:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

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

Примерът по-горе, разбира се, е много синтетичен, но разкрива идеята. В нашето приложение използваме отложени ограничения за реализиране на логиката, която отговаря за разрешаване на конфликти при едновременна работа на потребители с общи обекти-иджетове на дъската. Използването на такива ограничения ни позволява да направим приложния код малко по-прост.

В общи линии, в зависимост от типа ограничение в Postgres, съществуват три нива на грануларност на проверките: ниво на ред, транзакция и израз.

Източник: begriffs

CHECK и NOT NULL се проверяват винаги на ниво ред, за останалите ограничения, както е видно от таблицата, има различни варианти. Повече информация може да се прочете тук.

Ако обобщим накратко, отложените ограничения в редица ситуации предлагат по-ясен за четене код и по-малко команди. Въпреки това, за това се плаща с усложняване на дебъг процеса, тъй като моментът на възникване на грешка и моментът, в който научавате за нея, са раздалечени във времето. Още един възможен проблем е, че планировчикът не винаги може да състави оптимален план, ако в заявката участва отложено ограничение.

Допълнителна работа по pg_repack

Разбрахме какво представляват отложените ограничения, но как те са свързани с нашия проблем? Нека си спомним грешката, която получихме по-рано:

$ ./pg_repack -t tablename -o id
INFO: репакетиране на таблицата "tablename"
ERROR: заявката не успя:
    ERROR: дублираща стойност на ключа нарушава уникалното ограничение "index_16508"
DETAIL: Ключ (id, index)=(100500, 42) вече съществува.

Тя възниква в момента на копиране на данни от лог-таблицата в новата таблица. Това изглежда странно, тъй като данните в лог-таблицата се комитират заедно с данните в оригиналната таблица. Ако те отговарят на ограниченията на оригиналната таблица, как могат да нарушават същите тези ограничения в новата?

Както се оказа, коренът на проблема е в предишния етап на работа на pg_repack, където се създават само индекси, но не и ограничения: в старата таблица имаше уникално ограничение, а в новата вместо него се създаде уникален индекс.

Важно е да се отбележи, че ако ограничението е обикновено, а не отложено, то създаденият вместо него уникален индекс е равен на това ограничение, тъй като уникалните ограничения в Postgres се реализират чрез създаване на уникален индекс. Но в случай на отложено ограничение поведението не е същото, тъй като индексът не може да бъде отложен и се проверява в момента на изпълнение на sql-командата.

Следователно, същността на проблема е в „отложеността“ на проверката: в оригиналната таблица тя се извършва в момента на комита, а в новата – в момента на изпълнение на sql-командата. Значи трябва да направим така, че проверките да се извършват еднакво в двата случая: или винаги отложено, или винаги веднага.

И така, какви идеи имахме.

Да създадем индекс, аналогичен на отложен.

Първата идея е да се извършват и двете проверки в режим immediate. Това може да доведе до няколко false positive сработвания на ограниченията, но ако те са малко, то не би трябвало да повлияе на работата на потребителите, тъй като за тях тези конфликти са нормална ситуация. Те се случват, например, когато двама потребители започнат едновременно да редактират един и същи виджет и клиентът на втория потребител не успява да получи информация, че виджетът вече е блокиран за редактиране от първия потребител. В такава ситуация сървърът отговаря на втория потребител с отказ, а неговият клиент откатва промените и блокира виджета. Малко по-късно, когато първият потребител завърши редакцията, вторият ще получи информация, че виджетът вече не е блокиран и ще може да повтори своето действие.

За да бъдат проверките винаги в неотложен режим, създадохме нов индекс, аналогичен на оригиналното отложено ограничение:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

В тестовата среда получихме само няколко очаквани грешки. Успех! Отново стартирахме pg_repack на продукция и получихме 5 грешки на първия кластер за час работа. Това е приемлив резултат. Въпреки това на втория кластер броят на грешките значително се увеличи и ни се наложи да спрем pg_repack.

Защо се случи така? Вероятността от възникването на грешка зависи от това колко много потребители работят едновременно с едни и същи виджети. Очевидно, в момента с данните, съхранявани на първия кластер, е имало значително по-малко конкурентни промени, отколкото на останалите, т.е. просто сме имали “късмет”.

Идеята не сработи. В този момент видяхме два други варианта за решение: да препишем нашия приложение код, за да се откажем от отложените ограничения или да “научим” pg_repack да работи с тях. Избрахме втория вариант.

Да заменим индексите в новата таблица с отложените ограничения от оригиналната таблица

Целта на доработката беше ясна – ако оригиналната таблица има отложено ограничение, то за новата трябва да се създаде такова ограничение, а не индекс.

За да проверим нашите промени, написахме прост тест:

• таблица с отложено ограничение и една запис;
• вмъкваме в цикъл данни, които конфликтуват с вече съществуващия запис;
• правим обновление – данните вече не конфликтуват;
• комитираме промените.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

Исходната версия pg_repack винаги падаше при първия insert, подобрената версия работеше без грешки. Отлично.

Отиваме на продукцията и отново получаваме грешка в същата фаза на копиране на данни от лог-таблицата в новата:

$ ./pg_repack -t tablename -o id
INFO: репакетиране на таблицата "tablename"
ERROR: заявката не успя:
    ERROR: дублираща стойност на ключа нарушава уникалното ограничение "index_16508"
DETAIL: Ключ (id, index)=(100500, 42) вече съществува.

Класическа ситуация: на тестовите среди всичко работи, а на продукцията – не?!

APPLY_COUNT и свързване на два пакета

Започнахме да анализираме кода буквално ред по ред и открихме важен момент: преливането на данни от лог-таблицата в новата се извършва на пакети, константата APPLY_COUNT определяше размера на пакета:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  
 /* все още може да има някои кортежи, повтори. */
...
}

Проблемът е, че данните от първоначалната транзакция, в която няколко операции могат потенциално да нарушат ограничението, при прехвърляне могат да се окажат на ръба на два пакета – половината команди ще бъдат закомитнени в първия пакет, а другата половина – във втория. И тук каквото стане: ако командите в първия пакет не нарушават нищо, всичко е наред, а ако нарушават – възниква грешка.

APPLY_COUNT е 1000 записа, което обяснява защо нашите тестове минаваха успешно – те не покриваха случая „свързване на пакетите“. Използвахме две команди – insert и update, затова точно 500 транзакции с две команди винаги попадаха в пакета и не срещахме проблеми. След добавянето на втори update нашето изменение спря да работи:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- още един update
    COMMIT;
  END;
END LOOP;

И така, следващата задача е да направим така, че данните от първоначалната таблица, които са били променени в една транзакция, да попаднат в новата таблица също в рамките на една транзакция.

Отказ от пакетиране

И при нас отново имаше два варианта за решение. Първият: да се откажем напълно от разпределението на батчове и да прехвърлим данните с една транзакция. В полза на това решение беше неговата простота – нужните промени в кода бяха минимални (между другото, в по-старите версии тогава pg_reorg работеше именно така). Но има проблем – създаваме дълга транзакция, а това, както беше казано по-рано, е заплаха от ново bloat.

Второто решение е по-сложно, но вероятно и по-правилно: да създадем в лог-таблицата колона с идентификатор на транзакцията, която е добавила данните в таблицата. Тогава при копиране на данни ще можем да ги групираме по този атрибут и да гарантираме, че свързаните изменения ще бъдат пренесени заедно. Батчът ще се формира от няколко транзакции (или една голяма) и неговият размер ще варира в зависимост от това, колко много данни са били променени в тези транзакции. Важно е да се отбележи, че тъй като данните от различни транзакции попадат в лог-таблицата в произволен ред, вече не е възможно да се чете последователно, както беше по-рано. seqscan при всяко запитване с филтриране по tx_id е твърде скъпо, нужен е индекс, но и той ще забави работата на метода поради разходите за обновление. Общо взето, както винаги, трябва да жертваме нещо.

И така, решихме да започнем с първия вариант, тъй като е по-прост. Първо трябваше да разберем дали дългата транзакция ще бъде реален проблем. Тъй като основното прехвърляне на данни от старата таблица в новата също се извършва в една дълга транзакция, въпросът се трансформира в “колко много ще увеличим тази транзакция?” Продължителността на първата транзакция зависи основно от размера на таблицата. Продължителността на новата – от това колко много промени ще се натрупат в таблицата за времето на преливането на данни, т.е. от интензивността на натоварването. pg_repack беше пуснат по време на минимално натоварване на услугата, а обемът на промените беше несравнимо малък в сравнение с първоначалния обем на таблицата. Решихме, че можем да пренебрегнем времето на новата транзакция (средното време е 1ч и 2-3 минути за сравнение).

Експериментите бяха положителни. Пускането на продакшън също. За илюстрация – изображение с размера на една от базите след изпълнението:

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

Възможно е да сте се запитали защо изобщо се впуснахме в тази история с доработката на pg_repack, а не сме използвали неговите аналози? В определен момент и ние мислехме за това, но положителният опит от предишната му употреба върху таблици без отложени ограничения ни мотивира да опитаме да разберем същността на проблема и да го коригираме. Освен това използването на други решения също изисква време за провеждане на тестове, затова решихме първо да опитаме да решим проблема в него и ако разберем, че не можем да го направим в разумен срок, тогава ще започнем да обмисляме аналогите.

Изводи

Какво можем да препоръчаме на базата на собствения си опит:

1. Следете за вашия bloat. На базата на данните от мониторинга ще можете да разберете колко добре е настроен autovacuum.
2. Настройвайте AUTOVACUUM, за да поддържате bloat на допустимо ниво.
3. Ако bloat все пак нараства и не можете да го победите с помощта на средствата "извън кутията", не се колебайте да използвате външни разширения. Важното е всичко да бъде добре тествано.
4. Не се страхувайте да доработвате външни решения според собствените си нужди – понякога това може да бъде по-ефективно и даже по-просто, отколкото промяната на собствения ви код.

Източник: habr.com