Хабрахабр

[Перевод] История одного SQL расследования

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

Предыстория

Это платформа, с помощью которой можно запускать разные таргетированные кампании на своих сайтах: проводить A/B эксперименты, отслеживать посетителей и конверсии, делать анализ воронки продаж, отображать тепловые карты и проигрывать записи визитов. Чтобы было понятно о чём речь, я расскажу совсем немного о VWO.

Все вышеперечисленные функции связаны между собой. Но самое главное в платформе — составление отчетов. И для корпоративных клиентов, огромный массив из информации был бы просто бесполезен без мощной платформы, представляющей их в виде для аналитики.

Вот простенький пример: Используя платформу, можно сделать произвольный запрос на большом наборе данных.

Показать все клики на странице "abc.com"
ОТ <даты d1> ДО <даты d2>
для людей, которые
использовали Chrome ИЛИ
(находились в Европе И использовали iPhone)

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

Медленный запрос

Клиент, о котором идет речь, пытался сделать что-то, что интуитивно должно работать быстро:

Покажи все записи сессий
для пользователей посетивших любую страницу
с урлом, где есть "/jobs"

И они хотели найти довольно простой шаблон урла, относящегося к их бизнес-модели. На этом сайте было огромное количество трафика, и мы хранили более миллиона уникальных URL-адресов только для него.

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

Ниже приведен исходный медленный SQL-запрос: Давайте посмотрим, что же происходит в базе данных.

SELECT count(*) FROM acc_.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 трёх таблиц: Давайте поизучаем запрос дальше.

  1. sessions: для отображения сессионной информации: браузер, юзер агент, страна и так далее.
  2. recording_data: записанные урлы, страницы, продолжительность визитов
  3. urls: чтобы избежать дублирования чрезвычайно больших урлов, мы храним их в отдельной таблице.

Таким образом, исключена ситуация, когда из-за одного особо большого аккаунта проблемы возникают у остальных. Также обратите внимание, что все наши таблицы уже разделены по 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%' );

717 ms Пора праздновать?!? И окончательное время выполнения Time: 1898.

Сначала нужно проверить корректность. Не так быстро! Мы должны быть уверены, что мы не добавили неочевидную ошибку в запрос. Я был крайне подозрителен в отношении EXISTS оптимизации, так как она меняет логику на более раннее завершение.

Затем, для небольшого подмножества данных я проверил правильность всех результатов вручную. Простая проверка заключалась в выполнении count(*) и на медленных, и на быстрых запросах для большого количества разных наборов данных.

Мы всё починили! Все проверки дали стабильно положительные результаты.

Извлеченные Уроки

Из этой истории можно извлечь немало уроков:

  1. Планы запросов не рассказывают всю историю, но могут давать подсказки
  2. Главные подозреваемые не всегда являются настоящими виновниками
  3. Медленные запросы можно разбить, чтобы изолировать узкие места
  4. Не все оптимизации по природе редуктивны
  5. Использование EXIST, где это возможно, способно привести к резому росту производительности

Вывод

Хотя эта статья и вышла большой, все эксперименты, которые мы делали, произошли в один день, и по прикидкам, заняли от 1,5 до 2 часов для оптимизаций и тестирования. Мы прошли от времени запроса в ~24 минуты до 2 секунд — весьма серьёзный рост производительности!

Имея хорошее понимание того, как выполняются SQL-запросы, как БД генерирует планы запросов, как работают индексы и просто размера данных, с которым имеете дело, вы сможете очень преуспеть в оптимизации запросов. SQL — чудесный язык, если не бояться его, а попытаться познать и использовать. Не менее важно, однако, продолжать пробовать различные подходы и медленно разбивать проблему, находя узкие места.

Лучшая часть в достижении подобных результатов это заметное видимое улучшение скорости работы — когда отчет, который раньше даже не загружался, теперь загружается почти мгновенно.

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

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть