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

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

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

Тази статия се основава на моята реч на PgConf.Russia 2020.

Защо има подуване

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

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

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

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

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

Postgres има механизъм ВАКУУМ, който изчиства остарелите версии и прави място за нови данни. Но ако не е конфигуриран достатъчно агресивно или е зает да работи в други таблици, тогава „данните за боклука“ остават и трябва да използваме допълнителни страници за нови данни.

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

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

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

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

Имам ли подуване?

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

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

Малко количество подуване, до 20%, е приемливо. Може да се разглежда като аналог на фактора на запълване за маси и индекси. При 50% и повече може да започнат проблеми с производителността.

Начини за справяне с подуването

Има няколко готови начина за справяне с подуването в Postgres, но те далеч не са винаги и може да не отговарят на всички.

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

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

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

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

Отбор ВАКУУМ ПЪЛЕН възстановява съдържанието на таблици и индекси и оставя само актуални данни в тях. За да елиминира раздуването, той работи перфектно, но по време на изпълнението му се улавя изключително заключване на таблицата (AccessExclusiveLock), което няма да позволи заявки към тази таблица, дори селектира. Ако можете да си позволите да спрете услугата си или част от нея за известно време (от десетки минути до няколко часа в зависимост от размера на базата данни и вашия хардуер), тогава тази опция е най-добрата. Ние, за съжаление, нямаме време да стартираме VACUUM FULL по време на планираната поддръжка, така че този метод не ни подхожда.

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

Отбор REINDEX подобно на предишните два, но изгражда отново конкретен индекс или всички индекси в таблица. Заключванията са малко по-слаби: ShareLock на таблица (предотвратява модификации, но позволява селекции) и AccessExclusiveLock на повторно изграждащ се индекс (блокира заявки, използващи този индекс). Въпреки това, Postgres 12 въведе опцията ЕДНОВРЕМЕННО, което ви позволява да изградите отново индекс, без да блокирате едновременното добавяне, модифициране или изтриване на записи.

В по-ранните версии на Postgres можете да постигнете резултат, подобен на REINDEX ЕДНОВРЕМЕННО с СЪЗДАВАЙТЕ ИНДЕКС ЕДНОВРЕМЕННО. Позволява ви да създадете индекс без силно заключване (ShareUpdateExclusiveLock, което не пречи на паралелните заявки), след това да замените стария индекс с нов и да изтриете стария индекс. Това ви позволява да премахнете раздуването на индекса, без да пречи на приложението ви. Важно е да се има предвид, че при повторно изграждане на индекси ще има допълнително натоварване на дисковата подсистема.

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

Как работи pg_repack

Да кажем, че имаме съвсем обикновена таблица - с индекси, ограничения и, за съжаление, с раздуване. Като първа стъпка pg_repack създава журнална таблица, за да следи всички промени, докато работи. Тригерът ще репликира тези промени при всяко вмъкване, актуализиране и изтриване. След това се създава таблица, която е подобна на оригинала по структура, но без индекси и ограничения, за да не се забавя процеса на вмъкване на данни.

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

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

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

Отидохме до prod - и тогава всичко се обърка, както очаквахме.

Първа палачинка в продажба

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

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Това ограничение имаше автоматично генерирано име index_16508, създадено от pg_repack. По включените в състава му атрибути определихме „нашето” ограничение, което му съответства. Проблемът се оказа, че това не е съвсем обикновено ограничение, а забавено (отложено ограничение), т.е. неговата валидация се извършва по-късно от командата 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] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

Как да го направя по различен начин? Вариант първи: добавете допълнителна замяна на стойността с ред, който гарантирано не съществува в таблицата, например „-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: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

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

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

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

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

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

Създайте индекс, подобен на отложен

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

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

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

В тестовата среда получихме само няколко очаквани грешки. Успех! Стартирахме pg_repack отново на prod и получихме 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 винаги се срива при първото вмъкване, модифицираната версия работи без грешки. Страхотен.

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

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

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

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

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

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

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

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

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

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; -- one more update
    COMMIT;
  END;
END LOOP;

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

Отхвърляне на групирането

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

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

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

Експериментите бяха положителни. Пуснете и в продажба. За по-голяма яснота, ето снимка с размера на една от базите след пробега:

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

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

Данни

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

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

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