Qanday qilib samaraliroq serverga o'tmasdan ma'lumotlar bazasiga so'rovlar sonini 10 barobar oshirish va tizim funksionalligini saqlab qolish mumkin? Men sizga ma'lumotlar bazamiz unumdorligining pasayishiga qanday munosabatda bo'lganimizni, SQL so'rovlarini iloji boricha ko'proq foydalanuvchilarga xizmat ko'rsatish va hisoblash resurslari narxini oshirmaslik uchun optimallashtirishni aytib beraman.
Men qurilish kompaniyalarida biznes jarayonlarini boshqarish bo'yicha xizmat ko'rsataman. Biz bilan 3 mingga yaqin kompaniya ishlaydi. Bizning tizimimizda har kuni 10 mingdan ortiq odam 4-10 soat ishlaydi. Rejalashtirish, ogohlantirish, ogohlantirish, tekshirishning turli muammolarini hal qiladi... Biz PostgreSQL 9.6 dan foydalanamiz. Ma'lumotlar bazasida 300 ga yaqin jadval mavjud va har kuni 200 milliongacha so'rovlar (10 ming xil) olinadi. Bizda o'rtacha soniyada 3-4 ming so'rov bor, eng faol daqiqalarda soniyada 10 mingdan ortiq so'rovlar. So'rovlarning aksariyati OLAP. Qo'shimchalar, o'zgartirishlar va o'chirishlar kamroq, ya'ni OLTP yuki nisbatan engil. Loyihamiz ko'lamini baholashingiz va tajribamiz siz uchun qanchalik foydali ekanini tushunishingiz uchun men bu raqamlarning barchasini keltirdim.
Birinchi rasm. Lirik
Rivojlanishni boshlaganimizda, biz ma'lumotlar bazasiga qanday yuk tushishi va agar server tortishni to'xtatsa, nima qilishimiz haqida o'ylamagan edik. Ma'lumotlar bazasini loyihalashda biz umumiy tavsiyalarga amal qildik va oyog'imizga o'q uzmaslikka harakat qildik, lekin "naqshdan foydalanmang" kabi umumiy maslahatlardan tashqariga chiqdik. biz kirmadik. Biz ma'lumotlarning ortiqcha bo'lishiga yo'l qo'ymaslik uchun normalizatsiya tamoyillari asosida ishlab chiqdik va ba'zi so'rovlarni tezlashtirish haqida qayg'urmadik. Birinchi foydalanuvchilar kelishi bilan biz ishlash muammosiga duch keldik. Odatdagidek, biz bunga mutlaqo tayyor emas edik. Birinchi muammolar oddiy bo'lib chiqdi. Qoida tariqasida, hamma narsa yangi indeks qo'shish orqali hal qilindi. Ammo oddiy yamoqlar ishlamay qolgan vaqt keldi. Bizda tajriba yetishmasligini va muammolarga nima sabab bo‘layotganini tushunish tobora qiyinlashib borayotganini tushunib, serverni to‘g‘ri o‘rnatishga, monitoringni ulashga yordam bergan va qayerdan olish kerakligini ko‘rsatgan mutaxassislarni yolladik. .
Ikkinchi rasm. Statistik
Shunday qilib, bizning ma'lumotlar bazamizda kuniga 10 mingga yaqin turli so'rovlar bajariladi. Ushbu 10 mingdan 2-3 million marta o'rtacha bajarilish vaqti 0.1-0.3 ms bo'lgan yirtqich hayvonlar va kuniga 30 marta chaqiriladigan o'rtacha bajarilish vaqti 100 soniya bo'lgan so'rovlar mavjud.
Barcha 10 ming so'rovni optimallashtirishning iloji bo'lmadi, shuning uchun biz ma'lumotlar bazasi ish faoliyatini to'g'ri yaxshilash uchun harakatlarimizni qayerga yo'naltirishni aniqlashga qaror qildik. Bir necha marta takrorlangandan so'ng, biz so'rovlarni turlarga bo'lishni boshladik.
TOP soʻrovlar
Bu eng ko'p vaqt talab qiladigan eng og'ir so'rovlardir (umumiy vaqt). Bular tez-tez chaqiriladigan so'rovlar yoki bajarilishi juda uzoq vaqt talab qiladigan so'rovlardir (tezlik uchun kurashning birinchi iteratsiyasida uzoq va tez-tez so'rovlar optimallashtirilgan). Natijada, server ularning bajarilishiga eng ko'p vaqt sarflaydi. Bundan tashqari, eng yuqori so'rovlarni umumiy bajarilish vaqti va IO vaqti bo'yicha alohida ajratish muhimdir. Bunday so'rovlarni optimallashtirish usullari biroz boshqacha.
Barcha kompaniyalarning odatiy amaliyoti TOP so'rovlari bilan ishlashdir. Ulardan bir nechtasi bor; hatto bitta so'rovni optimallashtirish 5-10% resurslarni bo'shatishi mumkin. Biroq, loyiha etuklashgan sari, TOP so'rovlarni optimallashtirish tobora ahamiyatsiz vazifaga aylanadi. Barcha oddiy usullar allaqachon ishlab chiqilgan va eng "og'ir" so'rov "faqat" 3-5% resurslarni oladi. Agar TOP so'rovlar jami 30-40% dan kam vaqtni oladigan bo'lsa, demak, siz ularni tez ishlashiga harakat qilgansiz va keyingi guruhdagi so'rovlarni optimallashtirishga o'tish vaqti keldi.
Ushbu guruhga qancha eng yaxshi so'rovlarni kiritish kerakligi haqidagi savolga javob berish qoladi. Men odatda kamida 10 tani olaman, lekin 20 dan ko'p emas. TOP guruhidagi birinchi va oxirgining vaqti 10 martadan ko'p bo'lmasligini ta'minlashga harakat qilaman. Ya'ni, agar so'rovni bajarish vaqti 1-o'rindan 10-o'ringa keskin tushib qolsa, men TOP-10 ni olaman, agar pasayish asta-sekin bo'lsa, guruh hajmini 15 yoki 20 ga oshiraman.
O'rta dehqonlar
Bularning barchasi TOPdan so'ng darhol keladigan so'rovlardir, oxirgi 5-10% bundan mustasno. Odatda, ushbu so'rovlarni optimallashtirishda server ish faoliyatini sezilarli darajada oshirish imkoniyati mavjud. Ushbu so'rovlar 80% gacha bo'lishi mumkin. Ammo ularning ulushi 50% dan oshsa ham, ularga diqqat bilan qarash vaqti keldi.
Quyruq
Yuqorida aytib o'tilganidek, bu so'rovlar oxirida keladi va vaqtning 5-10% ni oladi. Agar siz avtomatik so'rovlarni tahlil qilish vositalaridan foydalanmasangiz, ular haqida unutishingiz mumkin, keyin ularni optimallashtirish ham arzon bo'lishi mumkin.
Har bir guruhni qanday baholash mumkin?
Men PostgreSQL uchun bunday baholashni amalga oshirishga yordam beradigan SQL so'rovidan foydalanaman (ishonchim komilki, shunga o'xshash so'rov boshqa ko'plab DBMSlar uchun ham yozilishi mumkin)
TOP-MEDIUM-TAIL guruhlari hajmini baholash uchun SQL so'rovi
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
So'rovning natijasi uchta ustun bo'lib, ularning har biri ushbu guruh so'rovlarini qayta ishlash uchun qancha vaqtni o'z ichiga oladi. So'rov ichida ikkita raqam mavjud (mening holimda bu 20 va 800) so'rovlarni bir guruhdan boshqasidan ajratib turadi.
So'rovlar ulushini optimallashtirish bo'yicha ish boshlangan vaqt va hozirda taxminan shunday taqqoslash mumkin.
Diagramma shuni ko'rsatadiki, TOP so'rovlarining ulushi keskin kamaydi, ammo "o'rta dehqonlar" ko'paydi.
Dastlab, TOP so'rovlar ochiq qo'pol xatolarni o'z ichiga olgan. Vaqt o'tishi bilan bolalar kasalliklari yo'qoldi, TOP so'rovlar ulushi kamaydi va qiyin so'rovlarni tezlashtirish uchun tobora ko'proq harakat qilish kerak edi.
So'rovlar matnini olish uchun biz quyidagi so'rovdan foydalanamiz
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 so'rovlarni tezlashtirishga yordam beradigan eng ko'p qo'llaniladigan texnikalar ro'yxati:
- Tizimni qayta loyihalash, masalan, ma'lumotlar bazasiga davriy so'rovlar o'rniga xabarlar brokeri yordamida bildirishnoma mantig'ini qayta ishlash.
- Indekslarni qo'shish yoki o'zgartirish
- ORM so'rovlarini sof SQL ga qayta yozish
- Dangasa ma'lumotlarni yuklash mantiqini qayta yozish
- Ma'lumotlarni denormalizatsiya qilish orqali keshlash. Misol uchun, bizda jadval ulanishi mavjud Etkazib berish -> Invoice -> So'rov -> Ilova. Ya'ni, har bir etkazib berish boshqa jadvallar orqali dastur bilan bog'lanadi. Har bir so'rovdagi barcha jadvallarni bog'lamaslik uchun biz etkazib berish jadvalidagi so'rovga havolani takrorladik.
- Statik jadvallarni ma'lumotnomalar bilan keshlash va dastur xotirasida jadvallarni kamdan-kam o'zgartirish.
Ba'zida o'zgarishlar ta'sirchan qayta dizaynga aylandi, ammo ular tizim yukining 5-10% ni ta'minladi va oqlandi. Vaqt o'tishi bilan egzoz kichikroq va kichikroq bo'lib qoldi va tobora jiddiy qayta ishlash talab qilindi.
Keyin e’tiborimizni so‘rovlarning ikkinchi guruhi – o‘rta dehqonlar guruhiga qaratdik. Unda yana ko'plab so'rovlar bor va butun guruhni tahlil qilish uchun ko'p vaqt kerak bo'lib tuyuldi. Biroq, ko'pchilik so'rovlarni optimallashtirish juda oddiy bo'lib chiqdi va ko'plab muammolar turli xil o'zgarishlarda o'nlab marta takrorlandi. Biz o'nlab shunga o'xshash so'rovlarga qo'llagan ba'zi bir tipik optimallashtirish misollari va optimallashtirilgan so'rovlarning har bir guruhi ma'lumotlar bazasini 3-5% ga tushirdi.
- COUNT va to'liq jadvalni skanerlash yordamida yozuvlar mavjudligini tekshirish o'rniga EXISTS ishlatila boshlandi
- DISTINCT dan xalos bo'ldi (umumiy retsepti yo'q, lekin ba'zida siz so'rovni 10-100 marta tezlashtirish orqali osongina qutulishingiz mumkin).
Masalan, etkazib berishning katta jadvalidan barcha drayverlarni tanlash uchun so'rov o'rniga (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDnisbatan kichik PERSON jadvaliga so'rov qildi
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Biz o'zaro bog'liq pastki so'rovdan foydalanganga o'xshaymiz, lekin u 10 martadan ko'proq tezlikni beradi.
- Ko'p hollarda, COUNT butunlay tark etildi va
- o'rniga
UPPER(s) LIKE JOHN%’foydalanish
s ILIKE “John%”
Har bir aniq so'rov ba'zan 3-1000 marta tezlashtirilgan. Ta'sirchan ishlashga qaramay, dastlab bizga 10 milodiy vaqt talab qiladigan, 3-yuzta eng og'ir so'rovlardan biri bo'lgan va umumiy ma'lumotlar bazasini yuklash vaqtining yuzdan bir foizini egallaydigan so'rovni optimallashtirishdan ma'no yo'qdek tuyuldi. Ammo bir xil turdagi so'rovlar guruhiga bir xil retseptni qo'llash orqali biz bir necha foizni qaytarib oldik. Yuzlab so'rovlarni qo'lda ko'rib chiqishga vaqt sarflamaslik uchun biz bir xil turdagi so'rovlarni topish uchun oddiy iboralardan foydalangan holda bir nechta oddiy skriptlarni yozdik. Natijada, so'rovlar guruhlarini avtomatik ravishda qidirish bizga kam harakat bilan ishlashimizni yanada yaxshilash imkonini berdi.
Natijada uch yildan buyon bir xil apparat ustida ishlayapmiz. O'rtacha kunlik yuk taxminan 30% ni tashkil qiladi, cho'qqilarda u 70% ga etadi. So'rovlar soni, shuningdek, foydalanuvchilar soni taxminan 10 barobar oshdi. Va bularning barchasi TOP-MEDIUM so'rovlarining bir xil guruhlarini doimiy monitoring qilish tufayli. TOP guruhida yangi so'rov paydo bo'lishi bilan biz uni darhol tahlil qilamiz va uni tezlashtirishga harakat qilamiz. Biz MEDIUM guruhini haftada bir marta so'rovlarni tahlil qilish skriptlari yordamida ko'rib chiqamiz. Agar biz qanday qilib optimallashtirishni allaqachon bilgan yangi so'rovlarga duch kelsak, ularni tezda o'zgartiramiz. Ba'zan biz bir vaqtning o'zida bir nechta so'rovlarga qo'llanilishi mumkin bo'lgan yangi optimallashtirish usullarini topamiz.
Bizning prognozlarimizga ko'ra, joriy server foydalanuvchilar sonining yana 3-5 barobar ko'payishiga bardosh beradi. To'g'ri, bizda yana bir eys bor - biz hali ham tavsiya qilinganidek SELECT so'rovlarini oynaga o'tkazmadik. Ammo biz buni ongli ravishda qilmaymiz, chunki biz "og'ir artilleriya" ni yoqishdan oldin "aqlli" optimallashtirish imkoniyatlarini to'liq tugatmoqchimiz.
Bajarilgan ishlarga tanqidiy qarash vertikal masshtabdan foydalanishni taklif qilishi mumkin. Mutaxassislarning vaqtini behuda sarflamasdan, yanada kuchliroq server sotib oling. Server unchalik qimmatga tushmasligi mumkin, ayniqsa vertikal o'lchov chegaralarini hali tugatmaganimiz uchun. Biroq, faqat so'rovlar soni 10 barobar oshdi. Bir necha yillar davomida tizimning funksionalligi oshdi va endi so'rovlarning ko'proq turlari mavjud. Keshlash tufayli mavjud funksiyalar kamroq so'rovlarda va samaraliroq so'rovlarda amalga oshiriladi. Bu haqiqiy tezlashtirish koeffitsientini olish uchun siz xavfsiz tarzda yana 5 ga ko'paytirishingiz mumkin degan ma'noni anglatadi. Shunday qilib, eng konservativ hisob-kitoblarga ko'ra, tezlashuv 50 marta yoki undan ko'p bo'lgan deb aytishimiz mumkin. Serverni vertikal ravishda aylantirish 50 baravar qimmatga tushadi. Ayniqsa, optimallashtirish amalga oshirilgandan so'ng, u doimo ishlaydi va ijaraga olingan server uchun hisob har oyda kelishini hisobga olsak.
Manba: www.habr.com