Kaip padidinti duomenų bazės užklausų skaičių 10 kartų neperėjus į produktyvesnį serverį ir išlaikyti sistemos funkcionalumą? Papasakosiu, kaip susidorojome su sumažėjusiu duomenų bazės našumu, kaip optimizavome SQL užklausas, kad aptarnautume kuo daugiau vartotojų ir nepadidintume skaičiavimo išteklių.
Teikiu verslo procesų valdymo paslaugą statybos įmonėse. Su mumis dirba apie 3 tūkst. Su mūsų sistema kasdien po 10-4 valandų dirba daugiau nei 10 tūkst. Jis sprendžia įvairias planavimo, pranešimo, įspėjimo, patvirtinimo problemas... Naudojame PostgreSQL 9.6. Duomenų bazėje turime apie 300 lentelių ir kasdien gaunama iki 200 milijonų užklausų (10 tūkst. skirtingų). Vidutiniškai turime 3-4 tūkstančius užklausų per sekundę, aktyviausiais momentais daugiau nei 10 tūkst. Dauguma užklausų yra OLAP. Yra daug mažiau papildymų, pakeitimų ir ištrynimų, o tai reiškia, kad OLTP apkrova yra palyginti nedidelė. Pateikiau visus šiuos skaičius, kad galėtumėte įvertinti mūsų projekto mastą ir suprasti, kokia naudinga mūsų patirtis gali būti jums.
Vaizdas vienas. Lyrinis
Pradėdami kūrimą tikrai negalvojome, kokia apkrova kris duomenų bazei ir ką darysime, jei serveris nustotų traukti. Kurdami duomenų bazę, laikėmės bendrų rekomendacijų ir stengėmės nešaudyti sau į koją, tačiau viršijome bendrus patarimus, pvz., „nenaudokite modelio“. mes neįėjome. Mes projektavome remdamiesi normalizavimo principais, vengdami duomenų pertekliaus ir nesirūpinome tam tikrų užklausų paspartinimu. Kai tik atvyko pirmieji vartotojai, susidūrėme su našumo problema. Kaip įprasta, buvome tam visiškai nepasiruošę. Pirmosios problemos pasirodė paprastos. Paprastai viskas buvo išspręsta pridedant naują indeksą. Tačiau atėjo laikas, kai paprasti pleistrai nustojo veikti. Suprasdami, kad mums trūksta patirties ir vis sunkiau suprasti, kas sukelia problemas, pasamdėme specialistus, kurie padėjo teisingai sukonfigūruoti serverį, prijungti stebėjimą ir parodė, kur ieškoti. .
Antras paveikslas. Statistiniai
Taigi per dieną turime apie 10 tūkstančių įvairių užklausų, kurios mūsų duomenų bazėje vykdomos. Iš šių 10 tūkstančių yra monstrų, kurie vykdomi 2-3 milijonus kartų, kurių vidutinis vykdymo laikas yra 0.1-0.3 ms, ir yra užklausų, kurių vidutinis vykdymo laikas yra 30 sekundžių, kurios iškviečiamos 100 kartų per dieną.
Nebuvo įmanoma optimizuoti visų 10 tūkstančių užklausų, todėl nusprendėme sugalvoti, kur nukreipti savo pastangas, kad duomenų bazės našumas būtų teisingas. Po kelių pakartojimų pradėjome skirstyti užklausas į tipus.
TOP užklausos
Tai yra sunkiausios užklausos, kurios užima daugiausiai laiko (bendras laikas). Tai yra užklausos, kurios yra arba labai dažnai iškviečiamos, arba užklausos, kurių vykdymas užtrunka labai ilgai (ilgos ir dažnos užklausos buvo optimizuotos pirmose kovos dėl greičio iteracijose). Dėl to serveris daugiausiai laiko praleidžia jų vykdymui. Be to, svarbu atskirti svarbiausias užklausas pagal bendrą vykdymo laiką ir atskirai pagal IO laiką. Tokių užklausų optimizavimo metodai šiek tiek skiriasi.
Įprasta visų įmonių praktika – dirbti su TOP užklausomis. Jų yra nedaug, optimizavus net vieną užklausą galima atlaisvinti 5-10% resursų. Tačiau projektui bręstant TOP užklausų optimizavimas tampa vis labiau nebanalia užduotimi. Visi paprasti metodai jau parengti, o „sunkiausia“ užklausa užima „tik“ 3–5% išteklių. Jei TOP užklausos iš viso užima mažiau nei 30–40% laiko, greičiausiai jau pasistengėte, kad jos veiktų greitai ir laikas pereiti prie kitos grupės užklausų optimizavimo.
Belieka atsakyti į klausimą, kiek populiariausių užklausų turėtų būti įtraukta į šią grupę. Dažniausiai imu bent 10, bet ne daugiau 20. Stengiuosi, kad TOP grupės pirmo ir paskutinio laikas skirtųsi ne daugiau 10 kartų. Tai yra, jei užklausos vykdymo laikas smarkiai sumažėja iš 1-os vietos į 10-ą, tada imu TOP-10, jei kritimas yra laipsniškesnis, padidinu grupės dydį iki 15 arba 20.
Viduriniai valstiečiai
Tai visos užklausos, kurios ateina iškart po TOP, išskyrus paskutinius 5-10 proc. Paprastai optimizuojant šias užklausas yra galimybė gerokai padidinti serverio našumą. Šios užklausos gali sverti iki 80 proc. Bet net jei jų dalis viršijo 50%, laikas į juos pažvelgti atidžiau.
Uodega
Kaip minėta, šios užklausos pateikiamos pabaigoje ir užtrunka 5–10 % laiko. Apie juos galite pamiršti tik nenaudodami automatinių užklausų analizės įrankių, tuomet jų optimizavimas taip pat gali būti pigus.
Kaip įvertinti kiekvieną grupę?
Aš naudoju SQL užklausą, kuri padeda atlikti tokį PostgreSQL įvertinimą (esu tikras, kad panašią užklausą galima parašyti daugeliui kitų DBVS)
SQL užklausa, skirta TOP-MEDIUM-TAIL grupių dydžiui įvertinti
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
Užklausos rezultatas yra trys stulpeliai, kurių kiekviename yra procentas, kiek laiko reikia apdoroti šios grupės užklausas. Užklausos viduje yra du skaičiai (mano atveju tai yra 20 ir 800), kurie atskiria vienos grupės užklausas nuo kitos.
Taip apytiksliai palyginamos užklausų dalys tuo metu, kai prasidėjo optimizavimo darbai, ir dabar.
Diagramoje matyti, kad TOP užklausų dalis smarkiai sumažėjo, tačiau išaugo „vidutiniai valstiečiai“.
Iš pradžių į TOP užklausas buvo įtrauktos akivaizdžios klaidos. Bėgant laikui vaikų ligos išnyko, TOP užklausų dalis mažėjo, vis labiau reikėjo stengtis paspartinti sunkius prašymus.
Norėdami gauti užklausų tekstą, naudojame šią užklausą
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
Čia pateikiamas dažniausiai naudojamų metodų, padėjusių mums pagreitinti TOP užklausas, sąrašas:
- Sistemos pertvarkymas, pavyzdžiui, pranešimų logikos pertvarkymas naudojant pranešimų tarpininką, o ne periodines duomenų bazės užklausas.
- Indeksų pridėjimas arba keitimas
- ORM užklausų perrašymas į gryną SQL
- Perrašoma tingi duomenų įkėlimo logika
- Talpykla per duomenų denormalizavimą. Pavyzdžiui, turime lentelę Pristatymas -> Sąskaita -> Prašymas -> Programa. Tai reiškia, kad kiekvienas pristatymas yra susietas su programa per kitas lenteles. Kad nebūtų susietos visos lentelės kiekvienoje užklausoje, mes dubliavome nuorodą į užklausą lentelėje Pristatymas.
- Statinių lentelių kaupimas talpykloje su žinynais ir retai keičiamos lentelės programos atmintyje.
Kartais pakeitimai prilygo įspūdingam pertvarkymui, tačiau jie suteikė 5–10% sistemos apkrovos ir buvo pateisinami. Bėgant laikui išmetimas vis mažėjo, reikėjo vis rimčiau perdaryti.
Tada atkreipėme dėmesį į antrąją prašymų grupę – viduriniųjų valstiečių grupę. Jame yra daug daugiau užklausų ir atrodė, kad visos grupės analizė užtruks daug laiko. Tačiau daugumą užklausų optimizuoti buvo labai paprasta, o daugelis problemų buvo kartojamos dešimtis kartų įvairiais variantais. Čia pateikiami kai kurių tipinių optimizacijų, kurias taikėme daugybei panašių užklausų, pavyzdžiai ir kiekviena optimizuotų užklausų grupė iškrovė duomenų bazę 3–5%.
- Užuot tikrinus, ar nėra įrašų, naudojant COUNT ir visos lentelės nuskaitymą, buvo pradėta naudoti EXISTS
- Atsikratė DISTINCT (bendro recepto nėra, bet kartais galite lengvai atsikratyti, pagreitindami užklausą 10-100 kartų).
Pavyzdžiui, vietoj užklausos pasirinkti visus tvarkykles iš didelės pristatymų lentelės (PRISTATYMAS)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDpateikė užklausą santykinai mažoje lentelėje 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)Atrodytų, kad naudojome koreliuojamą antrinę užklausą, tačiau ji pagreitina daugiau nei 10 kartų.
- Daugeliu atvejų COUNT buvo visiškai atsisakyta ir
- vietoj
UPPER(s) LIKE JOHN%’naudoti
s ILIKE “John%”
Kiekvienas konkretus prašymas kartais buvo pagreitintas 3–1000 kartų. Nepaisant įspūdingo našumo, iš pradžių mums atrodė, kad nėra prasmės optimizuoti užklausą, kuri užtrunka 10 ms, yra viena iš 3 šimto sunkiausių užklausų ir užima šimtąsias procentų bendros duomenų bazės įkėlimo laiko. Tačiau pritaikę tą patį receptą to paties tipo užklausų grupei, laimėjome kelis procentus. Kad nereikėtų gaišti laiko rankiniu būdu peržiūrint visas šimtus užklausų, parašėme kelis paprastus scenarijus, kurie naudojo reguliariąsias išraiškas to paties tipo užklausoms rasti. Dėl to automatiškai ieškodami užklausų grupėse galėjome toliau gerinti savo našumą įdėdami nedideles pastangas.
Dėl to jau trejus metus dirbame su ta pačia technine įranga. Vidutinis paros krūvis yra apie 30%, piko metu siekia 70%. Užklausų, kaip ir vartotojų, skaičius išaugo maždaug 10 kartų. Ir visa tai dėka nuolatinio tų pačių TOP-MEDIUM užklausų grupių stebėjimo. Kai tik TOP grupėje atsiranda nauja užklausa, iškart ją analizuojame ir stengiamės pagreitinti. MEDIUM grupę peržiūrime kartą per savaitę, naudodami užklausų analizės scenarijus. Jei susiduriame su naujomis užklausomis, kurias jau žinome, kaip optimizuoti, greitai jas keičiame. Kartais randame naujų optimizavimo metodų, kuriuos galima pritaikyti kelioms užklausoms vienu metu.
Mūsų prognozėmis, dabartinis serveris atlaikys vartotojų skaičiaus padidėjimą dar 3-5 kartus. Tiesa, turime dar vieną asą – vis dar neperkėlėme SELECT užklausų į veidrodį, kaip rekomenduojama. Bet mes to nedarome sąmoningai, nes norime iš pradžių visiškai išnaudoti „protingo“ optimizavimo galimybes prieš įjungiant „sunkiąją artileriją“.
Kritiškai pažvelgus į atliktą darbą, gali būti pasiūlyta naudoti vertikalųjį mastelį. Užuot gaišę specialistų laiką, pirkite galingesnį serverį. Serveris gali ne tiek daug kainuoti, juolab kad dar neišnaudojome vertikalaus mastelio ribų. Tačiau tik prašymų skaičius išaugo 10 kartų. Per kelerius metus sistemos funkcionalumas išaugo ir dabar atsiranda daugiau užklausų tipų. Funkcionalumas, kuris egzistavo dėl talpyklos saugojimo, atliekama mažiau užklausų, taip pat ir efektyvesnių užklausų. Tai reiškia, kad galite saugiai padauginti iš dar 5, kad gautumėte tikrąjį pagreičio koeficientą. Taigi, konservatyviausiais vertinimais, galime teigti, kad pagreitis buvo 50 ar daugiau kartų. Vertikalus serverio siūbavimas kainuotų 50 kartų daugiau. Ypač turint omenyje, kad atlikus optimizavimą jis veikia visą laiką, o sąskaita už nuomotą serverį ateina kas mėnesį.
Šaltinis: www.habr.com