Nzira yekukura sei kagumi nhamba yemibvunzo kune dhatabhesi pasina kutamira kune inobereka yakawanda sevha uye kuchengetedza sisitimu mashandiro? Ini ndichakuudza mabatiro atakaita nekudzikira kwekuita kwedhatabhesi yedu, magadzirisiro atakaita mibvunzo yeSQL kuti ishumire vashandisi vazhinji sezvinobvira uye kwete kuwedzera mutengo wemakomputa zviwanikwa.
Ini ndinoita sevhisi yekutarisira mabhizinesi maitiro mumakambani ekuvaka. Makambani anosvika zviuru zvitatu anoshanda nesu. Vanopfuura zviuru gumi vanhu vanoshanda nehurongwa hwedu mazuva ese kwe3-10 maawa. Inogadzirisa matambudziko akasiyana-siyana ekuronga, chiziviso, yambiro, kusimbisa ... Isu tinoshandisa PostgreSQL 4. Tine matafura mazana matatu mudhatabhesi uye anosvika mazana maviri emamiriyoni emibvunzo (10 zviuru zvakasiyana) inogamuchirwa zuva rega rega. Paavhareji tine 9.6-300 zviuru zvikumbiro pasekondi imwe, panguva dzinoshanda zvakanyanya kupfuura zviuru gumi zvikumbiro pasekondi. Yakawanda yemibvunzo ndeye OLAP. Kune mashoma ekuwedzera, kugadziridzwa uye kudzima, zvichireva kuti OLTP mutoro wakareruka. Ini ndakapa nhamba idzi dzese kuti iwe ugone kuongorora chiyero chepurojekiti yedu uye unzwisise kuti ruzivo rwedu rungakubatsira sei iwe.
Mufananidzo wekutanga. Lyrical
Patakatanga kusimudzira, isu hatina kunyatsofunga nezve rudzi rwemutoro waizowira pane dhatabhesi uye zvataizoita kana sevha yakamira kudhonza. Pakugadzira dhatabhesi, takatevera kurudziro uye takaedza kuti tisapfure mutsoka, asi takapfuura zano rakajairwa senge βusashandise pateni. hatina kupinda. Isu takagadzira zvichibva pamisimboti yekumisikidza, kudzivirira kudzokorora data uye hatina hanya nekumhanyisa mimwe mibvunzo. Pakangosvika vashandisi vekutanga, takasangana nedambudziko rekuita. Semazuva ose, takanga tisina kugadzirira zvachose izvi. Matambudziko ekutanga akazova nyore. Sezvo mutemo, zvinhu zvose zvakagadziriswa nekuwedzera index itsva. Asi yakasvika nguva apo zvigamba zviri nyore zvakamira kushanda. Nekuona kuti isu hatina ruzivo uye zviri kuramba zvichitiomera kuti tinzwisise kuti chii chiri kukonzera matambudziko, takahaya nyanzvi dzakatibatsira kumisikidza sevha nemazvo, kubatanidza kutarisa, uye vakatiratidza kwekutsvaga kuwana. .
Mufananidzo wechipiri. Statistical
Saka isu tine zviuru gumi zvemibvunzo yakasiyana inoitwa pane yedu database pazuva. Pakati pezviuru gumi izvi, pane zvikara zvinouraiwa 10-10 miriyoni nguva neavhareji yekuuraya nguva ye2-3 ms, uye pane mibvunzo ine avhareji yekuuraya nguva yemasekonzi makumi matatu anonzi ka0.1 pazuva.
Izvo zvanga zvisingaite kukwirisa ese zviuru gumi zvemibvunzo, saka takafunga kufunga kwatingatungamira kuedza kwedu kuitira kuti tivandudze mashandiro edhatabhesi nemazvo. Mushure mekudzokorora kakawanda, takatanga kupatsanura zvikumbiro mumhando.
TOP zvikumbiro
Iyi ndiyo mibvunzo inorema kwazvo inotora nguva yakawanda (nguva yese). Iyi mibvunzo inodaidzwa kunzi kazhinji kazhinji kana mibvunzo inotora nguva yakareba kuti iite (yakareba uye yakajairika mibvunzo yakagadziridzwa mukutanga kudzokorora kwekurwira kumhanya). Nekuda kweizvozvo, sevha inopedza nguva yakawanda pakuita kwavo. Uyezve, zvakakosha kuparadzanisa zvikumbiro zvepamusoro nenguva yekuuraya uye zvakasiyana neIO nguva. Nzira dzekugadzirisa mibvunzo yakadaro dzakasiyana zvishoma.
Iyo yakajairika tsika yemakambani ese kushanda nezvikumbiro zveTOP. Kune mashoma azvo; kugadzirisa kunyange mubvunzo mumwe chete unogona kusunungura 5-10% yezviwanikwa. Nekudaro, sezvo purojekiti inokura, kugadzirisa mibvunzo yeTOP inova iri kuwedzera risiri basa diki. Nzira dzose dziri nyore dzakatoitwa, uye chikumbiro "chinorema" chinotora "chete" 3-5% yezviwanikwa. Kana TOP mibvunzo yakazara ikatora isingasviki 30-40% yenguva, saka kazhinji iwe wakatoita nhamburiko dzekuita kuti dzishande nekukurumidza uye inguva yekuenderera mberi nekugadzirisa mibvunzo kubva muboka rinotevera.
Inoramba ichipindura mubvunzo wekuti vangani mibvunzo yepamusoro inofanira kuiswa muboka iri. Ini kazhinji ndinotora zvishoma 10, asi kwete kupfuura 20. Ndinoedza kuve nechokwadi kuti nguva yekutanga uye yekupedzisira muboka re TOP inosiyana nekusapfuura gumi. Ndokunge, kana nguva yekuuraya yemubvunzo ichidonha zvakanyanya kubva panzvimbo yekutanga kuenda kune yegumi, ipapo ini ndinotora TOP-10, kana kudonha kuri kuwedzera zvishoma nezvishoma, ipapo ndinowedzera saizi yeboka kusvika 1 kana 10.
Middle peasants
Izvi zvese zvikumbiro zvinouya nekukurumidza mushure meTOP, kunze kweiyo yekupedzisira 5-10%. Kazhinji, mukugadzirisa iyi mibvunzo kune mukana wekuwedzera zvakanyanya server performance. Zvikumbiro izvi zvinogona kurema kusvika 80%. Asi kunyange kana mugove wavo wakapfuura 50%, zvino ndiyo nguva yekuvatarisa zvakanyanya.
Muswe
Sezvambotaurwa, iyi mibvunzo inouya kumagumo uye inotora 5-10% yenguva. Iwe unogona kukanganwa nezvavo chete kana ukasashandisa otomatiki query yekuongorora maturusi, wozoagadzirisa anogona zvakare kudhura.
Nzira yekuongorora boka rega rega?
Ini ndinoshandisa SQL query inobatsira kuita ongororo yakadai yePostgreSQL (Ndine chokwadi chekuti mubvunzo wakafanana unogona kunyorerwa mamwe akawanda maDBMS)
SQL query yekufungidzira kukura kweTOP-MEDIUM-TAIL mapoka
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
Mhedzisiro yemubvunzo ndeye makoramu matatu, imwe neimwe ine chikamu chenguva inotorwa kugadzirisa mibvunzo kubva muboka iri. Mukati mechikumbiro mune nhamba mbiri (munyaya yangu i20 ne800) inoparadzanisa zvikumbiro kubva kune rimwe boka kubva kune rimwe.
Iyi ndiyo nzira iyo migove yezvikumbiro inofananidzwa panguva iyo basa rekugadzirisa rakatanga uye ikozvino.
Dhiagiramu inoratidza kuti chikamu cheTOP zvikumbiro chakaderera zvakanyanya, asi "varimi vepakati" vawedzera.
Pakutanga, zvikumbiro zveTOP zvaisanganisira kukanganisa kuri pachena. Nokufamba kwenguva, zvirwere zvehuduku zvakanyangarika, chikamu cheTOP zvikumbiro chakaderera, uye kuedza kwakawanda kwaifanira kuitwa kukurumidzira zvikumbiro zvakaoma.
Kuti tiwane chinyorwa chezvikumbiro tinoshandisa chikumbiro chinotevera
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
Heino rondedzero yeanonyanya kushandiswa matekiniki akatibatsira kukurumidzira TOP mibvunzo:
- Kugadziridzwazve kweiyo sisitimu, semuenzaniso, kugadzirisa iyo yekuzivisa logic uchishandisa meseji broker pane yenguva nenguva mibvunzo kune dhatabhesi.
- Kuwedzera kana kushandura indexes
- Kunyorazve ORM mibvunzo kune yakachena SQL
- Kunyora zvakare usimbe data kurodha logic
- Caching kuburikidza nedata denormalization. Semuenzaniso, isu tine tafura yekubatanidza Kuendesa -> Invoice -> Chikumbiro -> Chikumbiro. Ndiko kuti, kutumirwa kwega kwega kunosanganiswa nekushandisa kuburikidza nemamwe matafura. Kuti tisabatanidza matafura ese muchikumbiro chega chega, isu takadzokorora chinongedzo chechikumbiro chiri muDelivery table.
- Caching static matafura ane referensi mabhuku uye kashoma kushandura matafura mundangariro yechirongwa.
Dzimwe nguva shanduko dzakasvika pakugadziridzwa kunoshamisa, asi ivo vakapa 5-10% yemutoro wehurongwa uye vakaruramiswa. Nokufamba kwenguva, kubuda kwacho kwakava kudiki uye kudiki, uye zvakanyanya uye zvakanyanya kugadzirisa patsva.
Ipapo takaisa pfungwa dzedu kuboka rechipiri rezvikumbiro - boka revarimi vepakati. Pane mimwe mibvunzo yakawanda mairi uye zvaiita sekuti zvaizotora nguva yakawanda kuongorora boka rose. Nekudaro, mizhinji mibvunzo yakave nyore kugadzirisa, uye matambudziko mazhinji akadzokororwa kakawanda mumhando dzakasiyana. Heino mienzaniso yezvimwe zvakajairwa optimizations yatakaisa kune yakawanda yemibvunzo yakafanana uye boka rega rega remibvunzo yakakwenenzverwa yakaburitsa dhatabhesi ne3-5%.
- Panzvimbo pekutarisa kuvepo kwezvinyorwa uchishandisa COUNT uye yakazara tafura scan, EXISTS yakatanga kushandiswa
- Bvisa DISTINCT (hapana resipi yakajairika, asi dzimwe nguva unogona kuibvisa nyore nyore nekumhanyisa chikumbiro ne10-100 nguva).
Semuenzaniso, pachinzvimbo chemubvunzo wekusarudza vatyairi vese kubva patafura hombe yekutakura (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDakaita mubvunzo patafura diki 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)Zvingaite senge isu takashandisa correlated subquery, asi inopa kukurumidza kwekanopfuura kagumi.
- Kakawanda, COUNT yakasiiwa zvachose uye
- panzvimbo ye
UPPER(s) LIKE JOHN%βshandisa
s ILIKE βJohn%β
Chikumbiro chega chega chaikurumidza kukwidziridzwa ne3-1000 nguva. Zvisinei nekuita kunokatyamadza, pakutanga zvaiita sekuti paive pasina chikonzero chekugadzirisa mubvunzo unotora gumi ms kuti upedze, ndeimwe yemazana matatu emibvunzo inorema, uye inotora mazana ezana muzana yenguva yese yekuremerwa kwedhatabhesi. Asi nekushandisa resipi imwe chete kuboka remibvunzo yerudzi rumwe chete, takahwina zvakare zvishoma kubva muzana. Kuti tisapedze nguva tichiongorora mazana emibvunzo, takanyora zvinyorwa zvakapfava zvakashandisa mataurirwo enguva dzose kutsvaga mibvunzo yemhando imwe chete. Nekuda kweizvozvo, kutsvaga nekutsvaga mapoka emibvunzo kwakatibvumira kuti tiwedzere kunatsiridza mashandiro edu nekuedza zvine mwero.
Nekuda kweizvozvo, tave tichishanda pane imwecheteyo hardware kwemakore matatu ikozvino. Ivhareji yemutoro wezuva nezuva inenge 30%, mukukwirira inosvika 70%. Huwandu hwezvikumbiro, pamwe nenhamba yevashandisi, yakawedzera kanenge kagumi. Uye zvese izvi nekuda kwekutarisa nguva dzose kweaya mapoka mamwechete e TOP-MEDIUM zvikumbiro. Pakangoonekwa chikumbiro chitsva muboka reTOP, tinobva tangozviongorora uye kuyedza kukurumidzira. Isu tinoongorora boka reMEDIUM kamwe pasvondo tichishandisa zvinyorwa zvekuongorora mibvunzo. Kana tikasangana nemibvunzo mitsva yatinotoziva kugadzirisa, tinokurumidza kuichinja. Dzimwe nguva tinowana nzira nyowani dzekugadzirisa dzinogona kushandiswa kune akati wandei mibvunzo kamwechete.
Maererano nekufanotaura kwedu, sevha yezvino ichamira kuwedzera kwenhamba yevashandisi neimwe 3-5 nguva. Chokwadi, isu tine imwezve ace kumusoro kwemaoko edu - hatisati taendesa SELECT mibvunzo kugirazi, sezvazvinokurudzirwa. Asi isu hatiite izvi nekuziva, nekuti isu tinoda kutanga tapedza zvachose mikana ye "smart" optimization tisati tabatidza "inorema artillery".
Kutarisisa kwakadzama pabasa rakaitwa kungapa zano rekushandisa vertical scaling. Tenga sevha ine simba pane kutambisa nguva yenyanzvi. Sevha inogona kusadhura zvakanyanya, kunyanya sezvo isu tisati tapedza miganho yekuyera yakatwasuka. Zvisinei, nhamba chete yezvikumbiro yakawedzera ka10. Mukufamba kwemakore akati wandei, kushanda kwehurongwa kwakawedzera uye ikozvino kune mamwe marudzi ezvikumbiro. Kutenda kune caching, kushanda kwanga kuripo kunoitwa muzvikumbiro zvishoma, uye zvikumbiro zvinoshanda. Izvi zvinoreva kuti unokwanisa kuwanda zvakachengeteka neimwe 5 kuti uwane iyo chaiyo yekumhanyisa coefficient. Saka, maererano nekunyanya kufungidzira kufungidzira, tinogona kutaura kuti kukurumidza kwaiva makumi mashanu kana kupfuura. Kutenderedza sevha kwakatwasuka kunodhura zvakapetwa makumi mashanu. Kunyanya tichifunga kuti kana optimization yaitwa inoshanda nguva dzese, uye bhiri reiyo server rakarenda rinouya mwedzi wega wega.
Source: www.habr.com