Ефектот на надуеност на табелите и индексите е широко познат и е присутен не само во Postgres. Постојат начини да се справите со тоа надвор од кутијата, како VACUUM FULL или CLUSTER, но тие ги заклучуваат табелите за време на работата и затоа не можат секогаш да се користат.
Статијата ќе содржи мала теорија за тоа како надуеноста се јавува, како можете да се борите со него, за одложените ограничувања и проблемите што тие ги носат при користењето на екстензијата pg_repack.
Оваа статија е напишана врз основа на на PgConf.Russia 2020 година.
Зошто се јавува надуеност?
Postgres се заснова на модел со повеќе верзии (). Нејзината суштина е дека секој ред во табелата може да има неколку верзии, додека трансакциите не гледаат повеќе од една од овие верзии, но не мора иста. Ова им овозможува на неколку трансакции да работат истовремено и практично немаат влијание една врз друга.
Очигледно, сите овие верзии треба да се складираат. Postgres работи со меморија страница по страница и страница е минималната количина на податоци што може да се прочита од диск или да се запише. Ајде да погледнеме мал пример за да разбереме како се случува ова.
Да речеме дека имаме табела на која додадовме неколку записи. Нови податоци се појавија на првата страница од датотеката каде што е зачувана табелата. Ова се живи верзии на редови кои се достапни за други трансакции по извршеното извршување (за поедноставност, ќе претпоставиме дека нивото на изолација е Read Committed).
Потоа ажуриравме еден од записите, со што ја означивме старата верзија како повеќе нерелевантна.
Чекор по чекор, ажурирајќи и бришејќи ги верзиите на редови, завршивме со страница на која приближно половина од податоците се „ѓубре“. Овие податоци не се видливи за ниту една трансакција.
Postgres има механизам , што ги чисти застарените верзии и остава простор за нови податоци. Но, ако не е доволно агресивно конфигуриран или е зафатен со работа во други табели, тогаш остануваат „податоци за ѓубре“ и мораме да користиме дополнителни страници за нови податоци.
Така, во нашиот пример, во одреден момент табелата ќе се состои од четири страници, но само половина од неа ќе содржи податоци во живо. Како резултат на тоа, кога пристапуваме до табелата, ќе прочитаме многу повеќе податоци отколку што е потребно.
Дури и ако VACUUM сега ги избрише сите ирелевантни верзии на редови, ситуацијата нема драстично да се подобри. Ќе имаме слободен простор во страниците или дури и цели страници за нови редови, но сепак ќе читаме повеќе податоци отколку што е потребно.
Патем, ако целосно празна страница (втората во нашиот пример) беше на крајот од датотеката, тогаш VACUUM ќе може да ја скрати. Но, сега таа е во средината, па ништо не може да се направи со неа.
Кога бројот на такви празни или многу ретки страници станува голем, што се нарекува bloat, тоа почнува да влијае на перформансите.
Сè што е опишано погоре е механиката на појавата на надуеност во табелите. Кај индексите тоа се случува на ист начин.
Дали имам надуеност?
Постојат неколку начини да се утврди дали имате надуеност. Идејата на првата е да се користи внатрешна статистика на Postgres, која содржи приближни информации за бројот на редови во табелите, бројот на „живи“ редови итн. Можете да најдете многу варијации на готови скрипти на Интернет. Зедовме како основа од PostgreSQL Experts, кои можат да ги проценат табелите за надуеност заедно со индексите на тост и bloat btree. Според нашето искуство, нејзината грешка е 10-20%.
Друг начин е да се користи наставката , што ви овозможува да погледнете внатре во страниците и да добиете и проценета и точна вредност на надуеност. Но, во вториот случај, ќе треба да ја скенирате целата табела.
Сметаме дека е прифатлива мала вредност на надуеност, до 20%. Може да се смета како аналог на факторот за полнење за и . На 50% и повеќе, може да започнат проблеми со перформансите.
Начини за борба против надуеност
Postgres има неколку начини да се справи со надуеноста надвор од кутијата, но тие не се секогаш погодни за секого.
Конфигурирајте AUTOVACUUM за да не се појави надуеност. Или поточно да го задржите на ниво прифатливо за вас. Ова изгледа како „капетански“ совет, но во реалноста тоа не е секогаш лесно да се постигне. На пример, имате активен развој со редовни промени на шемата за податоци или се случува некој вид миграција на податоци. Како резултат на тоа, вашиот профил на оптоварување може често да се менува и вообичаено ќе се разликува од табела до табела. Ова значи дека треба постојано да работите малку напред и да го прилагодувате АВТОВАКУМОТ на променливиот профил на секоја маса. Но, очигледно ова не е лесно да се направи.
Друга честа причина зошто AUTOVACUUM не може да биде во чекор со табелите е затоа што има долготрајни трансакции кои го спречуваат да ги исчисти податоците што се достапни за тие трансакции. Препораката овде е исто така очигледна - ослободете се од „висечките“ трансакции и минимизирајте го времето на активни трансакции. Но, ако оптоварувањето на вашата апликација е хибрид на OLAP и OLTP, тогаш можете истовремено да имате многу чести ажурирања и кратки прашања, како и долгорочни операции - на пример, градење извештај. Во таква ситуација, вреди да се размислува за ширење на товарот низ различни основи, што ќе овозможи повеќе дотерување на секоја од нив.
Друг пример - дури и ако профилот е хомоген, но базата на податоци е под многу големо оптоварување, тогаш дури и најагресивниот АВТОВАКУМ може да не се справи и ќе се појави надуеност. Скалирањето (вертикално или хоризонтално) е единственото решение.
Што да направите во ситуација кога сте поставиле АВТОВАКУУМ, но надуеноста продолжува да расте.
Тим ПОЛН ВАКУУМ ја обновува содржината на табелите и индексите и во нив остава само релевантни податоци. За да се елиминира надуеноста, функционира совршено, но за време на неговото извршување се фаќа ексклузивна брава на масата (AccessExclusiveLock), која нема да дозволи извршување на прашања на оваа табела, дури и избира. Ако можете да си дозволите да ја прекинете вашата услуга или дел од неа некое време (од десетици минути до неколку часа во зависност од големината на базата на податоци и вашиот хардвер), тогаш оваа опција е најдобра. За жал, немаме време да работиме VACUUM FULL за време на закажаното одржување, така што овој метод не е соодветен за нас.
Тим ГРОЗД Ја обновува содржината на табелите на ист начин како VACUUM FULL, но ви овозможува да наведете индекс според кој податоците ќе бидат физички нарачани на дискот (но во иднина редоследот не е загарантиран за нови редови). Во одредени ситуации, ова е добра оптимизација за голем број прашања - со читање на повеќе записи по индекс. Недостатокот на командата е ист како оној на VACUUM FULL - ја заклучува масата за време на работата.
Тим РЕИНДЕКС слично на претходните два, но повторно гради специфичен индекс или сите индекси од табелата. Бравите се малку послаби: ShareLock на масата (спречува модификации, но дозволува избирање) и AccessExclusiveLock на индексот што се обновува (блокира прашања користејќи го овој индекс). Сепак, во 12-тата верзија на Postgres се појави параметар , кој ви овозможува повторно да го изградите индексот без да блокирате истовремено додавање, измена или бришење записи.
Во претходните верзии на Postgres, може да постигнете резултат сличен на REINDEX КОМКУРИНТНО користејќи . Ви овозможува да креирате индекс без строго заклучување (ShareUpdateExclusiveLock, што не се меша со паралелните прашања), потоа да го замените стариот индекс со нов и да го избришете стариот индекс. Ова ви овозможува да го елиминирате надуеноста на индексот без да се мешате со вашата апликација. Важно е да се земе предвид дека при обнова на индексите ќе има дополнително оптоварување на потсистемот на дискот.
Така, ако за индексите постојат начини да се елиминира надуеноста „во лет“, тогаш нема ниту еден за табелите. Тука влегуваат во игра различни надворешни екстензии: (порано pg_reorg), , и други. Во оваа статија нема да ги споредувам и ќе зборувам само за pg_repack, кој, по извесна модификација, ние самите го користиме.
Како функционира pg_repack
Да речеме дека имаме сосема обична табела - со индекси, ограничувања и, за жал, со надуеност. Првиот чекор на pg_repack е да се создаде табела за евиденција за складирање на податоци за сите промени додека работи. Активирањето ќе ги повторува овие промени за секое вметнување, ажурирање и бришење. Потоа се креира табела, слична на оригиналната по структура, но без индекси и ограничувања, за да не се забави процесот на вметнување податоци.
Следно, pg_repack ги пренесува податоците од старата табела во новата табела, автоматски ги филтрира сите ирелевантни редови, а потоа создава индекси за новата табела. За време на извршувањето на сите овие операции, промените се акумулираат во табелата за дневници.
Следниот чекор е да ги пренесете промените во новата табела. Миграцијата се врши преку неколку повторувања и кога остануваат помалку од 20 записи во табелата за дневници, pg_repack добива силно заклучување, ги мигрира најновите податоци и ја заменува старата табела со новата во табелите на системот Postgres. Ова е единственото и многу кратко време кога нема да можете да работите со масата. По ова, старата табела и табелата со дневници се бришат и се ослободува простор во датотечниот систем. Процесот е завршен.
Теоретски се изгледа одлично, но што се случува во пракса? Го тестиравме pg_repack без оптоварување и под оптоварување и ја проверивме неговата работа во случај на предвремено запирање (со други зборови, користејќи Ctrl+C). Сите тестови беа позитивни.
Отидовме во продавница за храна - и тогаш сè не одеше како што очекувавме.
Првата палачинка во продажба
На првиот кластер добивме грешка за прекршување на единствено ограничување:
$ ./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 има три нивоа на грануларност за нивно проверување: нивоа на ред, трансакција и изразување.
Извор:
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 на производство и добивме 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 секогаш паѓаше на првото вметнување, изменетата верзија работеше без грешки. Одлично.
Одиме во производство и повторно добиваме грешка во истата фаза на копирање на податоците од табелата за дневници на нова:
$ ./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, а не ги користевме, на пример, неговите аналози? Во одреден момент размислувавме и за ова, но позитивното искуство од користењето порано, на табели без одложени ограничувања, не мотивираше да се обидеме да ја разбереме суштината на проблемот и да го поправиме. Дополнително, користењето други решенија бара и време за спроведување тестови, па затоа решивме прво да се обидеме да го решиме проблемот во него и ако сфатиме дека не можеме да го направиме тоа во разумно време, тогаш ќе почнеме да гледаме аналози .
Наоди
Што можеме да препорачаме врз основа на нашето сопствено искуство:
- Следете ја вашата надуеност. Врз основа на податоците од мониторингот, можете да разберете колку добро е конфигуриран автовакуумот.
- Прилагодете го АВТОВАКУМОТ за да ја задржите надуеноста на прифатливо ниво.
- Ако надуеноста сè уште расте и не можете да ја надминете со помош на алатки што не се достапни, не плашете се да користите надворешни екстензии. Главната работа е да се тестира сè добро.
- Не плашете се да ги менувате надворешните решенија за да одговараат на вашите потреби - понекогаш ова може да биде поефективно, па дури и полесно отколку да го промените вашиот сопствен код.
Извор: www.habr.com