Andmete baasi päringute optimeerimine B2B teenuse näitel ehitajatele

Kuidas kasvatada B2B päringute arvu kümme korda, kolimata jõudsamale serverile ja säilitades süsteemi töövõime? Räägin, kuidas me võitlesime andmebaasi jõudluse langusega, kuidas me optimeerisime SQL päringud nii, et teenindada võimalikult palju kasutajaid ja mitte suurendada kulutusi arvutusressurssidele.

Tegelen teenuse loomisega äriprotsesside haldamiseks ehitusettevõtetes. Meiega töötab umbes 3000 ettevõtet. Üle 10 000 inimese töötab igapäevaselt meie süsteemis 4–10 tundi. See lahendab mitmesuguseid planeerimise, teavitamise, hoiatamise ja valideerimise ülesandeid... Me kasutame PostgreSQL 9.6. Meie andmebaasis on umbes 300 tabelit ja iga päev laekub sinna kuni 200 miljonit päringut (10 000 erinevat). Keskmiselt on meil 3–4 tuhat päringut sekundis, kõige aktiivsematel hetkedel üle 10 000 päringu sekundis. Suur osa päringutest on OLAP. Lisamised, muutmised ja kustutused on oluliselt vähem, seega OLTP koormus on suhteliselt väike. Kõiki neid numbreid tõin välja, et saaksite hinnata meie projekti ulatust ja mõista, kui väärtuslik meie kogemus teie jaoks võiks olla.

Esimene pilt. Lääneline

Kui me arendust alustasime, ei mõelnud me eriti sellele, milline koormus langeb andmebaasile ja mida me teeme, kui server enam ei jaksaks. Andmebaasi projekteerimisel järgnesime üldistele soovitustele ja püüdsime mitte endale jalga tulistada, kuid ei läinud kaugemale üldistest nõuannetest nagu "ärge kasutage mustrit Entity Attribute Values ," millega me ei olnud tuttavad. Projekteerisime normeerimise põhimõtetest lähtuvalt, vältides andmete liigset kopeerimist, ja ei tundnud muret erinevate päringute kiirususe pärast. Kui esimesed kasutajad ilmusid, seisime silmitsi jõudlusprobleemidega. Nagu tavaliselt, olime selleks täiesti ette valmistamata. Esimesed probleemid olid lihtsad. Kõik lahendati tavaliselt uue indeksi lisamisega. Kuid jõudis hetk, mil lihtsad lahendused enam ei töötanud. Teades, et meie kogemus ei ole piisav ja meil on üha keerulisem aru saada, mis probleemide põhjuseks on, palkasime spetsialiste, kes aitasid meil serverit õigesti seadistada, ühendada jälgimisega ja näitasid meile, kuhu vaadata, et saada statistikat.

Teine pilt. Statistiline

Nii et meil on umbes 10 000 erinevat päringut, mis täidetakse meie andmebaasis ööpäevas. Nendest 10 000 on koletised, mis täidetakse 2-3 miljonit korda keskmise täitmisajaga 0,1-0,3 ms, ja on päringud, mille keskmine täitmise aeg on 30 sekundit, mida kutsutakse 100 korda päevas.

Kuna ei olnud võimalik optimeerida kõiki 10 000 päringut, otsustasime mõista, kuhu suunata oma jõupingutusi, et tõsta andmebaasi jõudlust õigesti. Pärast mitu iteratsiooni hakkasime päringud liigitama tüüpideks.

TOP päringud

Need on kõige raskemad päringud, mis võtavad kõige rohkem aega (kokku aega). Need on päringud, mida kas kutsutakse väga sageli või päringud, mis täidetakse väga kaua (pikad ja sagedased päringud on optimeeritud juba varasemates iteratsioonides kiirusvõitluses). Lõppkokkuvõttes kulutab server nende täitmiseks kõige rohkem aega. Samuti on oluline eristada tipupäringud üldise täitmise ajaga ja eraldi IO ajaga. Nende päringute optimeerimise viisid on veidi erinevad.

Tavaline praktika kõigis ettevõtetes on töötada TOP päringutega. Neid on vähe, ühegi päringu optimeerimine võib vabastada 5-10% ressursse. Kuid projekti „kasvades” muutub TOP päringute optimeerimine üha keerulisemaks. Kõik lihtsad meetodid on juba rakendatud ning isegi kõige „raske” päring võtab „vaid” 3-5% ressursse. Kui TOP päringud kokku võtavad alla 30-40% ajast, siis tõenäoliselt olete juba teinud jõupingutusi, et need töötaksid kiirelt, ja on aeg liikuda järgmise grupi päringute optimeerimise juurde.
Jääb vastata küsimusele, kui palju ülemisi päringuid sellesse gruppi lisada. Ma tavaliselt võtaksin mitte vähem kui 10, kuid mitte rohkem kui 20. Püüan tagada, et TOP grupi esimese ja viimase täitmise aeg ei erine rohkem kui 10 korda. See tähendab, et kui päringute täitmise aeg langeb järsult 1. kohalt 10. kohale, võtan TOP-10, kui langus on sujuvam, siis suurendan grupi suurust 15 või 20-ni.

Keskmikud (medium)

Need on kõik päringud, mis tulevad kohe pärast TOP-i, välja arvatud viimased 5-10%. Tavalise optimeerimise puhul seisneb just nende päringute juures võimalus oluliselt tõsta serveri jõudlust. Need päringud võivad „kaaluda” kuni 80%. Kuid isegi kui nende osakaal ületab 50%, on aeg vaadata neile lähemalt.

Saba (tail)

Nagu öeldud, tulevad need päringud lõpus ja nendele kulub 5-10% ajast. Neist võib unustada, kui te ei kasuta automaatseid analüüsivahendeid, siis võib nende optimeerimine samuti olla odav.

Kuidas hinnata iga gruppi?

Kasutame SQL-päringut, mis aitab sellist hinnaandmist teha PostgreSQL jaoks (olen kindel, et paljude teiste andmebaasisüsteemide jaoks saab kirjutada sarnase päringu).

SQL-päring TOP-MEDIUM-TAIL gruppide 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 AND 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 tulemus – kolm veergu, millest igaüks sisaldab protsent aega, mis kulub selle grupi päringute töötlemiseks. Päringu sees on kaks numbrit (minu puhul need on 20 ja 800), mis eraldavad grupi ühte tüüpi päringud teistest.

Nii seondub päringute osakaal optimeerimise algusaegadel ja praegu.

Diagrammist on näha, et TOP päringute osakaal on järsult vähenenud, aga “keskmike” osakaal on suurenenud.
Alguses tõid TOP päringud sisse ilmseid vigu. Aja jooksul kadusid lapsehaigused, TOP päringute osakaal vähenes, tuli pingutada üha rohkem, et aeglaseid päringuid kiirendada.

Päringute tekstide 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 AND rn  800  -- TAIL

Siin on loetelu kõige sagedamini kasutatud meetoditest, mis aitasid meil kiirendada TOP päringuid:

• Süsteemi ümberkujundamine, näiteks teate loogika ümbersuunamine sõnumite vahendajale perioodiliste päringute asemel andmebaasi.
• Indeksite lisamine või muutmine.
• ORM päringute ümberkirjutamine puhtaks SQL-iks.
• Laise andmete laadimise loogika ümberkirjutamine.
• Küpsetamine läbi andmete denormaliseerimise. Näiteks on meil seos tabelite Transport -> Arve -> Päring -> Taotlus. Iga transport on seotud taotlusega teiste tabelite kaudu. Et mitte siduda iga päringuga kõiki tabeleid, kopeerisime viite taotlusele tabelisse Transport.
• Kohandatud staatiliste tabelite vahemälu, kus on viidatud ja harva muudetud tabelid programmi mälus.

Mõnikord tõid muudatused kaasa suure ümberkujundamise, kuid võimaldasid süsteemi koormuse vähendamist 5-10% ja olid seega õigustatud. Aja jooksul tõusis väljund järjest vähemaks, samas kui nõudmine tõsise ümberkujundamise järele kasvas.

Siis pöörasime tähelepanu teisele päringute rühmale – keskmike rühmale. Selles oli palju rohkem päringuid ja tundus, et terve grupi analüüsimine võtab kaua aega. Kuid enamik päringutest osutusid optimeerimiseks väga lihtsateks, ja paljud probleemid kordusid kümnete erinevate variatsioonide kaudu. Siin on mõned tüüpilised optimeerimised, mida kasutasime kümnete sarnaste päringute jaoks, ja iga optimeeritud päringute grupp vähendas andmebaasi koormust 3-5%.

• COUNT abil salvestuste olemasolu kontrollimise asemel hakkasime kasutama EXISTS.
• Vabandasime DISTINCT'i (üldreeglit ei ole, kuid mõnikord on võimalik sellest hõlpsasti vabaneda, kiirendades päringut 10-100 korda).

  Näiteks, suurte kohaletoimetamise tabelite (DELIVERY) põhjal kõikide juhtide valimise päringu asemel.

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  tegime päringu suhteliselt väikese PERSON tabeli põhjal.

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Tundub, et kasutasime seotud alam-päringut, kuid see kiirus tõusis rohkem kui 10 korda.

• Paljude juhtumite korral loobusime üldse COUNT-st ja
  asendasime selle ligikaudse väärtuse arvutamisega.
• asemel

  UPPER(s) LIKE JOHN%’ 
  

  kasutame

  s ILIKE “John%”
  

Iga konkreetset päringut õnnestus mõnikord kiirendada 3-1000 korda. Hoolimata muljetavaldavatest tulemustest arvasime alguses, et päringu optimeerimine, mis kestab 10 ms, ei ole mõttetu, olles kolmesaja kõige raskema päringu seas ja andmebaasi koormuse üldises ajas kulutades sajandik protsenti. Kuid sama retsepti rakendamine sarnaste päringute rühmale tõi meile tagasi mitu protsenti. Aja raiskamise vältimiseks kogu sadu päringute käsitsi ülevaatamiseks kirjutasime välja mõned lihtsad skriptid, mis regulaarsete avaldiste abil leidsid sarnased päringud. Lõppkokkuvõttes võimaldas automaatne päringute rühmade leidmine meil veelgi parandada oma efektiivsust, kulutades tagasihoidlikke jõupingutusi.

Oleme juba kolm aastat töötanud sama riistvaraga. Keskmine koormus on umbes 30%, tipphetkel jõuab see kuni 70%. Taotluste arvu ja kasutajate arv on kasvanud umbes kümme korda. Ja see kõik on võimalik pideva jälgimise tõttu nende TOP-MEDIUM päringute gruppide suhtes. Niipea, kui mõni uus päring ilmub TOP gruppi, analüüsime seda kohe ja üritame kiirusest maksimum võtta. MEDIUM gruppi vaatame kord nädalas päringute analüüsi skriptide abil. Kui leiame uusi päringuid, mille optimeerimise viise juba teame, muudetakse neid kiiresti. Mõnikord avastame uusi optimeerimise meetodeid, mida saab koheselt rakendada mitmele päringule.

Meie prognooside kohaselt talub praegune server kasutajate arvu kasvu veel 3-5 korda. Tõsi, meil on veel üks trump varrukas—me ei ole veel SELECT päringute peegeldust soovituslikult ümber lülitanud. Kuid me teeme seda teadlikult, kuna tahame esmalt täielikult ära kasutada "nutika" optimeerimise võimalusi, enne kui lülitame sisse "raskema suurtüki".
Kriitiline vaade tehtud tööle võib vihjata, et tasub kaaluda vertikaalset skaleerimist. Osta võimsam server, selle asemel et spetsialistide aega raisata. Server ei pruugi olla nii kallis, arvestades, et meie vertikaalse skaleerimise piirangud ei ole veel ammendatud. Kuigi päringute arv on kasvanud kümme korda. Aastate jooksul on süsteemi funktsionaalsus suurenenud ja praegu on erinevaid päringute tüüpe rohkem. Kõik, mis oli olemas, täitub vähemate, kuid tõhusamate päringute kaudu tänu sisseseadmiseks. Seega, et saada reaalseid kiirusetegureid, võib julgelt korrutada veel viiega. Nii midagi vähem ametlikku, võib öelda, et kiirus on suurenenud 50 ja rohkem korda. Serveri vertikaalne tõstmine 50 korda maksaks rohkem. Eriti arvestades, et üks kord teostatud optimeerimine töötab kogu aeg, samas kui renditud serveri arve tuleb igakuiselt.

Allikas: habr.com