W mojej praktyce zawodowej, miałem do czynienia z różnymi przypadkami, które w mniejszym, lub większym stopniu miały znaczący wpływ na obniżanie wydajności serwisów, których dotyczyły. Błędy lub niedopatrzenia jakie popełniono, mogły zdarzyć się każdemu i pewnie przy tworzeniu rozwiązań na "szybko" przytrafiają się częściej niż możemy sobie to wyobrazić.
Łączenie się w trakcie generowania strony do zewnętrznych serwisów
Początkowo wszystko wydaje się fajne, bo działa, jednakże w przypadku styku z użytkownikami
a. niepotrzebnie zapychamy sobie łącze
b. w przypadku padu połączenia z zewnętrznym serwisem, co może zdarzyć się np. jak domena przestanie istnieć, nasz serwis klasycznie dostanie DDOS.
Jak się tego ustrzec? Rozwiązanie wydaje się prostsze niż mogłoby się wydawać. Otóż można np przy pomocy CRONa zrzucać interesującą nas informację zewnętrznego serwisu do plików tymczasowych na naszym serwerze i to właśnie wykorzystywać w naszych skryptach
Karkołomne zapytania wykonywane przy każdym kliknięciu
Przypuśćmy, że chcemy w naszym serwisie pokazać dziesięć losowych spośród stu najnowsze wiadomości, które z kolei mogą znajdować się na forach, obrazkach, blogach lub w samym naszym serwisie. Przypuśćmy też, że treści generowanych przez użytkowników jest sporo powiedzmy 1000-100000 elementów dziennie a ilość wejść przez użytkowniów na nasz serwis to milion miesięcznie. Żeby stworzyć interesujący nas widok musimy wykonać, kilka zapytań, albo jedno i użyć konstrukcji union, gdyż dane będą pochodziły z kilku tabel. Genralnie jak się to wszystko policzy to masakra. W każdym razie dobrą praktyką jest użycie cache'y, ale teraz jak się z tematem zmierzyć? prawdę mówiąc istnieją 2 rozwiązania
1. Dane w czasie rzeczywistym
Dla każdej interesującej nas tabeli tworzymy trigger "after insert", który będzie ją umieszczał w tabeli cache_100 oraz dbał o to, żeby ilość elementów nie przekraczała 100. Praktyka pokazuje, że przy takiej ilości danych indeksy w tabeli cache_100 są zbędne i obniżałyby wydajność. Jednakże nawet w przedstawionym przeze mnie przypadek, może się nie sprawdzić, to znaczy wtedy, gdy ilość generowanych treści przez użytkowników będzie o rząd albo o dwa rzędy większa.
2. Dane opóźnione o jakiś czas
Innym podejściem do tematu jest wykonanie owego karkołomnego zapytania raz na jakiś czas np 5min-10min(CRON)i umieszczenie wyników w tabeli cache_100. Użytkownikowi i tak jakoś specjalnie to nie będzie przeszkadzało a my zyskamy na tym, że nawet znacznie zwiększony ruch nie zabije nam usługi.
Miliony rekordów w tabeli o wielu dowiązaniach
W sumie jest to najciekawszy przypadek jaki do tej pory miałem. Baza danych była dość skomplikowana, dowiązania wielokrotne w liczbie 15. Ogólnie sam widok schematu przyprawiał o zawrót głowy. Nie mówię, że to coś złego, po prostu takie było odzwierciedlenie nieabstrakcyjnych urządzeń znajdujących się w terenie. Jeśli doliczyć do tego ponad milion a nawet do dwóch milionów rekordów rocznie i wyniki generowane w czasie rzeczywistym filtrowane po subdevice_id i czasie to już w ogóle dech w piersiach zapiera a na usta cisną się niecenzuralne słowa. Pominę już fakt, że jeśli urządzenie zwariuje to może wysłać 10k rekordów w przeciągu jednej godziny a czasami nawet i więcej. Zapytanie takie jak poniżej, nie wchodziło w grę, choćby ze względu na to, że mogłoby się wykonywać nawet do 5-24h, bo inicjalne ustawienie pól cache'ujących trwało bagatela 1.5h.
Select * from msgs left join ... where .... order by ... limit ...;Użyłem triggerów, które zadbały o to, żeby baza sama "pilnowała" wariantów i przynajmniej częściowo cache'owała wyniki podzapytań z dowiązań.
msgs.subdevice_id = (select device_id from devices where devices.supdevice_id = msgs.device_id and devices.subdevice = msgs.subdevice))Czas wykonywania skrócił się do 30-60 sekund. Gdyby ten wynik zadowalał mnie i klienta to pewnie bym i na tym poprzestał, ale dla nas obojga było to niewystarczające.
Na odsiecz przybyło podzapytanie użyte w polu FROM. To zapytanie nie było specjalnie trudne ot, po prostu służyło do zawężania wyników po subdevice_id i dacie. Czyli w efekcie końcowym powstał taki twór
$x= "Select * from msgs where subdevice_id in ( $subdevices ) and rtime > $start and rtime < $koniec order by rtime desc limit 101;";a to z kolei zostało umieszczone w polu FROM, Czyli
$sql = "Select * from ( $x ) left join ..."Dla niektórych to, co zostało zrobione tutaj to kombinacja norweska z alpejską w jednej, ale tak naprawdę jest to bardzo proste do zrozumienia. Otóż w powyższym rozwiązaniu został wykorzystany fakt, iż zapytania na zindeksowanej "płaskiej" tabeli wykonują się o niebo szybciej niż zapytania, w których występują złączenia z innymi tabelami. Jest tak między innymi dlatego, że najpierw tworzone są wiersze wynikowe czyli między innymi rozwiązywanie dowiązań a dopiero potem następuje ich zawężanie. Zatem "Superszybkie" zapytanie zawężające liczbę wyników do 101 znacznie zmniejszyło ilość rozwiązywanych dowiązań z innymi tabelami, przez co baza danych w końcu zaczęła oddychać.
Możecie wierzyć lub nie, ale to rozwiązanie skróciło czas wykonywania zapytania do 100-300ms czyli w porównaniu z 5-24h wychodzi od 6M do 28.8M%, co wydaje się być wynikiem, co najmniej zadowalającym :].
Rafał Prasał.
