Kuidas kasvatada 10 korda andmebaasi päringute arvu ilma produktiivsemasse serverisse kolimata ja säilitada süsteemi funktsionaalsus? Ma räägin teile, kuidas saime hakkama oma andmebaasi jõudluse langusega, kuidas optimeerisime SQL-päringuid, et teenindada võimalikult palju kasutajaid ja mitte suurendada arvutusressursside kulusid.
Teen teenust äriprotsesside juhtimiseks ehitusettevõtetes. Meiega töötab umbes 3 tuhat ettevõtet. Meie süsteemiga töötab iga päev 10-4 tundi üle 10 tuhande inimese. See lahendab erinevaid planeerimise, teavitamise, hoiatamise, valideerimise probleeme... Kasutame PostgreSQL 9.6. Meil on andmebaasis umbes 300 tabelit ja iga päev laekub kuni 200 miljonit päringut (10 tuhat erinevat). Keskmiselt on meil 3-4 tuhat päringut sekundis, kõige aktiivsematel hetkedel üle 10 tuhande päringu sekundis. Enamik päringuid on OLAP. Lisamisi, muudatusi ja kustutamisi on palju vähem, mis tähendab, et OLTP koormus on suhteliselt väike. Esitasin kõik need numbrid, et saaksite hinnata meie projekti ulatust ja mõista, kui kasulikud meie kogemused teile võivad olla.
Pilt üks. Lüüriline
Arendust alustades ei mõelnud me eriti sellele, milline koormus andmebaasile langeb ja mida me teeme, kui server lõpetaks tõmbamise. Andmebaasi kujundamisel järgisime üldisi soovitusi ja püüdsime mitte endale jalga tulistada, kuid läksime kaugemale üldistest nõuannetest nagu "ära kasuta mustrit me ei läinud sisse. Projekteerimisel lähtusime normaliseerimise põhimõtetest, vältides andmete liiasust ega hoolinud teatud päringute kiirendamisest. Niipea kui esimesed kasutajad saabusid, tekkis jõudlusprobleem. Nagu tavaliselt, olime me selleks täiesti ette valmistamata. Esimesed probleemid osutusid lihtsaks. Reeglina lahenes kõik uue indeksi lisamisega. Kuid saabus aeg, mil lihtsad plaastrid lakkasid töötamast. Mõistes, et meil napib kogemusi ja meil on järjest raskem aru saada, mis probleeme põhjustab, palkasime spetsialistid, kes aitasid meil serveri õigesti seadistada, jälgimise ühendada ja näitasid, kust otsida. .
Pilt kaks. Statistiline
Nii et meil on päevas umbes 10 tuhat erinevat päringut, mida meie andmebaasis täidetakse. Nendest 10 tuhandest on koletisi, mida täidetakse 2-3 miljonit korda keskmise täitmisajaga 0.1-0.3 ms, ja on päringuid, mille keskmine täitmisaeg on 30 sekundit, mida kutsutakse 100 korda päevas.
Kõiki 10 tuhat päringut ei olnud võimalik optimeerida, mistõttu otsustasime välja mõelda, kuhu andmebaasi jõudluse korrektseks parandamiseks oma jõupingutused suunata. Pärast mitut iteratsiooni hakkasime taotlusi tüüpidesse jagama.
TOP taotlused
Need on kõige raskemad päringud, mis võtavad kõige rohkem aega (koguaeg). Need on päringud, mida kutsutakse kas väga sageli või päringud, mille täitmine võtab väga kaua aega (pikad ja sagedased päringud optimeeriti kiirusevõitluse esimestes iteratsioonides). Selle tulemusena kulutab server nende täitmisele kõige rohkem aega. Lisaks on oluline eraldada peamised päringud kogu täitmisaja ja eraldi IO aja järgi. Selliste päringute optimeerimise meetodid on veidi erinevad.
Kõigi ettevõtete tavapärane praktika on töötada TOP päringutega. Neid on vähe, isegi ühe päringu optimeerimine võib vabastada 5-10% ressurssidest. Kuid projekti küpsedes muutub TOP-i päringute optimeerimine üha vähem triviaalseks ülesandeks. Kõik lihtsad meetodid on juba välja töötatud ja kõige "raskem" taotlus võtab "ainult" 3-5% ressurssidest. Kui TOP-päringutele kulub kokku vähem kui 30-40% ajast, siis suure tõenäosusega oled nende kiireks toimimiseks juba vaeva näinud ja on aeg liikuda järgmise grupi päringute optimeerimise juurde.
Jääb vastata küsimusele, mitu populaarseimat päringut tuleks sellesse rühma lisada. Tavaliselt võtan vähemalt 10, aga mitte rohkem kui 20. Püüan jälgida, et TOP-grupi esimese ja viimase aeg ei erineks rohkem kui 10 korda. See tähendab, et kui päringu täitmise aeg langeb järsult 1. kohalt 10. kohale, siis võtan TOP-10, kui langus on järkjärgulisem, siis suurendan rühma suurust 15-ni või 20-ni.
Kesktalupojad
Need on kõik taotlused, mis tulevad kohe pärast TOP-i, välja arvatud viimased 5-10%. Tavaliselt peitub nende päringute optimeerimises võimalus serveri jõudlust oluliselt suurendada. Need taotlused võivad kaaluda kuni 80%. Kuid isegi kui nende osakaal on ületanud 50%, on aeg neid hoolikamalt vaadata.
Saba
Nagu mainitud, tulevad need päringud lõpus ja võtavad 5-10% ajast. Need võid unustada vaid siis, kui ei kasuta automaatseid päringuanalüüsi tööriistu, siis võib nende optimeerimine olla ka odav.
Kuidas iga rühma hinnata?
Kasutan SQL-päringut, mis aitab PostgreSQL-i jaoks sellist hinnangut anda (olen kindel, et sarnase päringu saab kirjutada ka paljude teiste DBMS-ide jaoks)
SQL-päring TOP-MEDIUM-TAIL rühmade suuruse hindamiseks
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
Päringu tulemuseks on kolm veergu, millest igaüks sisaldab protsentuaalselt aega, mis kulub selle rühma päringute töötlemiseks. Päringu sees on kaks numbrit (minu puhul on see 20 ja 800), mis eraldavad ühe rühma päringud teisest.
Nii võrreldakse taotluste osakaalu umbkaudu optimeerimistööde alguse ajal ja praegu.
Diagramm näitab, et TOP-i päringute osakaal on järsult vähenenud, kuid suurenenud on “kesktalupojad”.
Algul olid TOP-i taotluste hulgas räiged vead. Aja jooksul kadusid lastehaigused, vähenes TOP-i pöördumiste osakaal ning üha enam tuli vaeva näha raskete pöördumiste kiirendamisega.
Taotluste teksti saamiseks kasutame järgmist päringut
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
Siin on nimekiri kõige sagedamini kasutatavatest tehnikatest, mis aitasid meil kiirendada TOP-päringuid:
- Süsteemi ümberkujundamine, näiteks teavitusloogika ümbertöötamine andmebaasi perioodiliste päringute asemel sõnumivahendaja abil
- Indeksite lisamine või muutmine
- ORM-i päringute ümberkirjutamine puhtaks SQL-iks
- Laisa andmete laadimise loogika ümberkirjutamine
- Vahemälu andmete denormaliseerimise kaudu. Meil on näiteks tabeliühendus Kohaletoimetamine -> Arve -> Taotlus -> Taotlus. See tähendab, et iga tarne seostatakse rakendusega teiste tabelite kaudu. Selleks, et mitte linkida igas päringus kõiki tabeleid, dubleerisime päringu lingi tabelis Tarne.
- Staatiliste tabelite vahemällu salvestamine koos teatmeteostega ja harva vahetatavad tabelid programmimälus.
Mõnikord ulatusid muudatused muljetavaldava ümberkujunduseni, kuid need andsid 5–10% süsteemi koormusest ja olid õigustatud. Aja jooksul jäi heitgaas aina väiksemaks ja vaja oli aina tõsisemat ümbertegemist.
Seejärel pöörasime tähelepanu teisele taotluste rühmale - keskmiste talupoegade rühmale. Selles on palju rohkem päringuid ja tundus, et kogu grupi analüüsimine võtab palju aega. Enamik päringuid osutus aga väga lihtsalt optimeeritavaks ja paljusid probleeme korrati erinevates variatsioonides kümneid kordi. Siin on näited tüüpilistest optimeerimistest, mida rakendasime kümnetele sarnastele päringutele ja iga optimeeritud päringute rühm tühjendas andmebaasi 3–5%.
- Selle asemel, et kontrollida kirjete olemasolu COUNT ja täieliku tabeli skannimise abil, hakati kasutama EXISTS
- Sai lahti DISTINCTist (üldretsepti pole, kuid mõnikord saab sellest hõlpsasti lahti, kiirendades päringu esitamist 10-100 korda).
Näiteks päringu asemel, et valida suurest tarnete tabelist kõik draiverid (TOETUS)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDtegi päringu suhteliselt väikesele tabelile 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)Näib, et kasutasime korrelatsiooni alampäringut, kuid see kiirendab rohkem kui 10 korda.
- Paljudel juhtudel jäeti COUNT üldse ära ja
- asemel
UPPER(s) LIKE JOHN%’kasutama
s ILIKE “John%”
Iga konkreetset päringut kiirendati mõnikord 3-1000 korda. Vaatamata muljetavaldavale jõudlusele tundus meile alguses, et pole mõtet optimeerida päringut, mille täitmiseks kulub 10 ms, mis on üks 3. sajast kõige raskemast päringust ja võtab kogu andmebaasi laadimisajast sajandikprotsenti. Kuid rakendades sama retsepti sama tüüpi päringute rühmale, võitsime mõne protsendi tagasi. Et mitte raisata aega kõigi sadade päringute käsitsi ülevaatamisele, kirjutasime mitu lihtsat skripti, mis kasutasid sama tüüpi päringute leidmiseks regulaaravaldisi. Selle tulemusel võimaldas päringurühmade automaatne otsimine meil oma toimivust mõõduka vaevaga veelgi parandada.
Selle tulemusena oleme juba kolm aastat töötanud sama riistvara kallal. Keskmine päevakoormus on umbes 30%, tipptundidel ulatub see 70% -ni. Päringute arv ja ka kasutajate arv on kasvanud ligikaudu 10 korda. Ja seda kõike tänu nende samade TOP-MEDIUM päringurühmade pidevale jälgimisele. Niipea kui TOP-i gruppi ilmub uus päring, analüüsime seda koheselt ja proovime seda kiirendada. Vaatame rühma MEDIUM üle kord nädalas, kasutades päringuanalüüsi skripte. Kui puutume kokku uute päringutega, mida juba oskame optimeerida, muudame neid kiiresti. Mõnikord leiame uusi optimeerimismeetodeid, mida saab rakendada mitmele päringule korraga.
Meie prognooside kohaselt peab praegune server vastu kasutajate arvu kasvule veel 3-5 korda. Tõsi, meil on veel üks äss varrukas – me pole ikka veel SELECT päringuid peeglisse üle kandnud, nagu soovitatakse. Kuid me ei tee seda teadlikult, sest tahame enne "raskekahurväe" sisselülitamist "targa" optimeerimise võimalused täielikult ammendada.
Tehtud töö kriitiline pilk võib soovitada kasutada vertikaalset skaleerimist. Spetsialistide aega raiskamise asemel osta võimsam server. Server ei pruugi nii palju maksta, seda enam, et me pole veel vertikaalse skaleerimise piire ammendanud. Kuid ainult taotluste arv kasvas 10 korda. Mitme aasta jooksul on süsteemi funktsionaalsus suurenenud ja nüüd on päringuid rohkem. Tänu vahemällu salvestamisele täidetakse olemasolevaid funktsioone vähemate päringute ja tõhusamate taotlustega. See tähendab, et saate tõelise kiirenduskoefitsiendi saamiseks ohutult korrutada veel 5-ga. Seega võib kõige konservatiivsemate hinnangute kohaselt öelda, et kiirendus oli 50 korda või rohkem. Serveri vertikaalne kõikumine maksaks 50 korda rohkem. Eriti kui arvestada, et kui optimeerimine on tehtud, siis töötab see kogu aeg ja arve renditud serveri eest tuleb iga kuu.
Allikas: www.habr.com