Оптимизација упита базе података на примеру Б2Б услуге за градитеље

Како повећати 10 пута број упита бази података без преласка на продуктивнији сервер и одржати функционалност система? Рећи ћу вам како смо се носили са падом перформанси наше базе података, како смо оптимизовали СКЛ упите да опслужујемо што већи број корисника и да не повећавамо трошкове рачунарских ресурса.

Правим сервис за вођење пословних процеса у грађевинским фирмама. Са нама ради око 3 хиљаде компанија. Више од 10 хиљада људи ради са нашим системом сваког дана 4-10 сати. Решава различите проблеме планирања, обавештавања, упозорења, валидације... Користимо ПостгреСКЛ 9.6. У бази имамо око 300 табела и сваког дана у њу се прима до 200 милиона упита (10 хиљада различитих). У просеку имамо 3-4 хиљаде захтева у секунди, у најактивнијим тренуцима више од 10 хиљада захтева у секунди. Већина упита је ОЛАП. Много је мање додатака, модификација и брисања, што значи да је ОЛТП оптерећење релативно мало. Навео сам све ове бројеве како бисте могли да процените обим нашег пројекта и схватите колико наше искуство може бити корисно за вас.

Слика прва. Лирицал

Када смо започели развој, нисмо баш размишљали о томе какво ће оптерећење пасти на базу података и шта бисмо урадили ако сервер престане да вуче. Приликом дизајнирања базе података, следили смо опште препоруке и покушали да не пуцамо у ногу, али смо отишли ​​даље од општих савета као што је „немој да користиш шаблон Вредности атрибута ентитета нисмо ушли. Дизајнирали смо по принципима нормализације, избегавајући редундантност података и нисмо водили рачуна о убрзавању одређених упита. Чим су стигли први корисници, наишли смо на проблем са перформансама. Као и обично, били смо потпуно неспремни за ово. Испоставило се да су први проблеми једноставни. По правилу се све решавало додавањем новог индекса. Али дошло је време када су једноставне закрпе престале да раде. Схвативши да нам недостаје искуства и да нам је све теже да разумемо шта узрокује проблеме, ангажовали смо стручњаке који су нам помогли да правилно поставимо сервер, повежемо надгледање и показали где да тражимо statistika.

Слика два. Статистички

Дакле, имамо око 10 хиљада различитих упита који се извршавају у нашој бази података дневно. Од ових 10 хиљада, постоје чудовишта која се извршавају 2-3 милиона пута са просечним временом извршења од 0.1-0.3 мс, а постоје и упити са просечним временом извршења од 30 секунди који се позивају 100 пута дневно.

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

ТОП захтеви

Ово су најтежи упити који одузимају највише времена (укупно време). То су упити који се или позивају веома често или упити за које је потребно много времена да се изврше (дуги и чести упити су оптимизовани у првим итерацијама борбе за брзину). Као резултат тога, сервер троши највише времена на њихово извршење. Штавише, важно је раздвојити највише захтеве према укупном времену извршења и одвојено према ИО времену. Методе за оптимизацију таквих упита се мало разликују.

Уобичајена пракса свих компанија је да раде са ТОП захтевима. Мало их је оптимизовањем чак и једним упитом који може ослободити 5-10% ресурса. Међутим, како пројекат сазрева, оптимизација ТОП упита постаје све више нетривијалан задатак. Све једноставне методе су већ разрађене, а за „најтежи“ захтев је потребно „само“ 3-5% ресурса. Ако ТОП упити укупно заузимају мање од 30-40% времена, онда сте највероватније већ уложили напоре да брзо функционишу и време је да пређете на оптимизацију упита из следеће групе.
Остаје да се одговори на питање колико топ упита треба да буде укључено у ову групу. Обично узимам најмање 10, али не више од 20. Трудим се да се време првог и последњег у ТОП групи разликује не више од 10 пута. То јест, ако време извршења упита нагло падне са 1. места на 10., онда узимам ТОП-10, ако је пад постепенији, онда повећавам величину групе на 15 или 20.

Средњи сељаци

Све су то захтеви који долазе одмах после ТОП-а, са изузетком последњих 5-10%. Обично се у оптимизацији ових упита крије прилика за значајно повећање перформанси сервера. Ови захтеви могу тежити и до 80%. Али чак и ако је њихов удео премашио 50%, онда је време да их пажљивије погледате.

Реп

Као што је поменуто, ови упити долазе на крају и заузимају 5-10% времена. Можете заборавити на њих само ако не користите аутоматске алате за анализу упита, а онда њихова оптимизација такође може бити јефтина.

Како оценити сваку групу?

Користим СКЛ упит који помаже да се направи таква процена за ПостгреСКЛ (сигуран сам да се сличан упит може написати за многе друге ДБМС-ове)

СКЛ упит за процену величине ТОП-МЕДИУМ-ТАИЛ група

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

Резултат упита су три колоне, од којих свака садржи проценат времена потребног за обраду упита из ове групе. Унутар захтева постоје два броја (у мом случају то су 20 и 800) који одвајају захтеве једне групе од друге.

Овако се отприлике упоређују удео захтева у време када су радови на оптимизацији почели и сада.

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

За добијање текста захтева користимо следећи захтев

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

Ево листе најчешће коришћених техника које су нам помогле да убрзамо ТОП упите:

• Редизајн система, на пример, прерада логике обавештења помоћу посредника порука уместо периодичних упита бази података
• Додавање или мењање индекса
• Преписивање ОРМ упита на чисти СКЛ
• Преписивање лење логике учитавања података
• Кеширање кроз денормализацију података. На пример, имамо везу табеле Испорука -> Рачун -> Захтев -> Пријава. То јест, свака испорука је повезана са апликацијом преко других табела. Да не бисмо повезали све табеле у сваком захтеву, дуплирали смо везу ка захтеву у табели Испорука.
• Кеширање статичких табела са референтним књигама и ретко мењање табела у програмској меморији.

Понекад су промене довеле до импресивног редизајна, али су обезбедиле 5-10% оптерећења система и биле су оправдане. Временом је ауспух постајао све мањи, а захтевао се све озбиљнији редизајн.

Затим смо скренули пажњу на другу групу захтева – групу средњих сељака. У њему има много више упита и чинило се да ће за анализу целе групе бити потребно доста времена. Међутим, показало се да је већина упита веома једноставна за оптимизацију, а многи проблеми су се понављали десетине пута у различитим варијацијама. Ево примера неких типичних оптимизација које смо применили на десетине сличних упита и свака група оптимизованих упита је растеретила базу података за 3-5%.

• Уместо провере присуства записа помоћу ЦОУНТ и скенирања целе табеле, почео је да се користи ЕКСИСТС
• Отарасио сам се ДИСТИНЦТ-а (не постоји општи рецепт, али понекад га се лако можете решити убрзавањем захтева за 10-100 пута).

  На пример, уместо упита да изаберете све драјвере из велике табеле испорука (ДЕЛИВЕРИ)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  направио упит на релативно малој табели ПЕРСОН

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Чини се да смо користили корелирани подупит, али он даје убрзање више од 10 пута.

• У многим случајевима, ЦОУНТ је потпуно напуштен и
  замењен обрачуном приближне вредности
• уместо

  UPPER(s) LIKE JOHN%’ 
  

  коришћење

  s ILIKE “John%”
  

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

Као резултат тога, већ три године радимо на истом хардверу. Просечно дневно оптерећење је око 30%, у врховима достиже 70%. Број захтева, као и број корисника, повећан је приближно 10 пута. И све то захваљујући сталном праћењу ових истих група ТОП-МЕДИУМ захтева. Чим се појави нови захтев у ТОП групи, одмах га анализирамо и покушавамо да га убрзамо. Прегледамо МЕДИУМ групу једном недељно користећи скрипте за анализу упита. Ако наиђемо на нове упите које већ знамо да оптимизујемо, брзо их мењамо. Понекад налазимо нове методе оптимизације које се могу применити на неколико упита одједном.

Према нашим прогнозама, садашњи сервер ће издржати повећање броја корисника за још 3-5 пута. Истина, имамо још једног кеца у рукаву – још увек нисмо пренели СЕЛЕЦТ упите у огледало, као што се препоручује. Али ми то не радимо свесно, јер желимо да прво потпуно исцрпимо могућности „паметне“ оптимизације пре него што укључимо „тешку артиљерију“.
Критички поглед на обављени посао може предложити коришћење вертикалног скалирања. Купите моћнији сервер уместо да губите време стручњака. Сервер можда неће коштати толико, поготово што још нисмо исцрпили границе вертикалног скалирања. Међутим, само се број захтева повећао 10 пута. Током неколико година функционалност система се повећала и сада има више врста захтева. Функционалност која је постојала се, због кеширања, изводи у мањем броју захтева, али и у ефикаснијим захтевима. То значи да можете безбедно да помножите са још 5 да бисте добили прави коефицијент убрзања. Дакле, према најконзервативнијим проценама, можемо рећи да је убрзање било 50 пута или више. Вертикално окретање сервера коштало би 50 пута више. Поготово имајући у виду да када се изврши оптимизација, она ради све време, а рачун за изнајмљени сервер долази сваког месеца.

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