Ukuphucula imibuzo egciniweyo usebenzisa umzekelo wenkonzo ye-B2B kubakhi

Indlela yokukhulisa amaxesha angama-10 inani lemibuzo kwisiseko sedatha ngaphandle kokufudukela kwiseva evelisa ngakumbi kunye nokugcina ukusebenza kwenkqubo? Ndiza kukuxelela indlela esiye sajongana ngayo nokwehla kokusebenza kwedatha yethu, indlela esenze ngayo imibuzo ye-SQL ukuze sikhonze abasebenzisi abaninzi kangangoko kwaye singanyusi iindleko zezixhobo zekhompyutha.

Ndenza inkonzo yokulawula iinkqubo zoshishino kwiinkampani zokwakha. Malunga neenkampani ezingamawaka amathathu zisebenza nathi. Abantu abangaphezu kwe-3 lamawaka basebenza kunye nenkqubo yethu yonke imihla kwiiyure ze-10-4. Isombulula iingxaki ezahlukeneyo zokucwangcisa, isaziso, isilumkiso, ukuqinisekiswa ... Sisebenzisa i-PostgreSQL 10. Sinetafile ezimalunga nama-9.6 kuvimba weenkcukacha kwaye ukuya kuthi ga kwi-300 yezigidi zemibuzo (i-200 lamawaka ehlukeneyo) ifunyanwa yonke imihla. Ngokwe-avareji sinezicelo ezingamawaka angama-10-3 ngesekhondi, kwezona zihlandlo zisebenzayo ngaphezu kwezicelo ezingamawaka ali-4 ngesekhondi. Uninzi lwemibuzo yi OLAP. Kukho izongezo ezimbalwa, uhlengahlengiso kunye nokucinywa, okuthetha ukuba umthwalo we-OLTP ulula. Ndikunike onke la manani ukuze ukwazi ukuvavanya ubungakanani beprojekthi yethu kwaye uqonde ukuba anokuba luncedo kangakanani amava ethu kuwe.

Umfanekiso omnye. Iingoma zeengoma

Xa saqala ukuphuhliswa, asizange sicinge ngokwenene malunga nokuba luhlobo luni lomthwalo oluya kuwela kwisiseko sedatha kunye nento esiya kuyenza ukuba umncedisi wayeka ukutsala. Xa siyila isiseko sedatha, silandele iingcebiso ngokubanzi kwaye sizame ukuba singazidubuli enyaweni, kodwa sidlulele ngaphaya kweengcebiso ngokubanzi ezinje “ungasebenzisi ipateni. Iimpawu zophawu lwequmrhu khange singene. Siye sayila ngokusekwe kwimigaqo yokwenziwa kwesiqhelo, ukuphepha ukuphindaphindwa kwedatha kwaye asikhathali ngokukhawulezisa imibuzo ethile. Ngokukhawuleza nje ukuba abasebenzisi bokuqala bafike, sadibana nengxaki yokusebenza. Njengesiqhelo, besingayilungiselelanga kwaphela le nto. Iingxaki zokuqala zabonakala zilula. Njengomthetho, yonke into yasonjululwa ngokongeza isalathisi esitsha. Kodwa kwafika ixesha apho iipatches ezilula zayeka ukusebenza. Ukuqonda ukuba asinawo amava kwaye kuya kuba nzima kuthi ukuqonda ukuba yintoni ebangela iingxaki, saqesha iingcali ezisincedileyo ukuseta iseva ngokuchanekileyo, ukudibanisa iliso, kwaye zasibonisa ukuba sijonge phi ukuze sifumane. izibalo.

Umfanekiso wesibini. Ubalo

Ke sinemibuzo emalunga ne-10 lamawaka eyohlukeneyo eyenziwa kwisiseko sedatha yethu ngosuku. Kula mawaka ali-10, kukho izilo eziye zabulawa amaxesha ezigidi ezi-2 ukuya kwezi-3 kunye nexesha lokubulawa kwe-avareji ye-0.1-0.3 ms, kwaye kukho imibuzo enexesha eliqhelekileyo lokubulawa kwemizuzwana engama-30 ebizwa ngokuba ngamaxesha angama-100 ngosuku.

Kwakungenakwenzeka ukwenza yonke imibuzo eyi-10 lamawaka, ngoko ke siye sagqiba ekubeni siqonde apho siza kuqondisa khona iinzame zethu ukuze kuphuculwe ukusebenza kwedatha ngokuchanekileyo. Emva kokuphindaphinda kaninzi, saqala ukwahlula izicelo ngokweentlobo.

Izicelo eziphezulu

Le yeyona mibuzo inzima ethatha ixesha elininzi (ixesha lilonke). Le yimibuzo ekusenokwenzeka ukuba ibizwa rhoqo okanye imibuzo ethatha ixesha elide ukuyiqhuba (imibuzo emide neqhelekileyo iye yalungiswa kuphindaphindo lokuqala lomlo wesantya). Ngenxa yoko, umncedisi uchitha ixesha elininzi ekwenziweni kwabo. Ngaphezu koko, kubalulekile ukwahlula izicelo eziphezulu ngexesha elipheleleyo lokuphumeza kwaye ngokwahlukileyo ngexesha le-IO. Iindlela zokulungisa imibuzo enjalo zahluke kancinane.

Inkqubo eqhelekileyo yazo zonke iinkampani kukusebenza kunye nezicelo ze-TOP. Zimbalwa zazo; ukwenza nokuba umbuzo ube mnye kunokukhulula i-5-10% yezibonelelo. Nangona kunjalo, njengoko iprojekthi ikhula, ukwenza imibuzo ye-TOP ibe ngumsebenzi okhulayo ongengomsebenzi omncinci. Zonke iindlela ezilula sele zenziwe, kwaye isicelo "esinzima" sithatha "kuphela" i-3-5% yezibonelelo. Ukuba imibuzo ye-TOP iyonke ithatha ngaphantsi kwe-30-40% yexesha, ngoko ke kunokwenzeka ukuba sele wenze iinzame zokuyenza ukuba isebenze ngokukhawuleza kwaye lixesha lokuqhubela phambili ekulungiseleleni imibuzo kwiqela elilandelayo.
Kuhlala ukuphendula umbuzo wokuba mingaphi imibuzo ephezulu ekufuneka ifakwe kweli qela. Ndidla ngokuthatha ubuncinane i-10, kodwa akukho ngaphezu kwe-20. Ndizama ukuqinisekisa ukuba ixesha lokuqala nelokugqibela kwiqela le-TOP lihluke ngokungekho ngaphezu kwamaxesha e-10. Oko kukuthi, ukuba ixesha lokwenziwa kombuzo lehla ngokukhawuleza ukusuka kwindawo yokuqala ukuya kweye-1, emva koko ndithatha i-TOP-10, ukuba ukuhla kuhamba kancinci, ngoko ndonyusa ubungakanani beqela ukuya kwi-10 okanye i-15.

Abalimi abaphakathi

Ezi zizo zonke izicelo eziza ngokukhawuleza emva kwe-TOP, ngaphandle kwe-5-10 yokugqibela. Ngokwesiqhelo, ekulungiseleleni le mibuzo kukho ithuba lokunyusa kakhulu ukusebenza kweseva. Ezi zicelo zinokufikelela kuma-80%. Kodwa nokuba isabelo sabo sidlulile kwi-50%, ngoko lixesha lokujonga ngokucophelela.

Umsila

Njengoko kukhankanyiwe, le mibuzo iza ekupheleni kwaye ithatha i-5-10% yexesha. Unokuzilibala kuphela ukuba awusebenzisi zixhobo zokuhlalutya imibuzo ezizenzekelayo, emva koko ukuziphucula kunokuba nexabiso eliphantsi.

Ulivavanya njani iqela ngalinye?

Ndisebenzisa umbuzo we-SQL onceda ukwenza uvavanyo olunjalo lwe-PostgreSQL (ndiqinisekile ukuba umbuzo ofanayo ungabhalwa kwezinye ii-DBMS ezininzi)

SQL umbuzo ukuqikelela ubungakanani bamaqela 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

Isiphumo sombuzo ziikholamu ezintathu, nganye kuzo iqulethe ipesenti yexesha elithathayo ukubuza imibuzo kweli qela. Ngaphakathi kwesicelo kukho amanani amabini (kwimeko yam yi-20 kunye ne-800) eyahlula izicelo ezivela kwiqela elinye kwelinye.

Yile ndlela izabelo zezicelo zithelekiseka ngayo ngexesha lomsebenzi wokuphucula waqala kwaye ngoku.

Umzobo ubonisa ukuba isabelo sezicelo ze-TOP sinciphe kakhulu, kodwa "abalimi abaphakathi" baye banda.
Ekuqaleni, izicelo ze-TOP zazibandakanya iimpazamo ezicacileyo. Ngokuhamba kwexesha, izifo zabantwana zanyamalala, isabelo sezicelo ze-TOP sancipha, kwaye kwafuneka ukuba kwenziwe imizamo yokukhawulezisa izicelo ezinzima.

Ukufumana umbhalo wezicelo sisebenzisa esi sicelo silandelayo

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

Nalu uluhlu lwezona ndlela zixhaphakileyo ezisetyenziswayo ezisincede ukuba sikhawulezise imibuzo ye-TOP:

• Ukuyilwa ngokutsha kwesixokelelwano, umzekelo, ukuphinda kusetyenzwe ingqiqo yesaziso usebenzisa umthengisi womyalezo endaweni yemibuzo ngamaxesha athile kwisiseko sedatha.
• Ukongeza okanye ukutshintsha izalathisi
• Ukuphinda ubhale imibuzo yeORM kwiSQL esulungekileyo
• Ukubhala kwakhona ingqiqo yokulayisha idatha
• I-Caching ngokusebenzisa i-data denormalization. Umzekelo, sinonxibelelwano lwetafile Unikezelo -> I-invoyisi -> Isicelo -> Isicelo. Oko kukuthi, unikezelo ngalunye lunxulunyaniswa nesicelo ngezinye iitafile. Ukuze singaqhagamshelanisi zonke iitheyibhile kwisicelo ngasinye, siphindaphinde ikhonkco kwisicelo kwitheyibhile yokuHanjiswa.
• I-caching static tables kunye neencwadi zereferensi kwaye kunqabile ukutshintsha iitafile kwimemori yeprogram.

Ngamanye amaxesha utshintsho lwaba yi-redesign emangalisayo, kodwa banikezela nge-5-10% yomthwalo wenkqubo kwaye bafanelekile. Ngokuhamba kwexesha, ukukhupha kuye kwancipha, kwaye ngakumbi nangakumbi uhlengahlengiso oluyimfuneko.

Emva koko siphendulele ingqalelo yethu kwiqela lesibini lezicelo - iqela labalimi abaphakathi. Kukho imibuzo emininzi kuyo kwaye kubonakala ngathi kuya kuthatha ixesha elininzi ukuhlalutya lonke iqela. Nangona kunjalo, uninzi lwemibuzo luye lwabonakala lulula ukwenza, kwaye iingxaki ezininzi zaphindwa kaninzi kwiiyantlukwano ezahlukeneyo. Nantsi imizekelo yolungelelwaniso oluqhelekileyo esiye salusebenzisa kwimibuzo emininzi efanayo kwaye iqela ngalinye lemibuzo eyongeziweyo yothula isiseko sedatha nge-3-5%.

• Esikhundleni sokujonga ubukho beerekhodi usebenzisa i-COUNT kunye ne-scan epheleleyo yetafile, i-EXISTS yaqala ukusetyenziswa
• Ukulahla i-DISTINCT (ayikho iresiphi eqhelekileyo, kodwa ngamanye amaxesha unokuyisusa ngokulula ngokukhawulezisa isicelo ngamaxesha angama-10-100).

  Umzekelo, endaweni yombuzo ukukhetha bonke abaqhubi kwitheyibhile enkulu yokuhanjiswa (DELIVERY)

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

  wenze umbuzo kwitafile encinci 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)
  

  Kubonakala ngathi sisebenzise i-subquery edibeneyo, kodwa inika isantya esingaphezulu kwamaxesha ali-10.

• Kwiimeko ezininzi, COUNT ishiywe ngokupheleleyo kwaye
  endaweni yokubalwa kwexabiso eliqikelelweyo
• вместо

  UPPER(s) LIKE JOHN%’ 
  

  sebenzisa

  s ILIKE “John%”
  

Isicelo ngasinye esikhethekileyo ngamanye amaxesha sasikhawuleza ngamaxesha angama-3-1000. Ngaphandle komsebenzi oncomekayo, ekuqaleni kwakubonakala ngathi akukho sizathu sokuphucula umbuzo othatha i-10 ms ukuwugqiba, ngomnye wemibuzo engamakhulu amathathu enzima, kwaye ithatha ikhulu leepesenti yexesha lomthwalo wedatha. Kodwa ngokusebenzisa iresiphi efanayo kwiqela lemibuzo yohlobo olufanayo, siphumelele iipesenti ezimbalwa. Ukuze singachithi ixesha ngokwenza uphononongo lwawo onke amakhulu emibuzo, sibhale izikripthi ezininzi ezilula ezisebenzisa amabinzana aqhelekileyo ukufumana imibuzo yohlobo olufanayo. Ngenxa yoko, ukukhangela ngokuzenzekelayo amaqela emibuzo kusivumele ukuba siphucule ngakumbi ukusebenza kwethu ngomzamo omncinci.

Ngenxa yoko, sisebenze kwi-hardware efanayo iminyaka emithathu ngoku. Umyinge womthwalo wemihla ngemihla umalunga ne-30%, kwiindawo eziphakamileyo zifikelela kuma-70%. Inani lezicelo, kunye nenani labasebenzisi, linyuke malunga namaxesha ali-10. Kwaye konke oku kubulela ekubekweni esweni rhoqo kwala maqela afanayo e-TOP-MEDIUM izicelo. Ngokukhawuleza ukuba isicelo esitsha sivela kwiqela le-TOP, sihlalutya ngokukhawuleza kwaye sizame ukukhawulezisa. Siphonononga iqela le-MEDIUM kanye ngeveki sisebenzisa imibhalo yokuhlalutya imibuzo. Ukuba sidibana nemibuzo emitsha esele siyayazi indlela yokuphucula, siyitshintsha ngokukhawuleza. Ngamanye amaxesha sifumana iindlela ezintsha zokulungiselela ezinokuthi zisetyenziswe kwimibuzo emininzi ngexesha elinye.

Ngokwezibikezelo zethu, iseva yangoku iya kumelana nokunyuka kwenani labasebenzisi ngamanye amaxesha angama-3-5. Kuyinyani, sine-ace enye ngaphezulu komkhono wethu - asikadluliseli KHETHA imibuzo kwisibuko, njengoko kucetyiswa. Kodwa asikwenzi oku ngokuqaphela, kuba sifuna ukuphelisa ngokupheleleyo amathuba okuphucula "i-smart" ngaphambi kokuvula "i-artillery enzima".
Ukujongwa okunzulu kumsebenzi owenziweyo kunokucebisa ukuba kusetyenziswe ukulinganisa ngokuthe nkqo. Thenga iseva enamandla ngakumbi endaweni yokuchitha ixesha leengcaphephe. Umncedisi usenokungabizi mali ingako, ngakumbi kuba asikayigqibi imida yokukalwa ngokuthe nkqo. Nangona kunjalo, kuphela inani lezicelo eziye zanda izihlandlo ezili-10. Ekuhambeni kweminyaka emininzi, ukusebenza kwenkqubo kuye kwanda kwaye ngoku kukho iintlobo ezininzi zezicelo. Enkosi kwi-caching, ukusebenza obekukhona kwenziwa kwizicelo ezimbalwa, kunye nezicelo ezisebenzayo. Oku kuthetha ukuba ungaphinda-phinda ngokukhuselekileyo ngomnye u-5 ukufumana i-coefficient yokwenyani yokukhawulezisa. Ngoko ke, ngokolona qikelelo lucokisekileyo, sinokuthi ukukhawuleza kwakungamaxesha angama-50 okanye ngaphezulu. Ukujiwuzisa iseva ngokuthe nkqo kuya kuxabisa amaxesha angama-50 ngaphezulu. Ngokukodwa xa kuqwalaselwa ukuba ukulungelelaniswa kugqityiwe kusebenza ngalo lonke ixesha, kwaye i-bill ye-server eqeshiweyo iza nyanga zonke.

umthombo: www.habr.com