W referacie przedstawiono kilka podejść, które pozwalają śledzić wydajność zapytań SQL, gdy ich liczba sięga milionów dziennie, a kontrolowanych serwerów PostgreSQL — setki.
Jakie rozwiązania technologiczne pozwalają nam skutecznie przetwarzać taką ilość informacji i jak to ułatwia życie zwykłego programisty.
Dla kogo interesujący jest analiza konkretnych problemów oraz różne techniki optymalizacji zapytania SQL i rozwiązania typowych zadań DBA w PostgreSQL — można również na ten temat.
Nazywam się Kirill Borowikow, reprezentuję . Konkretnie specjalizuję się w pracy z bazami danych w naszej firmie.
Dziś opowiem wam, jak zajmujemy się optymalizacją zapytań, gdy musicie nie "rozgrzebywać" wydajność pojedynczego zapytania, ale rozwiązać problem masowo. Gdy zapytań jest miliony, a potrzebujecie znaleźć jakieś podejścia do rozwiązania tego dużego problemu.
W ogóle, «Tenzor» dla miliona naszych klientów to : korporacyjna sieć społecznościowa, rozwiązania do wideokonferencji, do obiegu dokumentów wewnętrznych i zewnętrznych, systemy księgowe i magazynowe,… Czyli taki „mega kombajn” do kompleksowego zarządzania biznesem, w którym znajduje się ponad 100 różnych projektów wewnętrznych.
Aby wszystkie one działały prawidłowo i rozwijały się — mamy 10 centrów rozwoju w całym kraju, w których pracuje ponad 1000 programistów.
Z PostgreSQL pracujemy od 2008 roku i zgromadziliśmy dużą ilość przetwarzanych danych — są to dane klientów, statystyczne, analityczne, dane z zewnętrznych systemów informacyjnych — ponad 400TB. Tylko "w produkcji" mamy około 250 serwerów, a łącznie monitorujemy około 1000 serwerów baz danych.
SQL — to język deklaratywny. Opisujesz nie "jak" coś ma działać, ale "co" chcesz otrzymać. DBMS lepiej wie, jak zrobić JOIN — jak połączyć twoje tabele, jakie warunki nałożyć, co przejdzie przez indeks, a co nie…
Niektóre DBMS przyjmują wskazówki: „Nie, te dwa tabele łącz w takiej kolejności”, ale PostgreSQL tak nie działa. To świadome stanowisko głównych deweloperów: „Lepiej poprawimy optymalizator zapytań, niż pozwolimy programistom korzystać z jakichkolwiek wskazówek.”
Jednak pomimo tego, że PostgreSQL nie umożliwia zarządzania sobą „zewnątrz”, doskonale pozwala zobaczyć, co dzieje się „wewnątrz”, gdy wykonujesz swoje zapytanie i gdzie pojawiają się problemy.
Ogólnie rzecz biorąc, z jakimi klasycznymi problemami zgłasza się zwykle programista [do DBA]? „Wykonaliśmy zapytanie i wszystko jest wolne, wszystko się zawiesza, coś się dzieje… Coś jest nie tak!”
Przyczyny są niemal zawsze te same:
- nieefektywny algorytm zapytania
Programista: „Teraz w SQL łączę 10 tabel za pomocą JOIN…”, i oczekuje, że jego warunki w magiczny sposób „odblokują się”, a on wszystko otrzyma szybko. Ale cuda się nie zdarzają, a każdy system przy takiej zmienności (10 tabel w jednym FROM) zawsze daje jakieś niedoskonałości. [] - nieaktualna statystyka
Moment ten jest szczególnie istotny dla PostgreSQL, gdy na serwer wprowadziłeś dużą ilość danych, wykonujesz zapytanie - a on „sekscan” po tabeli. Ponieważ wczoraj miało tam 10 rekordów, a dzisiaj 10 milionów, ale PostgreSQL o tym jeszcze nie wie, i musisz mu to zasugerować. [] - „wąskie gardło” pod względem zasobów
Postawiłeś dużą i obciążoną bazę danych na słabym serwerze, który nie ma wystarczającej pamięci dyskowej, RAM-u ani wydajności procesora. I to wszystko… Gdzieś jest sufit wydajności, powyżej którego już nie możesz skoczyć. - blokowania
To skomplikowany moment, ale jest on najbardziej istotny dla różnych modyfikujących zapytań (INSERT, UPDATE, DELETE) - to osobny, duży temat.
Uzyskiwanie planu
… A dla wszystkiego innego potrzebujemy planu! Musimy widzieć, co się dzieje wewnątrz serwera.
Plan wykonania zapytania dla PostgreSQL to drzewo algorytmu wykonania zapytania w postaci tekstowej. To właśnie ten algorytm, który w wyniku analizy przez planner został uznany za najbardziej efektywny.
Każdy węzeł drzewa to operacja: pobieranie danych z tabeli lub indeksu, budowanie bitmapy, łączenie dwóch tabel, unia, przecięcie lub różnica zbiorów. Wykonanie zapytania to przejście przez węzły tego drzewa.
Aby uzyskać plan zapytania, najprostszym sposobem jest wykonanie operatora EXPLAIN. Aby uzyskać wszystkie rzeczywiste atrybuty, czyli wykonać zapytanie na bazie - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
Zły moment: gdy go wykonujesz, dzieje się to „tu i teraz”, więc nadaje się tylko do lokalnego debugowania. Jeśli jednak masz jakiś serwer o wysokim obciążeniu, który jest pod silnym strumieniem zmian danych, i widzisz: „Ej! Tutaj mamy wolne wykonaniesię zapytania.” Pół godziny, godzinę temu — podczas gdy biegałeś i wyciągałeś to zapytanie z logów, wnosiłeś je z powrotem na serwer, cały twój zbiór danych i statystyki zmieniły się. Wykonujesz je, aby debugować — a ono wykonuje się szybko! I nie wiesz "dlaczego", dlaczego było wolno.
Aby zrozumieć, co dokładnie działo się w momencie, gdy zapytanie jest wykonywane na serwerze, mądrzy ludzie napisali . Jest on obecny praktycznie we wszystkich najpopularniejszych dystrybucjach PostgreSQL i można go po prostu aktywować w pliku konfiguracyjnym.
Jeśli zauważy, że jakieś zapytanie trwa dłużej, niż podałeś mu granicę, robi „zdjęcie” planu tego zapytania i zapisuje je razem w logu..
Wydaje się, że wszystko jest teraz w porządku; przechodzimy do logu i widzimy tam… [fragment tekstu]. Ale nic nie możemy o nim powiedzieć, poza tym, że to doskonały plan, ponieważ trwał 11 ms.
Wydaje się, że wszystko w porządku — ale nic nie jest jasne, co tak naprawdę się działo. Oprócz całkowitego czasu nie widzimy nic szczególnego. Bo patrzenie na taki „plądrowy” plain text jest w ogóle nieprzejrzyste.
Ale nawet jeśli to nieprzejrzyste, niewygodne, są znacznie poważniejsze problemy:
- W węźle wskazana jest suma zasobów całego poddrzewa pod nim. To znaczy, że po prostu nie można dowiedzieć się, ile konkretnie czasu zostało zużyte na tym Index Scan — nie można, jeśli pod nim istnieje jakieś zagnieżdżone warunki. Musimy dynamicznie sprawdzić, czy wewnątrz nie ma „dzieci” i warunkowych zmiennych, CTE — i odliczyć to wszystko „w głowie”.
- Drugi punkt: czas, który wskazany jest w węźle, to czas jednorazowego wykonania węzła.Jeśli ten węzeł był wykonany w wyniku, na przykład, pętli po rekordach tabeli, kilka razy, to w planie zwiększa się liczba loops — cykli tego węzła. Ale sam czas atomowego wykonania pozostaje w planie bez zmian. To znaczy, aby zrozumieć, ile czasu ten węzeł był wykonywany łącznie, trzeba jedno pomnożyć przez drugie — znowu „w głowie”.
W takiej sytuacji zrozumienie, kto jest najsłabszym ogniwem, jest praktycznie niemożliwe. Dlatego nawet sami deweloperzy w „instrukcji” piszą, że „Zrozumienie planu to sztuka, której trzeba się uczyć, doświadczenie…”.
Ale mamy 1000 deweloperów, i nie da się tego doświadczenia przekazać każdemu z nich. Ja, ty, on — wiedzą, a ktoś tam z boku — już nie. Może się nauczy, a może nie, ale musi pracować już teraz — a skąd ma wziąć to doświadczenie.
Wizualizacja planu
Dlatego zrozumieliśmy — aby poradzić sobie z tymi problemami, potrzebujemy dobrej wizualizacji planu.
Zaczęliśmy od przeszukania „rynku” — poszukajmy w internecie, co w ogóle istnieje.
Ale okazało się, że relatywnie „żywych” rozwiązań, które się nieco rozwijają, jest bardzo mało — dosłownie jedno: od Huberta Lubaczewskiego. W pole wejściowe „wrzucasz” tekstową reprezentację planu, on pokazuje tabelę z przeanalizowanymi danymi:
- własny czas realizacji węzła
- czas łączny w całym poddrzewie
- liczba rekordów, które zostały wydobyte, i która była statystycznie oczekiwana
- same ciało węzła
Ten serwis ma również możliwość dzielenia się archiwum linków. Wrzucasz tam swój plan i mówisz: „Hej, Wania, oto link, coś jest nie tak.”
Ale są też drobne problemy.
Po pierwsze, ogromna ilość „kopiowania i wklejania”. Bierzesz kawałek logu, wkładasz go tam, i znowu, i znowu.
Po drugie, brak analizy liczby odczytanych danych — tych samych buforów, które wyświetla EXPLAIN (ANALYZE, BUFFERS), tutaj tego nie widzimy. Po prostu nie potrafi ich analizować, rozumieć i z nimi pracować. Gdy czytasz dużo danych i rozumiesz, że możesz źle „rozłożyć się” na dysku i w pamięci podręcznej, ta informacja jest bardzo ważna.
Trzecim negatywnym punktem jest bardzo słabe rozwinięcie tego projektu. Komity są bardzo małe, dobrze jeśli raz na pół roku, a kod jest w Perlu.
Ale to wszystko to „lyryka”, z tym można żyć, ale jest jedna rzecz, która nas mocno odwróciła od tego serwisu. To błędy analizy Common Table Expression (CTE) i różnych dynamicznych węzłów takich jak InitPlan/SubPlan.
Jeśli wierzyć temu obrazkowi, to mamy łączny czas realizacji każdego pojedynczego węzła większy niż całkowity czas realizacji całego zapytania. Wszystko jest proste — z węzła CTE Scan nie odjęto czasu generowania tego CTE.Dlatego nie wiemy teraz, ile właściwie trwało samo skanowanie CTE.
Tutaj zrozumieliśmy, że czas napisać nasze własne — hurra! Każdy programista mówi: „Teraz napiszemy nasze, będzie super prosto!”
Wzięliśmy typowy stos dla usług internetowych: rdzeń na Node.js + Express, nałożyliśmy Bootstrap i dla ładnych diagramów — D3.js. A nasze oczekiwania zostały spełnione — pierwszy prototyp dostaliśmy w ciągu 2 tygodni:
- własny parser planu
To znaczy, że teraz możemy analizować każdy plan generowany przez PostgreSQL. - poprawna analiza dynamicznych węzłów — CTE Scan, InitPlan, SubPlan
- analiza rozkładu buforów — gdzie strony danych są odczytywane z pamięci, gdzie z lokalnej pamięci podręcznej, a gdzie z dysku
- uzyskaliśmy wizualizację
Aby nie kopać w logu we wszystko to „szukać”, ale od razu widzieć „najsłabsze ogniwo” na obrazku.
Otrzymaliśmy mniej więcej taki obrazek — od razu z podświetleniem składni. Zwykle nasi programiści nie pracują już z pełnym obrazem planu, ale z tym, co jest krótsze. Wszystkie liczby już sparseliśmy i odrzuciliśmy na lewo i prawo, a pośrodku zostawiliśmy tylko pierwszy wiersz, co to za węzeł: CTE Scan, generacja CTE lub Seq Scan dla jakiejś tabeli.
To skrócone przedstawienie nazywamy szablonem planu.
Co jeszcze byłoby wygodne? Byłoby wygodne widzieć, jaka część całkowitego czasu przypada na który węzeł — po prostu „przykleiliśmy” to z boku wykres kołowy.
Najedź na węzeł i widzisz — okazuje się, że Seq Scan zajmował mniej niż jedną czwartą całkowitego czasu, a pozostałe 3/4 zajmował CTE Scan. Horrendalnie! To mała uwaga na temat „szybkości” CTE Scan, jeśli ich aktywnie używasz w swoich zapytaniach. Nie są zbyt szybkie — przegrywają nawet z zwykłym skanowaniem tabel.
Ale zwykle takie wykresy są ciekawsze i bardziej skomplikowane, kiedy najedziemy na segment i widzimy, że na przykład ponad połowa całkowitego czasu została „skonsumowana” przez jakiś Seq Scan. A wewnątrz był jakiś filtr, mnóstwo rekordów zostało odrzuconych… Można ten obrazek wysłać od razu do programisty i powiedzieć: „Wania, u ciebie wszystko jest źle! Rozwiąż to, sprawdź — coś jest nie tak!”
Oczywiście, nie obeszło się bez „pułapek”.
Pierwszym problemem, na który natrafiliśmy, było zaokrąglanie. Czas węzła każdego poszczególnego w planie podawany jest z dokładnością do 1μs. Gdy liczba cykli węzła przekracza na przykład 1000 — po wykonaniu PostgreSQL 'dzieli z dokładnością do', to przy odwrotnym obliczaniu otrzymujemy łączny czas 'gdzieś między 0.95ms a 1.05ms'. Gdy czas jest mierzony w mikrosekundach — to jeszcze nic, ale gdy już w [milisekundach] — trzeba przy 'rozwiązywaniu' zasobów według węzłów planu uwzględnić tę informację 'kto ile zużył'.
Drugim, bardziej skomplikowanym zagadnieniem, jest przydzielenie zasobów (tych samych buforów) do dynamicznych węzłów. Kosztowało nas to dodatkowe 4 tygodnie po pierwszych 2 tygodniach pracy nad prototypem.
Taki problem można łatwo uzyskać — tworzymy CTE i w niej coś rzekomo odczytujemy. W rzeczywistości PostgreSQL jest 'inteligentny' i nic tam bezpośrednio nie odczyta. Później bierzemy z niej pierwszy wiersz, a do niego — sto pierwszy z tej samej CTE.
Patrzymy na plan i rozumiemy — dziwne, zużyliśmy 3 bufory (strony danych) w Seq Scan, jeszcze 1 w CTE Scan, a kolejne 2 w drugim CTE Scan. Więc jeśli wszystko po prostu zsumujemy, otrzymamy 6, ale z tabeli przeczytaliśmy tylko 3! CTE Scan niczego nie odczytuje, a pracuje bezpośrednio z pamięcią procesu. Więc coś tu zdecydowanie jest nie tak!
W rzeczywistości okazuje się, że te 3 strony danych, które zostały zażądane w Seq Scan, najpierw 1 zażądał pierwszy CTE Scan, a potem 2, i przeczytano mu jeszcze 2. Tak więc w sumie przeczytano 3 strony danych, a nie 6.
I ten obrazek doprowadził nas do zrozumienia, że wykonanie planu — to już nie drzewo, a po prostu jakiś acykliczny graf. Oto otrzymaliśmy mniej więcej taki diagram, abyśmy rozumieli 'co skąd przybyło'. Więc tutaj stworzyliśmy CTE z pg_class, i dwa razy ją zażądaliśmy, a prawie cały czas spędziliśmy na gałęzi, kiedy prosiliśmy ją drugi raz. Oczywiste jest, że przeczytanie 101. rekordu jest znacznie droższe niż po prostu 1 z tabeli.
Na moment odetchnęliśmy. Powiedzieliśmy: 'Teraz, Neo, znasz kung fu! Teraz nasze doświadczenie jest na twoim ekranie. Teraz możesz z niego korzystać.'
Konsolidacja logów
Nasi programiści w liczbie 1000 odetchnęli z ulgą. Ale my wiedzieliśmy, że mamy tylko setki „bojowych” serwerów, a to całe „copy-paste” ze strony programistów jest bardzo niewygodne. Zrozumieliśmy, że musimy to samodzielnie zebrać.
W ogóle, jest już dostępny moduł, który potrafi gromadzić statystyki, jednak również trzeba go aktywować w konfiguracji — to jest . Ale nie spełnił naszych oczekiwań.
Po pierwsze, tym samym zapytaniom w różnych schematach w ramach jednej bazy przypisuje różne QueryId. To znaczy, jeśli najpierw zrobimy SET search_path = '01'; SELECT * FROM user LIMIT 1;, a potem SET search_path = '02'; i zrobimy takie samo zapytanie, to w statystykach tego modułu będą różne wpisy, a ja nie będę mógł zebrać ogólnej statystyki dotyczącej tego profilu zapytania, bez uwzględniania schematów.
Drugi problem, który uniemożliwił jego użycie — brak planów. To znaczy, plan nie istnieje, istnieje tylko samo zapytanie. Widzimy, co spowalniało, ale nie rozumiemy, dlaczego. I tutaj wracamy do problemu szybko zmieniającego się zestawu danych.
I ostatni problem — brak „faktów”. To znaczy nie można odwołać się do konkretnego wystąpienia wykonania zapytania — nie ma go, jest tylko zgrubna statystyka. Z tym można choćby pracować, ale jest to bardzo trudne.
Dlatego postanowiliśmy walczyć z „copy-pastą” i zaczęliśmy pisać kolektor.
Kolektor łączy się przez SSH, „tworzy” za pomocą certyfikatu zabezpieczone połączenie z serwerem z bazą i tail -F „przypina się” do logu. W ten sposób, w tej sesji otrzymujemy pełne „lustro” całego pliku dziennika, który generuje serwer. Obciążenie samego serwera jest przy tym minimalne, ponieważ niczego nie analizujemy, po prostu lustrzujemy ruch.
Ponieważ już zaczęliśmy pisać interfejs w Node.js, kontynuowaliśmy pisanie kolektora również na nim. I ta technologia się sprawdziła, ponieważ do pracy z mało sformatowanymi danymi tekstowymi, którymi są logi, bardzo wygodnie jest używać JavaScript. A sama infrastruktura Node.js jako platforma backendowa umożliwia łatwe i wygodne zarządzanie połączeniami sieciowymi oraz ogólnie jakimiś strumieniami danych.
W związku z tym, "ciągniemy" dwa połączenia: pierwsze, aby "nasłuchiwać" samo logi i je pobierać, a drugie — aby okresowo pytać bazę. "A oto w logu pojawiło się, że tabela z oid 123 jest zablokowana", ale deweloperowi to nic nie mówi, i dobrze byłoby zapytać bazę "A co właściwie oznacza OID = 123?" Tak więc okresowo pytamy bazę o to, czego jeszcze nie wiemy.
"Tylko jedno pominąłeś, jest rodzaj słoniowatych pszczół!.." Zaczynaliśmy rozwijać ten system, gdy chcieliśmy monitorować 10 serwerów. Najbardziej krytycznych w naszym rozumieniu, na których pojawiały się jakieś problemy, z którymi trudno było się zmierzyć. Ale w trakcie pierwszego kwartału otrzymaliśmy na monitoringu setkę — ponieważ system "wchodził", wszyscy chcieli, wszystkim było wygodnie.
Wszystko to trzeba składać, strumień danych jest duży, aktywny. Właściwie, co monitorujemy, z czym potrafimy sobie poradzić — to i wykorzystujemy. Używamy również PostgreSQL jako magazynu danych. A nie ma nic szybszego, żeby "lać" w niego dane, niż operator. COPY jeszcze nie ma.
Ale po prostu "lać" dane — to nie do końca nasza technologia. Ponieważ jeśli na setce serwerów zachodzi około 50 tys. zapytań na sekundę, to generuje wam to 100-150 GB logów dziennie. Dlatego musieliśmy ostrożnie "piłować" bazę.
Po pierwsze, zrobiliśmy sekcjonowanie według dni, ponieważ, szczerze mówiąc, nikogo nie interesuje korelacja między dniami. Jaka różnica, co miałeś wczoraj, jeśli dziś w nocy wdrożyłeś nową wersję aplikacji — i już jest jakaś nowa statystyka.
Po drugie, nauczyliśmy się (musieliśmy) bardzo, bardzo szybko pisać za pomocą COPY. To znaczy nie tylko COPY, ponieważ jest szybszy niż INSERT, ale jeszcze szybciej.
Trzeci punkt — musieliśmy zrezygnować z triggerów, a co za tym idzie, z kluczy obcych. To znaczy, nie mamy całkowicie integralności referencyjnej. Ponieważ jeśli macie tabelę, na której znajduje się para FK, i mówicie w strukturze bazy danych, że "oto wpis z logu odnosi się przez FK, na przykład, do grupy wpisów", to kiedy ją wstawiasz, PostgreSQL nie ma innego wyjścia, jak tylko wziąć i uczciwie wykonać SELECT 1 FROM master_fk1_table WHERE ... z tym identyfikatorem, który próbujesz wstawić — po prostu, aby sprawdzić, czy ten wpis tam jest, żebyś nie "złamał" swoim wstawieniem tego klucza obcego.
Otrzymujemy zamiast jednego wpisu w docelowej tabeli i jej indeksów, jeszcze dodatkowo odczyty ze wszystkich tabel, do których się odnosi. A to zupełnie nie jest nam potrzebne — naszym celem jest zapisanie jak największej liczby danych tak szybko, jak to możliwe, przy minimalnym obciążeniu. Więc FK — precz!
Następna kwestia — agregacja i haszowanie. Początkowo były one u nas realizowane w bazie danych — to wygodne, kiedy w momencie przyjścia wpisu można to wykonać w jakiejś tabeli. „plus jeden” bezpośrednio w wyzwalaczu. Dobrze, wygodnie, ale źle z innego powodu — wstawiasz jeden wpis, a jesteś zmuszony przeczytać i zapisać coś jeszcze z innej tabeli. Co więcej, nie tylko przeczytać i zapisać — musisz zrobić to za każdym razem.
A teraz wyobraźcie sobie tabelę, w której po prostu liczycie liczbę zapytań, które przeszły przez konkretny host: +1, +1, +1, ..., +1. A to w zasadzie nie jest potrzebne — to wszystko można sumać w pamięci na kolektorze i wysłać do bazy za jednym razem. +10.
Tak, w przypadku jakichkolwiek problemów może dojść do "rozsypania się" logicznej integralności, ale to praktycznie nierealny przypadek — ponieważ macie normalny serwer, w nim bateria w kontrolerze, macie dziennik transakcji, dziennik w systemie plików… Ogólnie, to nie jest warte zachodu. Nie warto tej utraty wydajności, którą uzyskujecie dzięki wyzwalaczom/FK, tych kosztów, które ponosicie w tym przypadku.
To samo dotyczy haszowania. Przybywa do was pewne zapytanie, obliczacie w bazie identyfikator, zapisujecie go w bazie i wszystkim potem go podajecie. Wszystko jest w porządku, dopóki w momencie zapisu nie przyjdzie drugi chętny zapisać ten sam — wtedy pojawi się blokada, a to już jest źle. Dlatego, jeśli możecie przenieść generację jakichś ID na klienta (względem bazy), lepiej to zrobić.
Idealnie nadaje się do wykorzystania MD5 z tekstu — zapytania, planu, szablonu,… Obliczamy to po stronie kolektora i "wlewamy" do bazy już gotowy ID. Długość MD5 i codzienne segmentowanie pozwalają nam nie martwić się o możliwe kolizje.
Jednak żeby to wszystko szybko zapisać, musieliśmy zmodyfikować samą procedurę zapisu.
Jak zwykle zapisujemy dane? Mamy jakiś zestaw danych, dzielimy go na kilka tabel, a potem używamy COPY — najpierw do pierwszej, potem do drugiej, do trzeciej… To nie jest wygodne, ponieważ wydaje się, że piszemy jeden strumień danych w trzech krokach, co jest nieprzyjemne. Czy można to zrobić szybciej? Można!
Wystarczy, że rozłożymy te strumienie równolegle do siebie. Oznacza to, że błędy, zapytania, szablony, blokady… pojawiają się w osobnych strumieniach — i my zapisujemy to wszystko równolegle. Wystarczy utrzymywać na stałe otwarty kanał COPY do każdej osobnej docelowej tabeli.
To znaczy, że kolektor zawsze ma strumień, do którego mogę zapisać potrzebne mi dane. Ale aby baza danych zobaczyła te dane, a ktoś nie pozostawał w blokadzie, czekając na ich zapisanie, COPY trzeba przerywać z określoną częstotliwością. Najbardziej efektywny dla nas okazał się okres około 100 ms — zamykamy i od razu otwieramy ponownie tę samą tabelę. A jeśli jeden strumień nie wystarcza w przypadku pewnych szczytów, to robimy pulowanie do określonego limitu.
Dodatkowo ustaliliśmy, że dla takiego profilu obciążenia wszelkie agregacje, kiedy zapisy są zbierane w pakiety — to zło. Klasyczne zło to INSERT ... VALUES i dalej 1000 rekordów. Ponieważ w tym momencie pojawia się szczyt zapisu na nośniku, a wszyscy inni, którzy próbują coś zapisać na dysku, będą czekać.
Aby pozbyć się takich anomalii, po prostu nie agreguj niczego, nie buforuj w ogóle. A jeśli buforowanie na dysk jednak występuje (na szczęście, Stream API w Node.js pozwala to zauważyć) — odłóż to połączenie. Gdy otrzymasz zdarzenie, że jest znowu wolne — zapisz w nim z nagromadzonej kolejki. A póki jest zajęte — weź następne, wolne z puli i zapisz w nim.
Przed wdrożeniem takiego podejścia do zapisu danych mieliśmy około 4K operacji zapisu, a tym sposobem zmniejszyliśmy obciążenie czterokrotnie. Teraz wzrosło jeszcze sześć razy dzięki nowym widocznych bazom — do 100 MB/s. I teraz przechowujemy logi za ostatnie 3 miesiące w objętości około 10-15 TB, mając nadzieję, że przez trzy miesiące każdy programista jest w stanie rozwiązać jakikolwiek problem.
Rozumiemy problemy
Jednak zebranie wszystkich tych danych to dobrze, przydatne, odpowiednie, ale mało — trzeba je zrozumieć. Ponieważ to miliony różnych planów dziennie.
Miliony są niezarządzalne, najpierw trzeba zrobić „mniej”. Po pierwsze, trzeba zdecydować, jak to „mniej” będziecie organizować.
Wydzieliliśmy dla siebie trzy kluczowe punkty:
- kto ten żądanie wysłał
To znaczy, z jakiej aplikacji to „przyleciało”: interfejs webowy, backend, system płatności czy coś innego. - gdzie to się stało
Na którym konkretnym serwerze. Ponieważ jeśli macie pod jedną aplikacją kilka serwerów, a nagle jeden „zawiódł” (bo „dysk się zepsuł”, „pamięć wyciekła”, jakiś inny problem), trzeba się konkretnie odwołać do serwera. - jak właśnie objawiała się problem w tym czy innym planie
Aby zrozumieć „kto” wysłał nam żądanie, korzystamy z standardowego narzędzia — ustawiamy zmienną sesyjną: SET application_name = '{bl-host}:{bl-method}'; — przekazujemy nazwę hosta logiki biznesowej, z którego pochodzi żądanie, oraz nazwę metody lub aplikacji, która je zainicjowała.
Po tym, jak przekazaliśmy „gospodarza” żądania, trzeba go wyświetlić w logu — w tym celu konfigurujemy zmienną log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Kto jest zainteresowany, może , co to wszystko znaczy. Okazuje się, że w logu widzimy:
- czas
- identyfikatory procesu i transakcji
- nazwę bazy
- IP tego, kto wysłał to żądanie
- i nazwę metody
Dalej zrozumieliśmy, że nie ma zbyt dużego sensu badać korelacji jednego żądania między różnymi serwerami. Rzadko zdarza się sytuacja, gdy jedna aplikacja „zawodzi” zarówno tu, jak i tam. Ale nawet jeśli tak jest — spójrzcie na którykolwiek z tych serwerów.
Otóż, przekroju „jeden serwer — jeden dzień” okazał się wystarczający do jakiejkolwiek analizy.
Pierwszy przekrój analityczny — to ów „szablon” — skrócona forma przedstawienia planu, oczyszczona ze wszystkich wskaźników liczbowych. Drugi przekrój — aplikacja lub metoda, a trzeci — to konkretny węzeł planu, który wywołał u nas problemy.
Kiedy przeszliśmy od konkretnych instancji do szablonów, zyskaliśmy od razu dwie przewagi:
- znaczące zmniejszenie liczby obiektów do analizy
Musimy rozwiązywać problemy już nie z tysiącami żądań lub planów, ale z dziesiątkami szablonów. - oś czasu
Podsumowując "fakty" w ramach określonego kontekstu, można zobaczyć ich występowanie w ciągu dnia. W tym momencie można zrozumieć, że jeśli dany wzór występuje co godzinę, a powinien codziennie, warto zastanowić się, co poszło nie tak — kto i dlaczego go wywołuje, może w ogóle nie powinno go tu być. To kolejny nienumeryczny, czysto wizualny sposób analizy.
Pozostałe metody opierają się na wskaźnikach, które wyciągamy z planu: ile razy wystąpił dany wzór, całkowity i średni czas, ile danych odczytano z dysku, a ile z pamięci...
Na przykład przychodzisz na stronę analizy hosta, patrzysz — coś zaczyna zbyt dużo odczytywać z dysku. Dysk na serwerze nie wyrabia — a kto z niego korzysta?
Możesz posortować według dowolnej kolumny i zdecydować, z czym teraz będziesz się zajmować — z obciążeniem procesora, dysku, czy z ogólną liczbą zapytań... Posortowałeś, sprawdziłeś "najlepsze" i naprawiłeś — wdrożyłeś nową wersję aplikacji.
Natychmiast możesz zobaczyć różne aplikacje, które korzystają z tego samego wzoru od zapytania typu SELECT * FROM users WHERE login = 'Vasya'. Frontend, backend, przetwarzanie... I zaczynasz się zastanawiać, po co przetwarzaniu odczytywać użytkownika, jeśli nie wchodzi w interakcję z nim.
Odwrotna metoda — zobaczyć, co robi aplikacja. Na przykład frontend — to, to, to oraz to raz na godzinę (właśnie linia czasu pomaga). I pojawia się pytanie — wydaje się, że nie jest to zadanie frontendu, by coś robić raz na godzinę…
Po jakimś czasie zrozumieliśmy, że brakuje nam zagregowanej statystyki w kontekście węzłów planu. Wyodrębniliśmy z planów tylko te węzły, które coś robią z danymi samych tabel (czytają/zapisują je według indeksu czy nie). W zasadzie, w stosunku do poprzedniego obrazu dodawany jest tylko jeden aspekt — ile rekordów ten węzeł nam przyniósł, a ile odrzucił (Rows Removed by Filter).
Nie masz odpowiedniego indeksu na tabeli, wysyłasz do niej zapytanie, ono nie korzysta z indeksu, przechodzi w Seq Scan... wszystkie rekordy, oprócz jednego, zostały odfiltrowane. A po co ci w ciągu doby 100M odfiltrowanych rekordów, lepiej byłoby zbudować indeks?
Analizując wszystkie plany węzłów, zrozumieliśmy, że istnieją pewne typowe struktury w planach, które z bardzo dużym prawdopodobieństwem wyglądają podejrzanie. I dobrze by było zasugerować programiście: „Kolego, tu najpierw czytasz według indeksu, potem sortujesz, a następnie tniesz” — zazwyczaj jest tam jeden wpis.
Wszyscy, którzy pisali zapytania o takim wzorze, na pewno się z tym spotkali: „Daj mi ostatnie zamówienie od Wasy, jego datę”. A jeśli nie masz indeksu według daty, lub w używanym indeksie nie ma daty, to na takie właśnie „grabie” wdepniesz.
Ale przecież wiemy, że to „grabie” — więc czemu by od razu nie zasugerować programiście, co powinien zrobić. Otwierając teraz plan, nasz programista od razu widzi ładny obrazek z podpowiedziami, które mówią mu: „Masz problemy tutaj i tutaj, a rozwiązania są takie i takie.”
W rezultacie, ilość doświadczenia, które było potrzebne do rozwiązania problemów na początku i teraz, spadła wielokrotnie. Taki właśnie narzędzie stworzyliśmy.
Źródło: habr.com
