Andmete päringute optimeerimine B2B teenuse näitel ehitajatele

Kuidas kasvatada andmebaasi päringute arvu kümme korda, mitte liikudes jõudsamale serverile ja säilitada süsteemi töövõime? Räägin, kuidas me võitlesime andmebaasi jõudluse languse vastu, kuidas optimeerisime SQL-päringuid, et teenindada võimalikult palju kasutajaid ja mitte suurendada arvutusressursside kulusid.

Ma teen teenuseid ehitusettevõtete äriprotsesside haldamiseks. Meiega töötab ligikaudu 3000 ettevõtet. Iga päev kasutab meie süsteemi üle 10 000 inimese 4-10 tundi. See lahendab erinevaid ülesandeid, sealhulgas planeerimine, teavitamine, hoiatamine, valideerimine jne. Me kasutame PostgreSQL 9.6. Andmebaasis on meil umbes 300 tabelit ja iga päev tuleb 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. Uute andmete lisamine, muudatused ja kustutamised on oluliselt vähem, seega OLTP koormus on suhteliselt väike. Kõiki neid numbreid olen välja toonud, et teil oleks võimalik hinnata meie projekti ulatust ning mõista, kui kasulik meie kogemus võib teile olla.

Esimene pilt. Lüüriiline

Kui me arenduse alustasime, ei mõelnud me eriti sellele, milline koormus langeb andmebaasile ja mida me teeme, kui server enam ei suuda. Andmebaasi projekteerimisel järgnesime üldistele soovitustele ja püüdsime mitte endale jalga tulistada, kuid edasi liikudes olid üldised nõuanded, nagu "ärge kasutage mustrit Entity Attribute Values Me ei olnudun. Me projekteerisime lähtudes normaliseerimise põhimõtetest, vältides andmete liigset kordumist ning ei muretsenud teatud päringute kiirendamise pärast. Kui esimesed kasutajad tulid, kohtasime jõudlusprobleeme. Nagu tavaliselt, olime täiesti selleks ette valmistamata. Esimesed probleemid olid lihtsad. Enamasti lahenesid asjad uue indeksi lisamisega. Kuid tuli hetk, mil lihtsad täiendused enam ei töötanud. Teades, et meie kogemused ei piisa ja meil on üha raskem mõista, mis probleemide põhjused on, palkasime spetsialiste, kes aitasid meil serveri korralikult seadistada, monitorimise ühendada ning näitasid, kuhu vaadata, et saada. statistika.

Teine pilt. Statistika

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

Kuna 10 tuhat päringut polnud võimalik optimeerida, otsustasime, kuhu suunata jõupingutused andmebaasi jõudluse tõstmiseks. Pärast mitmeid iteratsioone hakkasime päringud liigitama tüüpide järgi.

TOP päringud

Need on kõige raskemad päringud, mis võtavad kõige rohkem aega (kogu aeg). Need on päringud, mida kas kutsutakse väga sageli või päringud, mis vajavad väga pikka täitmist (pikad ja sagedased päringud on juba varasematel iteratsioonidel kiirusvõitluses optimeeritud). Kokku kulutab server nende täitmiseks kõige rohkem aega. Oluline on eristada top päringud üldise täitmise aja järgi ja eraldi IO aja järgi. Selliste päringute optimeerimise viisid on veidi erinevad.

Kõigi ettevõtete tavaline praktika on töötada TOP-päringutega. Need on vähe, ühe päringu optimeerimine võib vabastada 5-10% ressursse. Siiski, projekti „küpsemise” käigus muutub TOP-päringute optimeerimine järjest keerukamaks ülesandeks. Kõik lihtsad meetodid on juba katsetatud ja isegi kõige „raske” päring kasutab „ainult” 3-5% ressursse. Kui TOP-päringud kokku kulutavad vähem kui 30-40% ajast, siis olete tõenäoliselt juba pingutanud selle nimel, et need töötaksid kiiresti, ja nüüd on aeg liikuda järgmise grupi päringute optimeerimisele.
Jääb vastata küsimusele, kui palju kõrgeid päringuid sellesse gruppi kuulutada. Ma võtan tavaliselt mitte vähem kui 10, kuid mitte rohkem kui 20. Proovin, et esimese ja viimase päringu aega TOP-grupis erineks mitte rohkem kui 10 korda. St kui päringute töötlemise aeg langeb järsult 1. kohalt 10. kohale, siis 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 lähevad kohe TOP-i järel, välja arvatud viimased 5–10%. Just nende päringute optimeerimises peitub sageli võimalus serveri jõudlust märgatavalt tõsta. Need päringud võivad moodustada kuni 80%. Kuid isegi kui nende osakaal ületab 50%, on aeg neile tõsisemalt pilk heita.

Sabapäringud (tail)

Nagu öeldud, lähevad need päringud viimaste hulka ja nendele kulub 5–10% ajast. Nendest võib unustada ainult siis, kui te ei kasuta automaatseid analüüsivahendeid; siis võib nende optimeerimine samuti odavalt käia.

Kuidas hinnata iga gruppi?

Kasutangi SQL-päringut, mis aitab sellist hindamist teha PostgreSQL-is (olen kindel, et paljudele muudele andmebaasisüsteemidele on võimalik kirjutada sarnane päring).

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 tulemused - kolm veergu, millest igaüks sisaldab protsenti ajast, mis kulub selle grupi päringute töötlemiseks. Päringu sees on kaks numbrit (minu juhtumi järgi on need 20 ja 800), mis eraldavad ühe grupi päringud teistest.

Nii umbes jagunevad päringute osakaalid optimeerimise alguses ja praegu.

Diagrammist on näha, et TOP päringute osakaal on järsult vähenenud, kuid "keskmike" osakaal on suurenenud.
Alguses sattusid TOP päringutesse ilmsed vead. Aja jooksul lastehaigused kadusid, TOP päringute osakaal vähenes ja 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 nimekiri kõige sagedamini kasutatud meetoditest, mis aitasid meil TOP päringute kiirus tõsta:

• Süsteemi ümberkujundamine, näiteks teate loogika ülekandmine sõnumi vahemenetlusele, mitte perioodilised päringud andmebaasi.
• Indeksite lisamine või muutmine
• ORM päringute ümberkirjutamine puhtaks SQL-iks
• Andmete paindliku laadimise loogika ümberkirjutamine
• Andmete denormaliseerimise kaudu vahemällu salvestamine. Näiteks, meil on tabelite seos Tarne -> Arve -> Taotlus -> Soovitus. See tähendab, et iga tarne on seotud taotlusega läbi teiste tabelite. Et mitte siduda kõiki tabeleid igas päringus, oleme dubleerinud viite taotlusele Tarne tabelis.
• Staatiliste tabelite ja harva muutuvate tabelite vahemällu salvestamine programmi mälu.

Mõnikord tõid muudatused kaasa ulatusliku uuenduse, kuid andsid 5-10% süsteemi koormuse vähenemist ja olid õigustatud. Aja jooksul sai tulu üha väiksemaks ja uuendus muutus üha tõsiseks.

Siis pöörasime tähelepanu teisele päringute grupile – keskmike grupp. Selles on palju rohkem päringuid ja tundus, et kogu grupi analüüs võib võtta väga palju aega. Siiski osutus enamik päringutest optimeerimiseks väga lihtsaks ja paljud probleemid kordusid kümneid kordi erinevates variantides. Siin on mõned tüüpilised optimeerimised, mida rakendasime kümnetele sarnastele päringutele, ja iga optimeeritud päringute grupp vähendas andmebaasi koormust 3-5%.

• Kasutame EXISTS-i, mitte COUNT-i ja täielikku tabeli skaneerimist.
• Vabanesime DISTINCT-ist (ühtset retsepti pole, aga mõnikord on kerge sellest loobuda, kiirendades päringut 10–100 korda).

  Näiteks, kui soovime küsida kõiki juhte suurel kohaletoimetamiste tabelil (DELIVERY).

  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ä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)
  

  Tundub, et kasutasime korreleerivat alamküsitlust, kuid see annab üle 10-kordse kiiruskasvu.

• Paljuski loobusime COUNT-ist täielikult ja
  asendasime selle umbkaudse väärtuse arvutamisega.
• asetatakse

  UPPER(s) LIKE JOHN%’ 
  

  kasutame

  s ILIKE “John%”
  

Iga konkreetne päring suudeti mõnikord kiiruselt tõsta 3-1000 korda. Kuigi tulemused olid muljetavaldavad, tundus alguses, et pole mõtet optimeerida päringut, mis kestab 10 ms, kuulub kolmanda saja kõige keerukama päringu hulka ja üldise koormuse ajal veedab andmebaasis ainult sajandik protsenti. Rakendades sama retsepti rühmale sarnastele päringutele suutsime saavutada mitme protsendi võrra paremad tulemused. Aja säästmiseks kõigi sadade päringute käsitsi läbi vaatamist, kirjutasime mõned lihtsad skriptid, mis regulaaravalduste abil leidsid sarnased päringud. Lõpuks võimaldas automaatne päringute grupi otsing meil veelgi paremini oma tulemuslikkust tõsta, kulutades sellele tagasihoidlikult aega.

Oleme juba kolm aastat töötanud sama riistvaraga. Keskmine koormus on umbes 30%, tipukoormuse ajal ulatub see 70%ni. Küsimuste ja kasutajate arv on kasvanud umbes kümme korda. Ja see kõik on tänu pidevale nende TOP-MEDIUM päringugruppide jälgimisele. Nii pea, kui mõni uus päring ilmub TOP-gruppi, analüüsime seda kohe ja püüame kiirusest maksimumi võtta. MEDIUM-gruppi vaatame kord nädalas üle päringuanalüüsi skriptide abil. Kui leiame uusi päringuid, mida me juba teame, kuidas optimeerida, muudame neid kiiresti. Mõnikord avastame uusi optimeerimise viise, mida saab rakendada kohe mitmele päringule.

Meie prognooside kohaselt suudab praegune server taluda kasutajate arvu suurenemist veel 3-5 korda. Tõsi, meil on veel üks trump varrukas – me ei ole veel SELECT-päringuid peegeldusele viinud, nagu soovitatakse. Kuid me ei tee seda teadlikult, kuna soovime esmalt täielikult ära kasutada „nutika“ optimeerimise võimalusi, enne kui lülitame sisse „raskema suured relvad“.
Kriitiline ülevaade tehtud tööst võib viidata, et tasub kaaluda vertikaalset skaleerimist. Osta võimsam server, selle asemel et raisata spetsialistide aega. Server ei pruugi olla kallis, eriti kuna meie vertikaalse skaleerimise piirangud pole veel ammendunud. Siiski on kümme korda kasvanud vaid päringute arv. Aastate jooksul on süsteemi funktsionaalsus suurenenud ja nüüd on päringute variantide arv kasvanud. Funktsionaalsus, mis oli, saab täiendava efektiivsuse tänu vahemällu, kasutades vähem päringute arvu, kuid vähem tõhusate päringute asemel. Seega võib julgelt korrutada veel 5, et saada reaalne kiirusetegur. Seega võib kõige tagasihoidlikumate arvutuste kohaselt öelda, et kiirusetegur on vähemalt 50 korda. Serveri vertikaalne skaleerimine 50 korda oleks olnud kallim. Eriti arvestades, et kord tehtud optimeerimine töötab pidevalt, samas kui renditud serveri arve tuleb iga kuu.

Allikas: habr.com