ПроХостер > блог > Администрација > Прича о једној СКЛ истрази

Прича о једној СКЛ истрази

Прошлог децембра добио сам занимљив извештај о грешци од ВВО тима за подршку. Време учитавања једног од аналитичких извештаја за клијента великог предузећа деловало је претерано. А пошто је ово моја област одговорности, одмах сам се фокусирао на решавање проблема.

praistorija

Да би било јасно о чему говорим, рећи ћу вам нешто о ВВО-у. Ово је платформа са којом можете покренути различите циљане кампање на својим веб локацијама: спроводити А/Б експерименте, пратити посетиоце и конверзије, анализирати ток продаје, приказати топлотне мапе и пуштати снимке посета.

Али најважнија ствар у вези са платформом је извештавање. Све горе наведене функције су међусобно повезане. А за корпоративне клијенте, огромна количина информација била би једноставно бескорисна без моћне платформе која их представља у форми аналитике.

Користећи платформу, можете направити насумични упит на великом скупу података. Ево једноставног примера:

Прикажи све кликове на страници „абц.цом“ ОД <датум д1> ДО <датум д2> за људе који су користили Цхроме ИЛИ (који се налазе у Европи И користили иПхоне)

Обратите пажњу на Булове операторе. Они су доступни клијентима у интерфејсу упита да праве произвољно сложене упите за добијање узорака.

Споро захтев

Дотични клијент је покушавао да уради нешто што би интуитивно требало да функционише брзо:

Прикажи све записе сесије за кориснике који су посетили било коју страницу са УРЛ-ом који садржи „/јобс“

Овај сајт је имао тону саобраћаја и чували смо преко милион јединствених УРЛ адреса само за то. И желели су да пронађу прилично једноставан УРЛ шаблон који се односи на њихов пословни модел.

Прелиминарна истрага

Хајде да погледамо шта се дешава у бази података. Испод је оригинални спори СКЛ упит:

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 мс Време извршења: 1431924.650 мс

Упит је прешао 150 хиљада редова. Планер упита показао је неколико занимљивих детаља, али без очигледних уских грла.

Хајде да даље проучимо захтев. Као што видите, има JOIN три табеле:

  1. сесије: за приказ информација о сесији: претраживач, кориснички агент, земља и тако даље.
  2. рецординг_дата: снимљене УРЛ адресе, странице, трајање посета
  3. урл адресе: Да бисмо избегли дуплирање изузетно великих УРЛ адреса, чувамо их у посебној табели.

Такође имајте на уму да су све наше табеле већ подељене по account_id. На овај начин се искључује ситуација у којој један посебно велики налог ствара проблеме другима.

Тражећи трагове

Пажљивијим прегледом видимо да нешто није у реду са одређеним захтевом. Вреди пажљивије погледати ову линију:

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

Прва помисао је била да можда зато ILIKE на свим овим дугачким УРЛ-овима (имамо преко 1,4 милиона јединствен УРЛ-ови прикупљени за овај налог) учинак може да се смањи.

Али не, није то поента!

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. 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-ами и сам потупит су брзи појединачно, али су страшно спори заједно?

  • Премештање подупита у ЦТЕ : Ако је упит сам по себи брз, можемо једноставно прво израчунати брзи резултат, а затим га доставити главном упиту

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

Што значи да вршимо претрагу шаблона на нашим УРЛ адресама, а затим проналазимо раскрсницу са свим УРЛ адресама са уобичајеним објавама. Ово је мало збуњујуће јер се „урлс“ овде не односи на табелу која садржи све УРЛ адресе, већ на колону „урлс“ у табели recording_data.

Уз све веће сумње у вези &&, покушао сам да пронађем потврду за њих у генерисаном плану упита EXPLAIN ANALYZE (Већ сам имао сачуван план, али ми је обично угодније да експериментишем у СКЛ-у него да покушавам да разумем непрозирност планера упита).

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-с су брзи и подупити су брзи, једино што је остало је && оператер.

Ово је само кључна операција. Увек морамо да претражимо целу табелу УРЛ-ова у основи да бисмо тражили образац, и увек морамо да пронађемо раскрснице. Не можемо директно претраживати по УРЛ записима, јер су то само ИД-ови на које се односе urls.

На путу ка решењу

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

Почео сам да тражим начин да направим раскрсницу у Постгресу без коришћења &&, али без већег успеха.

На крају смо одлучили да проблем решимо само изоловано: дај ми све 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.

Овде је најважније то && користи се за проверу да ли дати унос садржи одговарајући УРЛ. Ако мало жмирите, можете видети да се ова операција креће кроз елементе низа (или редова табеле) и зауставља се када се испуни услов (подударање). Не подсећа те ни на шта? да, 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(*) на спорим и брзим упитима за велики број различитих скупова података. Затим, за мали подскуп података, ручно сам проверио да ли су сви резултати тачни.

Сви тестови су дали константно позитивне резултате. Све смо поправили!

Научене лекције

Постоје многе поуке које се могу научити из ове приче:

  1. Планови упита не говоре целу причу, али могу дати трагове
  2. Главни осумњичени нису увек прави кривци
  3. Спори упити се могу рашчланити да би се изоловала уска грла
  4. Нису све оптимизације редуктивне природе
  5. Коришћење EXIST, где је то могуће, може довести до драматичног повећања продуктивности

Излаз

Прешли смо са времена упита од ~24 минута на 2 секунде – прилично значајно повећање перформанси! Иако је овај чланак изашао на велико, сви експерименти које смо радили десили су се у једном дану, а процењено је да им је за оптимизацију и тестирање потребно између 1,5 и 2 сата.

СКЛ је диван језик ако га се не плашите, али покушајте да га научите и користите. Ако добро разумете како се извршавају СКЛ упити, како база података генерише планове упита, како функционишу индекси и једноставно величину података са којима имате посла, можете бити веома успешни у оптимизацији упита. Подједнако је важно, међутим, наставити да испробавамо различите приступе и полако разбијамо проблем, проналазећи уска грла.

Најбољи део у постизању оваквих резултата је приметно, видљиво побољшање брзине – где се извештај који се раније није ни учитавао сада учитава скоро тренутно.

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

Извор: ввв.хабр.цом

Додај коментар