Jak zwiększyć 10-krotnie liczbę zapytań do bazy danych bez konieczności przechodzenia na bardziej produktywny serwer i zachowując funkcjonalność systemu? Opowiem jak poradziliśmy sobie ze spadkiem wydajności naszej bazy danych, jak zoptymalizowaliśmy zapytania SQL, aby obsłużyć jak największą liczbę użytkowników i nie zwiększać kosztów zasobów obliczeniowych.
Świadczę usługę zarządzania procesami biznesowymi w firmach budowlanych. Współpracuje z nami około 3 tysiące firm. Z naszym systemem codziennie po 10-4 godzin pracuje ponad 10 tysięcy osób. Rozwiązuje różne problemy związane z planowaniem, powiadamianiem, ostrzeganiem, walidacją... Używamy PostgreSQL 9.6. W bazie mamy około 300 tabel i każdego dnia otrzymujemy aż 200 milionów zapytań (10 tysięcy różnych). Średnio mamy 3-4 tysiące żądań na sekundę, w najbardziej aktywnych momentach ponad 10 tysięcy żądań na sekundę. Większość zapytań to zapytania OLAP. Dodano znacznie mniej dodatków, modyfikacji i usunięć, co oznacza, że obciążenie OLTP jest stosunkowo niewielkie. Podałem te wszystkie liczby, abyś mógł ocenić skalę naszego projektu i zrozumieć, jak przydatne może być dla Ciebie nasze doświadczenie.
Zdjęcie pierwsze. Liryczny
Kiedy zaczynaliśmy programowanie, tak naprawdę nie myśleliśmy o tym, jakie obciążenie spadnie na bazę danych i co byśmy zrobili, gdyby serwer przestał ciągnąć. Projektując bazę kierowaliśmy się ogólnymi zaleceniami i staraliśmy się nie strzelić sobie w stopę, ale wyszliśmy poza ogólne rady typu „nie używaj wzorca nie weszliśmy. Projektowaliśmy w oparciu o zasady normalizacji, unikając redundancji danych i nie zależało nam na przyspieszeniu niektórych zapytań. Gdy tylko pojawili się pierwsi użytkownicy, napotkaliśmy problem z wydajnością. Jak zwykle nie byliśmy na to kompletnie przygotowani. Pierwsze problemy okazały się proste. Z reguły wszystko rozwiązywano poprzez dodanie nowego indeksu. Ale przyszedł czas, kiedy proste łatki przestały działać. Zdając sobie sprawę, że brakuje nam doświadczenia i coraz trudniej jest nam zrozumieć, co jest przyczyną problemów, zatrudniliśmy specjalistów, którzy pomogli nam poprawnie skonfigurować serwer, podłączyć monitoring i pokazali, gdzie szukać, aby uzyskać .
Zdjęcie drugie. Statystyczny
Dziennie w naszej bazie danych wykonywanych jest więc około 10 tysięcy różnych zapytań. Z tych 10 tysięcy są potwory, które są wykonywane 2-3 miliony razy ze średnim czasem wykonania 0.1-0.3 ms i są zapytania ze średnim czasem wykonania 30 sekund, które są wywoływane 100 razy dziennie.
Nie udało się zoptymalizować wszystkich 10 tysięcy zapytań, dlatego postanowiliśmy zastanowić się, gdzie skierować nasze wysiłki, aby poprawnie poprawić wydajność bazy danych. Po kilku iteracjach zaczęliśmy dzielić żądania na typy.
NAJLEPSZE prośby
Są to najcięższe zapytania, które zajmują najwięcej czasu (czas całkowity). Są to zapytania, które albo są wywoływane bardzo często, albo zapytania, których wykonanie zajmuje bardzo dużo czasu (długie i częste zapytania zostały zoptymalizowane w pierwszych iteracjach walki o szybkość). W rezultacie serwer spędza najwięcej czasu na ich wykonaniu. Ponadto ważne jest, aby oddzielić najważniejsze żądania według całkowitego czasu wykonania i osobno według czasu IO. Metody optymalizacji takich zapytań są nieco inne.
Zwykłą praktyką wszystkich firm jest praca z TOP żądaniami. Jest ich niewiele, optymalizacja nawet jednego zapytania może uwolnić 5-10% zasobów. Jednak w miarę dojrzewania projektu optymalizacja zapytań TOP staje się zadaniem coraz bardziej nietrywialnym. Wszystkie proste metody zostały już opracowane, a najbardziej „ciężkie” żądanie zajmuje „tylko” 3-5% zasobów. Jeśli w sumie TOP zapytania zajmują mniej niż 30-40% czasu, to najprawdopodobniej już podjąłeś starania, aby działały szybko i czas przejść do optymalizacji zapytań z kolejnej grupy.
Pozostaje odpowiedzieć na pytanie, ile najpopularniejszych zapytań powinno znaleźć się w tej grupie. Zwykle biorę co najmniej 10, ale nie więcej niż 20. Staram się, aby czasy pierwszego i ostatniego w grupie TOP różniły się nie więcej niż 10-krotnie. Oznacza to, że jeśli czas wykonania zapytania gwałtownie spada z 1. na 10. miejsce, to biorę TOP-10, jeśli spadek jest bardziej stopniowy, to zwiększam wielkość grupy do 15 lub 20.
Średniowieczni chłopi
Są to wszystkie żądania, które pojawiają się bezpośrednio po TOP, z wyjątkiem ostatnich 5-10%. Zwykle optymalizacja tych zapytań kryje w sobie możliwość znacznego zwiększenia wydajności serwera. Żądania te mogą ważyć nawet 80%. Ale nawet jeśli ich udział przekroczył 50%, czas przyjrzeć się im bliżej.
Ogon
Jak wspomniano, zapytania te pojawiają się na końcu i zajmują 5–10% czasu. Można o nich zapomnieć tylko wtedy, gdy nie korzysta się z narzędzi do automatycznej analizy zapytań, wtedy ich optymalizacja też może być tania.
Jak ocenić każdą grupę?
Używam zapytania SQL, które pomaga dokonać takiej oceny dla PostgreSQL (jestem pewien, że podobne zapytanie można napisać dla wielu innych DBMS)
Zapytanie SQL służące do oszacowania wielkości grup TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN 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
Wynikiem zapytania są trzy kolumny, z których każda zawiera procent czasu potrzebny na przetworzenie zapytań z tej grupy. Wewnątrz żądania znajdują się dwie liczby (w moim przypadku jest to 20 i 800), które oddzielają żądania z jednej grupy od drugiej.
Tak mniej więcej przedstawia się udział żądań w momencie rozpoczęcia prac optymalizacyjnych i obecnie.
Diagram pokazuje, że udział wniosków TOP gwałtownie spadł, ale wzrósł „średnich chłopów”.
Początkowo TOP żądania zawierały rażące błędy. Z biegiem czasu zniknęły choroby wieku dziecięcego, zmniejszył się udział wniosków TOP i trzeba było podejmować coraz większe wysiłki, aby przyspieszyć trudne wnioski.
Aby uzyskać tekst żądań, używamy następującego żądania
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 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
Oto lista najczęściej stosowanych technik, które pomogły nam przyspieszyć TOP zapytania:
- Przeprojektowanie systemu, np. przerobienie logiki powiadomień przy użyciu brokera komunikatów zamiast okresowych zapytań do bazy danych
- Dodawanie lub zmiana indeksów
- Przepisywanie zapytań ORM na czysty SQL
- Przepisanie leniwej logiki ładowania danych
- Buforowanie poprzez denormalizację danych. Przykładowo mamy połączenie tabeli Dostawa -> Faktura -> Zapytanie -> Wniosek. Oznacza to, że każda dostawa jest powiązana z aplikacją za pośrednictwem innych tabel. Aby nie łączyć wszystkich tabel w każdym żądaniu, zduplikowaliśmy link do żądania w tabeli Dostawa.
- Buforowanie tabel statycznych z podręcznikami i rzadko zmieniającymi się tabelami w pamięci programu.
Czasami zmiany sprowadzały się do imponującego przeprojektowania, ale zapewniały 5-10% obciążenia systemu i były uzasadnione. Z biegiem czasu wydech stawał się coraz mniejszy i konieczne było coraz poważniejsze przeprojektowanie.
Następnie zwróciliśmy uwagę na drugą grupę próśb – grupę średnich chłopów. Zapytań jest w nim znacznie więcej i wydawało się, że analiza całej grupy zajmie dużo czasu. Większość zapytań okazała się jednak bardzo prosta w optymalizacji, a wiele problemów powtarzało się kilkadziesiąt razy w różnych odmianach. Oto przykłady typowych optymalizacji, które zastosowaliśmy w dziesiątkach podobnych zapytań i każda grupa zoptymalizowanych zapytań odciążała bazę danych o 3-5%.
- Zamiast sprawdzać obecność rekordów za pomocą COUNT i pełnego skanowania tabeli, zaczęto używać EXISTS
- Pozbyłem się DISTINCT (nie ma ogólnej recepty, ale czasami można się go łatwo pozbyć, przyspieszając żądanie 10-100 razy).
Przykładowo zamiast zapytania o wybranie wszystkich kierowców z dużej tabeli dostaw (DOSTAWA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDwykonał zapytanie na stosunkowo małym stole 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ć by się mogło, że zastosowaliśmy skorelowane podzapytanie, ale daje to przyspieszenie ponad 10-krotne.
- W wielu przypadkach COUNT zostało całkowicie porzucone i
- zamiast
UPPER(s) LIKE JOHN%’stosowanie
s ILIKE “John%”
Każde konkretne żądanie było czasami przyspieszane od 3 do 1000 razy. Pomimo imponującej wydajności, w pierwszej chwili wydawało nam się, że nie ma sensu optymalizować zapytania, którego wykonanie zajmuje 10 ms, jest jednym z 3 setek najcięższych zapytań i zajmuje setne części procenta całkowitego czasu ładowania bazy danych. Ale stosując ten sam przepis do grupy zapytań tego samego typu, odzyskaliśmy kilka procent. Aby nie tracić czasu na ręczne przeglądanie setek zapytań, napisaliśmy kilka prostych skryptów wykorzystujących wyrażenia regularne do wyszukiwania zapytań tego samego typu. W rezultacie automatyczne przeszukiwanie grup zapytań pozwoliło nam na dalszą poprawę wydajności przy niewielkim wysiłku.
W efekcie już od trzech lat pracujemy nad tym samym sprzętem. Średnie dzienne obciążenie wynosi około 30%, w szczytach sięga 70%. Liczba żądań, a także liczba użytkowników wzrosła około 10-krotnie. A wszystko to dzięki stałemu monitorowaniu tych samych grup zgłoszeń TOP-MEDIUM. Gdy tylko w grupie TOP pojawi się nowe zgłoszenie, natychmiast je analizujemy i staramy się przyspieszyć. Grupę MEDIUM przeglądamy raz w tygodniu za pomocą skryptów analizy zapytań. Jeśli natrafiamy na nowe zapytania, które już umiemy optymalizować, szybko je zmieniamy. Czasami znajdujemy nowe metody optymalizacji, które można zastosować do kilku zapytań jednocześnie.
Według naszych prognoz obecny serwer wytrzyma wzrost liczby użytkowników o kolejne 3-5 razy. To prawda, że mamy jeszcze jednego asa w rękawie - nadal nie przenieśliśmy zapytań SELECT na serwer lustrzany, jak jest to zalecane. Ale nie robimy tego świadomie, bo chcemy najpierw całkowicie wyczerpać możliwości „inteligentnej” optymalizacji, zanim włączymy „ciężką artylerię”.
Krytyczne spojrzenie na wykonaną pracę może sugerować zastosowanie skalowania pionowego. Kup mocniejszy serwer zamiast tracić czas specjalistów. Serwer może nie kosztować aż tak dużo, tym bardziej, że nie wyczerpaliśmy jeszcze granic skalowania w pionie. Jednak tylko liczba żądań wzrosła 10-krotnie. Na przestrzeni kilku lat funkcjonalność systemu wzrosła i obecnie pojawia się więcej rodzajów żądań. Dzięki buforowaniu istniejąca funkcjonalność jest wykonywana w mniejszej liczbie żądań, a żądania są bardziej wydajne. Oznacza to, że możesz bezpiecznie pomnożyć przez kolejne 5, aby uzyskać rzeczywisty współczynnik przyspieszenia. Zatem według najbardziej konserwatywnych szacunków możemy powiedzieć, że przyspieszenie było 50-krotne lub większe. Pionowe wahadłowe serwer będzie kosztować 50 razy więcej. Tym bardziej, że raz przeprowadzona optymalizacja działa cały czas, a rachunek za wynajęty serwer przychodzi co miesiąc.
Źródło: www.habr.com