Problemy wydajnościowe SQL. Od czego zacząć?

Relacyjna baza danych to podstawowy typ bazy danych używany w systemach informacyjnych w IT. Co robić, gdy nagle pojawiają się z nią problemy? Można złożyć wypowiedzenie, można przenieść wszystko na MongoDB, można też zobaczyć na spokojnie, w czym jest problem, naprawić go i przy okazji lepiej zrozumieć, jakie są mocne i słabe strony tych baz danych.

Przedstawię proces analizy problemów oraz kilka najczęstszych przyczyn ich występowania. Opiszę też, jak można zacząć je atakować. Temat jest na tyle szeroki, że napisano o tym wiele książek, a ten artykuł to zaledwie wprowadzenie. Od razu przyznaję, że największe doświadczenie mam w optymalizowaniu MySQL i to o nim będę tu głównie pisał. Ale te same wskazówki mają zastosowanie do innych relacyjnych baz danych. Większość ma podobne koncepty. Będzie też parę odwołań do systemów unixowych. Zakładam, że czytelnik ma podstawową wiedzę jeżeli chodzi o SQL.

I/O vs CPU

Gdy baza danych zaczyna się poddawać, problem najczęściej jest widoczny z zewnątrz - albo jako wysycenie zasobów I/O albo CPU. Skąd więc biorą się „niedostatki” w zasobach wejścia/wyjścia?

Zazwyczaj pojawiają się wtedy, gdy zbyt często sięgamy po dane z dysku.

Większość silników baz danych zapisuje dane na dysku twardym. Współcześnie używane SSD oferują o rząd wielkości wolniejszy transfer danych w porównaniu do RAM. Poza tym czas samego dostępu do danych na dysku - nawet SSD - jest wielokrotnie dłuższy, niż dostęp do pamięci.

Sytuacja wygląda dużo gorzej, gdy serwer bazy danych korzysta z HDD.

Co prawda transfer może dorównywać temu z SSD, ale ciężko będzie o zbliżone czasy dostępu. Bazy danych minimalizują jak tylko mogą potrzebę sięgania do danych na dysku. Np. silnik InnoDB oferuje buffer pool, w którym trzyma przede wszystkim indeksy, ale też cache’uje fragmenty tabel - o ile uzna, że są wystarczająco często używane. Należy też pamiętać, że przy zapisie również trzeba dotknąć dysku i tu może pojawić się taki sam kłopot.

Duży koszt ze względu na I/O to tworzenie tabel tymczasowych.

Pojawiają się one wtedy, gdy baza nie potrafi wykonać danego działania na istniejącej tabeli czy indeksie i tworzy w pamięci pomocniczy byt, by przygotować wyniki. W dokumentacji MySQL jest bardzo ładny artykuł na ten temat. Dygresja: MySQL da się lubić między innymi z tego właśnie powodu - na dowolny temat związany z wydajnością czy ogólnie tym, jak on działa, jest ładny artykuł w oficjalnej dokumentacji.

Problemy z CPU natomiast biorą się przeważnie z niezoptymalizowanych zapytań.

Ogólnie rzecz biorąc większość implementacji SQL oraz silników baz danych jest bardzo dobra w wyciąganiu danych i nieco gorsza w ich przetwarzaniu. Przetwarzanie oznacza tu intensywne łączenie tabel, obróbkę pól, sortowanie na podstawie wyniku agregacji czy stosowanie wielu podzapytań. Tu temat jest prostszy niż z I/O, bo oznacza, że trzeba po prostu uprościć zapytania. W dużej części jednak obydwa te problemy będą się nakładać.

Jakie są objawy?

W idealnym świecie masz monitoring i widzisz, jak zmienia się użycie procesora, pamięci oraz stale śledzisz wydajność kluczowych zapytań. Stąd obserwujesz, jak zmienia się obciążenie i reagujesz w porę na zbliżające się zagrożenia. Ale niestety częściej zauważysz, że serwer zaczyna opowiadać bardzo długo albo w ogóle przestaje być dostępny.

To bardzo nieprzyjemna cecha problemów z wydajnością - im bliżej limitu, tym gorzej wszystko działa. Wtedy nawet proste zapytanie SELECT … FROM … LIMIT n; może zająć kilkanaście ms. To wina wysyconych zasobów, o które rywalizuje coraz więcej procesów.

Najłatwiej ustalić, jakiego rodzaju to jest problem, gdy on właśnie zachodzi. Wtedy odpalenie top (zakładając oczywiście, że dany serwer jest unixowy) jest bardzo pomocne:

top - 22:15:01 up 35 days, 13:36,  1 user,  load average: 0.00, 0.01, 0.05

Tasks:  87 total,   3 running,  84 sleeping,   0 stopped,   0 zombie

%Cpu(s):  5.0 us,  1.7 sy,  0.0 ni, 93.0 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st

Interesuje nas rząd %Cpu(s). Na powyższym przykładzie wszystko jest ok - większość czasu procesora spędzane jest na obrotach jałowych (93% idling). Wysokie %us (procent czasu procesora zużyty przez programy użytkownika) wskazuje na problemy z CPU, wysokie %wa (procent czasu procesora zużyty na czekanie na I/O) wskazuje na problemy z I/O. Gdy w czołówce kolumny %CPU znajduje się proces bazy danych, to mamy tak zwany trafiony zatopiony.

Śledztwo

Możemy iść dalej - do konsoli bazy danych.

Warto podejrzeć, jakie zapytania w tym momencie sprawiają problem. W MySQL będzie to przez SHOW FULL PROCESSLIST, a w PSQL SELECT * FROM pg_stat_activity. Jeżeli jakieś zapytanie ewidentnie „zawiesiło się”, warto je ubić, co krótkoterminowo pomoże w odkorkowaniu serwera.

Przejrzenie tej listy wstępnie pokaże też, gdzie jest problem. Jednak uzyskanie pełniejszego obrazu sytuacji umożliwi przeanalizowanie slow loga. Jest to plik, w którym baza danych zapisuje zapytania, których wykonanie trwało więcej, niż określony w konfiguracji próg. Tu już można zobaczyć, jakie typy zapytań sprawiają problemy. Gorzej, gdy slow log jest wyłączony - wtedy należy zmienić konfigurację, by wykorzystać tę funkcję.

Ok - mamy powolną kwerendę - i co teraz?

Musimy mieć środowisko, na którym problem się reprodukuje. Warto więc zrobić sobie tzw. dump bazy - tylko pamiętajcie o wyczyszczeniu danych wrażliwych! Gdy załadujemy tak zrzuconą bazę lokalnie, mamy doskonałe środowisko do testowania. Niektórzy lubią jeszcze instalować narzędzia typu MySQL Workbench, by nieco ułatwić cały proces - dzięki takim narzędziom można sobie szybko podejrzeć schemat tabel, edytować i testować kolejne zapytania. Tu zaczyna się właściwa zabawa.

Najczęstsze przyczyny wolnych zapytań

Zdecydowanym numerem jeden jest brak lub złe indeksowanie pól.

By przekonać się, czy w tym właśnie tkwi problem, należy podejrzeć plan wykonania zapytania. A robi się to przy użyciu EXPLAIN. Składnia jest prosta: EXPLAIN {YOUR_QUERY_HERE}. Wynikiem jest rezultat, jaki zwraca jednostka zwana optimizer/planner, która zajmuje się przygotowaniem możliwie optymalnego planu wykonania zapytania. Każdy wiersz to kolejny etap wykonania query.

W przypadku prostego SELECT * FROM table_name;, będzie to jeden wiersz, ale gdy już zaczniemy łączyć tabele lub robić subquery, pojawi się ich więcej. Najbardziej przydatne na tym etapie są kolumny possible_keys i key. W kolumnie possible_keys są wylistowane wszystkie indeksy, jakie mogły posłużyć - czy to do operacji WHERE, czy to do JOIN. W key powinien być wybrany klucz. Jeżeli w tej kolumnie jest pusto, to należy zadbać, aby pojawił się tam się wpis, czyli utworzyć właściwy indeks. Dość istotne jest też pole extra, w którym są inne uwagi do wykonania query. Np. sortowanie po polu, które nie ma indeksu zaowocuje uwagą Using filesort. EXPLAIN produkuje bardzo wiele różnego typu uwag i w tym artykule nie będę się zagłębiać w każdą z nich, zamiast tego proponuję „zapytanie się Googla” co sądzi na temat każdej z nich (albo przeczytanie artykułu o formacie wyjściowym EXPLAIN). W zasadzie poprawnie zaindeksowana baza danych wystarcza w znakomitej większości wypadków.

Kolejną przyczyną może być wyciąganie zbyt dużej ilości danych na raz.

Tutaj znowu przychodzi nam z pomocą EXPLAIN, gdzie wartość kolumny rows, znacznie przekraczająca liczbę potrzebnych naszym zdaniem wierszy, sygnalizuje ten kłopot. Można się w takim razie zastanowić, czy nie możemy zrobić czegoś, by wcześniej ograniczyć dane tylko do tych, które są potrzebne. Ale co, gdy wszystko wygląda ok, ale problem nadal występuje? Dość częstym problemem jest SELECT *, bo oznacza „dodaj do wyniku wszystkie kolumny z każdej złączonej tabeli". Zdarza się, że w bazie są trzymane duże pola tekstowe albo - co gorsza - binarne.

Dygresja 2: to jest naprawdę bardzo słaby pomysł, należy się ich pozbyć w 98% przypadków. Do trzymania danych binarnych powinien służyć system plików. Spotkałem się kiedyś ze stroną firmy z branży IT, która piekielnie wolno się wczytywała. Okazało się, że jeden endpoint zwraca w odpowiedzi JSON-owej obrazki w postaci Base64. Mam obawy, że to mogło iść z bazy danych.

By zobaczyć, ile trwają poszczególne etapy wykonania zapytania, należy użyć profilera.

W MySQL trzeba najpierw włączyć profilowanie komendą SET profiling = 1;, następnie odpalić nasze query, a potem wydać komendę SHOW PROFILE. W wyniku będzie nazwa każdego kroku oraz czas wykonania w sekundach. W przypadku żądania zbyt wielu danych, zdecydowanie najwięcej czasu zajmie sending data. Jeżeli jakiś inny krok zajmuje dużo czasu, to polecam poszukanie informacji o tym w internecie.

Dodatkowo bardzo dużo informacji na temat problemów z wydajnością można znaleźć w performance_schema, jednak jest to temat na tyle zaawansowany i szeroki, że tylko odeślę do dokumentacji. W większości przypadków dane z profilera wystarczą do optymalizacji zapytań, natomiast przy wyciąganiu maksimum z MySQL, performance_schema może być bardzo przydantne.

Inną ważną przyczyną słabej wydajności DB jest zła konfiguracja.

Najczęściej będziemy mieli z nią do czynienia, gdy serwer przygotowywał deweloper zamiast admina lub DBA. Ustawienia, które są całkiem ok dla mniejszych baz danych, są niewystarczające, gdy wzrośnie ilość danych lub częstotliwość zapytań. Przy wystąpieniu jakichkolwiek problemów z wydajnością bazy danych, zalecane jest sprawdzenie konfiguracji. Artykułów w internecie na ten temat jest sporo, jednak zachęcam do ostrożnego wprowadzania znajdowanych sugestii. Najlepiej opierać się na wpisach z blogów, które mają dużą wiarygodność. Tutaj przykład takiego artykułu.

Na koniec zostawiłem sobie nierozważne używanie ORM-ów.

Są to cudowne twory, które uwielbiają szczególnie programiści full-stackowi. Największą ich zaletą jest elastyczność. Jednak gdy nie dba się o jakość, mogą wyprodukować naprawdę paskudne zapytania SQL.

Czy więc porzucić ORM-y na rzecz innych rozwiązań? Moim zdaniem nie ma takiej potrzeby. Ważne jest jednak dbanie o wysoką jakość zapytań i zwracanie minimalnego zestawu danych, potrzebnego do zrealizowania postawionego zadania. ORM to tylko narzędzie, które z jednej strony mocno ułatwia pracę, a z drugiej, w rękach nieodpowiedniej osoby jest „śmiertelnie niebezpieczne”. Podstawową metodyką poprawiania wydajności źle wygenerowanych zapytań jest usuwanie tych części, które są zbędne. To bywa trudne, bo może się okazać, że dane zbędne w jednym kontekście są potrzebne w drugim. Niestety wiedza o tym jest zaszyta gdzieś w kodzie aplikacji, tak więc wymagane jest dobre zrozumienie tematu lub dobre pokrycie testami integracyjnymi. Swoją drogą powolne testy integracyjne są mocnym sygnałem do rozpoczęcia profilowania aplikacji - nie tylko na poziomie bazy danych, ale też samego kodu.

Prościej = lepiej

W zasadzie najlepsza rada, jaką mogę dać przy okazji relacyjnych baz danych to to, by relacje były możliwie proste i oczywiste. Można pójść też w drugą stronę i uczynić z bazy danych osobną aplikację, ze swoją własną logiką. Tak robi się w wielu projektach klasy enterprise, jednak wtedy bez dedykowanego programisty od baz danych ani rusz! Większość programistów full-stack nie ma takiego komfortu i powinni mieć solidne podstawy jeżeli chodzi o SQL, włącznie z wiedzą o tym, co jest dobre, a co złe dla wydajności zapytań.

Dla większości projektów systemów informacyjnych relacyjna baza danych pozostanie podstawowym magazynem danych ze względu na transakcyjność i bezpieczeństwo danych, które zapewnia wiele mechanizmów. Znakomicie nadają się do wyciągania danych w uwzględnieniem relacji między nimi, co idealnie mapuje się na OOP. Gorzej radzą sobie z bardzo dużą ilością zapisów czy wyszukiwaniem pełnotekstowym. Zupełnie nie nadają się do przetwarzania danych. W tych obszarach lepiej wykorzystać alternatywy.

Jednak dla znakomitej większości zastosowań, bazy SQL zapewniają zupełnie wystarczającą wydajność, przy założeniu, że projektuje się je z myślą o wysokim obciążeniu. Dobra wiadomość jest taka, że nawet kiepski schemat można znacznie poprawić. Trzeba mieć do tego nieco wiedzy, którą można znaleźć w internecie, ale polecam też przeczytanie przynajmniej jednej książki, która koncentruje się na wydajności w SQL. Pozycją, która dobrze wyjaśnia ogólne koncepty, jest „SQL Performance Explained”, ale równie dobrze można przeczytać książkę dotyczącą konkretnej implementacji. Do tego serdecznie zachęcam!