ProHoster > Блог > Администриране > Днес ще говорим за открити инструменти за оценка на производителността на процесори, памет, файлови системи и системи за съхранение на данни. В списъка са включени инструменти, предлагани от резиденти на GitHub и участници.

Днес ще говорим за открити инструменти за оценка на производителността на процесори, памет, файлови системи и системи за съхранение на данни. В списъка са включени инструменти, предлагани от резиденти на GitHub и участници.

През декември миналата година получих интересен доклад за грешка от екипа за поддръжка на 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 адреси, за да правим търсене по шаблона, и винаги трябва да намираме пресечени стойности. Не можем да търсим директно по записите на урлите, защото това са просто идентификатори, свързани с 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 и да го обвием в подзапрос.

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

ИЗБЕРЕТЕ 
    количество(*) 
ОТ 
    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 заявки, как БД генерира планове за заявки, как работят индексите и просто размера на данните, с които работите, можете да постигнете страхотни резултати при оптимизацията на заявки. Не по-малко важно е обаче да продължите да пробвате различни подходи и бавно да разкъсвате проблема, намирайки тесните места.

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

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

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

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