ProHoster > Blog > administracja > Historia pewnego śledztwa SQL

Historia pewnego ś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ę zaporowy. A ponieważ jest to obszar moich obowiązków, od razu skupiłem się na rozwiązaniu problemu.

prehistoria

Żeby było jasne o czym mówię opowiem trochę o VWO. To platforma, za pomocą której możesz uruchamiać na swoich stronach internetowych różnorodne kampanie targetowane: przeprowadzać eksperymenty A/B, śledzić odwiedziny i konwersje, analizować lejek sprzedażowy, wyświetlać mapy ciepła i odtwarzać nagrania wizyt.

Ale najważniejszą rzeczą na platformie jest raportowanie. Wszystkie powyższe funkcje są ze sobą powiązane. Natomiast dla klientów korporacyjnych ogromna ilość informacji byłaby po prostu bezużyteczna bez potężnej platformy, która prezentuje je w formie analitycznej.

Korzystając z platformy możesz wykonać losowe zapytanie na dużym zbiorze danych. Oto prosty przykład:

Pokaż wszystkie kliknięcia na stronie „abc.com” OD <data d1> DO <data d2> dla osób, które korzystały z przeglądarki Chrome LUB (z siedzibą w Europie ORAZ korzystały z iPhone'a)

Zwróć uwagę na operatory logiczne. Są one dostępne dla klientów w interfejsie zapytań, umożliwiając tworzenie dowolnie złożonych zapytań w celu uzyskania próbek.

Powolne żądanie

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

Pokaż wszystkie rekordy sesji dla użytkowników, którzy odwiedzili dowolną stronę z adresem URL zawierającym „/jobs”

Ta witryna miała duży ruch i specjalnie dla niej przechowywaliśmy ponad milion unikalnych adresów URL. Chcieli znaleźć dość prosty szablon adresu URL, który byłby powiązany z ich modelem biznesowym.

Wstępne dochodzenie

Przyjrzyjmy się temu, co dzieje się 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 < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

A oto terminy:

Planowany czas: 1.480 ms Czas wykonania: 1431924.650 ms

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

Przeanalizujmy tę prośbę dalej. Jak widać, robi to JOIN trzy tabele:

  1. Sesje: aby wyświetlić informacje o sesji: przeglądarce, kliencie użytkownika, kraju i tak dalej.
  2. dane_nagrania: zarejestrowane adresy URL, strony, czas trwania wizyt
  3. urls: Aby uniknąć duplikowania bardzo dużych adresów URL, przechowujemy je w osobnej tabeli.

Należy również pamiętać, że wszystkie nasze tabele są już podzielone według account_id. W ten sposób wykluczona jest sytuacja, w której jedno szczególnie duże konto powoduje problemy dla innych.

Szukam wskazówek

Po bliższym przyjrzeniu się widzimy, że coś jest nie tak z konkretnym żądaniem. Warto przyjrzeć się bliżej tej linijce:

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

Pierwsza myśl była taka, że ​​może dlatego ILIKE na wszystkich tych długich adresach URL (mamy ponad 1,4 miliona wyjątkowy adresy URL zebrane dla tego konta) wydajność może ulec pogorszeniu.

Ale nie, nie o to chodzi!

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

Time: 5231.765 ms

Samo żądanie wyszukiwania szablonu zajmuje tylko 5 sekund. Znalezienie wzorca w milionie unikalnych adresów URL najwyraźniej nie stanowi problemu.

Kolejnym podejrzanym na liście jest kilku JOIN. Być może ich nadużywanie spowodowało spowolnienie? Zazwyczaj JOINsą najbardziej oczywistymi kandydatami na problemy z wydajnością, ale nie sądził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 < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

I tak też nie było w naszym przypadku. JOINokazało się dość szybkie.

Zawężenie kręgu podejrzanych

Byłem gotowy rozpocząć zmianę zapytania, aby osiągnąć możliwą poprawę wydajności. Mój zespół i ja opracowaliśmy 2 główne pomysły:

  • Użyj EXISTS dla adresu URL podzapytania: Chcieliśmy ponownie sprawdzić, czy wystąpiły jakieś problemy z podzapytaniem o adresy URL. Jednym ze sposobów osiągnięcia tego jest po prostu użycie EXISTS. EXISTS może znacznie poprawia wydajność, ponieważ kończy się natychmiast po znalezieniu jedynego ciągu znaków spełniającego warunek.

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 < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

No tak. Podzapytanie po opakowaniu EXISTS, sprawia, że ​​wszystko jest superszybkie. Następne logiczne pytanie brzmi: dlaczego żądanie with JOIN-ami i samo podzapytanie są pojedyncze szybkie, ale razem strasznie powolne?

  • Przeniesienie podzapytania do CTE : Jeśli zapytanie samo w sobie jest szybkie, możemy po prostu najpierw obliczyć szybki wynik, a następnie dostarczyć 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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Ale nadal było bardzo wolno.

Znalezienie winowajcy

Przez cały ten czas przed oczami mignęła mi jedna mała rzecz, którą ciągle odsuwałem na bok. Ale ponieważ nie pozostało już nic innego, postanowiłem też na nią spojrzeć. mówię o && operator. Do widzenia EXISTS po prostu poprawiona wydajność && był jedynym pozostałym wspólnym czynnikiem we wszystkich wersjach powolnego zapytania.

Patrzeć na dokumentacja, widzimy to && używane, gdy trzeba znaleźć wspólne elementy między dwiema tablicami.

W pierwotnym żądaniu jest to:

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

Oznacza to, że wyszukujemy wzorce w naszych adresach URL, a następnie znajdujemy punkt przecięcia ze wszystkimi adresami URL z częstymi postami. Jest to nieco mylące, ponieważ „adresy URL” nie odnoszą się tutaj do tabeli zawierającej wszystkie adresy URL, ale do kolumny „adresy URL” w tabeli recording_data.

Wraz z rosnącymi podejrzeniami dot &&, próbowałem znaleźć dla nich potwierdzenie w wygenerowanym planie zapytań EXPLAIN ANALYZE (Mam już zapisany plan, ale zwykle wygodniej jest mi eksperymentować z SQL niż próbować zrozumieć nieprzejrzystość planistów zapytań).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

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

Przetestowałem to, izolując warunek

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

To zapytanie było powolne. Ponieważ JOIN-s są szybkie, a podzapytania są szybkie, jedyne, co pozostało, to && operator.

To tylko kluczowa operacja. Zawsze musimy przeszukać całą tabelę adresów URL w celu znalezienia wzorca i zawsze musimy znaleźć punkty przecięcia. Nie możemy wyszukiwać bezpośrednio według rekordów URL, ponieważ są to tylko identyfikatory, do których się odnoszą urls.

W drodze do rozwiązania

&& powoli, ponieważ oba zestawy są ogromne. Jeśli wymienię, operacja będzie stosunkowo szybka urls na { "http://google.com/", "http://wingify.com/" }.

Zacząłem szukać sposobu na ustawienie skrzyżowania w Postgres bez użycia &&, ale bez większych sukcesów.

Ostatecznie postanowiliśmy rozwiązać problem w izolacji: daj mi wszystko urls linie, dla których adres URL pasuje do wzorca. Bez dodatkowych warunków będzie to - 

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

Zamiast JOIN składnia Właśnie użyłem podzapytania i rozwinąłem recording_data.urls array, dzięki czemu można bezpośrednio zastosować warunek w WHERE.

Najważniejszą rzeczą tutaj jest to && służy do sprawdzania, czy dany wpis zawiera pasujący adres URL. Jeśli trochę przymrużysz oczy, zobaczysz, że operacja ta przechodzi przez elementy tablicy (lub wiersze tabeli) i zatrzymuje się, gdy spełniony zostanie warunek (dopasowanie). Nic Ci nie przypomina? Tak, EXISTS.

Od recording_data.urls można się do niego odwoływać spoza kontekstu podzapytania, gdy tak się stanie, możemy odwołać się do naszego starego przyjaciela EXISTS i zawiń nim podzapytanie.

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

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 r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

I ostateczny czas realizacji Time: 1898.717 ms Czas świętować?!?

Nie tak szybko! Najpierw musisz sprawdzić poprawność. Byłem bardzo podejrzliwy EXISTS optymalizacja, ponieważ zmienia logikę, aby zakończyć wcześniej. Musimy mieć pewność, że do żądania nie dodaliśmy nieoczywistego błędu.

Prostym testem było uruchomienie count(*) zarówno w przypadku wolnych, jak i szybkich zapytań o dużą liczbę różnych zestawów danych. Następnie dla małego podzbioru danych ręcznie sprawdziłem, czy wszystkie wyniki są prawidłowe.

Wszystkie testy dały niezmiennie pozytywne wyniki. Naprawiliśmy wszystko!

Zdobyta wiedza

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

  1. Plany zapytań nie mówią wszystkiego, ale mogą dostarczyć wskazówek
  2. Nie zawsze głównymi podejrzanymi są prawdziwi winowajcy
  3. Powolne zapytania można podzielić na części, aby wyizolować wąskie gardła
  4. Nie wszystkie optymalizacje mają charakter redukcyjny
  5. Używać EXISTtam, gdzie to możliwe, może prowadzić do dramatycznego wzrostu produktywności

Wniosek

Przeszliśmy z czasu zapytania wynoszącego ~24 minuty do 2 sekund — całkiem znaczny wzrost wydajności! Chociaż ten artykuł okazał się duży, wszystkie eksperymenty, które przeprowadziliśmy, odbyły się w ciągu jednego dnia, a szacuje się, że optymalizacja i testowanie zajmowały od 1,5 do 2 godzin.

SQL to wspaniały język, jeśli się go nie boisz, ale spróbuj się go nauczyć i używać. Mając dobre zrozumienie sposobu wykonywania zapytań SQL, sposobu, w jaki baza danych generuje plany zapytań, działania indeksów i po prostu rozmiaru danych, z którymi masz do czynienia, możesz odnieść sukces w optymalizacji zapytań. Równie ważne jest jednak dalsze wypróbowywanie różnych podejść i powolne rozwiązywanie problemu, znajdowanie wąskich gardeł.

Największą zaletą osiągania takich wyników jest zauważalna, widoczna poprawa szybkości — raport, który wcześniej nawet się nie ładował, teraz ładuje się niemal natychmiast.

Specjalne podziękowania dla moi towarzysze na rozkaz Adityi Mishry, Aditya Gauru и Varuna Malhotry do burzy mózgów i Dinkar Pandir za znalezienie ważnego błędu w naszej ostatniej prośbie, zanim w końcu się z nim pożegnaliśmy!

Źródło: www.habr.com

Dodaj komentarz