Dobre praktyki odnośnie zmian na bazie danych

Lock

Podczas pracy z bazą powinieneś mieć z tyłu głowy, że każda operacja (tak, SELECT też) na bazie danych powoduje locka - czyli zablokowanie jakiegoś obiektu przed innymi operacjami. Jest to wypadkowa dwóch kwestii:

• każda operacja w postgresie odbywa się w transakcji (nawet jeśli użytkownik nie wywołał komendy BEGIN),

• transakcje chcemy przetwarzać współbieżnie.

Postgres posiada bardzo dobrą dokumentację (cześć dotycząca tematu tutaj), w telegraficznym skrócie:

• Locki mogą pojawić się na różnych obiektach, Ciebie najbardziej będą interesować te na tabelach/rekordach.

• Skoro każda operacja powoduje pojawienie się locków, same w sobie nie stanowią problemu. Problem pojawia się, kiedy dwie transakcje mają konfliktowe locki - potrzebują dostępu do tego samego obiektu.

• Powyższe prowadzi nas do konkluzji, że kluczowy jest czas trwania locka:

  • lock spada z końcem transakcji - oznacza to, że jeśli np. otworzysz transakcje, wykonasz update, który potrwa 10 ms, ale potem wykonasz 8 innych operacji, które potrwają łącznie 2 min, to łącznie zalokujesz wiersze z pierwszej komendy na 2 min 10 ms.

Ogólne dobre praktyki

1. Najbezpieczniejszym miejscem na sprawdzanie, co w bazie siedzi, jest slave (replika bazy). Nie wykonasz na nim omyłkowego usunięcia danych i Twój SELECT niczego nie wybuchnie.

2. Zazwyczaj zmiany na bazie wprowadza się w ramach jakichś zmian w aplikacji. Jako mądry i rozsądny człowiek zapewne przetestowałeś swoją pracę, ale nie miałeś gdzie i jak np. oszacować ile zajmie wprowadzenie zmian na produkcyjnych danych. Przy dużych modyfikacjach do takich celów możemy powołać klona bazy i tam przeprowadzić testy.

3. Zaplanuj zmianę z wyprzedzeniem.

4. Zastanów się, czy do wdrożenia nie jest Ci potrzebny ktoś, kto w jakiś sposób będzie monitorował Twoje prace (możesz o asystę poprosić DBA) - szczególnie warto o tym pomyśleć przy zmianach dużych lub gdy są one wynikiem awarii.

5. Uproszczony algorytm każdej operacji bazodanowej:

   

   1. DOBRY MOMENT oznacza, że :

      1. planowane przez Ciebie zmiana nie koliduje czasowo z innymi zmianami / operacjami cyklicznymi,

      2. baza jest w dobrej formie - ma niski load, ze szczególnym uwzględnieniem stanu dysków i ramu,

      3. Ty jesteś w dobrej formie - wyspany i przytomny - rollback zmian bazodanowych często jest trudny a zazwyczaj bolesny,

      4. piątki i godziny między 17 a 9 nie są dobrym momentem na prace ad hoc,

      5. pamiętaj, że to kiedy wykonasz operację, jest tak samo istotne jak sama operacja. Często prace na bazach wymagają zaplanowanie okienka i przeprowadzenia ich w godzinach o niskim natężeniu ruchu (np. wieczornych lub pogańsko porannych).

6. Uwzględnij “obleganie“ tabeli - czyli to, jak bardzo jest ona używana w procesach systemowych. Pamiętaj, że tabela nie musi być duża, żeby jej niedostępność była bardzo bolesna - wystarczy wyobrazić sobie mikroskopijna tabelkę z konfiguracją systemu, która zniknie.

7. Jedną z “rzeczy”, bez której nie osiągniesz nic na bazie, jest… sieć. Szczególnie przy dłuższych pracach, dobrą praktyką jest połączenie się po ssh z serwerem bazodanowym, tam otwarcie screena i dopiero w nim uruchomienie właściwego skryptu.

8. Jeśli wbrew sugestii z punktu 2 postanowiłeś działać bez asysty, monitoruj swoje działania:

   1. poniższe zapytanie ustaw w pętli 1s (jeśli pracujesz z psql użyj \watch 1)

      WITH locks AS (

      SELECT DISTINCT pg_locks.pid, 1 AS blocking, datname, state, query_start

      FROM pg_locks LEFT JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid

      WHERE granted = TRUE AND pg_locks.pid NOT IN (SELECT pid FROM pg_locks WHERE NOT granted) AND pg_locks.pid != pg_backend_pid()

      UNION

      SELECT DISTINCT pg_locks.pid, 0 AS blocking, datname, state, query_start

      FROM pg_locks LEFT JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid

      WHERE NOT granted)

      SELECT

      sum(blocking) AS blocking_cnt,

      sum(CASE WHEN blocking = 0 THEN 1 ELSE 0 END) AS blocked_cnt,

      sum(CASE WHEN blocking = 1 AND state = 'idle in transaction' THEN 1 ELSE 0 END) AS blocking_idle_in_transaction_cnt,

      FLOOR(max(EXTRACT(EPOCH FROM now() - COALESCE(query_start, now())))) AS max_lock_duration

      FROM locks

      WHERE datname = '{#DBNAME}'; --{#DBNAME} - zamień na nazwę bazy

      Jeśli któraś z wartości zacznie skokowo rosnąć lub blocked_cnt > 0 - musisz przerwać swoje działania.

   2. pod kątem zasobów (htop):

      1. OOM killer nie zawsze jest taki mądry, żeby wiedzieć, że to Twój proces można ubić,

      2. Twoje zapytanie może bardzo mocno wykorzystywać powierzchnię dyskową (nagromadzenie WALi, pliki tymczasowe lub naturalnie - INSERT / UPDATE wielu rekordów). Gdy silnikowi braknie powierzchni dyskowej, gasi się w trybie immediately i kasuje wszystkie otwarte transakcje.

      3. WALe są archiwizowane poprzez gzip - im więcej zmian , tym więcej WALi, a w konsekwencji większe obciążenie dla CPU.

9. Po każdej pracy z tabelą (tabelami) powinniśmy użyć polecenia ANALYZE [nazwa_tabeli];. Zbiera ono statystyki obiektu (np. szacuje ilość żywych i martwych krotek), a poprzez to optymalizuje pracę planera. Nigdy nie szkodzi, a po wielu zabawach jest konieczny.

10. Bazy produkcyjne mają repliki:

    1. w przypadku replik binarnych, duże operacje mogą sprawić, że będą one wymagały synchronizacji,

    2. repliki logiczne będą bardzo wrażliwe na zmiany struktury, zadbaj o to, żeby Twoja zmiana dotyczyła także ewentualnych subscriberów.

    3. Jeśli zapytanie SELECT * FROM pg_stat_replication; zwróci NULL lub kolumna state będzie miała inną wartość niż 'streaming', zgłoś to DBA.

11. DROP TABLE / DELETE / TRUNCATE - zasada jest prosta: łatwiej dane usunąć niż je odzyskać, dlatego przygotuj się do tego wcześniej - więcej w szczegółach każdej z operacji.

12. Zawsze traktuj środowisko produkcyjne jak żywy, myślący i… dość mściwy organizm.

Zasady ogólne:

1. Zmiany muszą być przeprowadzane za pomocą odpowiedniego narzędzia CI/CD - aktualnie jest to Flyway.

2. Zanim trafią na produkcję, zmiany muszą być wprowadzone na wszystkich niższych środowiskach.

3. Zmiany muszą być przetestowane.

4. Niestety, nie ma reguły, która by mówiła co jest lepsze - modyfikacja istniejącej tabeli, czy dodanie nowej. Jest natomiast zaskakująca zależność między konsultowaniem zmian a wybuchaniem baz - im szersze konsultacje tym rzadsze wybuchy.

Dodanie tabeli

Przedyskutuj w zespole / z DBA celowość takiego działania - szczególnie, jeśli planujesz dodać tabelę do bazy innej niż podstawowe źródło danych Twojego systemu.

Sama operacja krótkofalowo nie zrobi nic złego, jednak w dłuższej perspektywie może mieć poważne konsekwencje. Jeśli na tabeli może pojawić się duży przyrost danych, skonsultuj to z DBA - możliwe, że będzie trzeba dodać zasobów lub zmodyfikować monitoring.

P.S. Pomyślałeś o partycjach?

Usuniecie tabeli

Bezpieczne wykonanie tej operacji trzeba rozłożyć na etapy:

1. zmień nazwę tabeli dodając sufix np.: *to_remove,

2. wykonaj HOT backup lub poproś o to DBA (pamiętaj jednak, że pg_dump agresywnie lockuje tabele),

3. odczekaj ~2 tygodnie,

4. usuń tabelę,

5. zarchiwizuj backup.

Modyfikacja tabeli - ALTER TABLE

W pewnym uproszczeniu i dla bezpieczeństwa możemy uznać, że wszystkie operacje tego typu spowodują lock na poziomie całej tabeli w trybie ACCESS EXCLUSIVE. Jak nazwa wskazuje, podczas trwania tej operacji żadne inne zapytanie nie będzie miało dostępu do tej tabeli (SELECT też nie).

Czas trwania operacji, a zatem i locka zależny jest od rozmiaru tabeli - ilość wierszy - i typu kolumn (oraz oczywiście od zasobów jakie ma przydzielone baza). Małe jednak nie znaczy nieważne - zalockowanie tabeli, która ma nawet 10 rekordów, ale bierze udział w kilku procesach, może bazę “wybuchnąć“.

Po tych operacjach również konieczne jest wykonanie ANALYZE.

UPDATE

To najbardziej zasobożerna operacja na danych. Update, znów w pewnym uproszczeniu i skrócie, to oznaczenie rekordu jako do usunięcia i utworzenie nowego. W praktyce powoduje tzw. bloat, spuchnięcie tabeli i indeksu, poprzez nagromadzenie martwych krotek:

Im więcej bloatu tym wolniej baza działa, najbardziej poszkodowane są indeksy. Po każdym większym UPDATE trzeba przeprowadzić REINDEX oraz wykonać VACUUM ANALYZE na tabeli (na wielu bazach ten proces jest monitorowany, trzeba wiec przynajmniej zgłosić, że coś takiego będzie miało miejsce).

Przy mniejszej ilości rekordów zadziała proces autovacuum, który usunie dane z dysku, ale nie odzyska wolnej przestrzeni i nie zaopiekuje się indeksami, ponieważ nie lockuje tabeli w sposób agresywny (nie chcielibyśmy przecież, żeby baza sama wybierała sobie czas okna serwisowego). Przy większych operacjach istnieje kilka sposobów na poradzenie sobie z tym problemem - od VACUUM FULL z oknem serwisowym, przez rozszerzenia, po zestawienie replikacji logicznej.

DELETE

Jak zostało wspomniane, usuwanie danych również wymaga konsultacji.

Delete nie usuwa fizycznie danych - działa podobnie jak UPDATE i również powoduje bloat.

Jeśli potrzebujesz usunąć większość danych z tabeli szybciej niż DELETE zadziała:

1. CREATE tmp_table AS SELECT * FROM table WHERE [warunki dla rekordów, które mają zostać - tu warto sobie stworzyć na nie indeks]. Tą cześć możemy wykonać przy działającym systemie, jeśli:

   1. mamy 100% pewność, że aplikacja nie modyfikuje danych w tej tabeli,

   2. modyfikacji jest naprawdę bardzo mało - wówczas trzeba założyć trigger after update, który będzie odkładał zmiany w trakcie działania powyższego SELECTa

2. TRUNCATE table - tę część realizujemy w oknie serwisowym

3. INSERT INTO table SELECT * FROM tmp_table - tę część na ogół również

Zastosowanie takiego rozwiązania daje nam dodatkową korzyść w postaci braku bloatu.

TRUNCATE

Jak zostało wspomniane, usuwanie danych również wymaga konsultacji.

Podchwytliwe polecenie - w teorii robi dokładnie to samo, co DELETE bez klauzuli WHERE, w praktyce jednak zupełnie inaczej to realizuje. Zamiast oznaczać rekordy, wykonuje kopię struktury tabeli, usuwa tabelę źródłową i udostępnia kopię pod starą nazwą. Z tego powodu używa innych (tych najbardziej agresywnych) locków, ale za to nie zostawia bloatu.