🥇Историята на едно SQL разследване

Миналия декември получих интересен доклад за грешка от екипа за поддръжка на VWO. Времето за зареждане на един от отчетите за анализ за голям корпоративен клиент изглежда непосилно. И тъй като това е моята зона на отговорност, веднага се съсредоточих върху решаването на проблема.

праистория

За да стане ясно за какво говоря, ще ви разкажа малко за VWO. Това е платформа, с която можете да стартирате различни целеви кампании на уебсайтовете си: да провеждате A/B експерименти, да проследявате посетители и реализации, да анализирате фунията на продажбите, да показвате топлинни карти и да възпроизвеждате записи на посещения.

Но най-важното нещо за платформата е отчитането. Всички горепосочени функции са взаимосвързани. А за корпоративните клиенти огромно количество информация би било просто безполезно без мощна платформа, която да я представя под формата на анализ.

С помощта на платформата можете да направите произволна заявка за голям набор от данни. Ето един прост пример:

Показване на всички кликвания на страница „abc.com“ ОТ <дата d1> ДО <дата d2> за хора, които са използвали Chrome ИЛИ (намиращи се в Европа И използвали iPhone)

Обърнете внимание на булевите оператори. Те са достъпни за клиентите в интерфейса за заявки, за да правят произволно сложни заявки за получаване на проби.

Бавно искане

Въпросният клиент се опитваше да направи нещо, което интуитивно трябва да работи бързо:

Показване на всички записи на сесии за потребители, посетили която и да е страница с URL адрес, съдържащ "/jobs"

Този сайт имаше много трафик и ние съхранявахме над милион уникални URL адреса само за него. И те искаха да намерят доста прост URL шаблон, който да е свързан с техния бизнес модел.

Предварително разследване

Нека да разгледаме какво се случва в базата данни. По-долу е оригиналната бавна 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 ;

А ето и времената:

Планирано време: 1.480 ms Време за изпълнение: 1431924.650 ms

Заявката обходи 150 хиляди реда. Планировчикът на заявки показа няколко интересни подробности, но без очевидни тесни места.

Нека проучим заявката допълнително. Както можете да видите, той го прави JOIN три маси:

сесии: за показване на информация за сесията: браузър, потребителски агент, държава и т.н.
запис_данни: записани URL адреси, страници, продължителност на посещенията
URL адреси: За да избегнем дублирането на изключително големи URL адреси, ние ги съхраняваме в отделна таблица.

Също така имайте предвид, че всички наши таблици вече са разделени по account_id. По този начин се изключва ситуация, при която един особено голям акаунт създава проблеми на други.

Търся улики

При по-внимателна проверка виждаме, че нещо не е наред с конкретна заявка. Струва си да разгледаме по-отблизо този ред:

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

Първата мисъл беше, че може би защото ILIKE на всички тези дълги URL адреси (имаме над 1,4 милиона уникален URL адреси, събрани за този акаунт), производителността може да се влоши.

Но не, това не е важното!

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

Time: 5231.765 ms

Самата заявка за търсене на шаблон отнема само 5 секунди. Търсенето на модел в милион уникални URL адреси очевидно не е проблем.

Следващите заподозрени в списъка са няколко JOIN. Може би прекомерната им употреба е причинила забавянето? Обикновено JOINса най-очевидните кандидати за проблеми с производителността, но не вярвах, че нашият случай е типичен.

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

И това също не беше нашият случай. JOINОказа се доста бързо.

Стесняване на кръга на заподозрените

Бях готов да започна да променям заявката, за да постигна всички възможни подобрения на производителността. Моят екип и аз разработихме 2 основни идеи:

Използвайте EXISTS за URL адрес на подзаявка: Искахме да проверим отново дали има проблеми с подзаявката за URL адресите. Един от начините да постигнете това е просто да използвате EXISTS. EXISTS мога значително подобрява производителността, тъй като приключва незабавно, щом намери единствения низ, който отговаря на условието.

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

Е да. Подзаявка, когато е обвита EXISTS, прави всичко супер бързо. Следващият логичен въпрос е защо искането с JOIN-ami и самата подзаявка са бързи поотделно, но са ужасно бавни заедно?

Преместване на подзаявката към CTE : Ако заявката е бърза сама по себе си, можем просто да изчислим бързия резултат първо и след това да го предоставим на основната заявка

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;

Но все още беше много бавно.

Намиране на виновника

През цялото това време пред очите ми блесна едно малко нещо, което постоянно отхвърлях. Но понеже не остана нищо друго, реших да я погледна и аз. говоря за && оператор. Чао EXISTS просто подобрена производителност && беше единственият останал общ фактор във всички версии на бавната заявка.

Гледам към документация, виждаме това && използва се, когато трябва да намерите общи елементи между два масива.

В първоначалното искане това е:

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

Което означава, че извършваме търсене по шаблон на нашите URL адреси, след което намираме пресечната точка с всички URL адреси с общи публикации. Това е малко объркващо, защото „urls“ тук не се отнася до таблицата, съдържаща всички URL адреси, а към колоната „urls“ в таблицата recording_data.

С нарастващи подозрения относно &&, опитах се да намеря потвърждение за тях в генерирания план за заявка EXPLAIN ANALYZE (Вече имах записан план, но обикновено ми е по-удобно да експериментирам в SQL, отколкото да се опитвам да разбера непрозрачността на програмите за планиране на заявки).

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

Имаше няколко реда филтри само от &&. Което означаваше, че тази операция беше не само скъпа, но и правена няколко пъти.

Тествах това чрез изолиране на състоянието

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[]

Това запитване беше бавно. Тъй като JOIN-s са бързи и подзаявките са бързи, единственото нещо, което остана беше && оператор.

Това е просто ключова операция. Винаги трябва да претърсим цялата основна таблица с URL адреси, за да търсим модел, и винаги трябва да намерим пресечни точки. Не можем да търсим директно по URL записи, защото това са само идентификатори, отнасящи се до urls.

По пътя към решение

&& бавен, защото и двата комплекта са огромни. Операцията ще бъде сравнително бърза, ако сменя urls на { "http://google.com/", "http://wingify.com/" }.

Започнах да търся начин да направя пресичане на множество в Postgres, без да използвам &&, но без особен успех.

В крайна сметка решихме просто да решим проблема изолирано: дайте ми всичко urls редове, за които URL адресът съответства на модела. Без допълнителни условия ще бъде -

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%'

Вместо да JOIN синтаксис Току-що използвах подзаявка и я разширих recording_data.urls масив, за да можете директно да приложите условието в WHERE.

Най-важното тук е това && използва се за проверка дали даден запис съдържа съответстващ URL адрес. Ако присвиете малко очи, можете да видите, че тази операция се движи през елементите на масив (или редове от таблица) и спира, когато е изпълнено условие (съвпадение). Нищо не ти напомня? да EXISTS.

От нататък recording_data.urls може да бъде препратен извън контекста на подзаявката, когато това се случи, можем да се върнем към нашия стар приятел EXISTS и обвийте подзаявката с него.

Събирайки всичко заедно, получаваме крайната оптимизирана заявка:

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%'
    );

И крайното време за изпълнение Time: 1898.717 ms Време е за празнуване?!?

Не толкова бързо! Първо трябва да проверите правилността. Бях изключително подозрителен относно EXISTS оптимизация, тъй като променя логиката, за да завърши по-рано. Трябва да сме сигурни, че не сме добавили неочевидна грешка към заявката.

Прост тест беше да се изпълни count(*) на бавни и бързи заявки за голям брой различни набори от данни. След това, за малък поднабор от данни, ръчно проверих дали всички резултати са правилни.

Всички тестове дадоха постоянно положителни резултати. Поправихме всичко!

Поуки

Има много поуки, които можете да научите от тази история:

Плановете за запитване не разказват цялата история, но могат да предоставят улики
Главните заподозрени не винаги са истинските виновници
Бавните заявки могат да бъдат разбити, за да се изолират тесните места
Не всички оптимизации са редуктивни по природа
Употреба EXIST, където е възможно, може да доведе до драстични увеличения на производителността

Продукция

Преминахме от време за заявка от ~24 минути на 2 секунди - доста значително увеличение на производителността! Въпреки че тази статия излезе голяма, всички експерименти, които направихме, се случиха в един ден и беше изчислено, че са отнели между 1,5 и 2 часа за оптимизации и тестване.

SQL е прекрасен език, ако не се страхувате от него, но се опитайте да го научите и използвате. Като разбирате добре как се изпълняват SQL заявките, как базата данни генерира планове за заявки, как работят индексите и просто размера на данните, с които работите, можете да бъдете много успешни в оптимизирането на заявките. Също толкова важно е обаче да продължите да изпробвате различни подходи и бавно да разграждате проблема, като намирате тесните места.

Най-добрата част от постигането на тези резултати е забележимото, видимо подобрение на скоростта - където отчет, който преди дори не се зареждаше, сега се зарежда почти моментално.

Специални благодарности на моите другари по заповед на Адитя Мишра, Адитя Гауру и Варун Малхотра за мозъчна атака и Динкар Пандир за намирането на важна грешка в последната ни заявка, преди най-накрая да се сбогуваме с нея!

Източник: www.habr.com

Историята на едно SQL разследване