Оптимизирање на барањата за базата на податоци користејќи пример на услуга B2B за градители

Како да се зголеми 10 пати повеќе од бројот на прашања во базата на податоци без да се преселите на попродуктивен сервер и да се одржи функционалноста на системот? Ќе ви кажам како се справивме со падот на перформансите на нашата база на податоци, како ги оптимизиравме SQL барањата за да опслужуваме што е можно повеќе корисници и да не ги зголемуваме трошоците за компјутерските ресурси.

Правам услуга за управување со деловни процеси во градежни компании. Со нас работат околу 3 илјади фирми. Повеќе од 10 илјади луѓе работат со нашиот систем секој ден по 4-10 часа. Решава разни проблеми на планирање, известување, предупредување, валидација... Користиме PostgreSQL 9.6. Имаме околу 300 табели во базата на податоци и секој ден се примаат до 200 милиони барања (10 илјади различни). Во просек имаме 3-4 илјади барања во секунда, во најактивните моменти повеќе од 10 илјади барања во секунда. Повеќето прашања се OLAP. Има многу помалку дополнувања, модификации и бришења, што значи дека оптоварувањето на OLTP е релативно мало. Ги дадов сите овие бројки за да можете да го процените обемот на нашиот проект и да разберете колку нашето искуство може да биде корисно за вас.

Слика една. Лирски

Кога го започнавме развојот, навистина не размислувавме каков вид на оптоварување ќе падне на базата на податоци и што ќе направиме ако серверот престане да влече. Кога ја дизајниравме базата на податоци, ги следевме општите препораки и се обидовме да не си пукаме во стапалото, но отидовме подалеку од општите совети како „не користете ја шемата Вредности на атрибути на ентитет не влеговме. Дизајниравме врз основа на принципите на нормализација, избегнувајќи вишок податоци и не се грижевме за забрзување на одредени прашања. Штом пристигнаа првите корисници, наидовме на проблем со перформансите. Како и обично, бевме целосно неподготвени за ова. Првите проблеми се покажаа како едноставни. Како по правило, сè беше решено со додавање на нов индекс. Но, дојде време кога едноставните закрпи престанаа да работат. Сфаќајќи дека ни недостасува искуство и дека ни станува сè потешко да разбереме што ги предизвикува проблемите, ангажиравме специјалисти кои ни помогнаа правилно да го поставиме серверот, да го поврземе мониторингот и ни покажаа каде да бараме статистика.

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

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

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

ТОП барања

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

Вообичаена практика на сите компании е да работат со ТОП барања. Има неколку од нив; оптимизирањето дури и едно барање може да ослободи 5-10% од ресурсите. Меѓутоа, како што проектот созрева, оптимизирањето на ТОП прашања станува сè понетривијална задача. Сите едноставни методи се веќе разработени, а најтешкото барање зазема „само“ 3-5% од ресурсите. Ако ТОП барањата вкупно заземаат помалку од 30-40% од времето, тогаш најверојатно веќе сте направиле напори да ги направите да работат брзо и време е да продолжите кон оптимизирање на барањата од следната група.
Останува да се одговори на прашањето колку врвни прашања треба да бидат вклучени во оваа група. Обично земам најмалку 10, но не повеќе од 20. Се трудам да се погрижам времето на првиот и последниот во ТОП групата да се разликува не повеќе од 10 пати. Тоа е, ако времето на извршување на барањето нагло падне од 1-во место на 10-то, тогаш го земам ТОП-10, ако падот е постепен, тогаш ја зголемувам големината на групата на 15 или 20.

Средни селани

Тоа се сите барања кои доаѓаат веднаш по ТОП, со исклучок на последните 5-10%. Вообичаено, во оптимизирањето на овие прашања лежи можноста за значително зголемување на перформансите на серверот. Овие барања може да тежат до 80%. Но, дури и ако нивниот удел надмина 50%, тогаш е време да ги погледнете повнимателно.

Опашка

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

Како да се оцени секоја група?

Јас користам SQL барање што помага да се направи таква проценка за PostgreSQL (сигурен сум дека слично барање може да се напише и за многу други DBMS)

SQL барање за да се процени големината на TOP-MEDIUM-TAIL групите

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

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

• Редизајнирање на системот, на пример, преработка на логиката за известување со помош на брокер за пораки наместо периодични прашања во базата на податоци
• Додавање или менување на индекси
• Препишување ORM барања на чист SQL
• Препишување на мрзлива логика за вчитување на податоци
• Кеширање преку денормализација на податоците. На пример, имаме врска со маса Испорака -> Фактура -> Барање -> Апликација. Односно, секоја испорака е поврзана со апликација преку други табели. За да не ги поврземе сите табели во секое барање, ја дуплиравме врската до барањето во табелата за испорака.
• Кеширање на статични табели со референтни книги и ретко менување табели во програмската меморија.

Понекогаш промените изнесуваа импресивен редизајн, но тие обезбедуваа 5-10% од оптоварувањето на системот и беа оправдани. Со текот на времето, издувните гасови стануваа се помали и помали, а се бараше сè посериозен редизајн.

Потоа го свртевме вниманието кон втората група барања - групата средни селани. Има многу повеќе прашања во него и се чинеше дека ќе биде потребно многу време за да се анализира целата група. Сепак, повеќето прашања се покажаа како многу едноставни за оптимизирање и многу проблеми беа повторени десетици пати во различни варијации. Еве примери на некои типични оптимизации што ги применивме на десетици слични барања и секоја група оптимизирани барања ја растовари базата на податоци за 3-5%.

• Наместо да се провери присуството на записи со помош на COUNT и скенирање на целосна табела, почна да се користи EXISTS
• Се ослободив од DISTINCT (не постои општ рецепт, но понекогаш можете лесно да се ослободите од него со забрзување на барањето за 10-100 пати).

  На пример, наместо барање за избор на сите двигатели од голема табела со испораки (DELIVERY)

  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 пати.

• Во многу случаи, COUNT беше целосно напуштен и
  се заменува со пресметка на приближна вредност
• наместо

  UPPER(s) LIKE JOHN%’ 
  

  користат

  s ILIKE “John%”
  

Секое специфично барање понекогаш се забрзуваше за 3-1000 пати. И покрај импресивните перформанси, на почетокот ни се чинеше дека нема смисла да се оптимизира барањето за кое се потребни 10 ms за да се заврши, што е едно од 3. сто најтешки барања и зафаќа стотинки од целокупното време на вчитување на базата на податоци. Но, со примена на истиот рецепт на група прашања од ист тип, добивме неколку проценти назад. За да не губиме време рачно прегледувајќи ги сите стотици прашања, напишавме неколку едноставни скрипти кои користеа редовни изрази за наоѓање прашања од ист тип. Како резултат на тоа, автоматското пребарување на групи на прашања ни овозможи дополнително да ги подобриме нашите перформанси со скромен напор.

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

Според нашите прогнози, сегашниот сервер ќе издржи зголемување на бројот на корисници за уште 3-5 пати. Точно, имаме уште еден кец во ракавот - сè уште не сме ги префрлиле прашањата SELECT на огледалото, како што се препорачува. Но, ние не го правиме ова свесно, бидејќи сакаме прво целосно да ги исцрпиме можностите за „паметна“ оптимизација пред да ја вклучиме „тешката артилерија“.
Критичкиот поглед на завршената работа може да сугерира користење на вертикално скалирање. Купете помоќен сервер наместо да губите време на специјалисти. Можеби серверот не чини толку многу, особено затоа што сè уште не сме ги исцрпиле границите на вертикалното скалирање. Сепак, само бројот на барања се зголеми за 10 пати. Во текот на неколку години, функционалноста на системот се зголеми и сега има повеќе видови на барања. Благодарение на кеширањето, функционалноста што постоеше се изведува во помалку барања и поефикасни барања. Ова значи дека можете безбедно да се помножите со уште 5 за да го добиете вистинскиот коефициент на забрзување. Значи, според најконзервативните проценки, можеме да кажеме дека забрзувањето било 50 пати или повеќе. Вертикално замавнување на серверот би чинело 50 пати повеќе. Посебно имајќи предвид дека откако ќе се изврши оптимизацијата, таа работи постојано, а сметката за изнајмениот сервер доаѓа секој месец.

Извор: www.habr.com