Есепте мүмкіндік беретін кейбір тәсілдер берілген Күніне миллиондаған сұраулар болған кезде SQL сұрауларының өнімділігін бақылаңыз, және жүздеген бақыланатын PostgreSQL серверлері бар.
Қандай техникалық шешімдер бізге ақпараттың осындай көлемін тиімді өңдеуге мүмкіндік береді және бұл қарапайым әзірлеушінің өмірін қалай жеңілдетеді?
Кім қызықтырады? нақты есептерді талдау және әртүрлі оңтайландыру әдістері SQL сұраулары және PostgreSQL жүйесінде әдеттегі DBA мәселелерін шешу - сіз де жасай аласыз осы тақырыпқа.
Менің атым Кирилл Боровиков, мен өкілдік етемін . Атап айтқанда, мен біздің компанияда мәліметтер базасымен жұмыс істеуге маманданамын.
Бүгін мен сізге сұрауларды қалай оңтайландыратынымызды айтамын, бұл кезде сізге бір сұраудың өнімділігін «бөліп алу» қажет емес, бірақ мәселені жаппай шешу қажет. Миллиондаған сұраулар болған кезде және сіз кейбірін табуыңыз керек шешу тәсілдері бұл үлкен мәселе.
Жалпы алғанда, біздің миллиондаған клиенттеріміз үшін Tensor болып табылады : корпоративтік әлеуметтік желі, бейнебайланысқа арналған шешімдер, ішкі және сыртқы құжат айналымына арналған шешімдер, бухгалтерлік есеп пен қоймаларға арналған есеп жүйелері,... Яғни, 100-ден астам әртүрлі бизнесті кешенді басқаруға арналған осындай «мегакомбинат». ішкі жобалар.
Олардың барлығының жұмыс істеп, қалыпты дамуын қамтамасыз ету үшін бізде бүкіл ел бойынша 10 даму орталықтары бар, олардың ішінде одан да көп 1000 әзірлеушілер.
Біз PostgreSQL-пен 2008 жылдан бері жұмыс істеп келеміз және біз өңдейтін нәрселердің үлкен көлемін жинақтадық - клиенттік деректер, статистикалық, аналитикалық, сыртқы ақпараттық жүйелерден алынған деректер - 400 ТБ-тан астам. Тек өндірісте шамамен 250 сервер бар және барлығы біз бақылайтын 1000 дерекқор сервері бар.
SQL – декларативті тіл. Сіз бір нәрсенің «қалай жұмыс істейтінін» емес, «неге» қол жеткізгіңіз келетінін сипаттайсыз. ДҚБЖ JOIN жасауды жақсы біледі - кестелерді қалай қосу керек, қандай шарттар қою керек, индекстен не өтеді, не болмайды...
Кейбір ДҚБЖ кеңестерді қабылдайды: «Жоқ, осы екі кестені осындай және келесі кезекте қосыңыз», бірақ PostgreSQL мұны істей алмайды. Бұл жетекші әзірлеушілердің саналы ұстанымы: «Біз әзірлеушілерге қандай да бір кеңестерді пайдалануға рұқсат бергеннен гөрі, сұрауды оңтайландырғышты аяқтағанды жөн көреміз».
Бірақ, PostgreSQL «сырттың» өзін басқаруына мүмкіндік бермейтініне қарамастан, ол тамаша мүмкіндік береді. оның ішінде не болып жатқанын қараңызсұрауды орындаған кезде және оның қай жерде проблемалары бар.
Жалпы, әзірлеуші [DBA үшін] әдетте қандай классикалық мәселелермен келеді? «Міне, біз өтінішті орындадық және бізде бәрі баяу, бәрі ілулі тұр, бірдеңе болып жатыр... Қандай да бір қиындық!»
Себептер әрқашан бірдей:
- тиімсіз сұрау алгоритмі
Әзірлеуші: «Қазір мен оған JOIN арқылы SQL тілінде 10 кесте беремін...» - және оның шарттары керемет түрде тиімді түрде «ажыратылады» және ол бәрін тез алады деп күтеді. Бірақ кереметтер болмайды және мұндай өзгермелілігі бар кез келген жүйе (бір FROM-да 10 кесте) әрқашан қандай да бір қателік береді. [] - маңызды емес статистика
Бұл тармақ PostgreSQL үшін өте маңызды, сіз серверге үлкен деректер жинағын «құйып», сұраныс жасағанда және ол планшетіңізді «сексканит етеді». Өйткені кеше оның ішінде 10 жазба болған, ал бүгін 10 миллион, бірақ PostgreSQL бұл туралы әлі хабардар емес, және біз бұл туралы айтуымыз керек. [] - ресурстарға «қосу».
Сіз жеткілікті дискі, жады немесе процессор өнімділігі жоқ әлсіз серверде үлкен және қатты жүктелген дерекқорды орнаттыңыз. Мұның бәрі... Бір жерде өнімділік төбесі бар, одан жоғары секіруге болмайды. - бұғаттау
Бұл қиын нүкте, бірақ олар әртүрлі өзгертуші сұрауларға (INSERT, UPDATE, DELETE) ең өзекті - бұл бөлек үлкен тақырып.
Жоспар алу
...Ал қалғандары үшін біз жоспар керек! Біз серверде не болып жатқанын көруіміз керек.
PostgreSQL үшін сұрауды орындау жоспары мәтінді көрсетудегі сұрауды орындау алгоритмінің ағашы болып табылады. Дәл осы алгоритм жоспарлаушы жүргізген талдау нәтижесінде ең тиімді болып табылды.
Әрбір ағаш түйіні операция болып табылады: кестеден немесе индекстен деректерді алу, нүктелік кескінді құру, екі кестені біріктіру, таңдауларды біріктіру, қиылысу немесе алып тастау. Сұрауды орындау осы ағаштың түйіндері арқылы өтуді қамтиды.
Сұрау жоспарын алудың ең оңай жолы – операторды орындау EXPLAIN. Барлық нақты атрибуттарды алу үшін, яғни базада сұрауды нақты орындау үшін - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
Нашар бөлігі: оны іске қосқан кезде, ол «осында және қазір» орын алады, сондықтан ол тек жергілікті жөндеуге жарамды. Егер сіз деректер өзгерістерінің күшті ағынында болатын жоғары жүктелген серверді алсаңыз және мынаны көресіз: «О! Мұнда біз баяу орындаймызxia сұрау». Жарты сағат, бір сағат бұрын - сіз іске қосып, осы сұрауды журналдардан алып, оны серверге қайтарған кезде, бүкіл деректер жинағы мен статистика өзгерді. Сіз оны жөндеу үшін іске қосасыз және ол тез жұмыс істейді! Ал неге, неге екенін түсіне алмайсың болды баяу.
Серверде сұраныс орындалған сәтте не болғанын түсіну үшін ақылды адамдар жазды . Ол барлық кең таралған PostgreSQL дистрибутивтерінде бар және жай конфигурация файлында белсендіруге болады.
Егер ол қандай да бір сұрау сіз айтқан шектеуден ұзағырақ орындалатынын түсінсе, ол жасайды Осы сұраудың жоспарының «суретін» жасайды және оларды журналға бірге жазады.
Қазір бәрі жақсы сияқты, бөренеге барамыз, сонда көреміз... [мәтіндік аяқ киім]. Бірақ біз бұл туралы ештеңе айта алмаймыз, тек бұл тамаша жоспар, өйткені оны орындауға 11 мс уақыт кетті.
Бәрі жақсы сияқты, бірақ іс жүзінде не болғаны ештеңе анық емес. Жалпы уақыттан басқа біз ештеңе көрмейміз. Өйткені қарапайым мәтіннің мұндай «қозысына» қарау, әдетте, көрнекі емес.
Бірақ бұл анық болмаса да, тіпті ыңғайсыз болса да, одан да маңызды мәселелер бар:
- Түйін көрсетеді бүкіл ішкі ағаштың ресурстарының сомасы оның астында. Яғни, егер оның астында қандай да бір кірістірілген жағдай болса, осы нақты индексті сканерлеуге қанша уақыт жұмсалғанын біле алмайсыз. Біз динамикалық түрде «балалар» және шартты айнымалылар, ішінде CTE бар-жоғын білуіміз керек және мұның барлығын «ойымызда» алып тастауымыз керек.
- Екінші нүкте: түйінде көрсетілген уақыт бір түйіннің орындалу уақыты. Егер бұл түйін, мысалы, кесте жазбалары бойынша циклдің нәтижесінде бірнеше рет орындалса, онда циклдар саны — осы түйіннің циклдері — жоспарда артады. Бірақ атомды орындау уақытының өзі жоспар бойынша өзгеріссіз қалады. Яғни, бұл түйіннің жалпы қанша уақыт орындалғанын түсіну үшін сіз бір нәрсені екіншісіне көбейтуіңіз керек - қайтадан «басыңызда».
Мұндай жағдайларда «Ең әлсіз буын кім?» деп түсініңіз. мүмкін емес дерлік. Сондықтан, тіпті әзірлеушілердің өздері де «нұсқауда» деп жазады «Жоспарды түсіну - үйрену керек өнер, тәжірибе...».
Бірақ бізде 1000 әзірлеушілер бар және сіз олардың әрқайсысына бұл тәжірибені жеткізе алмайсыз. Мен, сен, ол біледі, бірақ ол жақта біреу білмейді. Мүмкін, ол үйренетін шығар, мүмкін емес, бірақ ол қазір жұмыс істеуі керек - және ол бұл тәжірибені қайдан алады?
Жоспар визуализациясы
Сондықтан біз бұл мәселелермен күресу үшін қажет екенін түсіндік жоспардың жақсы көрнекілігі.
Біз алдымен «нарық арқылы» бардық - тіпті не бар екенін білу үшін Интернетті қарастырайық.
Бірақ азды-көпті дамып келе жатқан салыстырмалы түрде «тірі» шешімдер өте аз екені белгілі болды - сөзбе-сөз, тек біреуі: Губерт Любачевски жазған. «Арна» өрісіне жоспардың мәтіндік көрінісін енгізген кезде, ол талданған деректері бар кестені көрсетеді:
- түйіннің өзіндік өңдеу уақыты
- бүкіл ішкі ағаш үшін жалпы уақыт
- статистикалық болжамды шығарылған жазбалар саны
- түйін денесінің өзі
Бұл қызметте сілтемелер мұрағатын ортақ пайдалану мүмкіндігі де бар. Сіз өз жоспарыңызды сол жерге тастап: «Ей, Вася, мына сілтеме, мұнда бірдеңе дұрыс емес», - дедіңіз.
Бірақ шағын мәселелер де бар.
Біріншіден, «көшіру-қоюдың» үлкен көлемі. Сіз бөрененің бір бөлігін алып, оны сол жерге жабыстырасыз, тағы да, тағы да.
Екіншіден, оқылған деректер көлеміне талдау жүргізілмейді — шығатын бірдей буферлер EXPLAIN (ANALYZE, BUFFERS), біз оны мұнда көрмейміз. Ол жай ғана оларды бөлшектеуді, түсінуді және олармен жұмыс істеуді білмейді. Көптеген деректерді оқып жатқанда және дискі мен жад кэшін қате бөлуіңіз мүмкін екенін түсінгенде, бұл ақпарат өте маңызды.
Үшінші жағымсыз тұсы – бұл жобаның өте әлсіз дамуы. Міндеттемелер өте аз, егер алты айда бір рет болса жақсы, ал код Perl тілінде.
Бірақ мұның бәрі «лирика», біз мұнымен қалай өмір сүре аламыз, бірақ бізді бұл қызметтен қатты бас тартқан бір нәрсе бар. Бұл Common Table Expression (CTE) және InitPlan/SubPlan сияқты әртүрлі динамикалық түйіндерді талдаудағы қателер.
Егер сіз бұл суретке сенсеңіз, әрбір жеке түйіннің жалпы орындалу уақыты бүкіл сұраудың жалпы орындалу уақытынан көп болады. Бәрі оңай - бұл CTE генерациялау уақыты CTE Scan түйінінен алынып тасталмады. Сондықтан біз CTE сканерлеуінің өзі қанша уақытқа созылғаны туралы дұрыс жауапты білмейміз.
Сонда біз өзімізді жазатын кез келгенін түсіндік - ура! Әрбір әзірлеуші: «Енді біз өзімізді жазамыз, бұл өте оңай!» дейді.
Біз веб-қызметтерге тән стек алдық: Node.js + Express негізіндегі ядро, әдемі диаграммалар үшін Bootstrap және D3.js пайдаланылды. Біздің үміттеріміз толығымен ақталды - біз 2 аптада бірінші прототипті алдық:
- теңшелетін жоспар талдаушысы
Яғни, енді біз PostgreSQL арқылы жасалған кез келген жоспарды талдай аламыз. - динамикалық түйіндерді дұрыс талдау - CTE Scan, InitPlan, Sub Plan
- буферлердің таралуын талдау - деректер беттері қай жерде жадтан, қай жерде жергілікті кэштен, қай жерде дисктен оқылады
- айқындық алды
Мұның бәрін журналда «қазу» емес, суреттегі «ең әлсіз сілтемені» бірден көру үшін.
Бізде синтаксисті бөлектеу қосылған осындай нәрсе бар. Бірақ әдетте біздің әзірлеушілер жоспардың толық көрінісімен жұмыс істемейді, бірақ қысқарақ. Өйткені, біз барлық сандарды талдап, оларды солға және оңға лақтырдық, ал ортасында біз тек бірінші жолды қалдырдық, бұл қандай түйін: CTE Scan, CTE генерациясы немесе қандай да бір белгі бойынша Seq Scan.
Бұл біз қысқартылған өкілдік деп атаймыз жоспар үлгісі.
Тағы не ыңғайлы болар еді? Біздің жалпы уақытымыздың қандай бөлігі қай түйінге бөлінгенін көру ыңғайлы болар еді - және оны жағына «жабыңыз». диаграмма диаграммасы.
Біз түйінді көрсетеміз және көреміз - Seq Scan жалпы уақыттың төрттен бірінен аз уақытты алды, ал қалған 3/4 бөлігі CTE Scan арқылы алынды. Ужас! Бұл CTE Scan «өрт жылдамдығы» туралы шағын ескерту, егер сіз оларды сұрауларыңызда белсенді пайдалансаңыз. Олар өте жылдам емес - олар әдеттегі кестені сканерлеуден де төмен.
Бірақ әдетте мұндай диаграммалар біз бірден сегментті көрсетіп, мысалы, уақыттың жартысынан көбі кейбір Seq Scan «жегенін» көргенде қызықтырақ, күрделірек болады. Оның үстіне ішінде қандай да бір сүзгі бар еді, соған сәйкес көптеген жазбалар жойылды... Сіз бұл суретті әзірлеушіге тікелей лақтырып: «Вася, мұнда сіз үшін бәрі нашар! Түсініп ал, бірдеңе дұрыс емес!»
Әрине, кейбір «тырмалар» қатысты.
Біз кездестірген бірінші нәрсе дөңгелектеу мәселесі болды. Жоспардағы әрбір жеке түйіннің уақыты 1 мкс дәлдікпен көрсетілген. Ал түйін циклдерінің саны, мысалы, 1000-нан асқанда - PostgreSQL орындалғаннан кейін «дәлдікте» бөлінеді, содан кейін кері есептеу кезінде біз «0.95 мс пен 1.05 мс аралығындағы» жалпы уақытты аламыз. Санау микросекундтарға жеткенде, бұл дұрыс, бірақ [милли]секундтар өткенде, «кім кімнен қанша тұтынды» жоспарының түйіндеріне ресурстарды «ажыратқанда» бұл ақпаратты ескеру керек.
Екінші, күрделірек, ресурстарды (сол буферлерді) динамикалық түйіндер арасында бөлу. Бұл бізге прототиптің алғашқы 2 аптасына және тағы 4 аптаға кететін.
Мұндай мәселені шешу өте оңай - біз CTE жасаймыз және онда бір нәрсені оқимыз. Шын мәнінде, PostgreSQL «ақылды» және ол жерде ештеңені оқымайды. Содан кейін біз одан бірінші жазбаны аламыз, ал оған сол CTE-ден жүз бірінші жазбаны аламыз.
Біз жоспарға қарап, түсінеміз - бұл таңқаларлық, бізде Seq Scan-да 3 буфер (деректер беттері), CTE Scan-да тағы 1 және екінші CTE Scan-да тағы 2 «тұтынылған» бар. Яғни, егер біз бәрін жай ғана қорытындылайтын болсақ, біз 6 аламыз, бірақ планшеттен біз тек 3-ті оқимыз! CTE Scan ешбір жерден ештеңе оқымайды, бірақ процесс жадымен тікелей жұмыс істейді. Яғни, бұл жерде бірдеңе дұрыс емес екені анық!
Шын мәнінде, мұнда Seq Scan-дан сұралған деректердің барлығы 3 бет бар, алдымен 1-ші CTE сканерлеуін сұрады, содан кейін 1-ші және тағы 2-і оқылды. Яғни, барлығы 2 бет емес, 3 бет оқылды.
Және бұл сурет бізді жоспардың орындалуы енді ағаш емес, жай ғана қандай да бір ациклдік график екенін түсінуге әкелді. «Ненің қайдан келгенін» түсіну үшін біз осындай диаграмма алдық. Яғни, мұнда біз pg_class-тен CTE жасап, оны екі рет сұрадық, 2-ші рет сұрағанымызда уақытымыздың барлығы дерлік филиалда өтті. 101-ші жазбаны оқу планшеттен 1-ші жазбаны оқудан әлдеқайда қымбат екені анық.
Біраз дем шығардық. Олар: «Енді, Нео, сен кунг-фуды білесің! Енді біздің тәжірибеміз сіздің экраныңызда. Енді сіз оны пайдалана аласыз ».
Журналды біріктіру
Біздің 1000 әзірлеушіміз жеңіл тыныс алды. Бірақ бізде тек жүздеген «жауынгерлік» серверлер бар екенін түсіндік және әзірлеушілер тарапынан осы «көшіру-қою» мүлдем ыңғайлы емес. Біз оны өзіміз жинауымыз керек екенін түсіндік.
Жалпы, статистиканы жинай алатын стандартты модуль бар, бірақ оны конфигурацияда белсендіру керек - бұл . Бірақ ол бізге жараспады.
Біріншіден, ол бір дерекқордағы әртүрлі схемаларды пайдалана отырып, бірдей сұрауларға тағайындайды әртүрлі QueryId. Яғни, егер сіз бірінші орындасаңыз SET search_path = '01'; SELECT * FROM user LIMIT 1;, ал содан кейін SET search_path = '02'; және сол сұраныс болса, онда осы модульдің статистикасы әртүрлі жазбаларға ие болады және мен схемаларды есепке алмай, осы сұрау профилінің контекстінде арнайы жалпы статистиканы жинай алмаймын.
Оны пайдалануымызға кедергі болған екінші мәселе жоспарлардың болмауы. Яғни, жоспар жоқ, сұраныстың өзі ғана бар. Біз ненің баяулағанын көреміз, бірақ неге екенін түсінбейміз. Міне, біз тез өзгеретін деректер жинағы мәселесіне қайта ораламыз.
Ал соңғы сәт - «фактілердің» болмауы. Яғни, сұрауды орындаудың белгілі бір данасына жүгіне алмайсыз - ол жоқ, тек жинақталған статистика бар. Мұнымен жұмыс істеу мүмкін болса да, бұл өте қиын.
Сондықтан біз көшіріп қоюмен күресуді шешіп, жазуды бастадық коллекционер.
Коллектор SSH арқылы қосылады, сертификат арқылы деректер қорымен серверге қауіпсіз қосылым орнатады және tail -F журнал файлында оған «жабысады». Сонымен, осы сессияда біз бүкіл журнал файлының толық «айнасын» аламызсервер жасайды. Сервердегі жүктеме аз, өйткені біз ол жерде ештеңені талдамаймыз, тек трафикті бейнелейміз.
Біз интерфейсті Node.js файлында жазуды әлдеқашан бастағандықтан, оған коллекторды жазуды жалғастырдық. Және бұл технология өзін ақтады, өйткені JavaScript-ті пайдалану журнал болып табылатын әлсіз пішімделген мәтіндік деректермен жұмыс істеуге өте ыңғайлы. Ал Node.js инфрақұрылымының өзі серверлік платформа ретінде желілік қосылымдармен және шын мәнінде кез келген деректер ағындарымен оңай және ыңғайлы жұмыс істеуге мүмкіндік береді.
Тиісінше, біз екі қосылымды «созамыз»: біріншісі журналдың өзін «тыңдау» және оны өзімізге қабылдау, ал екіншісі базаны мерзімді түрде сұрау. «Бірақ журнал oid 123 белгісінің бұғатталғанын көрсетеді», бірақ бұл әзірлеушіге ештеңе білдірмейді және дерекқордан: «Бәрібір OID = 123 деген не?» Деген дұрыс болар еді. Сондықтан біз базадан өзіміз туралы әлі білмеген нәрселерді жиі сұраймыз.
«Сіз ескермеген бір ғана нәрсе бар, пілге ұқсас аралар түрі бар!..» Біз бұл жүйені 10 серверді бақылаймыз келген кезде жасай бастадық. Біздің түсінуіміздегі ең маңыздысы, мұнда шешу қиын болған кейбір мәселелер туындады. Бірақ бірінші тоқсанда біз мониторинг үшін жүз алдық – өйткені жүйе жұмыс істеді, бәрі мұны қалайды, барлығы ыңғайлы болды.
Мұның бәрін қосу керек, деректер ағыны үлкен және белсенді. Шындығында, біз қадағалайтын нәрсе, біз немен күресе аламыз - біз қолданатын нәрсе. Біз PostgreSQL-ті деректерді сақтау ретінде де пайдаланамыз. Оған оператордан гөрі деректерді «құю» жылдамырақ ештеңе жоқ COPY Әзірше емес.
Бірақ жай ғана деректерді «құю» біздің технологиямыз емес. Өйткені жүз серверде секундына шамамен 50 мың сұрау болса, бұл күніне 100-150 ГБ журнал жасайды. Сондықтан біз негізді мұқият «кесіп» алуымыз керек еді.
Біріншіден, біз жасадық күні бойынша бөлу, өйткені, жалпы алғанда, күндер арасындағы корреляция ешкімді қызықтырмайды. Егер сіз бүгін түнде қолданбаның жаңа нұсқасын шығарсаңыз - және қазірдің өзінде бірнеше жаңа статистика болса, кеше болғаныңыздың айырмашылығы неде?
Екіншіден, біз үйрендік (мәжбүр еттік) пайдалану өте, өте жылдам жазу COPY. Яғни, жай ғана емес COPYөйткені ол жылдамырақ INSERT, және одан да жылдам.
Үшінші тармақ - маған тура келді тиісінше триггерлерден және сыртқы кілттерден бас тарту. Яғни, бізде сілтемелік тұтастық мүлде жоқ. Өйткені сізде жұп FK бар кесте болса және сіз дерекқор құрылымында «мұнда FK сілтеме жасайтын журнал жазбасы бар, мысалы, жазбалар тобына» десеңіз, оны енгізген кезде PostgreSQL оны қалай алып, адал жасаудан басқа ештеңе қалмады SELECT 1 FROM master_fk1_table WHERE ... кірістіргіңіз келетін идентификатормен - бұл жазбаның бар-жоғын, кірістіру арқылы осы Сыртқы кілтті «үзіп алмауыңызды» тексеру үшін.
Мақсатты кестеге және оның индекстеріне бір жазбаның орнына біз оған сілтеме жасайтын барлық кестелерден оқудың қосымша пайдасын аламыз. Бірақ бұл бізге мүлдем қажет емес - біздің міндет - ең аз жүктемемен мүмкіндігінше және мүмкіндігінше тез жазу. Сонымен, FK - төмен!
Келесі нүкте - біріктіру және хэштеу. Бастапқыда біз оларды дерекқорға енгіздік - ақыр соңында, жазба келгенде, оны планшеттің қандай да бір түрінде жасау ыңғайлы Триггерде "плюс бір". Бұл ыңғайлы, бірақ бірдей жаман нәрсе - сіз бір жазбаны енгізесіз, бірақ басқа кестеден басқа нәрсені оқуға және жазуға мәжбүрсіз. Оның үстіне, сіз тек оқып, жазып қана қоймай, оны әр уақытта жасайсыз.
Енді сізде белгілі бір хост арқылы өткен сұраулар санын есептейтін кесте бар деп елестетіп көріңіз: +1, +1, +1, ..., +1. Сізге, негізінен, бұл қажет емес - бәрі мүмкін коллектордағы жадтағы сома және бір әрекетте мәліметтер базасына жіберіңіз +10.
Иә, кейбір мәселелер туындаған жағдайда логикалық тұтастығыңыз «құлауы» мүмкін, бірақ бұл шындыққа жанаспайтын жағдай - өйткені сізде қалыпты сервер бар, оның контроллерінде батарея бар, сізде транзакциялар журналы, жүйеде журнал бар. файлдық жүйе... Жалпы, бұл тұрарлық емес. Іске қосу триггерлері/FK нәтижесінде алатын өнімділіктің жоғалуы сіз шеккен шығынға тұрарлық емес.
Хэшингпен бірдей. Белгілі бір сұраныс сізге ұшады, сіз одан белгілі бір идентификаторды дерекқорда есептейсіз, оны дерекқорға жазасыз, содан кейін оны бәріне айтасыз. Жазу кезінде сізге дәл сол нәрсені жазғысы келетін екінші адам келгенше бәрі жақсы - және сіз бұғатталадыңыз, бұл қазірдің өзінде нашар. Сондықтан, егер сіз кейбір идентификаторлардың генерациясын клиентке (деректер базасына қатысты) бере алсаңыз, мұны жасаған дұрыс.
Бізге мәтіннен MD5-ті пайдалану өте жақсы болды - сұраныс, жоспар, шаблон, ... Біз оны коллекторлық жағынан есептейміз және дайын идентификаторды дерекқорға «құйып жібереміз». MD5 ұзындығы және күнделікті бөлу ықтимал соқтығыстар туралы алаңдамауға мүмкіндік береді.
Бірақ мұның бәрін жылдам жазу үшін бізге жазу процедурасының өзін өзгерту керек болды.
Сіз әдетте деректерді қалай жазасыз? Бізде деректер жиынтығының қандай да бір түрі бар, оны бірнеше кестеге бөлеміз, содан кейін оны КӨШІРЕМІЗ – алдымен біріншіге, сосын екіншісіне, үшіншіге... Бұл ыңғайсыз, өйткені біз үш қадамда бір деректер ағынын жазып жатқан сияқтымыз. ретімен. Жағымсыз. Мұны тезірек жасауға бола ма? Болады!
Мұны істеу үшін бұл ағындарды бір-бірімен параллельді түрде ыдырату жеткілікті. Бізде қателер, сұраулар, шаблондар, блоктаулар, ... бөлек ағындарда ұшатыны белгілі болды - және біз мұның бәрін параллель жазамыз. Бұл үшін жеткілікті әрбір жеке мақсатты кесте үшін COPY арнасын үнемі ашық ұстаңыз.
Яғни коллекторда әрқашан ағын бар, оған қажетті деректерді жаза аламын. Бірақ дерекқор бұл деректерді көреді және біреу бұл деректердің жазылуын күтіп тұрып қалмауы үшін, КӨШІРУ белгілі бір уақыт аралығында үзілуі керек. Біз үшін ең тиімді кезең шамамен 100 мс болды - біз оны жауып, бірден сол кестеге қайта ашамыз. Кейбір шыңдар кезінде бір ағын жеткіліксіз болса, біз белгілі бір шекке дейін біріктіреміз.
Бұған қоса, біз мұндай жүктеме профилі үшін жазбалар топтамамен жиналған кездегі кез келген жинақтау жаман екенін білдік. Классикалық зұлымдық INSERT ... VALUES және одан әрі 1000 жазба. Өйткені бұл кезде сізде медиада жазу шыңы бар және дискіге бірдеңе жазуға тырысатындардың барлығы күтеді.
Мұндай ауытқулардан құтылу үшін ештеңені біріктірмеңіз, мүлде буферлемеңіз. Ал егер дискіге буферлеу орын алса (бақытымызға орай, Node.js ішіндегі Stream API анықтауға мүмкіндік береді) - бұл қосылымды кейінге қалдырыңыз. Оқиғаны қайтадан тегін алған кезде, оған жинақталған кезектен жазыңыз. Бос емес кезде бассейннен келесі тегін алып, оған жазыңыз.
Деректерді жазуға осы тәсілді енгізбес бұрын бізде шамамен 4K жазу операциялары болды және осылайша біз жүктемені 4 есе азайттық. Енді олар жаңа бақыланатын дерекқорлардың арқасында тағы 6 есе өсті - 100 МБ/с дейін. Енді біз соңғы 3 айдағы журналдарды шамамен 10-15 ТБ көлемінде сақтаймыз, үш айда кез келген әзірлеуші кез келген мәселені шеше алады деп үміттенеміз.
Біз проблемаларды түсінеміз
Бірақ бұл деректерді жинау жақсы, пайдалы, өзекті, бірақ жеткіліксіз - оны түсіну керек. Өйткені бұл күніне миллиондаған түрлі жоспарлар.
Бірақ миллиондарды басқару мүмкін емес, біз алдымен «кіші» әрекет етуіміз керек. Ең алдымен, сіз бұл «кіші» нәрсені қалай ұйымдастыру керектігін шешуіңіз керек.
Біз үш негізгі тармақты анықтадық:
- кім осы сұрауды жіберді
Яғни, ол қандай қолданбадан «келді»: веб-интерфейс, сервер, төлем жүйесі немесе басқа нәрсе. - қайда болды
Қандай нақты серверде? Өйткені, егер сізде бір қосымшаның астында бірнеше серверлер болса және біреуі кенеттен «ақымақ болып» қалса («диск шірік», «жад ағып кеткен», басқа мәселе болғандықтан), серверге арнайы жүгіну керек. - қалай мәселе бір немесе басқа жолмен көрінді
Бізге сұрауды «кім» жібергенін түсіну үшін біз стандартты құралды қолданамыз - сеанс айнымалысын орнату: SET application_name = '{bl-host}:{bl-method}'; — сұрау келіп жатқан бизнес-логикалық хост атауын және оны бастаған әдіс немесе қолданбаның атын жібереміз.
Сұраныстың «иесін» өткізгеннен кейін ол журналға шығарылуы керек - бұл үшін біз айнымалы мәнді конфигурациялаймыз log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Қызығушылық танытқандар үшін, мүмкін мұның бәрі нені білдіреді. Журналда біз мынаны көреміз:
- время
- процесс және транзакция идентификаторлары
- дерекқор атауы
- Осы сұрауды жіберген адамның IP
- және әдіс атауы
Содан кейін біз әртүрлі серверлер арасындағы бір сұраныстың корреляциясын қарау өте қызықты емес екенін түсіндік. Сізде бір қолданбаның осында және сол жерде бірдей бұралып қалатын жағдайлары жиі бола бермейді. Бірақ ол бірдей болса да, осы серверлердің кез келгенін қараңыз.
Міне, кесу «бір сервер – бір күн» кез келген талдау үшін бізге жеткілікті болып шықты.
Бірінші аналитикалық бөлім де солай «үлгі» - барлық сандық көрсеткіштерден тазартылған жоспарды ұсынудың қысқартылған түрі. Екінші кесу - қолданба немесе әдіс, ал үшінші кесу - бізге қиындықтар туғызған нақты жоспар түйіні.
Біз белгілі бір үлгілерден үлгілерге көшкен кезде бірден екі артықшылыққа ие болдық:
- талдауға арналған объектілер санының бірнеше есе азаюы
Біз мәселені мыңдаған сұраулар немесе жоспарлар арқылы емес, ондаған үлгілер бойынша талдауымыз керек. - уақыт шкаласы
Яғни, белгілі бір бөлімдегі «фактілерді» қорытындылау арқылы сіз олардың күндізгі көрінісін көрсете аласыз. Мұнда сізде, мысалы, сағатына бір рет болатын үлгі болса, бірақ ол күніне бір рет болуы керек екенін түсінуге болады, сіз ненің дұрыс еместігі туралы ойлануыңыз керек - оны кім және неге тудырды, мүмкін бұл жерде болуы керек. болмауы керек. Бұл тағы бір сандық емес, таза көрнекі талдау әдісі.
Қалған әдістер біз жоспардан шығаратын көрсеткіштерге негізделген: мұндай үлгінің қанша рет орын алғаны, жалпы және орташа уақыт, дискіден қанша деректер оқылған және жадтан қанша ...
Өйткені, мысалы, сіз хосттың аналитикалық бетіне келесіз, қараңыз - дискіде бір нәрсе тым көп оқи бастады. Сервердегі диск оны басқара алмайды - оны кім оқиды?
Сіз кез келген баған бойынша сұрыптап, дәл қазір немен айналысатыныңызды шеше аласыз - процессорға немесе дискіге жүктеме, немесе сұраулардың жалпы саны... Біз оны сұрыптадық, «жоғары» дегенді қарадық, түзеттік және қолданбаның жаңа нұсқасын шығарды.
Сол сияқты сұраудан бір үлгімен келетін әртүрлі қолданбаларды бірден көре аласыз SELECT * FROM users WHERE login = 'Vasya'. Фронт, бэкэнд, өңдеу... Ал егер ол онымен әрекеттеспесе, өңдеу неге оны оқиды деп ойлайсың.
Қарама-қарсы әдіс - қолданбадан оның не істейтінін бірден көру. Мысалы, frontend - бұл, мынау, мынау және бұл сағатына бір рет (хронология көмектеседі). Бірден сұрақ туындайды: сағатына бір нәрсе жасау фронтендтің жұмысы емес сияқты ...
Біраз уақыттан кейін біз жинақтаудың жетіспейтінін түсіндік жоспар түйіндері бойынша статистика. Біз жоспарлардан тек кестелердің деректерімен бірдеңе жасайтын түйіндерді ғана оқшауладық (оларды индекс бойынша оқу/жазбау). Шын мәнінде, алдыңғы суретке қатысты бір ғана аспект қосылады - бұл түйін бізге қанша жазба әкелді?, және қаншасы жойылды (Сүзгі арқылы жойылған жолдар).
Пластинада сәйкес индекс жоқ, сіз оған сұраныс жасайсыз, ол индекстен өтіп кетеді, Seq Scan-ға түседі... сіз біреуінен басқа барлық жазбаларды сүзіп алдыңыз. Сізге күніне 100 миллион сүзгіден өткен жазбалар не үшін қажет? Индексті жинаған дұрыс емес пе?
Барлық жоспарларды түйін бойынша талдай отырып, біз жоспарларда күдікті болып көрінетін кейбір типтік құрылымдар бар екенін түсіндік. Әзірлеушіге: «Досым, мұнда сіз алдымен индекс бойынша оқисыз, содан кейін сұрыптаңыз, содан кейін кесіңіз» - әдетте, бір жазба бар.
Сұрауларды жазған әрбір адам мына үлгіге тап болған шығар: «Маған Васяның соңғы тапсырысын, оның күнін беріңіз.» Ал егер сізде күні бойынша индекс болмаса немесе сіз пайдаланған индексте күн болмаса, онда сіз дәл сол «тырмаға» қадам жасаңыз.
Бірақ біз бұл «тырма» екенін білеміз, сондықтан әзірлеушіге не істеу керектігін бірден айтпасқа. Тиісінше, қазір жоспарды ашқан кезде, біздің әзірлеушіміз бірден кеңестері бар әдемі суретті көреді, онда олар бірден оған: «Сізде мұнда және мұнда проблемалар бар, бірақ олар осылай және осылай шешіледі».
Нәтижесінде бастапқыда мәселелерді шешу үшін қажет болған тәжірибе көлемі қазір айтарлықтай төмендеді. Бұл бізде бар құрал түрі.
Ақпарат көзі: www.habr.com