ProHoster > Блог > Администриране > Историята на едно SQL разследване

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

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

Предистория

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

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

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

Покажи всички кликове на страницата "abc.com"
ОТ  ДО 
за хора, които
използвали 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 = 5 
    AND recording_data.num_of_pages > 0 ;

А ето тайминги:

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

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

Нека разгледаме запитването по-надълбоко. Както се вижда, то извършва JOIN три таблици:

  1. sessions: за показване на сесийната информация: браузър, потребителски агент, страна и т.н.
  2. recording_data: записани URL адреси, страници, продължителност на посещенията
  3. urls: за да избегнем дублирането на изключително големи 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 реда)

Време: 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 =5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 ред)

Време: 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 = 5
    AND recording_data.num_of_pages > 0;
 count
 32519
(1 ред)
Време: 1636.637 ms

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

  • Преместваме подзаявката в 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 = 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, отколкото да се опитвам да разбера неясноти в планирания на заявки).

Филтър: ((urls && ($0)::text[]) И (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) И (r_time = '5'::double precision) И (num_of_pages > 0))
                           Редове, отстранени от филтъра: 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-ите са бързи и подзапитванията са бързи, оставаше само && оператор.

Това е ключовата операция. Винаги трябва да търсим в цялата основна таблица с 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 и да обвием подзаявката.

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

ИЗБЕРЕТЕ 
    брой(*) 
ОТ 
    acc_{account_id}.urls като recordings_urls, 
    acc_{account_id}.recording_data като recording_data, 
    acc_{account_id}.sessions като sessions 
КЪДЕ 
    recording_data.usp_id = sessions.usp_id 
    И  (  1 = 1  )  
    И sessions.referrer_id = recordings_urls.id 
    И r_time > to_timestamp(1542585600) 
    И r_time =5 
    И recording_data.num_of_pages > 0
    И СЪЩЕСТВУВА(
        ИЗБЕРЕТЕ urls.url
        ОТ 
            acc_{account_id}.urls като urls,
            (ИЗБЕРЕТЕ unnest(urls) КАТО rec_url_id ОТ acc_{account_id}.recording_data) 
            КАТО unrolled_urls
        КЪДЕ
            urls.id = unrolled_urls.rec_url_id И
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

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

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

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

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

Извлечени Уроци

От тази история могат да се извлекат много уроци:

  1. Плановете за заявки не разказват цялата история, но могат да дадат насоки
  2. Главните заподозрени не винаги са истинските виновници
  3. Бавните заявки могат да бъдат разделени, за да се изолират тесните места
  4. Не всички оптимизации по природа са редуктивни
  5. Използване СЪЩЕСТВУВА, където е възможно, може да доведе до рязко повишаване на производителността

Извод

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

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

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

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

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

Купете надежден хостинг за сайтове с защита от DDoS, VPS VDS сървъри 🔥 Купете надежден хостинг за сайтове с защита от DDoS, VPS VDS сървъри | ProHoster