Аптымізацыя запытаў базы дадзеных на прыкладзе B2B сэрвісу для будаўнікоў

Як вырасці ў 10 разоў пад колькасці запытаў да БД не пераязджаючы на ​​больш прадукцыйны сервер і захаваць працаздольнасць сістэмы? Я раскажу, як мы змагаліся з падзеннем прадукцыйнасці нашай базы дадзеных, як аптымізавалі SQL запыты, каб абслугоўваць як мага больш карыстальнікаў і не павышаць выдаткі на вылічальныя рэсурсы.

Я раблю сэрвіс для кіравання бізнес працэсамі ў будаўнічых кампаніях. З намі працуе каля 3 тысяч кампаній. Больш за 10 тысяч чалавек кожны дзень працуюць з нашай сістэмай па 4-10 гадзін. Яна вырашае розныя задачы планавання, абвесткі, папярэджанні, валідацыі… Мы выкарыстоўваем PostgreSQL 9.6. У базе даных у нас каля 300 табліц і кожныя суткі ў яе паступае да 200 млн запытаў (10 тысяч розных). У сярэднім у нас 3-4 тысячы запытаў у секунду, у самыя актыўныя моманты больш за 10 тысяч запытаў у секунду. Большая частка запытаў - OLAP. Дадаткаў, мадыфікацый і выдаленняў нашмат менш, гэта значыць OLTP нагрузка адносна невялікая. Усе гэтыя лічбы я прывёў, каб вы маглі ацаніць маштаб нашага праекту і зразумець наколькі наш вопыт можа быць карысны для вас.

Карціна першая. Лірычная

Калі мы пачыналі распрацоўку, то асоба не задумваліся аб тым, якая нагрузка ляжа на БД і што мы будзем рабіць калі сервер перастане выцягваць. Пры праектаванні БД мы прытрымліваліся агульных рэкамендацый і імкнуліся не страляць сабе ў нагу, але далей агульных парад накшталт “не выкарыстоўвайце патэрн Entity Attribute Values мы не заходзілі. Праектавалі зыходзячы з прынцыпаў нармалізацыі пазбягаючы надмернасці дадзеных і не клапаціліся аб паскарэнні тых ці іншых запытаў. Як толькі дашлі першыя карыстачы мы сутыкнуліся з праблемай прадукцыйнасці. Як звычайна мы аказаліся абсалютна не гатовыя да гэтага. Першыя праблемы аказаліся простымі. Як правіла ўсё вырашалася даданнем новага азначніка. Але надышоў момант калі простыя латкі перасталі працаваць. Усвядоміўшы, што досведу бракуе і нам усё складаней зразумець у чым чыннік праблем, мы нанялі адмыслоўцаў, якія дапамаглі нам правільна наладзіць сервер, падлучыць маніторынг, паказалі куды глядзець, каб атрымаць статыстыку.

Карціна другая. Статыстычная

Такім чынам, у нас ёсць каля 10 тысяч розных запытаў, якія выконваюцца на нашай БД за суткі. З гэтых 10 тысяч ёсць монстры, якія выконваюцца па 2-3 млн разоў з сярэднім часам выканання 0.1-0.3 мс і ёсць запыты з сярэднім часам выканання 30 секунд, якія выклікаюцца 100 разоў у суткі.

Аптымізаваць усе 10 тысяч запытаў не ўяўлялася магчымым, таму мы вырашылі разабрацца з тым, куды накіроўваць намаганні, каб падвышаць прадукцыйнасць БД правільна. Пасля некалькіх ітэрацый мы сталі дзяліць запыты на тыпы.

TOP запыты

Гэта самыя цяжкія запыты, якія займаюць больш за ўсё часу (total time). Гэта запыты, якія альбо вельмі часта выклікаюцца, альбо запыты, якія вельмі доўга выконваюцца (доўгія і частыя запыты былі аптымізаваныя яшчэ на першых ітэрацыях барацьбы за хуткасць). У выніку сумарна на іх выкананне сервер марнуе больш за ўсё часу. Прычым важна адлучаць топ запыты па агульным часе выканання і асобна па IO time. Спосабы аптымізацыі такіх запытаў крыху розныя.

Звычайная практыка ўсіх кампаній-працаваць з TOP запытамі. Іх няшмат, аптымізацыя нават аднаго запыту можа вызваліць 5-10% рэсурсаў. Аднак, па меры "сталення" праекту аптымізацыя TOP запытаў становіцца ўсё больш нетрывіяльнай задачай. Усе простыя спосабы ўжо адпрацаваны, ды і самы "цяжкі" запыт адымае "ўсяго" 3-5% рэсурсаў. Калі TOP запыты ў суме займаюць менш за 30-40% часу, то хутчэй за ўсё вы ўжо прыклалі намаганні, каб яны працавалі хутка і прыйшла пара пераходзіць да аптымізацыі запытаў з наступнай групы.
Застаецца адказаць на пытанне колькі верхніх запытаў уключыць у гэтую групу. Я звычайна бяру не менш за 10, але не больш за 20. Стараюся, каб час першага і апошняга ў TOP групе адрозніваўся не больш чым у 10 разоў. Гэта значыць, калі час выканання запытаў рэзка падае з 1 месца да 10, то бяру TOP-10, калі падзенне больш плыўнае, то павялічваю памер групы да 15 або 20.

Сераднякі (medium)

Гэта ўсё запыты, якія ідуць адразу за TOP, за выключэннем апошніх 5-10 працэнтаў. Звычайна ў аптымізацыі менавіта гэтых запытаў крыецца магчымасць моцна падняць прадукцыйнасць сервера. Гэтыя запыты могуць "важыць" да 80%. Але нават калі іх доля пераваліла за 50%, значыць час на іх зірнуць больш уважліва.

Хвост (tail)

Як было сказана, гэтыя запыты ідуць у канцы і на іх ідзе 5-10% часу. Пра іх можна забыцца, толькі калі вы не выкарыстоўваеце аўтаматычныя сродкі аналізу запытаў, тады іх аптымізацыя таксама можа танна абысціся.

Як ацаніць кожную групу?

Я выкарыстоўваю SQL запыт, які дапамагае зрабіць такую ​​адзнаку для PostgreSQL (упэўнены што для шматлікіх іншых СКБД можна напісаць падобны запыт)

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), якія адлучае запыты адной групы ад іншай.

Вось так прыкладна суадносяцца долі запытаў на момант пачатку работ па аптымізацыі і зараз.

З дыяграмы відаць, што доля TOP запытаў рэзка знізілася, затое выраслі "сярэднякі".
Спачатку ў TOP запыты пападалі адкрытыя ляпы. З часам дзіцячыя хваробы зніклі, доля TOP запытаў скарачалася, даводзілася прыкладаць усё больш намаганняў, каб паскорыць цяжкія запыты.

Каб атрымаць тэкст запытаў выкарыстоўваем такі запыт

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

Вось спіс самых часта выкарыстоўваных прыёмаў, якія дапамагалі нам паскараць TOP запыты:

• Redesign сістэмы, напрыклад перапрацоўка логікі апавяшчэнняў на message broker замест перыядычных запытаў да БД
• Даданне ці змена індэксаў
• Перапісванне ORM запытаў на чысты SQL
• Перапісванне логікі lazy падгрузкі дадзеных
• Кешаванне праз дэнармалізацыю дадзеных. Напрыклад у нас ёсць сувязь табліц Дастаўка -> Рахунак -> Запыт -> Заяўка. Гэта значыць, кожная дастаўка звязана з заяўкай праз іншыя табліцы. Каб не звязваць у кожным запыце ўсе табліцы, мы прадубліравалі спасылку на заяўку ў табліцы Дастаўка.
• Кэшаванне статычных табліц з даведнікамі і рэдка якія змяняюцца табліц у памяці праграмы.

Часам змены цягнулі на вялікі рэдызайн, але давалі 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
  

  зрабілі запыт па параўнальна невялікай табліцы PERSON

  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 мс, уваходзіць у 3. сотню самых цяжкіх запытаў і ў агульным часе нагрузкі на БД займае сотыя долі працэнта. Але ужываючы адзін і той жа рэцэпт да групы аднатыпных запытаў мы адыгрывалі па некалькі адсоткаў. Каб не марнаваць час на ручны прагляд усіх сотняў запытаў мы напісалі некалькі простых скрыптоў, якія з дапамогай рэгулярных выразаў знаходзілі аднатыпныя запыты. У выніку аўтаматычны пошук груп запытаў дазволіў нам яшчэ больш палепшыць нашу прадукцыйнасць, затраціўшы сціплыя намаганні.

У выніку мы ўжо тры гады працуем на адным і тым жа жалезе. Сярэднясутачная нагрузка каля 30%, у піках даходзіць да 70%. Колькасць запытаў як і колькасць карыстальнікаў вырасла прыкладна ў 10 разоў. І ўсё гэта дзякуючы сталаму маніторынгу гэтых самых груп запытаў TOP-MEDIUM. Як толькі нейкі новы запыт з'яўляецца ў групе TOP, мы яго тут жа аналізуем і спрабуем паскорыць. Групу MEDIUM мы раз на тыдзень праглядаем з дапамогай скрыптоў аналізу запытаў. Калі трапляюцца новыя запыты, якія мы ўжо ведаем як аптымізаваць, то мы іх хутка мяняем. Часам знаходзім новыя спосабы аптымізацыі, якія можна прымяніць адразу да некалькіх запытаў.

Па нашых прагнозах бягучы сервер вытрымае павелічэнне колькасці карыстачоў яшчэ ў 3-5 раз. Праўда ў нас ёсць яшчэ адзін козыр у рукаве- мы да гэтага часу не перавялі SELECT- запыты на люстэрка, як рэкамендуецца рабіць. Але мы гэтага не робім свядома, бо жадаем спачатку да канца вычарпаць магчымасці "разумнай" аптымізацыі, перш чым уключаць "цяжкую артылерыю".
Крытычны погляд на праведзеную працу можа падказаць выкарыстоўваць вертыкальнае маштабаванне. Купіць больш магутны сервер, замест таго, каб марнаваць час спецыялістаў. Сервер можа каштаваць не так дорага, тым больш што ліміты вертыкальнага маштабавання ў нас яшчэ не вычарпаны. Аднак у 10 разоў вырасла толькі колькасць запытаў. За некалькі гадоў, павялічыўся функцыянал сістэмы і зараз разнавіднасцяў запытаў стала больш. Той функцыянал, які быў, за кошт кэшавання выконваецца меншай колькасцю запытаў, да таго ж больш эфектыўных запытаў. Значыць можна смела памножыць яшчэ на 5, каб атрымаць рэальны каэфіцыент паскарэння. Такім чынам па самых сціплых падліках можна сказаць, што паскарэнне склала 50 і больш разоў. Вертыкальна разгайдаць сервер у 50 раз абышлося б даражэй. Асабліва ўлічваючы, што аднойчы праведзеная аптымізацыя працуе ўвесь час, а рахунак за арандаваны сервер прыходзіць кожны месяц.

Крыніца: habr.com