L-ottimizzazzjoni tal-mistoqsijiet tad-database billi tuża l-eżempju ta’ servizz B2B għall-bennejja

Kif tikber 10 darbiet in-numru ta 'mistoqsijiet fid-database mingħajr ma tiċċaqlaq għal server aktar produttiv u żżomm il-funzjonalità tas-sistema? Jien ngħidlek kif ittrattajna t-tnaqqis fil-prestazzjoni tad-database tagħna, kif ottimizzajna l-mistoqsijiet SQL biex naqdu kemm jista 'jkun utenti u ma nżidux l-ispiża tar-riżorsi tal-kompjuter.

Nagħmel servizz għall-ġestjoni tal-proċessi tan-negozju f'kumpaniji tal-kostruzzjoni. Madwar 3 elf kumpanija jaħdmu magħna. Aktar minn 10 elf ruħ jaħdmu mas-sistema tagħna kuljum għal 4-10 sigħat. Issolvi diversi problemi ta’ ppjanar, notifika, twissija, validazzjoni... Aħna nużaw PostgreSQL 9.6. Għandna madwar 300 tabella fid-database u sa 200 miljun mistoqsija (10 elf waħda differenti) jiġu riċevuti kuljum. Bħala medja għandna 3-4 elf talba kull sekonda, fl-aktar mumenti attivi aktar minn 10 elf talba kull sekonda. Ħafna mill-mistoqsijiet huma OLAP. Hemm ħafna inqas żidiet, modifiki u tħassir, li jfisser li t-tagħbija OLTP hija relattivament ħafifa. Jien ipprovdejt dawn in-numri kollha sabiex tkun tista’ tevalwa l-iskala tal-proġett tagħna u tifhem kemm l-esperjenza tagħna tista’ tkun utli għalik.

Stampa waħda. Lirika

Meta bdejna l-iżvilupp, aħna ma tantx naħsbu dwar x'tip ta 'tagħbija taqa' fuq id-database u x'se nagħmlu jekk is-server ma jibqax jiġbdu. Meta fassal id-database, segwejna rakkomandazzjonijiet ġenerali u ppruvajna ma nisparawx lilna nfusna fis-sieq, iżda morna lil hinn minn pariri ġenerali bħal "tużax il-mudell Valuri tal-Attribut tal-Entità ma dħalniex. Iddisinjajna bbażati fuq il-prinċipji tan-normalizzazzjoni, nevitaw ir-redundancy tad-dejta u ma ħadna ħsieb li nħaffu ċerti mistoqsijiet. Hekk kif waslu l-ewwel utenti, iltqajna ma 'problema ta' prestazzjoni. Bħas-soltu, konna kompletament mhux ippreparati għal dan. L-ewwel problemi rriżultaw li kienu sempliċi. Bħala regola, kollox ġie solvut biż-żieda ta 'indiċi ġdid. Iżda wasal żmien meta l-irqajja sempliċi waqfu jaħdmu. Billi rrealizzajna li aħna neqsin mill-esperjenza u qed isir dejjem aktar diffiċli għalina li nifhmu x’qed jikkawża l-problemi, impjegajna speċjalisti li għenuna nwaqqfu s-server b’mod korrett, nikkonnettjaw il-monitoraġġ, u wrewna fejn għandna nfittxu biex niksbu statistika.

Stampa tnejn. Statistika

Allura għandna madwar 10 elf mistoqsija differenti li huma esegwiti fuq id-database tagħna kuljum. Minn dawn l-10 elf, hemm monsters li huma esegwiti 2-3 miljun darba b'ħin medju ta 'eżekuzzjoni ta' 0.1-0.3 ms, u hemm mistoqsijiet b'ħin medju ta 'eżekuzzjoni ta' 30 sekonda li jissejħu 100 darba kuljum.

Ma kienx possibbli li jiġu ottimizzati l-10 elf mistoqsija kollha, għalhekk iddeċidejna li nifhmu fejn nidderieġu l-isforzi tagħna sabiex intejbu l-prestazzjoni tad-database b'mod korrett. Wara diversi iterazzjonijiet, bdejna naqsmu t-talbiet f'tipi.

TOP talbiet

Dawn huma l-itqal mistoqsijiet li jieħdu l-aktar ħin (ħin totali). Dawn huma mistoqsijiet li jew jissejħu spiss ħafna jew mistoqsijiet li jieħdu żmien twil ħafna biex jitwettqu (il-mistoqsijiet twal u frekwenti ġew ottimizzati fl-ewwel iterazzjonijiet tal-ġlieda għall-veloċità). Bħala riżultat, is-server iqatta 'l-aktar ħin fuq l-eżekuzzjoni tagħhom. Barra minn hekk, huwa importanti li t-talbiet ewlenin jiġu sseparati bil-ħin totali tal-eżekuzzjoni u separatament skont il-ħin tal-IO. Il-metodi biex jiġu ottimizzati tali mistoqsijiet huma kemmxejn differenti.

Il-prattika tas-soltu tal-kumpaniji kollha hija li taħdem ma 'talbiet TOP. Hemm ftit minnhom; l-ottimizzazzjoni ta' anke mistoqsija waħda tista' tillibera 5-10% tar-riżorsi. Madankollu, hekk kif il-proġett jimmatura, l-ottimizzazzjoni tal-mistoqsijiet TOP isir kompitu dejjem aktar mhux trivjali. Il-metodi sempliċi kollha diġà ġew maħduma, u l-aktar talba "tqal" tieħu "biss" 3-5% tar-riżorsi. Jekk il-mistoqsijiet TOP b'kollox jieħdu inqas minn 30-40% tal-ħin, allura x'aktarx diġà għamilt sforzi biex tagħmilhom jaħdmu malajr u wasal iż-żmien li tgħaddi għall-ottimizzazzjoni tal-mistoqsijiet mill-grupp li jmiss.
Fadal biex tingħata risposta għall-mistoqsija dwar kemm għandhom jiġu inklużi mistoqsijiet ewlenin f'dan il-grupp. Normalment nieħu mill-inqas 10, iżda mhux aktar minn 20. Nipprova niżgura li l-ħin tal-ewwel u tal-aħħar fil-grupp TOP ivarja b'mhux aktar minn 10 darbiet. Jiġifieri, jekk il-ħin tal-eżekuzzjoni tal-mistoqsija jonqos drastikament mill-1 post għall-10, allura nieħu TOP-10, jekk il-waqgħa hija aktar gradwali, allura nżid id-daqs tal-grupp għal 15 jew 20.

bdiewa tan-nofs

Dawn huma kollha talbiet li jiġu immedjatament wara TOP, bl-eċċezzjoni tal-aħħar 5-10%. Normalment, fl-ottimizzazzjoni ta 'dawn il-mistoqsijiet tinsab l-opportunità li tiżdied ħafna l-prestazzjoni tas-server. Dawn it-talbiet jistgħu jiżnu sa 80%. Imma anke jekk is-sehem tagħhom qabeż il-50%, allura wasal iż-żmien li nħarsu lejhom aktar bir-reqqa.

Denb

Kif issemma, dawn il-mistoqsijiet jiġu fl-aħħar u jieħdu 5-10% tal-ħin. Tista 'tinsa dwarhom biss jekk ma tużax għodod awtomatiċi ta' analiżi tal-mistoqsijiet, allura l-ottimizzazzjoni tagħhom tista 'wkoll tkun irħisa.

Kif tevalwa kull grupp?

Jiena nuża mistoqsija SQL li tgħin biex tagħmel valutazzjoni bħal din għal PostgreSQL (ninsab ċert li mistoqsija simili tista' tinkiteb għal ħafna DBMSs oħra)

Mistoqsija SQL biex jiġi stmat id-daqs tal-gruppi TOP-MEDIUM-TAIL

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

Ir-riżultat tal-mistoqsija huwa tliet kolonni, li kull waħda fiha l-perċentwal ta 'żmien li tieħu biex tipproċessa mistoqsijiet minn dan il-grupp. Ġewwa t-talba hemm żewġ numri (fil-każ tiegħi huwa 20 u 800) li jifred it-talbiet minn grupp minn ieħor.

Dan huwa kif l-ishma tat-talbiet iqabblu bejn wieħed u ieħor fiż-żmien li x-xogħol ta 'ottimizzazzjoni beda u issa.

Id-dijagramma turi li s-sehem tat-talbiet TOP naqas drastikament, iżda l-"bdiewa tan-nofs" żdiedu.
Għall-ewwel, it-talbiet TOP kienu jinkludu blunders sfaċċati. Maż-żmien, il-mard tat-tfulija sparixxa, is-sehem tat-talbiet TOP naqas, u kellhom isiru aktar u aktar sforzi biex jitħaffu t-talbiet diffiċli.

Biex tikseb it-test tat-talbiet nużaw it-talba li ġejja

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

Hawnhekk hawn lista tat-tekniki l-aktar użati komunement li għenuna nħaffu l-mistoqsijiet TOP:

• Disinn mill-ġdid tas-sistema, pereżempju, ħidma mill-ġdid tal-loġika tan-notifika billi tuża sensar tal-messaġġi minflok mistoqsijiet perjodiċi għad-database
• Żieda jew tibdil ta' indiċi
• Kitba mill-ġdid ta' mistoqsijiet ORM għal SQL pur
• Kitba mill-ġdid tal-loġika għażżien tat-tagħbija tad-dejta
• Caching permezz tad-denormalizzazzjoni tad-dejta. Per eżempju, għandna konnessjoni tabella Kunsinna -> Fattura -> Talba -> Applikazzjoni. Jiġifieri, kull konsenja hija assoċjata ma 'applikazzjoni permezz ta' tabelli oħra. Sabiex ma torbotx it-tabelli kollha f'kull talba, aħna dduplipjajna l-link għat-talba fit-tabella Kunsinna.
• Caching tabelli statiċi bi kotba ta 'referenza u tabelli rarament jinbidlu fil-memorja tal-programm.

Xi drabi l-bidliet ammontaw għal disinn mill-ġdid impressjonanti, iżda pprovdew 5-10% tat-tagħbija tas-sistema u kienu ġġustifikati. Maż-żmien, l-exhaust sar iżgħar u iżgħar, u kien meħtieġ disinn mill-ġdid aktar u aktar serju.

Imbagħad dawwarna l-attenzjoni lejn it-tieni grupp ta 'talbiet - il-grupp ta' bdiewa tan-nofs. Hemm ħafna aktar mistoqsijiet fiha u deher li kien se jieħu ħafna ħin biex tanalizza l-grupp kollu. Madankollu, il-biċċa l-kbira tal-mistoqsijiet irriżultaw li kienu sempliċi ħafna biex jiġu ottimizzati, u ħafna problemi ġew ripetuti għexieren ta 'drabi f'varjazzjonijiet differenti. Hawn huma eżempji ta 'xi ottimizzazzjonijiet tipiċi li applikajna għal għexieren ta' mistoqsijiet simili u kull grupp ta 'mistoqsijiet ottimizzati ħatt id-database bi 3-5%.

• Minflok ma ċċekkja l-preżenza ta’ rekords bl-użu ta’ COUNT u ta’ skan tat-tabella sħiħa, EŻISTI beda jintuża
• Neħles minn DISTINCT (m'hemm l-ebda riċetta ġenerali, iżda xi drabi tista 'faċilment teħles minnha billi tħaffef it-talba b'10-100 darba).

  Pereżempju, minflok mistoqsija biex tagħżel is-sewwieqa kollha minn tabella kbira ta' kunsinni (KUNSIN)

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

  għamel mistoqsija fuq mejda relattivament żgħira 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)
  

  Jidher li użajna subquery korrelata, iżda tagħti veloċità ta 'aktar minn 10 darbiet.

• F'ħafna każijiet, COUNT ġie abbandunat għal kollox u
  sostitwit bil-kalkolu tal-valur approssimattiv
• minflok

  UPPER(s) LIKE JOHN%’ 
  

  użu

  s ILIKE “John%”
  

Kull talba speċifika ġieli tħaffef bi 3-1000 darba. Minkejja l-prestazzjoni impressjonanti, għall-ewwel dehrilna li ma kien hemm l-ebda skop fl-ottimizzazzjoni ta 'mistoqsija li tieħu 10 ms biex titlesta, hija waħda mit-3rd mitt mistoqsija l-aktar itqal, u tieħu mijiet ta' mija tal-ħin globali tat-tagħbija tad-database. Iżda bl-applikazzjoni tal-istess riċetta għal grupp ta 'mistoqsijiet tal-istess tip, irbaħna lura ftit fil-mija. Sabiex ma naħlux il-ħin manwalment nirrevedu l-mijiet ta’ mistoqsijiet kollha, ktibna diversi skripts sempliċi li użaw espressjonijiet regolari biex isibu mistoqsijiet tal-istess tip. Bħala riżultat, it-tfittix awtomatiku ta' gruppi ta' mistoqsijiet ippermettilna ntejbu aktar il-prestazzjoni tagħna bi sforz modest.

Bħala riżultat, ilna naħdmu fuq l-istess hardware għal tliet snin issa. It-tagħbija medja ta 'kuljum hija madwar 30%, fil-qċaċet tilħaq 70%. In-numru ta' talbiet, kif ukoll in-numru ta' utenti, żdiedu madwar 10 darbiet. U dan kollu grazzi għall-monitoraġġ kostanti ta 'dawn il-gruppi stess ta' talbiet TOP-MEDIUM. Hekk kif talba ġdida tidher fil-grupp TOP, aħna immedjatament nanalizzawha u nippruvaw inħaffuha. Aħna nirrevedu l-grupp MEDIUM darba fil-ġimgħa bl-użu ta 'skripts ta' analiżi tal-mistoqsijiet. Jekk niltaqgħu ma' mistoqsijiet ġodda li diġà nafu kif ottimizzaw, aħna malajr nibdluhom. Xi drabi nsibu metodi ġodda ta 'ottimizzazzjoni li jistgħu jiġu applikati għal diversi mistoqsijiet f'daqqa.

Skont it-tbassir tagħna, is-server attwali se jiflaħ żieda fin-numru ta 'utenti b'3-5 darbiet oħra. Veru, għandna ass ieħor fil-kmiem tagħna - għadna ma ttrasferijniex mistoqsijiet SELECT lill-mera, kif huwa rakkomandat. Imma ma nagħmlux dan b'mod konxju, għaliex irridu l-ewwel neżawrixxu kompletament il-possibbiltajiet ta 'ottimizzazzjoni "intelliġenti" qabel ma nixgħel l-"artillerija tqila".
Ħarsa kritika lejn ix-xogħol li sar tista' tissuġġerixxi l-użu ta' skalar vertikali. Ixtri server aktar b'saħħtu minflok taħli l-ħin ta 'speċjalisti. Is-server jista 'ma jiswa daqshekk, speċjalment peress li għadna ma eżawrewx il-limiti ta' skalar vertikali. Madankollu, in-numru ta' talbiet biss żdied b'10 darbiet. Matul diversi snin, il-funzjonalità tas-sistema żdiedet u issa hemm aktar tipi ta’ talbiet. Bis-saħħa tal-caching, il-funzjonalità li kienet teżisti titwettaq f'inqas talbiet, u talbiet aktar effiċjenti. Dan ifisser li tista 'timmultiplika b'mod sikur b'5 oħra biex tikseb il-koeffiċjent ta' aċċelerazzjoni reali. Allura, skont l-aktar stimi konservattivi, nistgħu ngħidu li l-aċċelerazzjoni kienet 50 darba jew aktar. It-tbandil ta' server vertikalment ikun jiswa 50 darba aktar. Speċjalment meta wieħed iqis li ladarba titwettaq l-ottimizzazzjoni taħdem il-ħin kollu, u l-kont għas-server mikrija jiġi kull xahar.

Sors: www.habr.com