Jak zwiększyć liczbę zapytań do bazy danych dziesięciokrotnie, nie przechodząc na wydajniejszy serwer, i jednocześnie zachować funkcjonalność systemu? Opowiem, jak walczyliśmy z spadkiem wydajności naszej bazy danych, jak optymalizowaliśmy zapytania SQL, aby obsługiwać jak najwięcej użytkowników, nie zwiększając kosztów zasobów obliczeniowych.
Tworzę usługę do zarządzania procesami biznesowymi w firmach budowlanych. Współpracujemy z około 3 tysiącami firm. Ponad 10 tysięcy osób codziennie korzysta z naszego systemu przez 4-10 godzin. Rozwiązuje on różne problemy związane z planowaniem, powiadamianiem, ostrzeganiem, walidowaniem… Używamy PostgreSQL 9.6. W naszej bazie danych znajduje się około 300 tabel, a każdego dnia dociera do niej do 200 mln zapytań (10 tysięcy różnych). Średnio mamy 3-4 tysiące zapytań na sekundę, a w najbardziej aktywnych momentach ponad 10 tysięcy zapytań na sekundę. Większość zapytań to OLAP. Dodania, modyfikacje i usunięcia są dużo rzadziej, co oznacza, że obciążenie OLTP jest stosunkowo małe. Wszystkie te liczby podaję, aby mogli Państwo ocenić skalę naszego projektu i zrozumieć, jak nasza wiedza może być dla Państwa przydatna.
Obraz pierwszy. Liryczny
Gdy rozpoczynaliśmy rozwój, nie zastanawialiśmy się szczególnie nad tym, jakie obciążenie spadnie na bazę danych i co zrobimy, jeśli serwer przestanie działać. Podczas projektowania bazy danych kierowaliśmy się ogólnymi zaleceniami i staraliśmy się nie strzelać sobie w stopę, ale dalej nie zagłębialiśmy się w ogólne porady, takie jak „nie używaj wzorca nie sięgaliśmy. Projektowaliśmy zgodnie z zasadami normalizacji, unikając nadmiarowości danych i nie martwiliśmy się o przyspieszenie poszczególnych zapytań. Gdy tylko przyszli pierwsi użytkownicy, napotkaliśmy problemy z wydajnością. Jak to zazwyczaj bywa, byliśmy całkowicie nie gotowi na to. Pierwsze problemy okazały się proste. Zazwyczaj wystarczało dodanie nowego indeksu. Jednak nadszedł moment, kiedy proste łatki przestały działać. Zdałem sobie sprawę, że brakuje nam doświadczenia i coraz trudniej jest nam zrozumieć, co jest przyczyną problemów, więc zatrudniliśmy specjalistów, którzy pomogli nam właściwie skonfigurować serwer, podłączyć monitoring, pokazali, gdzie patrzeć, aby uzyskać .
Obraz drugi. Statystyczny
Mamy około 10 tysięcy różnych zapytań, które są wykonywane na naszej bazie danych dziennie. Z tych 10 tysięcy są potwory, które są wykonywane od 2 do 3 milionów razy, z średnim czasem wykonania wynoszącym 0.1-0.3 ms, oraz zapytania z średnim czasem wykonania 30 sekund, które są wywoływane 100 razy dziennie.
Optymalizacja wszystkich 10 tysięcy zapytań nie była możliwa, dlatego postanowiliśmy ustalić, na jakie obszary skierować nasze wysiłki, aby poprawić wydajność bazy danych w odpowiedni sposób. Po kilku iteracjach zaczęliśmy dzielić zapytania na typy.
TOP zapytania
To są najcięższe zapytania, które wymagają najwięcej czasu (czas całkowity). Są to zapytania, które są wywoływane bardzo często lub które mają bardzo długi czas wykonania (długie i częste zapytania były optymalizowane już w pierwszych iteracjach walki o szybkość). Ostatecznie serwer spędza najwięcej czasu na ich wykonaniu. Warto oddzielić top zapytania według całkowitego czasu wykonywania i osobno według czasu IO. Sposoby optymalizacji takich zapytań są nieco różne.
Zwyczajową praktyką wszystkich firm jest praca z TOP zapytaniami. Jest ich niewiele, a optymalizacja nawet jednego zapytania może uwolnić 5-10% zasobów. Jednak w miarę „dojrzewania” projektu optymalizacja TOP zapytań staje się coraz bardziej złożonym zadaniem. Wszystkie proste rozwiązania zostały już zastosowane, a najcięższe zapytanie zabiera „zaledwie” 3-5% zasobów. Jeśli TOP zapytania zajmują łącznie mniej niż 30-40% czasu, to najprawdopodobniej już podjęliście wysiłki, aby działały szybko i nadszedł czas, aby przejść do optymalizacji zapytań z następnej grupy.
Pozostaje odpowiedzieć na pytanie, ile górnych zapytań włączyć do tej grupy. Zwykle biorę nie mniej niż 10, ale nie więcej niż 20. Staram się, aby czas pierwszego i ostatniego zapytania w grupie TOP różnił się nie więcej niż dziesięciokrotnie. To znaczy, jeśli czas wykonania zapytań spada gwałtownie z 1 miejsca do 10, biorę TOP-10, jeśli spadek jest bardziej stopniowy, zwiększam rozmiar grupy do 15 lub 20.
Średniacy (medium)
To są wszystkie zapytania, które są bezpośrednio za TOP, z wyjątkiem ostatnich 5-10%. Zazwyczaj w optymalizacji to właśnie te zapytania kryją możliwość znacznego zwiększenia wydajności serwera. Mogą one „zajmować” do 80%. Ale nawet jeśli ich udział przekroczył 50%, to znaczy, że czas na bardziej wnikliwe spojrzenie na nie.
Ogon (tail)
Jak zostało wspomniane, te zapytania pojawiają się na końcu i zajmują 5-10% czasu. Można o nich zapomnieć, tylko jeśli nie korzystasz z automatycznych narzędzi analizy zapytań, wtedy ich optymalizacja również może być tania.
Jak ocenić każdą grupę?
Używam zapytania SQL, które pomaga dokonać takiej oceny dla PostgreSQL (jestem pewien, że dla wielu innych systemów bazodanowych można napisać podobne zapytanie)
Zapytanie SQL do oceny rozmiaru grup TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) FROM ( SELECT CASE WHEN rn 20 AND rn 800 THEN tt_percent ELSE 0 END AS time_tail FROM ( SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query, ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn FROM pg_stat_statements ORDER BY total_time DESC ) AS t ) AS ts
Wynik zapytania – trzy kolumny, z których każda zawiera procent czasu poświęconego na przetwarzanie zapytań z tej grupy. W obrębie zapytania znajdują się dwie liczby (w moim przypadku 20 i 800), które oddzielają zapytania jednej grupy od drugiej.
Oto jak mniej więcej odnoszą się udziały zapytań w momencie rozpoczęcia prac nad optymalizacją i teraz.
Z diagramu widać, że udział zapytań TOP znacznie się zmniejszył, za to wzrosły zapytania “średnie”.
Początkowo w zapytaniach TOP znajdowały się oczywiste błędy. Z czasem choroby wieku dziecięcego zniknęły, udział zapytań TOP się zmniejszył, a my musieliśmy włożyć coraz więcej wysiłku, aby przyspieszyć ciężkie zapytania.
Aby uzyskać tekst zapytań, używamy takiego zapytania
SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query FROM pg_stat_statements ORDER BY total_time DESC ) AS T WHERE rn 20 AND rn 800 -- TAIL
Oto lista najczęściej stosowanych technik, które pomogły nam przyspieszyć zapytania TOP:
- Przeprojektowanie systemu, na przykład przekształcenie logiki powiadomień na message broker zamiast okresowych zapytań do bazy danych
- Dodawanie lub zmiana indeksów
- Przepisywanie zapytań ORM na czysty SQL
- Przepisywanie logiki leniwego ładowania danych
- Buforowanie poprzez denormalizację danych. Na przykład mamy połączenie tabeli Dostawa -> Faktura -> Zapytanie -> Wniosek. To znaczy, że każda dostawa jest powiązana z wnioskiem przez inne tabele. Aby nie powiązywać wszystkich tabel w każdym zapytaniu, skopiowaliśmy odniesienie do wniosku w tabeli Dostawa.
- Cache'owanie statycznych tabel z słownikami oraz rzadko zmieniających się tabel w pamięci programu.
Czasami zmiany prowadziły do znacznych redesignów, ale przynosiły 5-10% odciążenia systemu i były uzasadnione. Z czasem wydajność malała, a redesign stawał się coraz poważniejszy.
Wtedy zwróciliśmy uwagę na drugą grupę zapytań - grupę średniaków. Było w niej znacznie więcej zapytań i wydawało się, że analiza całej grupy zajmie dużo czasu. Jednak większość zapytań okazała się bardzo prosta do optymalizacji, a wiele problemów powtarzało się dziesiątki razy w różnych wariacjach. Oto przykłady niektórych typowych optymalizacji, które zastosowaliśmy do dziesiątek podobnych zapytań, a każda grupa zoptymalizowanych zapytań odciążała bazę danych o 3-5%.
- Zamiast sprawdzania obecności rekordów za pomocą COUNT i pełnego skanowania tabeli, zaczęto używać EXISTS.
- Pozbyliśmy się DISTINCT (nie ma ogólnego przepisu, ale czasami można się go łatwo pozbyć, przyspieszając zapytanie 10-100 razy).
Na przykład, zamiast zapytania do pobrania wszystkich kierowców z dużej tabeli dostaw (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDzrobiliśmy zapytanie na stosunkowo małej tabeli PERSON
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Wydawałoby się, że użyliśmy podzapytania skorelowanego, ale przynosi ono przyspieszenie powyżej 10 razy.
- W wielu przypadkach całkowicie zrezygnowaliśmy z COUNT i
- zamiast
UPPER(s) LIKE JOHN%używamy
s ILIKE 'John%'
Każde konkretne zapytanie udało się przyspieszyć czasami o 3-1000 razy. Mimo imponujących wskaźników, na początku uznawaliśmy, że nie ma sensu optymalizować zapytania, które wykonuje się w 10 ms, zajmuje trzecie setne miejsce wśród najcięższych zapytań i w ogólnym czasie obciążenia bazy danych zajmuje ułamki procenta. Jednak stosując tę samą receptę do grupy jednolitarnych zapytań uzyskiwaliśmy kilka procent oszczędności. Aby nie tracić czasu na ręczne przeglądanie wszystkich setek zapytań, napisaliśmy kilka prostych skryptów, które z pomocą wyrażeń regularnych znajdowały jednolite zapytania. W końcu automatyczne wyszukiwanie grup zapytań pozwoliło nam jeszcze bardziej poprawić wydajność, poświęcając skromne wysiłki.
W rezultacie pracujemy już od trzech lat na tym samym sprzęcie. Średnie obciążenie w ciągu dnia wynosi około 30%, a w szczytowych momentach sięga 70%. Liczba zapytań, jak i użytkowników wzrosła prawie dziesięciokrotnie. A wszystko to dzięki stałemu monitorowaniu grup zapytań TOP-MEDIUM. Gdy tylko pojawia się nowe zapytanie w grupie TOP, natychmiast je analizujemy i próbujemy przyspieszyć. Grupę MEDIUM przeglądamy raz w tygodniu za pomocą skryptów analizy zapytań. Jeśli znajdziemy nowe zapytania, które już wiemy jak zoptymalizować, szybko je zmieniamy. Czasami odkrywamy nowe metody optymalizacji, które można zastosować od razu do kilku zapytań.
Z naszych prognoz wynika, że aktualny serwer wytrzyma wzrost liczby użytkowników jeszcze 3-5 razy. Prawda, że mamy jeden as w rękawie — nadal nie przekierowaliśmy zapytań SELECT na lustrzane serwery, jak to się zaleca. Ale nie robimy tego świadomie, ponieważ chcemy najpierw w pełni wykorzystać możliwości 'inteligentnej' optymalizacji przed włączeniem 'ciężkiej artylerii'.
Krytyczne spojrzenie na wykonaną pracę może zasugerować zastosowanie pionowego skalowania. Zakupienie mocniejszego serwera, zamiast tracić czas specjalistów. Serwer może kosztować niewiele, tym bardziej, że nasze limity pionowego skalowania są jeszcze nieosiągnięte. Jednak liczba zapytań wzrosła tylko dziesięciokrotnie. W ciągu kilku lat zwiększył się również funkcjonalność systemu, a obecnie jest więcej rodzajów zapytań. Funkcjonalność, która istniała, dzięki buforowaniu jest realizowana mniejszą liczbą zapytań, a co więcej, bardziej efektywnych zapytań. Oznacza to, że można śmiało razy pięć, aby uzyskać rzeczywisty współczynnik przyspieszenia. Tak więc, przy najbardziej skromnych obliczeniach, można powiedzieć, że przyspieszenie wyniosło 50 razy lub więcej. Pionowe zeskalowanie serwera 50 razy byłoby droższe. Szczególnie biorąc pod uwagę, że raz przeprowadzona optymalizacja działa cały czas, a rachunek za wynajmowany serwer przychodzi co miesiąc.
Źródło: habr.com
