Кантип жемиштүү серверге өтпөстөн, маалымат базасына суроо-талаптардын санын 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дан көп эмес. ТОП-топтогу биринчи менен акыркынын убактысы 10 эседен ашпоого аракет кылам. Башкача айтканда, эгерде сурамдарды аткаруу убактысы 1-орундан 10-орунга кескин төмөндөсө, анда мен ТОП-10ду алам, эгерде төмөндөө акырындык менен болсо, анда топтун көлөмүн 15 же 20га чейин көбөйтөм.
Орто дыйкандар
Бул акыркы 5-10% кошпогондо, TOP кийин дароо келген бардык суроо-талаптар. Адатта, бул сурамдарды оптималдаштырууда сервердин иштешин кыйла жогорулатуу мүмкүнчүлүгү бар. Бул сурамдардын салмагы 80% га чейин болушу мүмкүн. Бирок алардын үлүшү 50% дан ашкан күндө да, аларга кылдаттык менен кароого убакыт келди.
Куйрук
Жогоруда айтылгандай, бул сурамдар аягында келип, убакыттын 5-10% алат. Эгер сиз автоматтык суроо-талаптарды талдоо куралдарын колдонбосоңуз, алар жөнүндө унута аласыз, анда аларды оптималдаштыруу да арзан болушу мүмкүн.
Ар бир топту кантип баалоо керек?
Мен PostgreSQL үчүн ушундай баа берүүгө жардам берген SQL суроосун колдоном (ушул сыяктуу суроо башка көптөгөн DBMS үчүн да жазылышы мүмкүн деп ишенем)
TOP-MEDIUM-TAIL топторунун өлчөмүн баалоо үчүн SQL сурамы
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салыштырмалуу кичинекей 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 эсе кымбатка турат. Айрыкча, оптималдаштыруу жүргүзүлгөндөн кийин, ал ар дайым иштейт жана ижарага алынган сервер үчүн эсеп ай сайын келип турарын эске алсак.
Source: www.habr.com