Чӣ гуна бояд 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 мс иҷро мешаванд ва дархостҳое ҳастанд, ки мӯҳлати иҷрои миёна 30 сонияро ташкил медиҳанд, ки дар як рӯз 100 маротиба даъват карда мешаванд.
Ҳама 10 ҳазор дархостро оптимизатсия кардан имконнопазир буд, аз ин рӯ мо тасмим гирифтем, ки кӯшишҳои худро ба куҷо равона кунем, то кори базаи маълумотро дуруст беҳтар созем. Пас аз чанд такрор, мо дархостҳоро ба намудҳо тақсим кардем.
ТОП дархостҳо
Инҳо пурсишҳои вазнинтарин мебошанд, ки вақти бештарро мегиранд (вақти умумӣ). Инҳо дархостҳое мебошанд, ки зуд-зуд даъват мешаванд ё дархостҳое, ки барои иҷро вақти хеле тӯлонӣ лозиманд (дар такрори аввалини мубориза барои суръат пурсишҳои тӯлонӣ ва зуд-зуд оптимизатсия карда шуданд). Дар натиҷа, сервер вақти бештарро барои иҷрои онҳо сарф мекунад. Ғайр аз он, муҳим аст, ки дархостҳои болоро аз рӯи вақти умумии иҷро ва алоҳида аз рӯи вақти IO ҷудо кунед. Усулҳои оптимизатсияи чунин дархостҳо каме фарқ мекунанд.
Таҷрибаи маъмулии ҳама ширкатҳо кор кардан бо дархостҳои TOP мебошад. Шумораи онҳо каманд; оптимизатсияи ҳатто як дархост метавонад 5-10% захираҳоро озод кунад. Бо вуҷуди ин, вақте ки лоиҳа ба камол мерасад, оптимизатсияи дархостҳои TOP ба кори рӯзафзун табдил меёбад. Ҳама усулҳои оддӣ аллакай кор карда шудаанд ва дархости аз ҳама "вазнин" танҳо 3-5% захираҳоро мегирад. Агар дархостҳои TOP дар маҷмӯъ камтар аз 30-40% вақтро дар бар гиранд, пас эҳтимоли зиёд шумо аллакай кӯшиш кардаед, ки онҳо зуд кор кунанд ва вақти он расидааст, ки ба оптимизатсияи дархостҳо аз гурӯҳи навбатӣ гузаред.
Ҷавоб ба саволе боқӣ мемонад, ки чӣ қадар саволҳои олӣ бояд ба ин гурӯҳ дохил карда шаванд. Ман одатан ҳадди аққал 10-ро мегирам, аммо на бештар аз 20. Ман кӯшиш мекунам, ки вақти аввалин ва охирин дар гурӯҳи TOP на бештар аз 10 маротиба фарқ кунад. Яъне, агар вақти иҷрои дархост якбора аз зинаи 1-ум ба 10-ум паст шавад, пас ман ТОП-10-ро мегирам, агар коҳиш тадриҷан бошад, он гоҳ андозаи гурӯҳро то 15 ё 20 зиёд мекунам.
Дехконони миёна
Ин ҳама дархостҳое мебошанд, ки фавран пас аз TOP меоянд, ба истиснои 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 аст), ки дархостҳоро аз як гурӯҳ аз гурӯҳи дигар ҷудо мекунанд.
Ин аст, ки ҳиссаи дархостҳо тақрибан дар вақти оғоз шудани кори оптимизатсия ва ҳоло муқоиса карда мешавад.
Диаграмма нишон медиҳад, ки ҳиссаи дархостҳои ТОП якбора кам шудааст, аммо «деҳқонони миёна» зиёд шудааст.
Дар аввал, дархостҳои 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 кӯмак карданд:
- Азнавсозии система, масалан, аз нав кор кардани мантиқи огоҳинома бо истифода аз брокери паём ба ҷои дархостҳои даврии пойгоҳи додаҳо
- Илова ё тағир додани индексҳо
- Навиштани дархостҳои 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дар ҷадвали нисбатан хурд 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 маротиба гаронтар мешавад. Хусусан бо назардошти он, ки пас аз анҷом додани оптимизатсия он ҳама вақт кор мекунад ва ҳисоб барои сервери иҷора ҳар моҳ меояд.
Манбаъ: will.com