🥇Історія одного SQL розслідування

Торік у грудні я отримав цікавий звіт про помилку від команди підтримки VWO. Час завантаження одного з аналітичних звітів для великого корпоративного клієнта видавався непомірно великим. А оскільки це сфера моєї відповідальності, я відразу зосередився на вирішенні проблеми.

Передісторія

Щоб було зрозуміло про що мова, я розповім зовсім небагато про VWO. Це платформа, за допомогою якої можна запускати різні таргетовані кампанії на своїх сайтах: проводити A/B експерименти, відстежувати відвідувачів та конверсії, робити аналіз вирви продажів, відображати теплові карти та програвати записи візитів.

Але найголовніше у платформі — складання звітів. Усі перелічені функції пов'язані між собою. І для корпоративних клієнтів, величезний масив з інформації був би просто непотрібним без потужної платформи, що представляє їх у вигляді для аналітики.

Використовуючи платформу, можна зробити довільний запит великого набору даних. Ось простенький приклад:

Показати всі кліки на сторінці "abc.com" ВІД <дати d1> ДО <дати d2> для людей, які використовували Chrome АБО (перебували в Європі І використовували iPhone)

Зверніть увагу на булеві оператори. Вони доступні для клієнтів в інтерфейсі запиту, щоб робити як завгодно складні запити для отримання вибірок.

Повільний запит

Клієнт, про якого йдеться, намагався зробити щось, що інтуїтивно має працювати швидко:

Покажи всі записи сесій для користувачів, які відвідали будь-яку сторінку з урлом, де є "/jobs"

На цьому сайті була величезна кількість трафіку, і ми зберігали більше мільйона унікальних 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 трьох таблиць:

сесіях: для відображення сесійної інформації: браузер, користувач агент, країна і так далі.
recording_data: записані урли, сторінки, тривалість візитів
URL-адреси: щоб уникнути дублювання надзвичайно великих урлів, ми зберігаємо їх у окремій таблиці.

Також зверніть увагу, що всі наші таблиці вже розділені на account_id. Таким чином, виключено ситуацію, коли через один особливо великий обліковий запис проблеми виникають в інших.

У пошуках доказів

При найближчому розгляді бачимо, що щось у конкретному запиті негаразд. Варто придивитися до цього рядка:

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

Перша думка була, що можливо, через ILIKE на всіх цих довгих урлах (у нас є понад 1,4 мільйона унікальних URL-адрес, зібраних для цього облікового запису) продуктивність може просідати.

Але, ні – справа не в цьому!

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

Time: 5231.765 ms

Сам запит пошуку за шаблоном займає лише 5 секунд. Пошук за шаблоном на мільйон унікальних урлів явно не є проблемою.

Наступний підозрюваний за списком – кілька 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 підзапиту: Ми хотіли ще раз перевірити, чи немає проблем із підзапитом для урлів Один із способів цього досягти — просто використати 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-ами і сам підзапит швидкі окремо, але жахливо гальмують разом?

Переміщуємо підзапит у 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[]   )

Що означає, що ми робимо пошук за шаблоном за нашими урлами, потім знаходимо перетин з усіма урлами із загальними записами. Це трохи заплутано, оскільки «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-и швидкі та підзапити швидкі, залишався тільки && оператор.

Ось лише це ключова операція. Нам завжди потрібно шукати по всій основній таблиці URL-адрес, щоб шукати за шаблоном, і нам завжди потрібно знаходити перетину. Ми не можемо шукати за записами урлів безпосередньо, тому що це просто айдішники, що посилаються на urls.

На шляху до вирішення

&& повільний, тому що обидва сета величезні. Операція буде відносно швидкою, якщо заміню urls на { "http://google.com/", "http://wingify.com/" }.

Я почав шукати спосіб зробити у Postgres перетин множин без використання &&але без особливого успіху.

Зрештою ми вирішили просто вирішити проблему ізольовано: дай мені все urls рядки, для яких урл відповідає шаблону. Без додаткових умов це буде.

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-запити, як БД генерує плани запитів, як працюють індекси та просто розміру даних, з яким маєте справу, ви зможете дуже досягти успіху в оптимізації запитів. Не менш важливо, однак, продовжувати пробувати різні підходи та повільно розбивати проблему, знаходячи вузькі місця.

Найкраща частина у досягненні подібних результатів – це помітне видиме покращення швидкості роботи — коли звіт, який раніше навіть не завантажувався, тепер завантажується майже миттєво.

Особлива подяка моїм товаришам за командою Адітьє Мішре, Адітьє Гауру и Варуну Малхотре за мозковий штурм та Дінкару Пандіру за те, що знайшов важливу помилку у нашому фінальному запиті, перш ніж ми остаточно з ним розпрощалися!

Джерело: habr.com

Історія одного SQL розслідування