Hisobotda imkon beradigan ba'zi yondashuvlar keltirilgan Agar kuniga millionlab so'rovlar bo'lsa, SQL so'rovlarining ishlashini kuzatib boring, va yuzlab nazorat qilinadigan PostgreSQL serverlari mavjud.
Qanday texnik echimlar bizga bunday hajmdagi ma'lumotlarni samarali qayta ishlashga imkon beradi va bu oddiy ishlab chiquvchining hayotini qanday osonlashtiradi?
Kim qiziqadi? muayyan muammolarni tahlil qilish va turli xil optimallashtirish usullari SQL so'rovlari va PostgreSQLda odatiy DBA muammolarini hal qilish - siz ham qila olasiz Ushbu mavzu bo'yicha.
Mening ismim Kirill Borovikov, men vakiliman . Xususan, men kompaniyamizdagi ma'lumotlar bazalari bilan ishlashga ixtisoslashganman.
Bugun men sizga so'rovlarni qanday optimallashtirishimiz haqida gapirib beraman, qachonki siz bitta so'rovning ishlashini "tanlash" kerak emas, balki muammoni ommaviy ravishda hal qiling. Millionlab so'rovlar mavjud bo'lganda va siz ba'zilarini topishingiz kerak yechimga yondashuvlar bu katta muammo.
Umuman olganda, millionlab mijozlarimiz uchun Tensor : korporativ ijtimoiy tarmoq, videomuloqot uchun yechimlar, ichki va tashqi hujjat aylanishi uchun, buxgalteriya hisobi va omborlar uchun buxgalteriya tizimlari,... Ya'ni, integratsiyalashgan biznes boshqaruvi uchun shunday "mega-kombinat", unda 100 dan ortiq turli xil ichki loyihalar.
Ularning barchasi normal ishlashi va rivojlanishini ta'minlash uchun bizda butun mamlakat bo'ylab 10 ta rivojlanish markazlari mavjud bo'lib, ularda ko'proq 1000 ishlab chiquvchilar.
Biz PostgreSQL bilan 2008 yildan beri ishlaymiz va biz qayta ishlayotgan narsalarning katta miqdorini to'pladik - mijoz ma'lumotlari, statistik, analitik, tashqi axborot tizimlaridan ma'lumotlar - 400 TB dan ortiq. Faqat ishlab chiqarishda 250 ga yaqin serverlar mavjud va jami biz kuzatib boradigan 1000 ga yaqin ma'lumotlar bazasi serverlari mavjud.
SQL deklarativ tildir. Siz biror narsaning "qanday ishlashini" emas, balki "nimaga" erishmoqchi ekanligingizni tasvirlaysiz. DBMS JOINni qanday qilishni yaxshi biladi - jadvallarni qanday ulash kerak, qanday shartlar qo'yish kerak, indeksdan nima o'tadi, nima bo'lmaydi...
Ba'zi DBMSlar maslahatlarni qabul qiladi: "Yo'q, bu ikkita jadvalni falon navbat bilan ulang", ammo PostgreSQL buni qila olmaydi. Bu etakchi ishlab chiquvchilarning ongli pozitsiyasi: "Biz ishlab chiquvchilarga qandaydir maslahatlardan foydalanishga ruxsat berishdan ko'ra, so'rovlarni optimallashtiruvchini tugatishni afzal ko'ramiz."
Ammo, PostgreSQL "tashqi" ning o'zini boshqarishiga yo'l qo'ymasa ham, u juda yaxshi imkon beradi. uning ichida nima bo'layotganini ko'ringso'rovingizni bajarganingizda va qayerda muammolar borligi.
Umuman olganda, ishlab chiquvchi [DBA uchun] odatda qanday klassik muammolarga duch keladi? “Mana biz so'rovni bajardik va bizda hamma narsa sekin, hamma narsa osilgan, nimadir sodir bo'lmoqda ... Qandaydir muammo!"
Sabablari deyarli har doim bir xil:
- samarasiz so'rovlar algoritmi
Ishlab chiquvchi: "Endi men unga JOIN orqali SQL-da 10 ta jadval beraman..." - va uning shartlari mo''jizaviy tarzda samarali tarzda "echilishini" kutadi va u hamma narsani tezda oladi. Ammo mo''jizalar sodir bo'lmaydi va bunday o'zgaruvchanlikka ega bo'lgan har qanday tizim (bitta FROMda 10 ta jadval) har doim qandaydir xatoliklarni beradi. [] - eskirgan statistika
Bu nuqta PostgreSQL uchun juda dolzarb bo'lib, siz serverga katta ma'lumotlar to'plamini "to'ksangiz", so'rov yuboring va u planshetingizni "sekskanit qiladi". Chunki kecha unda 10 ta yozuv bor edi, bugun esa 10 million, lekin PostgreSQL bu haqda hali xabardor emas va biz bu haqda aytib berishimiz kerak. [] - resurslarga "ulang"
Siz yetarlicha disk, xotira yoki protsessor unumdorligiga ega bo'lmagan zaif serverga katta va og'ir yuklangan ma'lumotlar bazasini o'rnatdingiz. Va bu hammasi ... Bir joyda siz endi sakrashingiz mumkin bo'lmagan ishlash shifti mavjud. - blokirovka qilish
Bu qiyin nuqta, lekin ular turli xil o'zgartirish so'rovlari uchun eng mos keladi (INSERT, UPDATE, DELETE) - bu alohida katta mavzu.
Reja olish
...Va qolgan hamma narsa uchun biz reja kerak! Biz server ichida nima sodir bo'layotganini ko'rishimiz kerak.
PostgreSQL uchun so'rovlarni bajarish rejasi matn ko'rinishidagi so'rovlarni bajarish algoritmining daraxtidir. Aynan algoritm rejalashtiruvchi tomonidan tahlil natijasida eng samarali deb topildi.
Har bir daraxt tugun operatsiya hisoblanadi: jadval yoki indeksdan ma'lumotlarni olish, bitmap yaratish, ikkita jadvalni birlashtirish, birlashtirish, kesishish yoki tanlashni istisno qilish. So'rovni bajarish ushbu daraxtning tugunlari bo'ylab yurishni o'z ichiga oladi.
So'rov rejasini olishning eng oson yo'li bayonotni bajarishdir EXPLAIN. Barcha haqiqiy atributlarga ega bo'lish, ya'ni so'rovni asosda bajarish uchun - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
Yomon tomoni: uni ishga tushirganingizda, bu "bu erda va hozir" sodir bo'ladi, shuning uchun u faqat mahalliy disk raskadrovka uchun mos keladi. Agar siz kuchli ma'lumotlar oqimi ostida bo'lgan yuqori yuklangan serverni olsangiz va ko'rasiz: “Oh! Bu erda biz sekin ijro etyapmizXia iltimos." Yarim soat, bir soat oldin - siz ishlayotganingizda va jurnallardan ushbu so'rovni olib, uni serverga qaytarganingizda, butun ma'lumotlar to'plami va statistikangiz o'zgardi. Siz uni disk raskadrovka uchun ishga tushirasiz - va u tez ishlaydi! Va nima uchun, nima uchun tushunolmaysiz edi sekin.
So'rov serverda bajarilgan paytda nima sodir bo'lganini tushunish uchun aqlli odamlar yozishdi . U deyarli barcha eng keng tarqalgan PostgreSQL distributivlarida mavjud va oddiygina konfiguratsiya faylida faollashtirilishi mumkin.
Agar u ba'zi so'rovlar siz aytgan chegaradan uzoqroq ishlayotganini tushunsa, u amalga oshiradi Ushbu so'rov rejasining "suratini" va ularni jurnalga birga yozadi.
Hozir hammasi yaxshidek tuyuladi, biz jurnalga boramiz va u erda ko'ramiz ... [matnli oyoq kiyim]. Ammo biz bu haqda hech narsa deya olmaymiz, bundan tashqari, bu ajoyib reja, chunki uni amalga oshirish uchun 11ms vaqt ketdi.
Hammasi yaxshi ko'rinadi - lekin aslida nima bo'lganini hech narsa aniq emas. Umumiy vaqtdan tashqari, biz hech narsani ko'rmayapmiz. Chunki oddiy matnning bunday "qo'zichoq" ga qarash odatda ingl.
Ammo bu aniq bo'lmasa ham, hatto noqulay bo'lsa ham, ko'proq asosiy muammolar mavjud:
- Tugun ko'rsatadi butun pastki daraxtning resurslari yig'indisi uning ostida. Ya'ni, agar uning ostida qandaydir ichki holat mavjud bo'lsa, ushbu Index Scan-ga qancha vaqt sarflanganligini bilib bo'lmaydi. Biz dinamik ravishda "bolalar" va shartli o'zgaruvchilar, CTElar mavjudligini ko'rishimiz kerak va bularning barchasini "ongimizda" olib tashlashimiz kerak.
- Ikkinchi nuqta: tugunda ko'rsatilgan vaqt bitta tugunni bajarish vaqti. Agar bu tugun, masalan, jadval bo'ylab bir necha marta yozib olish natijasida bajarilgan bo'lsa, rejada tsikllar soni - bu tugunning tsikllari ortadi. Ammo atomni bajarish vaqtining o'zi reja jihatidan bir xil bo'lib qoladi. Ya'ni, ushbu tugun jami qancha vaqt bajarilganligini tushunish uchun siz bir narsani boshqasiga ko'paytirishingiz kerak - yana "boshingizda".
Bunday vaziyatlarda "Eng zaif bo'g'in kim?" Deb tushuning. deyarli imkonsiz. Shuning uchun, hatto ishlab chiquvchilarning o'zlari ham "qo'llanmada" buni yozadilar "Rejani tushunish - bu o'rganish, tajriba qilish kerak bo'lgan san'at ...".
Ammo bizda 1000 ta dasturchi bor va ularning har biriga bu tajribani yetkaza olmaysiz. Men, siz, u biladi, lekin u erda kimdir bilmaydi. Ehtimol, u o'rganadi yoki yo'q, lekin u hozir ishlashi kerak - va u bu tajribani qaerdan oladi?
Vizuallashtirish rejasi
Shu sababli, biz ushbu muammolarni hal qilish uchun zarur ekanligini angladik rejaning yaxshi vizualizatsiyasi.
Biz birinchi bo'lib "bozor bo'ylab" chiqdik - keling, Internetda nima borligini ko'rib chiqaylik.
Ammo ko'proq yoki kamroq rivojlanayotgan nisbatan "jonli" echimlar juda kamligi ma'lum bo'ldi - tom ma'noda faqat bittasi: Hubert Lubaczewski tomonidan. "Tasma" maydoniga rejaning matnli ko'rinishini kiritganingizda, u sizga tahlil qilingan ma'lumotlar bilan jadvalni ko'rsatadi:
- tugunning o'ziga xos ishlov berish vaqti
- butun pastki daraxt uchun umumiy vaqt
- statistik kutilgan olingan yozuvlar soni
- tugun tanasining o'zi
Ushbu xizmat shuningdek, havolalar arxivini almashish imkoniyatiga ega. Siz o'z rejangizni u erga tashladingiz va dedingiz: "Hey, Vasya, bu erda havola, u erda nimadir noto'g'ri."
Ammo kichik muammolar ham bor.
Birinchidan, juda ko'p miqdordagi "nusxalash-joylashtirish". Siz logning bir qismini olib, u erga yopishtirasiz va yana va yana.
Ikkinchidan, o'qilgan ma'lumotlar miqdorining tahlili yo'q — chiqadigan bir xil buferlar EXPLAIN (ANALYZE, BUFFERS), biz buni bu erda ko'rmayapmiz. U shunchaki ularni qismlarga ajratish, tushunish va ular bilan ishlashni bilmaydi. Agar siz ko'p ma'lumotlarni o'qiyotgan bo'lsangiz va disk va xotira keshini noto'g'ri taqsimlashingiz mumkinligini tushunsangiz, bu ma'lumot juda muhimdir.
Uchinchi salbiy nuqta - bu loyihaning juda zaif rivojlanishi. Majburiyatlar juda kichik, har olti oyda bir marta va kod Perlda bo'lsa yaxshi bo'ladi.
Ammo bularning barchasi "qo'shiq matni", biz qandaydir tarzda bu bilan yashashimiz mumkin edi, lekin bizni bu xizmatdan uzoqlashtirgan bir narsa bor. Bular Common Table Expression (CTE) va InitPlan/SubPlan kabi turli dinamik tugunlarni tahlil qilishdagi xatolardir.
Agar siz ushbu rasmga ishonsangiz, unda har bir alohida tugunning umumiy bajarilish vaqti butun so'rovning umumiy bajarilish vaqtidan kattaroqdir. Hammasi oddiy - ushbu CTE ni yaratish vaqti CTE Scan tugunidan ayirilmagan. Shuning uchun, biz endi CTE skanerining o'zi qancha davom etganiga to'g'ri javobni bilmaymiz.
Keyin biz o'zimizni yozish vaqti kelganini angladik - hurray! Har bir ishlab chiquvchi aytadi: "Endi biz o'zimizni yozamiz, bu juda oson bo'ladi!"
Biz veb-xizmatlar uchun odatiy stek oldik: Node.js + Express asosidagi yadro, chiroyli diagrammalar uchun Bootstrap va D3.js-dan foydalanilgan. Va bizning umidlarimiz to'liq oqlandi - biz 2 hafta ichida birinchi prototipni oldik:
- shaxsiy reja tahlilchisi
Ya'ni, endi biz PostgreSQL tomonidan yaratilgan har qanday rejani tahlil qilishimiz mumkin. - dinamik tugunlarni to'g'ri tahlil qilish - CTE Scan, InitPlan, SubPlan
- buferlarni taqsimlash tahlili - ma'lumotlar sahifalari qaerda xotiradan o'qiladi, qaerda mahalliy keshdan, qayerdan diskdan
- aniqlik oldi
Bularning barchasini jurnalda "qazish" uchun emas, balki darhol rasmda "eng zaif aloqa" ni ko'rish uchun.
Bizda shunga o'xshash narsa bor, sintaksis ta'kidlangan. Ammo, odatda, bizning ishlab chiquvchilarimiz endi rejaning to'liq ifodasi bilan emas, balki qisqaroq bilan ishlaydilar. Axir, biz allaqachon barcha raqamlarni tahlil qildik va ularni chapga va o'ngga tashladik va o'rtada faqat birinchi qatorni qoldirdik, bu qanday tugun: CTE Scan, CTE Generation yoki Seq Scan ba'zi bir belgiga ko'ra.
Bu biz chaqiradigan qisqartirilgan vakillikdir reja shabloni.
Yana nima qulay bo'lar edi? Bizning umumiy vaqtimizning qaysi ulushi qaysi tugunga ajratilganligini ko'rish qulay bo'lar edi - va shunchaki yon tomonga "yopishib qo'ying" pirog grafigi.
Biz tugunni ko'rsatamiz va ko'ramiz - ma'lum bo'lishicha, Seq Scan umumiy vaqtning to'rtdan bir qismidan kamroq vaqtni olgan, qolgan 3/4 qismi esa CTE Scan tomonidan olingan. Dahshat! Bu CTE Scan-ning "olov tezligi" haqida kichik eslatma, agar siz ularni so'rovlaringizda faol ishlatsangiz. Ular juda tez emas - ular oddiy jadvallarni skanerlashdan ham past.
Ammo, odatda, bunday diagrammalar yanada qiziqarli va murakkabroq bo'ladi, biz darhol segmentga ishora qilsak va, masalan, Seq Scan vaqtining yarmidan ko'pi "yeganini" ko'rsak. Bundan tashqari, ichkarida qandaydir Filtr bor edi, unga ko'ra ko'plab yozuvlar yo'q qilindi ... Siz ushbu rasmni ishlab chiquvchiga to'g'ridan-to'g'ri tashlab, shunday deyishingiz mumkin: "Vasya, bu erda siz uchun hamma narsa yomon! Tushun, qarang, nimadir noto'g'ri!»
Tabiiyki, ba'zi "rakeslar" ishtirok etdi.
Biz duch kelgan birinchi narsa yaxlitlash muammosi edi. Rejadagi har bir alohida tugunning vaqti 1 mks aniqlik bilan ko'rsatilgan. Va tugun tsikllari soni, masalan, 1000 dan oshganda - PostgreSQL bajarilgandan so'ng, "aniqlik doirasida" bo'linadi, keyin qayta hisoblashda biz umumiy vaqtni "0.95 ms dan 1.05 ms gacha" olamiz. Hisoblash mikrosekundlarga yetganda, bu yaxshi, lekin [milli] soniyalar bo'lganda, "kim qancha iste'mol qilgan" rejasining tugunlariga resurslarni "echishda" bu ma'lumotni hisobga olishingiz kerak.
Ikkinchi nuqta, murakkabroq, bu resurslarni (bu buferlarni) dinamik tugunlar o'rtasida taqsimlashdir. Bu bizga prototipning dastlabki 2 haftasini va yana 4 haftani sarfladi.
Bunday muammoni hal qilish juda oson - biz CTE qilamiz va go'yoki unda biror narsani o'qiymiz. Aslida, PostgreSQL "aqlli" va u erda hech narsani o'qimaydi. Keyin biz undan birinchi rekordni olamiz va unga o'sha CTE dan yuz birinchi rekordni olamiz.
Biz rejani ko'rib chiqamiz va tushunamiz - bu g'alati, bizda Seq Scan-da 3 ta bufer (ma'lumotlar sahifalari), CTE Scan-da yana 1 ta va ikkinchi CTE Scan-da yana 2 ta "iste'mol qilingan". Ya'ni, agar biz hamma narsani oddiygina jamlasak, biz 6 ni olamiz, lekin planshetdan biz faqat 3 ni o'qiymiz! CTE Scan hech qanday joydan hech narsani o'qimaydi, lekin to'g'ridan-to'g'ri jarayon xotirasi bilan ishlaydi. Ya'ni, bu erda nimadir noto'g'ri ekanligi aniq!
Darhaqiqat, ma'lum bo'lishicha, bu erda Seq Scan-dan so'ralgan 3 sahifa ma'lumotlarning barchasi bor, avval 1-si 1-CTE skanerini so'ragan, keyin esa 2-chi va yana 2 tasi unga o'qilgan. Ya'ni, jami 3 sahifa o'qilgan ma'lumotlar, 6 emas.
Va bu rasm bizni rejaning bajarilishi endi daraxt emas, balki oddiygina qandaydir asiklik grafik ekanligini tushunishga olib keldi. Va biz shunday diagramma oldik, shunda biz "birinchi navbatda qaerdan kelganini" tushunamiz. Ya'ni, bu yerda biz pg_class dan CTE ni yaratdik va uni ikki marta so'radik va 2-marta so'raganimizda deyarli barcha vaqtimiz filialga o'tdi. 101-yozuvni o'qish planshetdan 1-yozuvni o'qishdan ko'ra ancha qimmatga tushishi aniq.
Biz bir muddat nafas oldik. Ular: “Endi, Neo, sen kung-funi bilasan! Endi bizning tajribamiz sizning ekraningizda. Endi siz undan foydalanishingiz mumkin."
Jurnalni birlashtirish
1000 ta ishlab chiquvchilarimiz yengil nafas olishdi. Ammo bizda faqat yuzlab "jangovar" serverlar borligini tushundik va ishlab chiquvchilar tomonidan bu "nusxa ko'chirish-joylashtirish" unchalik qulay emas. Biz buni o'zimiz yig'ishimiz kerakligini tushundik.
Umuman olganda, statistik ma'lumotlarni to'plashi mumkin bo'lgan standart modul mavjud, ammo uni konfiguratsiyada ham faollashtirish kerak - bu . Ammo u bizga mos kelmadi.
Birinchidan, u bir xil ma'lumotlar bazasida turli xil sxemalar yordamida bir xil so'rovlarni tayinlaydi turli xil QueryIdlar. Ya'ni, agar siz birinchi marta qilsangiz SET search_path = '01'; SELECT * FROM user LIMIT 1;va keyin SET search_path = '02'; va bir xil so'rov bo'lsa, unda ushbu modulning statistikasi turli xil yozuvlarga ega bo'ladi va men sxemalarni hisobga olmasdan, ushbu so'rov profili kontekstida umumiy statistikani to'play olmayman.
Uni ishlatishimizga to'sqinlik qilgan ikkinchi nuqta rejalarning etishmasligi. Ya'ni, hech qanday reja yo'q, faqat so'rovning o'zi bor. Biz nima sekinlashayotganini ko'ramiz, lekin nima uchun ekanligini tushunmayapmiz. Va bu erda biz tez o'zgaruvchan ma'lumotlar to'plami muammosiga qaytamiz.
Va oxirgi daqiqa - "faktlar" yo'qligi. Ya'ni, siz so'rovni bajarishning ma'lum bir misoliga murojaat qila olmaysiz - yo'q, faqat jamlangan statistika mavjud. Bu bilan ishlash mumkin bo'lsa-da, bu juda qiyin.
Shuning uchun biz copy-paste bilan kurashishga qaror qildik va yozishni boshladik kollektor.
Kollektor SSH orqali ulanadi, sertifikat yordamida ma'lumotlar bazasi bilan serverga xavfsiz ulanishni o'rnatadi va tail -F jurnal faylida unga "yopishadi". Shunday qilib, ushbu sessiyada biz butun jurnal faylining to'liq "oynasini" olamizserver yaratadigan . Serverdagi yuk minimal, chunki biz u erda hech narsani tahlil qilmaymiz, faqat trafikni aks ettiramiz.
Biz allaqachon Node.js da interfeys yozishni boshlaganimiz sababli, undagi kollektorni yozishni davom ettirdik. Va bu texnologiya o'zini oqladi, chunki u log bo'lgan zaif formatlangan matnli ma'lumotlar bilan ishlash uchun JavaScript-dan foydalanish juda qulay. Node.js infratuzilmasining o‘zi esa backend platformasi sifatida tarmoq ulanishlari va, albatta, har qanday ma’lumot oqimlari bilan oson va qulay ishlash imkonini beradi.
Shunga ko'ra, biz ikkita ulanishni "uzatamiz": birinchisi, jurnalning o'zini "tinglash" va uni o'zimizga olib borish, ikkinchisi esa vaqti-vaqti bilan bazani so'rash. "Ammo jurnal oid 123 belgisi bloklanganligini ko'rsatadi", ammo bu ishlab chiquvchi uchun hech narsani anglatmaydi va ma'lumotlar bazasidan "OID = 123 nima bo'lsa ham?" deb so'rash yaxshi bo'lardi. Va shuning uchun biz vaqti-vaqti bilan bazadan o'zimiz haqimizda hali bilmagan narsalarni so'raymiz.
"Faqat bir narsani siz hisobga olmadingiz, filga o'xshash asalarilar turi bor!.." Biz 10 ta serverni kuzatmoqchi bo'lganimizda ushbu tizimni ishlab chiqishni boshladik. Bizning tushunishimizdagi eng muhimi, bu erda hal qilish qiyin bo'lgan ba'zi muammolar paydo bo'ldi. Ammo birinchi chorak davomida biz monitoring uchun yuzta oldik - chunki tizim ishlagan, hamma buni xohladi, hamma qulay edi.
Bularning barchasini qo'shish kerak, ma'lumotlar oqimi katta va faol. Darhaqiqat, biz kuzatadigan, nima bilan shug'ullanishimiz mumkin bo'lsa, biz foydalanadigan narsadir. Shuningdek, biz PostgreSQL-dan ma'lumotlarni saqlash sifatida foydalanamiz. Va unga ma'lumotni "to'kish" uchun operatordan tezroq hech narsa yo'q COPY Hali emas.
Ammo oddiygina ma'lumotlarni "to'kish" bizning texnologiyamiz emas. Chunki yuzta serverda sekundiga taxminan 50 ming so'rov bo'lsa, bu kuniga 100-150 Gb loglarni yaratadi. Shuning uchun, biz bazani ehtiyotkorlik bilan "kesishimiz" kerak edi.
Birinchidan, biz qildik kun bo'yicha bo'linish, chunki, umuman olganda, kunlar orasidagi bog'liqlik hech kimni qiziqtirmaydi. Agar bugun kechqurun dasturning yangi versiyasini chiqargan bo'lsangiz - va allaqachon yangi statistik ma'lumotlarga ega bo'lsangiz, kechagi narsangiz qanday farq qiladi.
Ikkinchidan, biz o'rgandik (majbur qildik) yordamida yozish uchun juda, juda tez COPY. Ya'ni, shunchaki emas COPYchunki u tezroq INSERT, va undan ham tezroq.
Uchinchi nuqta - men majbur bo'ldim mos ravishda triggerlarni va chet el kalitlarini tark etish. Ya'ni, bizda umuman referent yaxlitligi yo'q. Chunki agar sizda FK juftligi bo‘lgan jadvalingiz bo‘lsa va siz ma’lumotlar bazasi tuzilmasida “bu yerda FK tomonidan, masalan, yozuvlar guruhiga havola qilingan jurnal yozuvi” desangiz, uni kiritganingizda PostgreSQL, uni qanday qabul qilish va buni halol qilishdan boshqa hech narsa qolmadi SELECT 1 FROM master_fk1_table WHERE ... Siz kiritmoqchi bo'lgan identifikator bilan - bu yozuv mavjudligini tekshirish uchun, siz kiritishingiz bilan ushbu Chet el kalitini "buzmasligingiz" uchun.
Maqsadli jadval va uning indekslari uchun bitta yozuv o'rniga, biz unga tegishli bo'lgan barcha jadvallarni o'qishdan qo'shimcha foyda olamiz. Ammo bu bizga umuman kerak emas - bizning vazifamiz imkon qadar ko'proq va eng kam yuk bilan imkon qadar tezroq yozib olishdir. Shunday qilib, FK - pastga!
Keyingi nuqta - yig'ish va xeshlash. Dastlab, biz ularni ma'lumotlar bazasida joriy qildik - axir, yozuv kelganda, uni qandaydir planshetda qilish qulay "ortiqcha bitta" to'g'ridan-to'g'ri triggerda. Xo'sh, bu qulay, lekin bir xil yomon narsa - siz bitta yozuvni kiritasiz, lekin boshqa jadvaldan boshqa narsani o'qish va yozishga majbur bo'lasiz. Bundan tashqari, siz nafaqat o'qiysiz va yozasiz, balki har safar ham qilasiz.
Endi sizda ma'lum bir xost orqali o'tgan so'rovlar sonini hisoblaydigan jadval borligini tasavvur qiling: +1, +1, +1, ..., +1. Va siz, qoida tariqasida, bunga muhtoj emassiz - bu mumkin kollektordagi xotira summasi va bir vaqtning o'zida ma'lumotlar bazasiga yuboring +10.
Ha, ba'zi muammolar yuzaga kelganda, sizning mantiqiy yaxlitligingiz "yiqilib ketishi" mumkin, ammo bu deyarli haqiqatga to'g'ri kelmaydigan holat - chunki sizda oddiy server bor, u boshqaruvchida batareya bor, sizda tranzaktsiyalar jurnali, jurnalda jurnal mavjud. fayl tizimi ... Umuman olganda, bunga loyiq emas. Triggerlar/FK-ni ishga tushirishdan olingan mahsuldorlikning yo'qolishi siz sarflagan xarajatlarga arzimaydi.
Hashing bilan ham xuddi shunday. Muayyan so'rov sizga uchib ketadi, siz undan ma'lum bir identifikatorni ma'lumotlar bazasida hisoblaysiz, uni ma'lumotlar bazasiga yozasiz va keyin hammaga aytasiz. Hammasi yaxshi, yozib olish paytida sizga xuddi shu narsani yozmoqchi bo'lgan ikkinchi odam kelmaguncha - va siz bloklanasiz va bu allaqachon yomon. Shuning uchun, agar siz ba'zi identifikatorlarni yaratishni mijozga o'tkaza olsangiz (ma'lumotlar bazasiga nisbatan), buni qilish yaxshiroqdir.
MD5 dan matndan foydalanish biz uchun juda zo'r bo'ldi - so'rov, reja, shablon, ... Biz uni kollektor tomonida hisoblaymiz va tayyor identifikatorni ma'lumotlar bazasiga "quyamiz". MD5 uzunligi va kunlik bo'linish bizga mumkin bo'lgan to'qnashuvlar haqida tashvishlanmaslik imkonini beradi.
Ammo bularning barchasini tezda yozib olish uchun biz yozish jarayonini o'zgartirishimiz kerak edi.
Odatda ma'lumotlarni qanday yozasiz? Bizda qandaydir ma’lumotlar to‘plami bor, biz uni bir nechta jadvallarga bo‘lamiz, so‘ngra undan NUSHIRA OLAMIZ – avval birinchisiga, keyin ikkinchisiga, uchinchisiga... Bu noqulay, chunki biz bir ma’lumot oqimini uch bosqichda yozayotganga o‘xshaymiz. ketma-ket. Noxush. Buni tezroq qilish mumkinmi? Mumkin!
Buning uchun bu oqimlarni bir-biriga parallel ravishda parchalash kifoya. Ma'lum bo'lishicha, bizda xatolar, so'rovlar, shablonlar, blokirovkalar, ... alohida mavzularda uchib ketish - va biz hammasini parallel ravishda yozamiz. Buning uchun yetarli har bir alohida maqsadli jadval uchun COPY kanalini doimiy ravishda ochiq tuting.
Ya'ni, kollektorda har doim oqim bor, unga kerakli ma'lumotlarni yozishim mumkin. Ammo ma'lumotlar bazasi ushbu ma'lumotlarni ko'rishi va kimdir bu ma'lumotlarning yozilishini kutishda qolib ketmasligi uchun, COPY ma'lum vaqt oralig'ida to'xtatilishi kerak. Biz uchun eng samarali davr taxminan 100 ms edi - biz uni yopamiz va darhol o'sha stolga qayta ochamiz. Va agar ba'zi cho'qqilarda bizda bitta oqim etarli bo'lmasa, biz ma'lum bir chegaragacha birlashamiz.
Bundan tashqari, biz bunday yuk profili uchun yozuvlar to'plamlarda yig'ilganda har qanday yig'ish yomon ekanligini aniqladik. Klassik yovuzlik INSERT ... VALUES va yana 1000 ta yozuvlar. Chunki o'sha paytda siz ommaviy axborot vositalarida yozish cho'qqisiga ega bo'lasiz va diskka biror narsa yozishga harakat qilayotgan har bir kishi kutadi.
Bunday anomaliyalardan xalos bo'lish uchun hech narsani jamlamang, umuman bufer qilmang. Va agar diskda buferlash sodir bo'lsa (xayriyatki, Node.js-dagi Stream API sizga buni aniqlash imkonini beradi) - bu ulanishni kechiktiring. Siz yana bepul bo'lgan voqeani olganingizda, unga to'plangan navbatdan yozing. Va u band bo'lsa, basseyndan keyingi bepulni oling va unga yozing.
Ma'lumotlarni yozishga ushbu yondashuvni joriy etishdan oldin bizda taxminan 4K yozish operatsiyalari mavjud edi va shu bilan biz yukni 4 baravar kamaytirdik. Endi ular yangi nazorat qilinadigan ma'lumotlar bazalari hisobiga yana 6 marta o'sdi - 100MB/s gacha. Va endi biz so'nggi 3 oy davomida jurnallarni taxminan 10-15 TB hajmda saqlaymiz, chunki atigi uch oy ichida har qanday ishlab chiquvchi har qanday muammoni hal qila oladi.
Biz muammolarni tushunamiz
Ammo bu ma'lumotlarni yig'ish yaxshi, foydali, dolzarb, ammo etarli emas - buni tushunish kerak. Chunki bular kuniga millionlab turli rejalar.
Ammo millionlarni boshqarib bo'lmaydi, biz birinchi navbatda "kichikroq" qilishimiz kerak. Va, birinchi navbatda, siz bu "kichikroq" narsani qanday tashkil qilishingizni hal qilishingiz kerak.
Biz uchta asosiy nuqtani aniqladik:
- kim? ushbu so'rovni yubordi
Ya'ni, u qaysi dasturdan "keldi": veb-interfeys, backend, to'lov tizimi yoki boshqa narsa. - qayerda sodir bo'ldi
Qaysi maxsus serverda? Agar bitta dastur ostida bir nechta serverlar mavjud bo'lsa va to'satdan bittasi "ahmoqona" bo'lib qolsa (chunki "disk chirigan", "xotira oqib ketgan", boshqa muammo), unda siz serverga maxsus murojaat qilishingiz kerak. - qanday muammo u yoki bu tarzda o'zini namoyon qildi
"Kim" bizga so'rov yuborganini tushunish uchun biz standart vositadan foydalanamiz - sessiya o'zgaruvchisini o'rnatish: SET application_name = '{bl-host}:{bl-method}'; — biz soʻrov kelayotgan biznes mantiqiy xost nomini va uni boshlagan usul yoki dastur nomini yuboramiz.
So'rovning "egasini" topshirganimizdan so'ng, u jurnalga chiqarilishi kerak - buning uchun biz o'zgaruvchini sozlaymiz log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Qiziq bo'lganlar uchun, ehtimol hammasi nimani anglatadi. Ma'lum bo'lishicha, biz jurnalda ko'ramiz:
- время
- jarayon va tranzaksiya identifikatorlari
- ma'lumotlar bazasi nomi
- Ushbu so'rovni yuborgan shaxsning IP
- va usul nomi
Keyin biz turli serverlar o'rtasidagi bitta so'rov uchun korrelyatsiyani ko'rib chiqish unchalik qiziq emasligini tushundik. Bitta dastur bu erda va u erda teng ravishda ishlamay qoladigan vaziyat tez-tez uchramaydi. Ammo bir xil bo'lsa ham, ushbu serverlardan biriga qarang.
Shunday qilib, bu erda kesish "bir server - bir kun" har qanday tahlil uchun bizga yetarli bo'lib chiqdi.
Birinchi tahliliy qism ham xuddi shunday "namuna" - barcha raqamli ko'rsatkichlardan tozalangan rejani taqdim etishning qisqartirilgan shakli. Ikkinchi kesish - bu dastur yoki usul, uchinchi kesish esa bizga muammolarni keltirib chiqaradigan aniq reja tugunidir.
Muayyan misollardan shablonlarga o'tganimizda, biz bir vaqtning o'zida ikkita afzalliklarga ega bo'ldik:
- tahlil qilish uchun ob'ektlar sonining bir necha bor qisqarishi
Muammoni endi minglab so'rovlar yoki rejalar bilan emas, balki o'nlab andozalar bo'yicha tahlil qilishimiz kerak. - vaqt jadvali
Ya'ni, ma'lum bir bo'limdagi "faktlar" ni umumlashtirib, siz kun davomida ularning ko'rinishini ko'rsatishingiz mumkin. Va bu erda siz tushunishingiz mumkinki, agar sizda, masalan, soatiga bir marta sodir bo'ladigan biron bir naqsh bo'lsa, lekin kuniga bir marta sodir bo'lishi kerak bo'lsa, nima noto'g'ri bo'lganligi haqida o'ylashingiz kerak - bunga kim va nima uchun, ehtimol bu erda bo'lishi kerak. qilmaslik kerak. Bu boshqa raqamli bo'lmagan, sof vizual tahlil usuli.
Qolgan usullar biz rejadan chiqaradigan ko'rsatkichlarga asoslanadi: bunday naqsh necha marta sodir bo'lgan, umumiy va o'rtacha vaqt, diskdan qancha ma'lumot o'qilgan va xotiradan qancha ...
Chunki, masalan, siz xost uchun tahlil sahifasiga keldingiz, qarang - diskda biror narsa juda ko'p o'qiy boshladi. Serverdagi disk buni uddalay olmaydi - undan kim o'qiydi?
Va siz har qanday ustun bo'yicha saralashingiz va hozir nima bilan shug'ullanishingizni hal qilishingiz mumkin - protsessor yoki diskdagi yuk yoki so'rovlarning umumiy soni ... Biz uni tartibladik, "yuqori"larini ko'rib chiqdik, tuzatdik va ilovaning yangi versiyasini chiqardi.
Va darhol siz kabi so'rovdan bir xil shablon bilan kelgan turli xil ilovalarni ko'rishingiz mumkin SELECT * FROM users WHERE login = 'Vasya'. Frontend, backend, processing... Va agar u bilan aloqa qilmasa, nima uchun ishlov berish foydalanuvchini o'qiydi, deb o'ylaysiz.
Buning teskari yo'li - dasturdan nima qilishini darhol ko'rish. Misol uchun, frontend - bu, bu, bu va bu soatda bir marta (xronologiya yordam beradi). Va darhol savol tug'iladi: soatiga bir marta nimadir qilish frontendning ishi emasga o'xshaydi ...
Biroz vaqt o'tgach, biz birlashma etishmayotganimizni angladik reja tugunlari bo'yicha statistika. Biz rejalardan faqat jadval ma'lumotlari bilan biror narsa qiladigan tugunlarni ajratib oldik (ularni indeks bo'yicha o'qing/yozing yoki yo'q). Aslida, oldingi rasmga nisbatan faqat bitta jihat qo'shilgan - bu tugun bizga qancha rekordlarni olib keldi?, va qanchasi o'chirildi (Qatorlar filtr orqali o'chirildi).
Plastinada mos indeks yo'q, siz unga so'rov yuborasiz, u indeksdan o'tib ketadi, Seq Scan-ga tushadi ... siz bittadan tashqari barcha yozuvlarni filtrladingiz. Nima uchun kuniga 100 million filtrlangan yozuvlar kerak? Indeksni yig'ish yaxshiroq emasmi?
Barcha rejalarni tugun bo'yicha tahlil qilib, biz rejalarda shubhali ko'rinishga ega bo'lgan ba'zi tipik tuzilmalar mavjudligini tushundik. Va ishlab chiquvchiga aytish yaxshi bo'lardi: "Do'stim, bu erda siz avval indeks bo'yicha o'qiysiz, keyin saralaysiz va keyin kesib tashlaysiz" - qoida tariqasida, bitta yozuv mavjud.
So'rovlarni yozgan har bir kishi, ehtimol, bu naqshga duch kelgan bo'lishi mumkin: "Menga Vasya uchun oxirgi buyurtmani bering, uning sanasi." Va agar sizda sana bo'yicha indeks bo'lmasa yoki siz foydalangan indeksda sana bo'lmasa, unda siz shunday bo'lasiz. aynan bir xil "rake" ga qadam qo'ying.
Ammo biz bu "rake" ekanligini bilamiz - shuning uchun nima uchun darhol ishlab chiquvchiga nima qilish kerakligini aytmaysiz. Shunga ko'ra, hozir rejani ochganda, bizning ishlab chiquvchimiz darhol maslahatlar bilan chiroyli rasmni ko'radi, u erda ular darhol unga: "Sizda bu erda va u erda muammolar bor, lekin ular bu tarzda va boshqa tarzda hal qilinadi".
Natijada, boshida muammolarni hal qilish uchun zarur bo'lgan tajriba miqdori hozir sezilarli darajada kamaydi. Bizda shunday vosita bor.
Manba: www.habr.com