У снежні мінулага гады я атрымаў цікавую справаздачу аб памылцы ад каманды падтрымкі 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