Efekt rozrostu tabel i indeksów (bloat) jest dobrze znany i występuje nie tylko w Postgresie. Istnieją sposoby jego zwalczania „z pudełka”, takie jak VACUUM FULL czy CLUSTER, ale z powodu blokowania tabel podczas pracy nie zawsze mogą być stosowane.
W artykule znajdzie się nieco teorii na temat tego, jak powstaje bloat, jak można sobie z nim radzić, o deferred constraints oraz o problemach, które wprowadza ich użycie w rozszerzeniu pg_repack.
Artykuł jest napisany na podstawie na PgConf.Russia 2020.
Dlaczego pojawia się bloat
Podstawą Postgresa jest model wielowersyjny (). Jego istota polega na tym, że każdy wiersz w tabeli może mieć kilka wersji, przy czym transakcje widzą nie więcej niż jedną z tych wersji, lecz niekoniecznie tę samą. Umożliwia to równoległe działanie kilku transakcji, które praktycznie nie wpływają na siebie nawzajem.
Oczywiste jest, że wszystkie te wersje należy przechowywać. Postgres operuje na pamięci strona po stronie, a strona to minimalna ilość danych, która może zostać odczytana z dysku lub zapisana. Rozważmy mały przykład, aby zrozumieć, jak to się odbywa.
Załóżmy, że mamy tabelę, do której dodaliśmy kilka rekordów. Na pierwszej stronie pliku, w którym przechowywana jest tabela, znajdują się nowe dane. Są to aktualne wersje wierszy, które są dostępne dla innych transakcji po zatwierdzeniu (dla uproszczenia zakładamy, że poziom izolacji to Read Committed).
Następnie zaktualizowaliśmy jeden z rekordów, co oznacza, że oznaczyliśmy starą wersję jako nieaktualną.
Krok po kroku, aktualizując i usuwając wersje wierszy, uzyskaliśmy stronę, w której około połowa danych to „śmieci”. Te dane są niewidoczne dla żadnej transakcji.
W Postgresie istnieje mechanizm , który oczyszcza nieaktualne wersje i zwalnia miejsce na nowe dane. Ale jeśli nie jest on skonfigurowany wystarczająco agresywnie lub zajęty pracą w innych tabelach, „dane śmieciowe” pozostają, a my musimy używać dodatkowych stron dla nowych danych.
W naszym przykładzie w pewnym momencie tabela będzie składać się z czterech stron, ale żywe dane będą stanowić tylko połowę. W rezultacie, przy odwoływaniu się do tabeli będziemy odczytywać znacznie więcej danych, niż to konieczne.
Nawet jeśli VACUUM usunie wszystkie nieaktualne wersje wierszy, sytuacja nie ulegnie drastycznej poprawie. Zyskamy wolne miejsce na stronach lub nawet całe strony dla nowych wierszy, ale nadal będziemy przetwarzać więcej danych niż to konieczne.
Swoją drogą, gdyby całkowicie pusta strona (druga w naszym przykładzie) znalazła się na końcu pliku, VACUUM mógłby ją usunąć. Ale obecnie znajduje się ona pośrodku, więc nic nie da się z nią zrobić.
Kiedy liczba takich pustych lub mocno rozrzedzonych stron staje się duża, co określa się mianem bloat, zaczyna to wpływać na wydajność.
Wszystko, co powyżej, opisuje mechanikę powstawania bloat w tabelach. W indeksach dzieje się to mniej więcej tak samo.
Czy mam bloat?
Istnieje kilka sposobów, aby określić, czy masz bloat. Pierwsza z idei to wykorzystanie wewnętrznej statystyki Postgresa, która zawiera przybliżone informacje na temat liczby wierszy w tabelach, liczby „żywych” wierszy itd. W Internecie można znaleźć wiele wariacji gotowych skryptów. My oparliśmy się na od PostgreSQL Experts, który może ocenić bloat tabel razem z toastem oraz bloat btree-indeksów. Z naszego doświadczenia jego dokładność wynosi 10-20%.
Innym sposobem jest użycie rozszerzenia , które pozwala zajrzeć w głąb stron i uzyskać zarówno szacunkową, jak i dokładną wartość bloat. Ale w drugim przypadku trzeba będzie przeskanować całą tabelę.
Niska wartość bloat, do 20%, uważana jest za akceptowalną. Można ją traktować jako odpowiednik fillfactor dla i . Przy 50% i więcej mogą wystąpić problemy z wydajnością.
Sposoby zwalczania bloat
W Postgresie istnieje kilka sposobów na zwalczanie bloat „z pudełka”, jednak nie zawsze będą one odpowiednie dla każdego.
Skonfiguruj AUTOVACUUM, aby zapobiec powstawaniu bloatA jeśli dokładniej, aby utrzymać go na akceptowalnym dla was poziomie. Wydaje się, że to rada „kapitańska”, ale w rzeczywistości nie zawsze jest to łatwe do osiągnięcia. Na przykład, jeśli prowadzicie aktywny rozwój z regularnymi zmianami schematu danych lub następuje jakaś migracja danych. W rezultacie wasz profil obciążenia może często się zmieniać i zazwyczaj jest różny dla różnych tabel. Oznacza to, że musicie ciągle pracować trochę z wyprzedzeniem i dostosowywać AUTOVACUUM do zmieniającego się profilu każdej tabeli. Ale oczywiście, zrobienie tego nie jest proste.
Inną powszechną przyczyną, dla której AUTOVACUUM nie nadąża z przetwarzaniem tabel, są długoterminowe transakcje, które uniemożliwiają mu usuwanie danych, ponieważ są one dostępne dla tych transakcji. Rekomendacja w tym przypadku jest również oczywista – pozbyć się „wiszących” transakcji i zminimalizować czas aktywnych transakcji. Ale jeśli obciążenie na waszej aplikacji to hybryda OLAP i OLTP, to możecie mieć jednocześnie zarówno wiele częstych aktualizacji i krótkich zapytań, jak i długie operacje – na przykład budowanie jakiegoś raportu. W takiej sytuacji warto pomyśleć o rozdzieleniu obciążenia na różne bazy, co pozwoli na bardziej precyzyjne dostrojenie każdej z nich.
Kolejny przykład – nawet jeśli profil jest jednorodny, ale baza danych jest pod bardzo dużym obciążeniem, to nawet maksymalnie agresywne AUTOVACUUM może sobie nie radzić, a bloat będzie się pojawiać. Skalowanie (wertykalne lub horyzontalne) jest jedynym rozwiązaniem.
Co zatem zrobić w sytuacji, gdy skonfigurowaliście AUTOVACUUM, ale bloat nadal rośnie.
Zespół VACUUM FULL przebudowuje zawartość tabel i indeksów, pozostawiając w nich tylko aktualne dane. Działa idealnie w celu eliminacji bloat, ale podczas jej wykonywania zajmuje wyłączny blokadę na tabeli (AccessExclusiveLock), która uniemożliwia wykonywanie zapytań do tej tabeli, nawet selectów. Jeśli możecie sobie pozwolić na wstrzymanie waszej usługi lub jej części na jakiś czas (od kilkunastu minut do kilku godzin w zależności od rozmiaru bazy danych i waszego sprzętu), to ta opcja jest najlepsza. Niestety, nie zdążamy uruchomić VACUUM FULL w czasie zaplanowanej konserwacji, więc ta metoda nam nie odpowiada.
Zespół CLUSTER Reorganizuje zawartość tabel w taki sam sposób jak VACUUM FULL, umożliwiając określenie indeksu, według którego dane będą fizycznie uporządkowane na dysku (jednakże w przyszłości dla nowych wierszy porządek nie jest gwarantowany). W niektórych sytuacjach jest to niezła optymalizacja dla wielu zapytań – przy wczytywaniu kilku rekordów za pomocą indeksu. Wada polecenia jest taka sama jak w przypadku VACUUM FULL – blokuje tabelę podczas działania.
Zespół REINDEX Jest podobne do dwóch poprzednich, ale wykonuje reorganizację konkretnego indeksu lub wszystkich indeksów tabeli. Blokady są nieco słabsze: ShareLock na tabeli (uniemożliwia modyfikacje, ale pozwala na wykonywanie zapytań select) oraz AccessExclusiveLock na reorganizowanym indeksie (blokuje zapytania korzystające z tego indeksu). Jednak w 12. wersji Postgresa wprowadzono parametr , który umożliwia reorganizowanie indeksu bez blokowania równoległego dodawania, zmieniania lub usuwania rekordów.
W starszych wersjach Postgresa można osiągnąć efekt podobny do REINDEX CONCURRENTLY za pomocą . Umożliwia to tworzenie indeksu bez ścisłej blokady (ShareUpdateExclusiveLock, która nie przeszkadza w równoległych zapytaniach), a następnie zamianę starego indeksu na nowy oraz usunięcie starego indeksu. To pozwala na usunięcie bloatu indeksów, nie przeszkadzając w pracy aplikacji. Warto zauważyć, że podczas reorganizacji indeksów wystąpi dodatkowe obciążenie dla systemu dyskowego.
Tak więc, jeżeli dla indeksów istnieją sposoby na usunięcie bloatu “na gorąco”, to dla tabel takie nie istnieją. Tu wkraczają różne zewnętrzne rozszerzenia: (wcześniej pg_reorg), , i inne. W ramach tego artykułu nie będę ich porównywał i opowiem tylko o pg_repack, które po pewnych przeróbkach używamy u siebie.
Jak działa pg_repack
Załóżmy, że mamy całkiem zwykłą tabelę – z indeksami, ograniczeniami i, niestety, bloatem. Pierwszym krokiem pg_repack tworzy tabelę logów, aby przechowywać dane o wszystkich zmianach podczas pracy. Wyzwalacz będzie replikował te zmiany przy każdym wstawieniu, aktualizacji i usunięciu. Następnie tworzona jest tabela podobna do pierwotnej pod względem struktury, ale bez indeksów i ograniczeń, aby nie spowalniać procesu wstawiania danych.
Następnie pg_repack przenosi dane z tabeli źródłowej do nowej tabela, automatycznie filtrując wszystkie nieaktualne wiersze, a następnie tworzy indeksy dla nowej tabeli. W trakcie wykonywania wszystkich tych operacji w tabeli dziennika gromadzą się zmiany.
Kolejnym krokiem jest przeniesienie zmian do nowej tabeli. Przeniesienie odbywa się w kilku iteracjach, a gdy w tabeli dziennika pozostaje mniej niż 20 rekordów, pg_repack przechwytuje ścisłą blokadę, przenosi ostatnie dane i zastępuje starą tabelę nową w systemowych tabelach Postgres. To jedyny i bardzo krótki moment w czasie, kiedy nie będziesz mógł pracować z tabelą. Po tym stara tabela i tabela z dziennikiem są usuwane, a w systemie plików zwalniane jest miejsce. Proces zakończony.
Teoretycznie wszystko wygląda świetnie, ale jak jest w praktyce? Przetestowaliśmy pg_repack bez obciążenia i pod obciążeniem, sprawdziliśmy jego działanie w przypadku przedwczesnego zatrzymania (krótko mówiąc, przez Ctrl+C). Wszystkie testy wypadły pozytywnie.
Wkrótce ruszyliśmy na produkcję – i tutaj wszystko poszło nie tak, jak się spodziewaliśmy.
Pierwsza porażka na produkcji
Na pierwszym klastrze otrzymaliśmy błąd o naruszeniu unikalnego ograniczenia:
$ ./pg_repack -t tablename -o id
INFO: rekapitulacja tabeli "tablename"
ERROR: zapytanie nie powiodło się:
ERROR: wartość klucza duplikatu narusza unikalne ograniczenie "index_16508"
DETAIL: Klucz (id, index)=(100500, 42) już istnieje.
To ograniczenie miało automatycznie generowaną nazwę index_16508 – zostało stworzone przez pg_repack. Na podstawie atrybutów, które wchodziły w jego skład, zidentyfikowaliśmy „nasze” ograniczenie, które mu odpowiada. Problem polegał na tym, że to nie jest całkowicie zwykłe ograniczenie, lecz opóźnione (), tzn. jego walidacja jest wykonywana później niż polecenie sql, co prowadzi do nieprzewidzianych konsekwencji.
Ograniczenia opóźnione: po co są i jak działają
Trochę teorii o ograniczeniach opóźnionych.
Rozważmy prosty przykład: mamy tabelę referencyjną samochodów z dwoma atrybutami – nazwą i kolejnym numerem samochodu w referencji.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Załóżmy, że musimy zamienić pierwszy i drugi samochód miejscami. Rozwiązanie „na prosto” – zaktualizować pierwszą wartość na drugą, a drugą na pierwszą:
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Ale przy wykonaniu tego kodu uzyskamy oczekiwane naruszenie ograniczenia, ponieważ kolejność wartości w tabeli jest unikalna:
[23305] BŁĄD: wartość duplikatu klucza narusza unikalny ograniczenie “uk_cars”
Szczegóły: Klucz (ord)=(2) już istnieje.
Jak zrobić to inaczej? Opcja pierwsza: dodać dodatkową zamianę wartości na porządek, który z pewnością nie istnieje w tabeli, na przykład “-1”. W programowaniu nazywa się to “wymianą wartości dwóch zmiennych przez trzecią”. Jedyną wadą tej metody jest dodatkowa aktualizacja.
Opcja druga: przemyśleć ponownie projekt tabeli, aby używać dla wartości porządku typu danych zmiennoprzecinkowego zamiast liczb całkowitych. Wtedy podczas aktualizacji wartości z 1, na przykład, na 2.5 pierwszy wpis automatycznie “stanie” pomiędzy drugim a trzecim. To rozwiązanie działa, ale ma dwa ograniczenia. Po pierwsze, nie nadaje się, jeśli wartość jest używana gdzieś w interfejsie. Po drugie, w zależności od dokładności typu danych, będziesz miał ograniczoną liczbę możliwych wstawień do momentu przeliczenia wartości wszystkich wpisów.
Opcja trzecia: ustawić ograniczenie na opóźnione, aby było sprawdzane tylko w momencie zatwierdzania:
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Ponieważ logika naszego początkowego zapytania gwarantuje, że w momencie zatwierdzania wszystkie wartości są unikalne, więc zostanie ono wykonane pomyślnie.
Przykład omówiony powyżej jest oczywiście bardzo syntetyczny, ale ujawnia ideę. W naszej aplikacji używamy opóźnionych ograniczeń do realizacji logiki, która odpowiada za rozwiązywanie konfliktów podczas jednoczesnej pracy użytkowników z współdzielonymi obiektami-widgetami na tablicy. Używanie takich ograniczeń pozwala nam uprościć kod aplikacji.
Ogólnie, w zależności od typu ograniczenia w Postgres istnieją trzy poziomy granularity ich sprawdzania: poziom wiersza, transakcji i wyrażenia.
Źródło:
CHECK i NOT NULL są zawsze sprawdzane na poziomie wiersza, dla pozostałych ograniczeń, jak można zobaczyć w tabeli, są różne opcje. Szczegóły można poczytać .
Podsumowując, opóźnione ograniczenia w pewnych sytuacjach prowadzą do bardziej czytelnego kodu oraz mniejszej liczby poleceń. Jednak wiąże się to z komplikacjami w procesie debugowania, ponieważ moment wystąpienia błędu i moment, kiedy się o nim dowiadujesz, są rozdzielone w czasie. Innym możliwym problemem jest to, że planner nie zawsze może stworzyć optymalny plan, jeśli w zapytaniu uczestniczy opóźnione ograniczenie.
Udoskonalenie pg_repack
Zrozumieliśmy, czym są opóźnione ograniczenia, ale jak mają się one do naszego problemu? Przypomnijmy sobie błąd, który wcześniej otrzymaliśmy:
$ ./pg_repack -t tablename -o id
INFO: rekapitulacja tabeli "tablename"
ERROR: zapytanie nie powiodło się:
ERROR: wartość klucza duplikatu narusza unikalne ograniczenie "index_16508"
DETAIL: Klucz (id, index)=(100500, 42) już istnieje.Pojawia się on w momencie kopiowania danych z tabeli logów do nowej tabeli. Wygląda to dziwnie, ponieważ dane w tabeli logów są commitowane razem z danymi z tabeli źródłowej. Jeśli spełniają ograniczenia tabeli źródłowej, to jak mogą naruszać te same ograniczenia w nowej tabeli?
Okazało się, że sedno problemu tkwi w poprzednim etapie działania pg_repack, na którym tworzone są tylko indeksy, ale nie ograniczenia: w starej tabeli istniało ograniczenie unikalne, a w nowej zamiast niego utworzono unikalny indeks.
Warto zauważyć, że jeśli ograniczenie jest zwykłe, a nie opóźnione, to utworzony zamiast niego unikalny indeks jest równoważny temu ograniczeniu, ponieważ unikalne ograniczenia w Postgres realizowane są poprzez tworzenie unikalnego indeksu. Jednak w przypadku opóźnionego ograniczenia zachowanie nie jest takie samo, ponieważ indeks nie może być opóźniony i zawsze jest sprawdzany w momencie wykonania polecenia sql.
Zatem istota problemu polega na „opóźnieniu” sprawdzenia: w tabeli źródłowej występuje ono w momencie commit, a w nowej – w momencie wykonania polecenia sql. Oznacza to, że musimy sprawić, aby kontrole były wykonywane identycznie w obu przypadkach: albo zawsze opóźnione, albo zawsze natychmiastowo.
Zatem jakie mieliśmy pomysły.
Utworzyć indeks analogiczny do deferred
Pierwszy pomysł to przeprowadzenie obu kontrolek w trybie natychmiastowym. Może to spowodować kilka fałszywych pozytywów w ograniczeniach, ale jeśli będzie ich niewiele, nie powinno to wpłynąć na użytkowników, ponieważ takie konflikty są dla nich naturalną sytuacją. Zdarzają się na przykład, gdy dwóch użytkowników zaczyna jednocześnie edytować ten sam widget, a klient drugiego użytkownika nie zdąży otrzymać informacji, że widget został już zablokowany do edycji przez pierwszego użytkownika. W takiej sytuacji serwer odpowiada drugiemu użytkownikowi odmową, a jego klient cofa zmiany i blokuje widget. Nieco później, gdy pierwszy użytkownik zakończy edycję, drugi otrzyma informację, że widget nie jest już zablokowany i będzie mógł powtórzyć swoje działanie.
Aby kontrole były zawsze w trybie pilnym, stworzyliśmy nowy indeks, podobny do oryginalnego opóźnionego ograniczenia:
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- uruchom pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;W środowisku testowym otrzymaliśmy tylko kilka oczekiwanych błędów. Sukces! Ponownie uruchomiliśmy pg_repack na produkcji i uzyskaliśmy 5 błędów w pierwszym klastrze w ciągu godziny. To akceptowalny wynik. Jednak już w drugim klastrze liczba błędów wzrosła wielokrotnie i musieliśmy zatrzymać pg_repack.
Dlaczego tak się stało? Prawdopodobieństwo wystąpienia błędu zależy od tego, jak wielu użytkowników pracuje jednocześnie z tymi samymi widgetami. Zdaje się, że w momencie pracy z danymi, które były przechowywane w pierwszym klastrze, było znacznie mniej konkurencyjnych zmian niż w pozostałych, więc po prostu mieliśmy „szczęście”.
Pomysł nie zadziałał. W tym momencie widzieliśmy dwa inne rozwiązania: przepisać nasz kod aplikacyjny, aby zrezygnować z opóźnionych ograniczeń, lub „nauczyć” pg_repack działać z nimi. Wybraliśmy to drugie.
Zamienić indeksy w nowej tabeli na opóźnione ograniczenia z tabeli źródłowej
Cel modyfikacji był oczywisty – jeśli tabela źródłowa ma opóźnione ograniczenie, to dla nowej należy stworzyć takie ograniczenie, a nie indeks.
Aby sprawdzić nasze zmiany, napisaliśmy prosty test:
- tabela z opóźnionym ograniczeniem i jednym zapisem;
- w pętli wstawiamy dane, które powodują konflikt z istniejącym zapisem;
- robimy aktualizację – dane już nie kolidują;
- wprowadzamy zmiany.
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;Oryginalna wersja pg_repack zawsze kończyła się błędem przy pierwszym wstawieniu, wersja poprawiona działała bez problemów. Świetnie.
Idziemy na produkcję i znowu otrzymujemy błąd na tej samej fazie kopiowania danych z tabeli log do nowej:
$ ./pg_repack -t tablename -o id
INFO: rekapitulacja tabeli "tablename"
ERROR: zapytanie nie powiodło się:
ERROR: wartość klucza duplikatu narusza unikalne ograniczenie "index_16508"
DETAIL: Klucz (id, index)=(100500, 42) już istnieje.Klasyczna sytuacja: w środowiskach testowych wszystko działa, a na produkcji — nie?!
APPLY_COUNT i złączenie dwóch partii
Zaczęliśmy analizować kod dosłownie linijka po linijce i odkryliśmy ważny moment: przepływ danych z tabeli log do nowej odbywa się partiami, stała APPLY_COUNT określała rozmiar partii:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* może być jeszcze kilka krotek, powtórz. */
...
}Problem w tym, że dane z pierwotnej transakcji, w której kilka operacji potencjalnie może naruszać ograniczenia, podczas przenoszenia mogą znaleźć się na styku dwóch partii – jedna połowa komend zostanie zrealizowana w pierwszej partii, a druga połowa – w drugiej. I tu jak się uda: jeśli komendy w pierwszej partii nic nie naruszają, wszystko jest w porządku, a jeśli naruszają – występuje błąd.
APPLY_COUNT wynosi 1000 zapisów, co wyjaśnia, dlaczego nasze testy przechodziły pomyślnie – nie obejmowały one przypadku 'styku partii'. Użyliśmy dwóch komend – insert i update, dlatego dokładnie 500 transakcji po dwie komendy zawsze mieściło się w partii i nie mieliśmy problemów. Po dodaniu drugiego update nasza poprawka przestała działać:
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; -- jeszcze jeden update
COMMIT;
END;
END LOOP;Zatem kolejne zadanie – dopilnować, aby dane z pierwotnej tabeli, które były zmieniane w jednej transakcji, trafiły do nowej tabeli również w ramach jednej transakcji.
Rezygnacja z batchowania
I znowu mieliśmy dwa rozwiązania do rozważenia. Pierwsze: zrezygnujmy w ogóle z podziału na paczki i przeprowadzimy transfer danych w jednej transakcji. Na korzyść tego rozwiązania przemawiała jego prostota – wymagane zmiany w kodzie były minimalne (zresztą w starszych wersjach pg_reorg działał właśnie w ten sposób). Ale jest problem – tworzymy długą transakcję, a to, jak wcześniej wspomniano, stanowi zagrożenie dla powstawania nowego bloat.
Drugie rozwiązanie – bardziej złożone, ale chyba bardziej poprawne: utworzyć w tabeli logów kolumnę z identyfikatorem transakcji, która dodała dane do tabeli. Wówczas podczas kopiowania danych będziemy mogli grupować je według tego atrybutu i zagwarantować, że powiązane zmiany będą przenoszone wspólnie. Paczka będzie tworzona z kilku transakcji (lub jednej dużej) i jej rozmiar będzie się różnić w zależności od tego, ile danych zostało zmienionych w tych transakcjach. Ważne jest, aby zauważyć, że ponieważ dane z różnych transakcji trafiają do tabeli logów w losowej kolejności, nie będzie już można odczytywać jej sekwencyjnie, jak to miało miejsce wcześniej. seqscan przy każdym zapytaniu z filtrowaniem po tx_id – to zbyt kosztowne, konieczny jest indeks, ale on również spowolni działanie metody z powodu dodatkowych kosztów związanych z jego aktualizacją. Generalnie, jak zawsze, trzeba z czymś się pogodzić.
Zatem postanowiliśmy, aby zacząć od pierwszej opcji, uznając ją za prostszą. Na początek należało zrozumieć, czy długa transakcja będzie rzeczywistym problemem. Ponieważ zasadniczy transfer danych ze starej tabeli do nowej również odbywa się w jednej długiej transakcji, pytanie przeszło w „jak bardzo zwiększymy tę transakcję?”. Czas trwania pierwszej transakcji zależy głównie od rozmiaru tabeli. Czas trwania nowej – od tego, ile zmian gromadzi się w tabeli w czasie transferu danych, tzn. od intensywności obciążenia. Uruchomienie pg_repack odbyło się w czasie minimalnego obciążenia serwisu, a objętość zmian była nieporównywalnie mała w porównaniu z pierwotną wielkością tabeli. Uznałem, że możemy zignorować czas nowej transakcji (w porównaniu – przeciętnie to 1 godz. i 2-3 minuty).
Eksperymenty były pozytywne. Uruchomienie na produkcji również. Dla zobrazowania – zdjęcie z rozmiarem jednej z baz po przetwarzaniu:
Ponieważ to rozwiązanie całkowicie nas usatysfakcjonowało, nie zdecydowaliśmy się na próby wprowadzenia drugiego, ale rozważamy możliwość omówienia tego z deweloperami rozszerzenia. Nasza bieżąca poprawka, niestety, nie jest jeszcze gotowa do publikacji, ponieważ rozwiązaliśmy problem tylko z unikalnymi odłożonymi ograniczeniami, a aby stworzyć pełnoprawną łatkę, musimy zapewnić wsparcie dla innych typów. Liczymy na to, że uda nam się to zrealizować w przyszłości.
Możesz się zastanawiać, dlaczego w ogóle zaangażowaliśmy się w tę historię z poprawą pg_repack, a nie zdecydowaliśmy się na jego odpowiedniki? W pewnym momencie też o tym myśleliśmy, ale pozytywne doświadczenie związane z jego używaniem wcześniej, na tabelach bez odłożonych ograniczeń, zmotywowało nas do spróbowania zrozumienia sedna problemu i jego naprawienia. Poza tym korzystanie z innych rozwiązań również wymaga czasu na przeprowadzenie testów, dlatego zdecydowaliśmy, że najpierw spróbujemy naprawić problem w nim, a jeśli zrozumiemy, że nie będziemy w stanie to zrobić w rozsądnym czasie, wtedy zaczniemy rozważać alternatywy.
Wnioski
Co możemy polecić na podstawie własnych doświadczeń:
- Monitoruj swój bloat. Na podstawie danych z monitoringu będziesz mógł zrozumieć, jak dobrze skonfigurowany jest autovacuum.
- Konfiguruj AUTOVACUUM, aby utrzymać bloat na dopuszczalnym poziomie.
- Jeśli bloat nadal rośnie i nie możesz go pokonać za pomocą narzędzi „z pudełka”, nie bój się korzystać z zewnętrznych rozszerzeń. Najważniejsze to dobrze je przetestować.
- Nie bój się dostosowywać zewnętrznych rozwiązań do swoich potrzeb – czasami może to być efektywniejsze, a nawet prostsze, niż zmiana własnego kodu.
Źródło: habr.com
