Құрылысшыларға арналған 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 миллион рет орындалатын құбыжықтар және күніне 30 рет шақырылатын орташа орындалу уақыты 100 секунд болатын сұраулар бар.

Барлық 10 мың сұрауды оңтайландыру мүмкін болмады, сондықтан біз деректер қорының жұмысын дұрыс жақсарту үшін күш-жігерімізді қайда бағыттау керектігін анықтауды шештік. Бірнеше қайталаудан кейін біз сұрауларды түрлерге бөле бастадық.

ТОП сұраулар

Бұл ең көп уақытты алатын ең ауыр сұраулар (жалпы уақыт). Бұл өте жиі шақырылатын сұраулар немесе орындалуы өте ұзақ уақытты қажет ететін сұраулар (ұзақ және жиі сұраулар жылдамдық үшін күрестің алғашқы итерацияларында оңтайландырылған). Нәтижесінде сервер олардың орындалуына көп уақыт жұмсайды. Сонымен қатар, жоғары сұраныстарды жалпы орындау уақыты және IO уақыты бойынша бөлек бөлу маңызды. Мұндай сұрауларды оңтайландыру әдістері сәл өзгеше.

Барлық компаниялардың әдеттегі тәжірибесі - TOP сұраныстарымен жұмыс істеу. Олардың саны аз; тіпті бір сұрауды оңтайландыру ресурстардың 5-10% босатуы мүмкін. Дегенмен, жоба жетілген сайын, ТОП сұрауларды оңтайландыру барған сайын тривиальды емес тапсырмаға айналады. Барлық қарапайым әдістер әзірленген және ең «ауыр» сұраныс ресурстардың «бар болғаны» 3-5% алады. Егер жалпы ТОП сұраулар уақыттың 30-40%-дан азын алса, онда сіз олардың жылдам жұмыс істеуіне күш салып қойған боларсыз және келесі топтағы сұрауларды оңтайландыруға көшетін кез келді.
Бұл топқа қанша сұранысты қосу керек деген сұраққа жауап беру қалды. Мен әдетте кем дегенде 10 қабылдаймын, бірақ 20-дан көп емес. ТОП топтағы бірінші және соңғы уақыттың 10 еседен артық емес айырмашылығын қамтамасыз етуге тырысамын. Яғни, егер сұраныстың орындалу уақыты 1-орыннан 10-ға дейін күрт төмендесе, онда мен ТОП-10-ды аламын, егер төмендеу біртіндеп болса, онда мен топтың көлемін 15 немесе 20-ға дейін көбейтемін.

Орта шаруалар

Бұл соңғы 5-10% қоспағанда, ТОП-тан кейін бірден келетін сұраулардың барлығы. Әдетте, бұл сұрауларды оңтайландыруда сервер өнімділігін айтарлықтай арттыру мүмкіндігі бар. Бұл сұраныстардың салмағы 80%-ға дейін жетуі мүмкін. Бірақ олардың үлесі 50% асқан болса да, оларға мұқият қараудың уақыты келді.

Құйрық

Жоғарыда айтылғандай, бұл сұраулар соңында келеді және уақыттың 5-10% алады. Автоматты сұрауларды талдау құралдарын пайдаланбасаңыз ғана олар туралы ұмытуға болады, содан кейін оларды оңтайландыру да арзан болуы мүмкін.

Әр топты қалай бағалауға болады?

Мен PostgreSQL үшін осындай бағалау жасауға көмектесетін SQL сұрауын қолданамын (ұқсас сұрауды көптеген басқа ДҚБЖ үшін жазуға болатынына сенімдімін)

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 есе жылдамдату арқылы одан оңай құтылуға болады).

  Мысалы, жеткізулердің үлкен кестесінен барлық драйверлерді таңдау сұрауының орнына (ЖЕТКІЗУ)

  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 есе өсті. Мұның бәрі ТОП-ОРТА сұраулардың дәл осы топтарын үнемі бақылаудың арқасында. ТОП тобында жаңа сұрау пайда болғаннан кейін біз оны дереу талдап, оны тездетуге тырысамыз. Сұрауларды талдау сценарийлерін пайдалана отырып, біз аптасына бір рет ORTA тобын қарастырамыз. Біз қалай оңтайландыру керектігін білетін жаңа сұрауларды тапсақ, біз оларды тез өзгертеміз. Кейде біз бірден бірнеше сұрауларға қолдануға болатын жаңа оңтайландыру әдістерін табамыз.

Біздің болжамдарымыз бойынша, қазіргі сервер пайдаланушылар санының тағы 3-5 есе артуына төтеп береді. Рас, бізде тағы бір эйс бар - біз ұсынылғандай ТАҢДАУ сұрауларын айнаға әлі жіберген жоқпыз. Бірақ біз мұны саналы түрде жасамаймыз, өйткені біз алдымен «ауыр артиллерияны» қоспас бұрын «ақылды» оңтайландыру мүмкіндіктерін толығымен сарқып алғымыз келеді.
Орындалған жұмысқа сыни тұрғыдан қарау тік масштабтауды қолдануды ұсынуы мүмкін. Мамандардың уақытын босқа өткізбей, әлдеқайда қуатты сервер сатып алыңыз. Сервер соншалықты қымбат болмауы мүмкін, әсіресе біз тік масштабтау шектеулерін әлі таусылған жоқпыз. Алайда тек өтініштердің саны 10 есеге өсті. Бірнеше жыл ішінде жүйенің функционалдығы артты және қазір сұраныстардың түрлері көбірек. Кэштеу арқасында бұрыннан бар функционалдылық аз сұрауларда және тиімдірек сұрауларда орындалады. Бұл нақты үдеу коэффициентін алу үшін тағы 5-ке қауіпсіз көбейтуге болатындығын білдіреді. Сонымен, ең консервативті бағалаулар бойынша, жеделдету 50 есе немесе одан да көп болды деп айта аламыз. Серверді тігінен бұру 50 есе қымбатқа түседі. Әсіресе, оңтайландыру жүргізілгеннен кейін ол үнемі жұмыс істейтінін және жалға алынған сервер үшін шот ай сайын келетінін ескерсек.

Ақпарат көзі: www.habr.com