Historia jednego śledztwa SQL

W grudniu ubiegłego roku otrzymałem interesujący raport o błędzie od zespołu wsparcia VWO. Czas ładowania jednego z raportów analitycznych dla dużego klienta korporacyjnego wydawał się nieproporcjonalnie długi. Ponieważ to leżało w moich kompetencjach, od razu skupiłem się na rozwiązaniu problemu.

Tło

Aby wyjaśnić, o co chodzi, opowiem krótko o VWO. To platforma, która umożliwia prowadzenie różnych ukierunkowanych kampanii na swoich stronach: przeprowadzanie eksperymentów A/B, śledzenie odwiedzających i konwersji, analizowanie lejków sprzedażowych, wyświetlanie map cieplnych oraz odtwarzanie nagrań wizyt.

Ale najważniejszą funkcją platformy jest tworzenie raportów. Wszystkie wymienione funkcje są ze sobą powiązane. Dla klientów korporacyjnych ogromna ilość informacji byłaby po prostu bezużyteczna bez solidnej platformy, która przedstawia je w formacie analitycznym.

Korzystając z platformy, można złożyć dowolne zapytanie na dużym zbiorze danych. Oto prosty przykład:

Pokaż wszystkie kliknięcia na stronie "abc.com" OD  DO  dla osób, które używały Chrome LUB (były w Europie I używały iPhone'a)

Zauważ, że operatory logiczne są dostępne dla klientów w interfejsie zapytania, aby tworzyć dowolnie skomplikowane zapytania do pozyskiwania próbek.

Wolne zapytanie

Klient, o którym mowa, próbował zrobić coś, co intuicyjnie powinno działać szybko:

Pokaż wszystkie nagrania sesji dla użytkowników, którzy odwiedzili dowolną stronę z URL-em zawierającym "\/jobs"

Na tej stronie było ogromne natężenie ruchu, a my przechowywaliśmy ponad milion unikalnych adresów URL tylko dla niej. A oni chcieli znaleźć dość prosty wzór URL-a, odnoszący się do ich modelu biznesowego.

Wstępne dochodzenie

Spójrzmy, co się dzieje w bazie danych. Poniżej znajduje się oryginalne powolne zapytanie SQL:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time = 5 
    AND recording_data.num_of_pages > 0 ;

A oto czasy:

Planowany czas: 1,480 ms
Czas realizacji: 1431924,650 ms

Zapytanie obejmowało 150 tysięcy wierszy. Planner zapytań ujawnił kilka interesujących szczegółów, ale nie pokazał żadnych oczywistych wąskich gardeł.

Przyjrzyjmy się temu zapytaniu bardziej szczegółowo. Jak widać, wykonuje ono JOIN trzy tabele:

1. sessions: do wyświetlania informacji o sesjach: przeglądarka, agent użytkownika, kraj itd.
2. recording_data: zapisane URL-e, strony, czas trwania wizyt
3. urls: aby uniknąć duplikacji niezwykle dużych URL-i, przechowujemy je w osobnej tabeli.

Zwróć uwagę, że wszystkie nasze tabele są już podzielone według account_id. W ten sposób wyklucza się sytuację, w której z powodu jednego szczególnie dużego konta problemy występują u innych.

W poszukiwaniu dowodów

Przy bliższym przyjrzeniu się widzimy, że coś jest nie tak z konkretnym zapytaniem. Warto zwrócić uwagę na ten wiersz:

urls && array(
	select id from acc_{account_id}.urls 
	where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]

Pierwszą myślą było, że być może z powodu ILIKE na tych wszystkich długich URL-ach (mamy ponad 1,4 miliona unikalnych adresów URL zgromadzonych dla tego konta) wydajność może być niska.

Ale nie, to nie o to chodzi!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 wierszy)

Czas: 5231.765 ms

Same zapytanie wyszukiwania wzorca zajmuje zaledwie 5 sekund. Wyszukiwanie wzorca w milionie unikalnych URL-i zdecydowanie nie stanowi problemu.

Następny podejrzany na liście to kilka JOIN. Być może ich nadmierne użycie prowadzi do spowolnienia? Zwykle JOIN‘y są najbardziej oczywistymi kandydatami do problemów z wydajnością, ale nie wierzyłem, że nasz przypadek jest typowy.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time = 5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 wiersz)

Czas: 147.851 ms

I to również nie był nasz przypadek. JOIN‘y okazały się bardzo szybkie.

Zawężamy krąg podejrzanych

Byłem gotów zacząć zmieniać zapytanie, aby osiągnąć jakiekolwiek możliwe poprawki wydajności. My i zespół opracowaliśmy 2 główne pomysły:

• Użyć EXISTS dla podzapytania URL: Chcieliśmy jeszcze raz sprawdzić, czy są jakieś problemy z podzapytaniem dla URL-i. Jednym ze sposobów, aby to osiągnąć, jest po prostu użycie EXISTS. EXISTS może znacząco poprawić wydajność, ponieważ kończy się natychmiast, gdy znajdzie jedną linię zgodnie z warunkiem.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time =5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Tak. Podzapytanie, gdy jest owinięte w EXISTS, sprawia, że wszystko działa super szybko. Następne logiczne pytanie to, dlaczego zapytanie z JOIN-ami i samo podzapytanie są szybkie z osobna, ale wolno działają razem?

• Przenosimy podzapytanie do CTE : jeśli zapytanie jest szybkie samo w sobie, możemy najpierw obliczyć szybki wynik, a następnie przekazać go do głównego zapytania

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time =5 
    AND recording_data.num_of_pages > 0;

Ale to nadal było bardzo wolne.

Szukamy winowajcy

Cały czas przed oczyma migał jeden szczegół, od którego wciąż odsuwałem się. Ale ponieważ nie miałem już nic, postanowiłem na niego spojrzeć. Mówię o && operatorze. Dopóki EXISTS po prostu poprawił wydajność, && był jedynym wspólnym czynnikiem we wszystkich wersjach wolnego zapytania.

Patrząc na dokumentację, widzimy, że && jest używany, gdy trzeba znaleźć wspólne elementy między dwoma tablicami.

W oryginalnym zapytaniu to:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Co oznacza, że robimy wyszukiwanie wzorca w naszych URL-ach, a następnie znajdujemy przecięcie ze wszystkimi URL-ami z ogólnymi zapisami. To jest trochę mylące, ponieważ „urls” tutaj nie odnosi się do tabeli, która zawiera wszystkie adresy URL, lecz do kolumny „urls” w tabeli recording_data.

W miarę wzrastania podejrzeń dotyczących &&, próbowałem znaleźć potwierdzenie w planie zapytania, który został wygenerowany EXPLAIN ANALYZE (miałem już zapisany plan, ale zazwyczaj wygodniej jest mi eksperymentować w SQL, niż próbować zrozumieć nieprzezroczystości planistów zapytań).

Filtr: ((adresy URL && ($0)::text[]) I (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) I (r_time = '5'::double precision) I (liczba_stron > 0))\n                           Wiersze usunięte przez filtr: 52710

Było tam kilka wierszy filtrów tylko z &&. Co oznaczało, że ta operacja była nie tylko kosztowna, ale także wykonywana wielokrotnie.

Sprawdziłem to, izolując warunek

SELECT 1\nFROM \n    acc_{account_id}.urls jako recordings_urls, \n    acc_{account_id}.recording_data_30 jako recording_data_30, \n    acc_{account_id}.sessions_30 jako sessions_30 \nGDZIE \n\turls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

To zapytanie działało wolno. Ponieważ JOIN-y są szybkie, a podzapytania są szybkie, pozostawał tylko && operator.

Ale to kluczowa operacja. Zawsze musimy przeszukiwać całą główną tabelę adresów URL w celu wyszukiwania według wzoru, a zawsze musimy znajdować przecięcia. Nie możemy przeszukiwać bezpośrednio po wpisach adresów URL, ponieważ są to tylko identyfikatory odnoszące się do urls.

W drodze do rozwiązania

&& wolna, ponieważ oba zestawy są ogromne. Operacja będzie stosunkowo szybka, jeśli zastąpię urls na { "http://google.com/", "http://wingify.com/" }.

Zacząłem szukać sposobu na wykonanie w Postgres przecięcia zbiorów bez używania &&, ale bez większego sukcesu.

W końcu postanowiliśmy po prostu rozwiązać problem w izolacji: daj mi wszystkie urls wiersze, dla których adres URL odpowiada wzorowi. Bez dodatkowych warunków to będzie — 

SELECT urls.url\nFROM \n\tacc_{account_id}.urls jako urls,\n\t(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls\nGDZIE\n\turls.id = unrolled_urls.id I\n\turls.url  ILIKE  '%jobs%'

Zamiast JOIN syntaktyka, po prostu użyłem podzapytania i rozwinąłem recording_data.urls tablicę, aby można było bezpośrednio stosować warunek w WHERE.

Najważniejsze tutaj to, że && jest używane do sprawdzenia, czy dany wpis zawiera odpowiadający adres URL. Patrząc nieco bardziej uważnie, można dostrzec w tej operacji przechodzenie przez elementy tablicy (lub wiersze tabeli) i zatrzymanie się przy spełnieniu warunku (zgodności). Przypomina to coś? Aha, EXISTS.

Ponieważ na recording_data.urls można odnosić się zewnętrznie do kontekstu podzapytania, gdy to ma miejsce, możemy wrócić do naszego starego znajomego EXISTS i owinąć go podzapytaniem.

Łącząc wszystko razem, otrzymujemy ostateczne zoptymalizowane zapytanie:

WYBIERZ 
    count(*) 
Z 
    acc_{account_id}.urls jako recordings_urls, 
    acc_{account_id}.recording_data jako recording_data, 
    acc_{account_id}.sessions jako sessions 
GDZIE 
    recording_data.usp_id = sessions.usp_id 
    I (  1 = 1  )  
    I sessions.referrer_id = recordings_urls.id 
    I r_time > to_timestamp(1542585600) 
    I r_time = 5 
    I recording_data.num_of_pages > 0
    I ISTNIEJE(
        WYBIERZ urls.url
        Z 
            acc_{account_id}.urls jako urls,
            (WYBIERZ unnest(urls) JAKO rec_url_id Z acc_{account_id}.recording_data) 
            AS unrolled_urls
        GDZIE
            urls.id = unrolled_urls.rec_url_id I
            urls.url  ILIKE  '%enterprise_customer.com\/jobs%'
    );

I ostateczny czas wykonania Czas: 1898.717 ms Czas na świętowanie?!?

Nie tak szybko! Najpierw musimy sprawdzić poprawność. Byłem bardzo sceptyczny wobec EXISTS optymalizacji, ponieważ zmienia ona logikę na wcześniejsze zakończenie. Musimy upewnić się, że nie dodaliśmy żadnego ukrytego błędu do zapytania.

Prosta weryfikacja polegała na wykonaniu count(*) zarówno w wolnych, jak i szybkich zapytaniach dla różnych zestawów danych. Następnie, dla małego podzbioru danych, sprawdziłem poprawność wszystkich wyników ręcznie.

Wszystkie kontrole dały stabilnie pozytywne wyniki. Wszystko naprawiliśmy!

Wyciągnięte Wnioski

Z tej historii można wyciągnąć wiele lekcji:

1. Plany zapytań nie mówią całej historii, ale mogą dawać wskazówki
2. Główni podejrzani nie zawsze są prawdziwymi winowajcami
3. Wolne zapytania można podzielić, aby zlokalizować wąskie gardła
4. Nie wszystkie optymalizacje są z natury redukcyjne
5. Użycie EXIST, gdzie to możliwe, może prowadzić do znacznego wzrostu wydajności

Wnioski

Przeszliśmy od czasu zapytania wynoszącego ~24 minuty do 2 sekund — to znaczny wzrost wydajności! Chociaż ten artykuł jest obszerny, wszystkie eksperymenty, które przeprowadziliśmy, miały miejsce w ciągu jednego dnia i szacunkowo zajęły od 1,5 do 2 godzin na optymalizację i testowanie.

SQL to wspaniały język, jeśli się go nie boicie, a spróbujecie poznać i wykorzystać. Posiadając dobre zrozumienie, jak wykonują się zapytania SQL, jak BD generuje plany zapytań, jak działają indeksy i po prostu rozmiar danych, z którymi się macie do czynienia, możecie bardzo skutecznie optymalizować zapytania. Niemniej jednak równie ważne jest, aby nadal próbować różnych podejść i stopniowo rozwiązywać problemy, znajdując wąskie gardła.

Najlepszą częścią osiągania takich wyników jest widoczne, zauważalne poprawienie szybkości działania – raport, który wcześniej nawet się nie ładował, teraz ładuje się niemal natychmiast.

Szczególne podziękowania moim towarzyszom z zespołu Aditya Mishra, Aditya Gaur i Varun Malhotra za burzę mózgów i Dinkarowi Pandirze za wykrycie ważnego błędu w naszym końcowym zapytaniu, zanim ostatecznie się z nim pożegnaliśmy!

Źródło: habr.com