Chirevo chinopa dzimwe nzira dzinobvumira tarisa mashandiro eSQL mibvunzo kana paine mamirioni awo pazuva, uye kune mazana eanotariswa PostgreSQL maseva.
Ndedzipi mhinduro dzehunyanzvi dzinotitendera kuti tinyatso kugadzirisa huwandu hwakadaro hweruzivo, uye izvi zvinoita sei kuti hupenyu hwemugadziri akajairika huve nyore?
Ndiani anofarira? kuongororwa kwezvinetso zvakananga uye nzira dzakasiyana-siyana dzekugadzirisa SQL mibvunzo uye kugadzirisa zvakajairika DBA matambudziko muPostgreSQL - unogona zvakare pamusoro penyaya iyi.
Zita rangu ndiKirill Borovikov, ndinomiririra . Kunyanya, ini ndine hunyanzvi mukushanda nemadatabase mukambani yedu.
Nhasi ini ndichakuudza maitiro atinoita mibvunzo, kana iwe usingade "kusarudza" kuita kwemubvunzo mumwe chete, asi gadzirisa dambudziko rakawanda. Kana paine mamirioni ezvikumbiro, uye iwe unofanirwa kuwana zvimwe nzira dzekugadzirisa dambudziko guru iri.
Kazhinji, Tensor yemiriyoni yevatengi vedu ndeye : corporate social network, zvigadziriso zvekukurukurirana kwevhidhiyo, yemukati uye kunze kwegwaro kuyerera, accounting system ye accounting uye matura, ... Ndiko kuti, "mega-combine" yakadaro yekubatanidza bhizinesi manejimendi, umo mune anopfuura zana akasiyana. zvirongwa zvemukati.
Kuti tive nechokwadi chekuti vese vanoshanda nekusimukira zvakaringana, tine nzvimbo gumi dzebudiriro munyika yese, dziine dzakawanda mairi 1000 vagadziri.
Isu tanga tichishanda nePostgreSQL kubvira 2008 uye takaunganidza huwandu hukuru hwezvatinogadzirisa - data revatengi, nhamba, analytical, data kubva kune ekunze ruzivo masisitimu - kupfuura 400TB. Kune anenge mazana maviri nemakumi mashanu maseva mukugadzira chete, uye pamwe chete kune anenge zana edhatabhesi maseva atinotarisa.
SQL mutauro unozivisa. Iwe unotsanangura kwete "sei" chimwe chinhu chinofanira kushanda, asi "chii" chaunoda kuzadzisa. Iyo DBMS inoziva zvirinani kuita JOIN - maitiro ekubatanidza matafura ako, ndeapi mamiriro ekumanikidza, chii chichapfuura nepakati index, chii chisingazo ...
Mamwe maDBMS anogamuchira mazano: "Kwete, batanidza matafura maviri aya mumutsara wakadaro," asi PostgreSQL haigone kuita izvi. Iyi ndiyo nzvimbo yekuziva yevanotungamira vanogadzira: "Tinoda kupedzisa muvhunzo optimizer pane kubvumidza vanogadzira kushandisa imwe mhando yemazano."
Asi, zvisinei nekuti PostgreSQL haitenderi iyo "kunze" kuzvidzora pachayo, inobvumira zvakakwana ona zviri kuitika mukati makepaunomhanya kubvunza kwako, uye parikunetsa.
Kazhinji, ndeapi matambudziko anowanzouya nemugadziri [kuDBA]? βPano takazadzisa chikumbiro, uye zvinhu zvose zvinononoka nesu, zvinhu zvose zvarembera, chimwe chinhu chiri kuitika... Rumwe rudzi rwedambudziko!β
Zvikonzero zvinenge zvakafanana nguva dzose:
- inefficient query algorithm
Developer: "Iye zvino ndiri kumupa 10 matafura muSQL kuburikidza neJOIN ..." - uye anotarisira kuti mamiriro ake achaita chishamiso "kusunungurwa" uye achawana zvose nokukurumidza. Asi zvishamiso hazviitike, uye chero hurongwa hune kusiyana kwakadaro (matafura gumi mune imwe FROM) nguva dzose inopa imwe mhando yekukanganisa. [] - nhamba dzechinyakare
Iyi poindi yakakosha chaizvo kuPostgreSQL, pawaka "durura" dhatabheti hombe pane server, ita chikumbiro, uye "sexcanits" piritsi rako. Nekuti nezuro maive nemarekodhi gumi mairi, uye nhasi kune mamirioni gumi, asi PostgreSQL haisati yaziva izvi, uye isu tinoda kuzvitaura nezvazvo. [] - "plug" pane zviwanikwa
Wakaisa dhatabhesi yakakura uye yakaremerwa pane isina simba sevha isina dhisiki yakakwana, ndangariro, kana processor kuita. Uye ndizvo chete ... Pane imwe nzvimbo pane sirin'i yekuita pamusoro iyo iwe hauchakwanisi kusvetuka. - ichivharira
Iyi ipfungwa yakaoma, asi inonyanya kukosha kune akasiyana ekugadzirisa mibvunzo (INSERT, UPDATE, DELETE) - iyi inyaya hombe yakaparadzana.
Kuwana chirongwa
... Uye kune zvimwe zvese isu vanoda chirongwa! Tinofanira kuona zviri kuitika mukati me server.
A query execution plan yePostgreSQL muti weiyo query execution algorithm mukumiririra zvinyorwa. Izvo chaizvo iyo algorithm iyo, semugumisiro wekuongorora neanoronga, yakawanikwa kuti ndiyo inonyanya kushanda.
Imwe neimwe yemuti node ibasa: kutora data kubva patafura kana index, kuvaka bitmap, kujoinha matafura maviri, kujoinha, kupindirana, kana kusasanganisa sarudzo. Kuita mubvunzo kunosanganisira kufamba nepakati pemuti uyu.
Kuti uwane chirongwa chemubvunzo, nzira iri nyore ndeyekuita chirevo EXPLAIN. Kuti uwane neese hunhu chaihwo, ndiko kuti, kunyatso bvunza mubvunzo pachigadziko - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
Chikamu chakaipa: kana iwe uchimhanya, zvinoitika "pano uye ikozvino", saka zvakakodzerwa chete nekugadzirisa kwenzvimbo. Kana iwe ukatora sevha yakaremerwa iri pasi pekuyerera kwakasimba kwekuchinja kwedata, uye iwe unoona: "Ah! Pano tine kuuraya kunononokavakadzika musasa chikumbiro." Hafu yeawa, awa yapfuura - iwe uchimhanya uye uchiwana chikumbiro ichi kubva kumatanda, uchichidzosera kuseva, yako yese dataset uye manhamba zvakachinja. Iwe unoimhanyisa kuti igadzirise - uye inomhanya nekukurumidza! Uye haugone kunzwisisa kuti sei, sei zvakanga zvishoma nezvishoma.
Kuti unzwisise zvakaitika chaizvo panguva iyo chikumbiro chakaitwa paserver, vanhu vakangwara vakanyora . Iyo iripo mune dzinenge dzese dzakajairika PostgreSQL kugovera, uye inogona kungoitwa muiyo config faira.
Kana ikaona kuti chimwe chikumbiro chiri kutora nguva yakareba kupfuura muganhu wawakaudza, zvinoita "snapshot" yechirongwa chechikumbiro ichi uye anonyora pamwe chete murogi.
Zvose zvinoratidzika kunge zvakanaka zvino, tinoenda kurogi uye tinoona ikoko ... [text footcloth]. Asi isu hatigone kutaura chero chinhu nezvazvo, kunze kwekuti chirongwa chakanakisa nekuti zvakatora 11ms kuita.
Zvese zvinoita kunge zvakanaka - asi hapana chiri pachena kuti chii chaizvo chakaitika. Kunze kwenguva yakajairika, isu hatinyatsoona chero chinhu. Nekuti kutarisa βgwayanaβ rakadaro remavara akapfava kazhinji hakusi kwekuona.
Asi kunyangwe kana zvisiri pachena, kunyangwe zvikaita zvisingaite, kune mamwe matambudziko akakosha:
- Node inoratidza huwandu hwezviwanikwa zve subtree yese pasi pake. Kureva kuti, haugone kungoona kuti inguvai yakashandiswa pane iyi Index Scan kana paine imwe nested mamiriro pasi payo. Tinofanira kutarisa zvine simba kuti tione kana muine βvanaβ uye nezvinomisikidzwa zvakasiyana, maCTE mukati - tobvisa zvese izvi βmupfungwa dzeduβ.
- Chechipiri poindi: iyo nguva inoratidzwa pane node ndeye single node execution nguva. Kana iyi node yakaitwa semugumisiro we, semuenzaniso, loop kuburikidza netafura rekodhi kakawanda, ipapo nhamba yezvishwe-kutenderera kwenode iyi-inowedzera muurongwa. Asi nguva yekuuraya atomu pachayo inoramba yakafanana maererano nehurongwa. Ndiko kuti, kuti unzwisise kuti inguva yakareba sei iyo node yakaitwa muhuwandu, iwe unofanirwa kuwedzera chinhu chimwe nechimwe - zvakare, "mumusoro wako."
Mumamiriro ezvinhu akadaro, nzwisisa kuti "Ndiani asina kusimba chinongedzo?" zvinenge zvisingaiti. Naizvozvo, kunyangwe ivo vanogadzira ivo pachavo vanonyora mu "manual" iyo "Kunzwisisa chirongwa hunyanzvi hunofanirwa kudzidzwa, ruzivo ...".
Asi isu tine 1000 vanogadzira, uye haugone kuendesa chiitiko ichi kune mumwe nemumwe wavo. Ini, imi, iye anoziva, asi hakuna munhu ari ikoko achaziva. Zvichida achadzidza, kana kuti kwete, asi anofanira kushanda iye zvino - uye aizowana kupi ruzivo urwu?
Ronga kuona
Naizvozvo, takaona kuti kuti tigadzirise matambudziko aya, tinofanira kuona kwakanaka kwechirongwa.
Takatanga taenda "pamusika" - ngatitarisei paInternet kuti tione kuti chii chiripo.
Asi zvakazoitika kuti kune mashoma mashoma "mhenyu" mhinduro dziri kuwedzera kana kushoma kukura - chaizvo, imwe chete: naHubert Lubaczewski. Paunopinda mundima ye "feed" chinyorwa chinomiririra chirongwa, chinokuratidza tafura ine data rakapatsanurwa:
- node's own processing time
- nguva yakazara ye subtree yese
- nhamba yezvinyorwa zvakatorwa izvo zvaitarisirwa nenhamba
- node body pachayo
Iyi sevhisi zvakare ine kugona kugovera archive yemalink. Iwe wakakanda hurongwa hwako imomo ndokuti: "Hei, Vasya, heino chinongedzo, pane chakashata ipapo."
Asi kunewo matambudziko madiki.
Chekutanga, huwandu hukuru hwe "copy-paste". Iwe unotora chidimbu chedanda, wochinamira imomo, uye zvakare, uye zvakare.
Chechipiri, hapana kuongororwa kwehuwandu hwe data yakaverengwa - mabhafa akafanana anobuda EXPLAIN (ANALYZE, BUFFERS), hatizvione pano. Iye haatomboziva nzira yekuaparadzanisa, kunzwisisa uye kushanda nawo. Paunenge uchiverenga data rakawanda uye woona kuti unogona kugovera zvisizvo dhisiki uye ndangariro cache, ruzivo urwu rwakakosha.
Pfungwa yechitatu yakaipa ndeyekusimba kwakanyanya kwechirongwa ichi. Iwo madhipatimendi madiki kwazvo, zvakanaka kana kamwe chete mwedzi mitanhatu yega yega, uye kodhi iri muPerl.
Asi izvi zvese "nziyo", isu tinogona neimwe nzira kurarama neizvi, asi pane chinhu chimwe chakatibvisa zvakanyanya kubva pasevhisi iyi. Aya ndiwo zvikanganiso mukuongororwa kweCommon Table Expression (CTE) uye node dzakasiyana siyana seInitPlan/SubPlan.
Kana iwe uchitenda mufananidzo uyu, saka nguva yakazara yekuuraya yega yega node yakakura kupfuura yakazara nguva yekuuraya yechikumbiro chose. Zviri nyore - nguva yechizvarwa cheCTE iyi haina kubviswa kubva kuCTE Scan node. Naizvozvo, isu hatichaziva mhinduro chaiyo yekuti iyo CTE scan pachayo yakatora nguva yakareba sei.
Ipapo takaona kuti yakanga yava nguva yekunyora zvedu - hurray! Wese anovandudza anoti: "Iye zvino tichanyora zvedu, zvichave nyore kwazvo!"
Takatora stack yakajairwa yewebhu masevhisi: musimboti wakavakirwa paNode.js + Express, yakashandiswa Bootstrap uye D3.js yemadhayagiramu akanaka. Uye tarisiro yedu yakaruramiswa zvizere - takagamuchira yekutanga prototype mumavhiki maviri:
- custom plan parser
Ndokunge, ikozvino isu tinokwanisa kupatsanura chero chirongwa kubva kune chakagadzirwa nePostgreSQL. - kuongororwa kwakarurama kwemanodhi ane simba -CTE Scan, InitPlan, SubPlan
- kuongororwa kwekugoverwa kwebhafa - uko mapeji e data anoverengwa kubva mundangariro, kupi kubva kune yemuno cache, kupi kubva ku diski
- akawana kujeka
Kuti urege "kuchera" zvese izvi mugiyo, asi kuti uone "iyo isina simba link" ipapo pamufananidzo.
Isu tine chimwe chinhu chakadai, nekujekesa kwe syntax kunosanganisirwa. Asi kazhinji vagadziri vedu havachashandi neinomiririra yakakwana yechirongwa, asi neipfupi. Mushure mezvose, isu tatove taparadzanisa nhamba dzose uye takadzikanda kuruboshwe uye kurudyi, uye pakati takasiya chete mutsara wekutanga, rudzii rwe node: CTE Scan, CTE chizvarwa kana Seq Scan maererano nechimwe chiratidzo.
Iyi ndiyo pfupikiso inomiririra yatinodaidza plan template.
Chii chimwe chingave chakanakira? Zvingave zviri nyore kuona kuti ndeipi chikamu chenguva yedu yakazara yakagoverwa kune iyo node - uye ingo "inamatira" padivi. pie chati.
Isu tinonongedza pane node uye tinoona - zvinoitika kuti Seq Scan yakatora isingasviki chikamu chechina chenguva yakazara, uye yakasara 3/4 yakatorwa neCTE Scan. Horror! Ichi chinyorwa chidiki nezve "mwero wemoto" weCTE Scan kana iwe uchivashandisa nesimba mumibvunzo yako. Ivo havana kukurumidza zvakanyanya - ivo vakaderera kunyangwe kune yakajairwa tafura scanning.
Asi kazhinji madhayagiramu akadaro anonyanya kunakidza, akaoma kunzwisisa, patinobva tanongedza chikamu uye toona, semuenzaniso, inopfuura hafu yenguva imwe Seq Scan "yakadya". Uyezve, pane imwe mhando yeSefa mukati, zvinyorwa zvakawanda zvakaraswa maererano nazvo ... Unogona kukanda zvakananga mufananidzo uyu kumugadziri uye uchiti: "Vasya, zvinhu zvose zvakaipa pano kwauri! Zvione, tarisa - chimwe chinhu hachina kunaka! "
Nomuzvarirwo, pakanga pane mamwe βmarakiβ aibatanidzwa.
Chinhu chekutanga chatakawana idambudziko rekutenderera. Nguva yenode yega yega muurongwa inoratidzwa nekururama kwe1 ΞΌs. Uye kana huwandu hwemanode cycles huchipfuura, semuenzaniso, 1000 - mushure mekuurayiwa PostgreSQL yakakamurwa "mukati mechokwadi", ipapo kana tichiverenga tichidzoka tinowana iyo yakazara nguva "kumwe pakati pe0.95ms uye 1.05ms". Kana iyo kuverenga ichienda kumamicroseconds, zvakanaka, asi kana yatova [milli] masekondi, iwe unofanirwa kutora ruzivo urwu kana "kusunungura" zviwanikwa kumanodhi e "uyo adya yakawanda sei" chirongwa.
Pfungwa yechipiri, yakanyanya kuoma, kugovera zviwanikwa (iyo mabhafa) pakati pemanodhi ane simba. Izvi zvakatidyira mavhiki maviri ekutanga eiyo prototype pamwe nemamwe mavhiki mana.
Zviri nyore kuwana rudzi urwu rwedambudziko - tinoita CTE uye tinofungidzirwa kuti tinoverenga chimwe chinhu mairi. Muchokwadi, PostgreSQL "yakangwara" uye haiverenge chero chinhu ipapo. Zvadaro tinotora rekodhi yekutanga kubva kwairi, uye kune iyo zana nekutanga kubva kune imwechete CTE.
Isu tinotarisa hurongwa uye tinonzwisisa - zvinoshamisa, isu tine 3 buffers (mapeji edata) "yakadyiwa" muSeq Scan, imwezve muCTE Scan, uye 1 mamwe mune yechipiri CTE Scan. Izvi zvinoreva, kana tikangopfupisa zvese, tichawana 2, asi kubva pahwendefa tinongoverenga 6 chete! CTE Scan haiverenge chero chinhu kubva chero kupi, asi inoshanda yakananga neyekurangarira maitiro. Ndiko kuti, chimwe chinhu chiri pachena chakakanganisika pano!
Muchokwadi, zvinoitika kuti heano ese aya mapeji matatu e data akakumbirwa kubva kuSeq Scan, kutanga 3 akabvunza 1st CTE Scan, uyezve yechipiri, uye 1 mamwe akaverengerwa kwaari. 2 mapeji akaverengwa data, kwete 2.
Uye mufananidzo uyu wakaita kuti tinzwisise kuti kuitwa kwechirongwa hakusisiri muti, asi kungori imwe mhando yeacyclic girafu. Uye isu tine dhizaini rakadai, kuti tinzwisise "zvakabva kupi pakutanga." Ndiko, pano takagadzira CTE kubva pg_class, uye takaikumbira kaviri, uye inenge nguva yedu yese yakashandiswa pabazi patakakumbira iyo 2nd nguva. Zviri pachena kuti kuverenga 101st kupinda kunodhura zvakanyanya pane kungoverenga 1st yekupinda kubva pahwendefa.
Takambotura befu kwekanguva. Vakati: βZvino, Neo, unoziva kung fu! Iye zvino ruzivo rwedu rwave pachiratidziro chako. Zvino unogona kuishandisa."
Log consolidation
Vagadziri vedu 1000 vakatura befu. Asi isu takanzwisisa kuti isu tine mazana e "combat" maseva chete, uye zvese izvi "copy-paste" kune vanogadzira hazvisi nyore. Takaona kuti taifanira kuitora isu pachedu.
Kazhinji, kune yakajairwa module inogona kuunganidza manhamba, zvisinei, inodawo kuisirwa muiyo config - iyi. . Asi haana kutikodzera.
Chekutanga, inopa kumibvunzo yakafanana uchishandisa zvirongwa zvakasiyana mukati medhatabhesi rimwe chete akasiyana QueryIds. Ndiko, kana iwe ukatanga kuita SET search_path = '01'; SELECT * FROM user LIMIT 1;uye ipapo SET search_path = '02'; uye chikumbiro chimwe chete, ipapo nhamba dzemodule iyi dzichava nezvinyorwa zvakasiyana, uye ini handizokwanisi kuunganidza nhamba dzehuwandu zvakananga mumamiriro echikumbiro ichi, pasina kufunga nezvezvirongwa.
Chinhu chechipiri chakatitadzisa kuishandisa ndechekuti kushaya zvirongwa. Ndiko kuti, hapana chirongwa, pane chete chikumbiro pachacho. Isu tinoona izvo zvainonoka, asi isu hatinzwisise chikonzero nei. Uye pano tinodzokera kune dambudziko rekukurumidza kuchinja dataset.
Uye nguva yekupedzisira - kushaya "chokwadi". Ndiko kuti, haugone kugadzirisa imwe muenzaniso yekuitwa kwemubvunzo - hapana, pane chete akaunganidzwa manhamba. Kunyangwe zvichikwanisika kushanda neizvi, zvakangooma zvakanyanya.
Naizvozvo, takasarudza kurwisa copy-paste ndokutanga kunyora muteresi.
Muunganidzi anobatanidza kuburikidza neSSH, anogadzira chinongedzo chakachengeteka kune sevha ine dhatabhesi uchishandisa chitupa, uye tail -F "inonamatira" pairi mufaira regi. Saka muchikamu chino tinowana "girazi" rakakwana refaira rose regi, iyo server inogadzira. Kuremerwa pasevha pachayo ishoma, nekuti isu hapana chatiri kufambisa ipapo, isu tinongoratidzira traffic.
Sezvo takanga tatotanga kunyora interface muNode.js, takaramba tichinyora muunganidzi mairi. Uye tekinoroji iyi yakazviruramisa, nekuti zviri nyore kushandisa JavaScript kushanda ine isina kusimba mameseji data, inova irogi. Uye iyo Node.js zvivakwa pachayo senge backend chikuva inokubvumira kuti ushande zviri nyore uye zviri nyore netiweki yekubatanidza, uye zvechokwadi nechero data hova.
Saizvozvo, isu "tinotambanudza" zvibatanidza zviviri: yekutanga "kuteerera" kune irogi pachayo uye tozvitora isu, uye yechipiri kubvunza nguva nenguva hwaro. "Asi irogi rinoratidza kuti chiratidzo chine oid 123 chakavharwa," asi izvi hazvireve chero chinhu kumugadziri, uye zvingava zvakanaka kubvunza dhatabhesi, "Chii chinonzi OID = 123 zvakadaro?" Uye saka isu nguva nenguva tinobvunza hwaro izvo zvatisati taziva nezvedu.
"Pane chinhu chimwe chete chausina kurangarira, kune mhando yenyuchi dzakaita senzou!.." Takatanga kugadzira system iyi pataida kuongorora masevha gumi. Chinonyanya kukosha mukunzwisisa kwedu, apo mamwe matambudziko akamuka ayo akaoma kubata nawo. Asi mukati mekota yekutanga, takagamuchira zana rekutarisa - nekuti sisitimu yakashanda, munhu wese aiida, munhu wese aive akasununguka.
Zvese izvi zvinoda kuwedzerwa, kuyerera kwedata kwakakura uye kunoshanda. Muchokwadi, izvo zvatinotarisa, zvatinogona kubata nazvo, ndizvo zvatinoshandisa. Isu tinoshandisawo PostgreSQL sekuchengetedza data. Uye hapana chinokurumidza "kudira" data mairi kupfuura mushandisi COPY Kwete izvozvi.
Asi kungo "durura" data haisi iyo tekinoroji yedu. Nekuti kana iwe uine zvingangoita makumi mashanu zvikumbiro pasekondi pane zana maseva, zvino izvi zvinogadzira 50-100GB yematanda pazuva. Nokudaro, taifanira kunyatso "cheka" chigadziko.
Kutanga, takazviita kugovera zuva nezuva, nekuti, kazhinji, hapana anofarira kuwirirana pakati pemazuva. Ndeupi mutsauko wazvinoita izvo zvawaive nazvo nezuro, kana manheru ano waburitsa vhezheni nyowani yekushandisa - uye yatove mamwe manhamba matsva.
Chechipiri, takadzidza (takamanikidzwa) chaizvo, nekukurumidza kunyora uchishandisa COPY. Ndiko kuti, kwete chete COPYnokuti anokurumidza kupfuura INSERT, uye kunyange nekukurumidza.
Pfungwa yechitatu - ndaifanira siya zvinokonzeresa, zvichiteerana, uye makiyi ekunze. Ndiko kuti, isu hatina referensi kuperera zvachose. Nekuti kana iwe uine tafura ine mbiri yeFKs, uye iwe uchiti mune dhatabhesi dhizaini kuti "heino rekodhi rekodhi rinotaurwa neFK, semuenzaniso, kune boka remarekodhi," ipapo paunoiisa, PostgreSQL. hapana chasara kunze kwekuitora nekuzviita nekutendeka SELECT 1 FROM master_fk1_table WHERE ... nechiziviso chauri kuyedza kuisa - kungotarisa kuti rekodhi iriko here, kuti hau "kutyora" Kiyi yekune dzimwe nyika nekuisa kwako.
Panzvimbo pechinyorwa chimwe kutafura inotarirwa uye indekisi dzayo, tinowana betsero yakawedzerwa yokurava kubva pamatafura ose ainoreva. Asi isu hatidi izvi zvachose - basa redu nderekurekodha zvakanyanya sezvinobvira uye nekukurumidza sezvinobvira nemutoro mudiki. Saka FK - pasi!
Pfungwa inotevera ndeye kuunganidza uye hashing. Pakutanga, takaashandisa mudhatabhesi - mushure mezvose, zviri nyore kuti pakarepo, kana rekodhi rasvika, riite mune imwe mhando yehwendefa. "plus one" mukati chaimo. Zvakanaka, zviri nyore, asi zvakaipa zvakafanana - iwe unoisa rekodhi imwe, asi unomanikidzwa kuverenga nekunyora chimwe chinhu kubva kune imwe tafura. Uyezve, haungoverenge nekunyora chete, unozviitawo nguva dzese.
Iye zvino fungidzira kuti une tafura yaunongoverenga nhamba yezvikumbiro zvakapfuura kuburikidza nemugamuchiri chaiwo: +1, +1, +1, ..., +1. Uye iwe, pamusimboti, haudi izvi - zvese zvinogoneka sum mundangariro pamuunganidzi uye tumira kune dhatabhesi mune imwe kuenda +10.
Hongu, kana paine mamwe matambudziko, kutendeseka kwako kunogona "kuparara", asi iyi inyaya isingaite - nekuti iwe une sevha yakajairika, ine bhatiri mune controller, iwe une transaction log, danda pa faira system ... Kazhinji, hazvina kukodzera. Kurasikirwa kwechigadzirwa chaunowana kubva mukumhanyisa zvinokonzeresa/FK hakukodzeri mari yaunotora.
Ndizvo zvakafanana nehashing. Chimwe chikumbiro chinobhururuka kwauri, iwe unoverenga imwe identifier kubva mairi mudhatabhesi, nyora kune dhatabhesi uye wozoudza munhu wese. Zvose zvakanaka kusvikira, panguva yekurekodha, munhu wechipiri anouya kwauri anoda kurekodha chinhu chimwe chete - uye iwe unovharwa, uye izvi zvatove zvakaipa. Naizvozvo, kana iwe uchikwanisa kuendesa chizvarwa chemamwe maID kune mutengi (ane dhatabhesi), zviri nani kuita izvi.
Yakanga yakangonaka kuti isu tishandise MD5 kubva pane zvinyorwa - chikumbiro, chirongwa, template, ... Isu tinoiverenga padivi rekuunganidza, uye "dururira" ID yakagadzirira-yakagadzirwa mudhatabhesi. Hurefu hweMD5 uye kupatsanurwa kwezuva nezuva kunotitendera kuti tisanetseke nekukonana kunogona kuitika.
Asi kuti tinyore zvese izvi nekukasira, taifanira kushandura nzira yekurekodha pachayo.
Iwe unowanzo nyora sei data? Tine imwe mhando yedataset, tinoiparadzanisa mumatafura akati wandei, uye tozoIKOPA - kutanga mune yekutanga, yozoenda kune yechipiri, yechitatu ... Hazvina kunaka, nekuti isu tinoita senge tiri kunyora imwe data rwizi mumatanho matatu. zvakatevedzana. Zvisina kunaka. Inogona kuitwa nokukurumidza here? Unogona!
Kuti uite izvi, zvakakwana kungoora izvi zvinoyerera zvakafanana kune mumwe nemumwe. Zvinoitika kuti tine zvikanganiso, zvikumbiro, matemplate, zvivharo, ... kubhururuka neshinda dzakasiyana - uye tinonyora zvese zvakafanana. Zvakwana izvi chengeta COPY chiteshi nguva dzose yakavhurika kune yega yega tafura inotarirwa.
Ndiko kuti, pamuunganidzi panogara paine rukova, iyo yandinogona kunyora data randinoda. Asi kuti dhatabhesi rione iyi data, uye mumwe munhu asabatike akamirira kuti iyi data inyorwe, COPY inofanira kuvhiringwa pane dzimwe nguva. Kwatiri, iyo yakanyanya kushanda nguva yaive ye100ms - tinoivhara uye nekukasira kuivhura zvakare kune imwechete tafura. Uye kana isu tisina kukwana kwekuyerera kumwe panguva yemamwe mapikisi, saka tinoita kubatanidza kusvika kune imwe muganhu.
Pamusoro pezvo, isu takaona kuti kune yakadaro mitoro mbiri, chero kuunganidzwa, kana marekodhi akaunganidzwa mumabheti, kwakaipa. Classic zvakaipa ndizvo INSERT ... VALUES uye mamwe 1000 marekodhi. Nekuti panguva iyoyo iwe une peak yekunyora pane midhiya, uye vamwe vese vanoedza kunyora chimwe chinhu ku diski vachange vakamirira.
Kuti ubvise zvisizvo zvakadaro, usango unganidza chero chinhu, usaite buffer zvachose. Uye kana kubhuroka ku diski kuchiitika (nerombo rakanaka, iyo Stream API muNode.js inobvumidza iwe kuti uzive) - kumisikidza kubatana uku. Paunogamuchira chiitiko chekuti ndechemahara zvakare, chinyorere kubva pamutsetse wakaunganidzwa. Uye ichiri kubatikana, tora inotevera yemahara kubva padziva uye nyora kwairi.
Tisati tasuma nzira iyi yekurekodha data, takanga tine angangoita 4K kunyora ops, uye nenzira iyi takadzikisa mutoro ne4 nguva. Ikozvino vakura dzimwe nguva 6 nekuda kwezvitsva zvakatariswa dhatabhesi - kusvika ku100MB/s. Uye zvino tinochengeta matanda kwemwedzi mitatu yekupedzisira muhuwandu hwe3-10TB, tichitarisira kuti mumwedzi mitatu chete chero mugadziri achakwanisa kugadzirisa chero dambudziko.
Tinonzwisisa matambudziko
Asi kungounganidza iyi data yese yakanaka, inobatsira, yakakosha, asi haina kukwana - inoda kunzwisiswa. Nekuti aya mamirioni ezvirongwa zvakasiyana pazuva.
Asi mamiriyoni haakwanisike, tinofanira kutanga taita "zviduku". Uye, kutanga kwezvose, iwe unofanirwa kusarudza kuti ucharonga sei ichi "chidiki" chinhu.
Takaona zvinhu zvitatu zvakakosha:
- uyo akatumira chikumbiro ichi
Ndokunge, kubva kunei application "yakasvika": web interface, backend, kubhadhara system kana chimwe chinhu. - apo zvakaitika
Pane chaiyo server? Nekuti kana uine maseva akati wandei pasi pechishandiso chimwe chete, uye kamwe kamwe "inoenda benzi" (nekuti "dhisiki yakaora", "ndangariro yakadonhedza", rimwe dambudziko), saka unofanirwa kunyatsogadzirisa sevha. - sei dambudziko rakazviratidza neimwe nzira
Kuti tinzwisise kuti "ndiani" atitumira chikumbiro, tinoshandisa dhizaini - kuseta shanduko yechikamu: SET application_name = '{bl-host}:{bl-method}'; - Isu tinotumira zita reiyo bhizinesi logic host kubva kune iyo chikumbiro chiri kuuya, uye zita renzira kana mashandisirwo ayo akazvitanga.
Mushure mekunge tapfuura "muridzi" wechikumbiro, inofanira kubuda kune logi - nekuda kweizvi tinogadzirisa shanduko. log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Kune avo vanofarira, pamwe zvese zvinorevei. Zvinoitika kuti tinoona murogi:
- Π²ΡΠ΅ΠΌΡ
- process uye transaction identifiers
- zita re database
- IP yemunhu atumira chikumbiro ichi
- uye nzira zita
Ipapo takaona kuti hazvinakidze kutarisa kuwirirana kwechikumbiro chimwe pakati pemaseva akasiyana. Haisi kazhinji kuti une mamiriro apo imwe application inokwira zvakaenzana pano nepapo. Asi kunyangwe zvakangofanana, tarisa chero yeaya maseva.
Saka heino kucheka "one server - rimwe zuva" zvakava zvakakwana kwatiri nokuda kwekuongorora kupi nokupi.
Chikamu chekutanga chekuongorora chakafanana "muenzaniso" - chimiro chakapfupikiswa chekuratidzira kwechirongwa, chakacheneswa pane zvese zviratidzo zvenhamba. Chechipiri chekucheka mashandisirwo kana nzira, uye yechitatu kucheka ndiyo chaiyo chirongwa node iyo yatikonzera matambudziko.
Patakasimuka kubva kune chaiwo mamiriro kuenda kumatemplate, takawana mabhenefiti maviri kamwechete:
- kuderedzwa kwakawanda muhuwandu hwezvinhu zvekuongorora
Isu tinofanirwa kuongorora dambudziko racho kwete nezviuru zvemibvunzo kana zvirongwa, asi neakawanda ematemplate. - nguva
Ndiko kuti, nekupfupisa "chokwadi" mukati mechimwe chikamu, unogona kuratidza chitarisiko chavo mukati mezuva. Uye pano iwe unogona kunzwisisa kuti kana uine imwe mhando yemuenzaniso inoitika, somuenzaniso, kamwe paawa, asi inofanira kuitika kamwe chete pazuva, unofanira kufunga pamusoro pezvakakanganisika - ndiani akazvikonzera uye nei, zvichida zvinofanira kuva pano. handifanire kudaro. Iyi ndiyo imwe nzira isiri yenhamba, yekuona chete, nzira yekuongorora.
Nzira dzakasara dzinobva pane zviratidzo zvatinotora kubva muurongwa: kangani muenzaniso wakadaro wakaitika, nguva yakazara uye yepakati, yakawanda sei data yakaverengwa kubva ku diski, uye yakawanda sei kubva mumusoro ...
Nokuti, semuenzaniso, iwe unouya kune analytics peji yemubati, tarisa - chimwe chinhu chiri kutanga kuverenga zvakanyanya pa diski. Iyo dhisiki pane sevha haigone kuibata - ndiani anoverenga kubva mairi?
Uye iwe unogona kuronga nekoramu chero ipi neipi uye sarudza zvauchabata nazvo izvozvi - mutoro uri pa processor kana dhisiki, kana nhamba yese yezvikumbiro ... yakaburitsa shanduro itsva yeapp.
Uye nekukasira iwe unogona kuona akasiyana maapplication anouya neayo template kubva kuchikumbiro senge SELECT * FROM users WHERE login = 'Vasya'. Frontend, backend, processing... Uye unoshamisika kuti sei kugadzirisa kungaverenga mushandisi kana asingadyidzane naye.
Iyo yakapesana nzira ndeyekuona nekukurumidza kubva mukushandisa zvazvinoita. Semuenzaniso, iyo yekumberi ndeiyi, iyi, iyi, uye ino kamwe paawa (iyo nguva inobatsira). Uye mubvunzo unomuka pakarepo: zvinoita sekunge harisi basa rekumberi kuita chimwe chinhu kamwe paawa ...
Pashure penguva yakati, takaona kuti takanga tisina kuunganidzwa nhamba nemanodhi ehurongwa. Isu takazviparadzanisa kubva kuzvirongwa chete idzo node dzinoita chimwe chinhu nedata yematafura pachawo (verenga / nyora iwo nendekisi kana kwete). Muchokwadi, chikamu chimwe chete chinowedzerwa maererano nemufananidzo wapfuura - marekodhi mangani atiunzira node iyi?, uye vangani vakaraswa (Rows Removed by Filter).
Iwe hauna indekisi yakakodzera pandiro, iwe unoita chikumbiro kwairi, inobhururuka kupfuura index, inowira muSeq Scan ... iwe wakasefa zvinyorwa zvese kunze kweimwe. Sei uchida 100M marekodhi akasefa pazuva? Hazvisi nani kupeta index?
Taongorora ese zvirongwa node node, takaona kuti kune zvimwe zvimiro zvakajairwa muzvirongwa izvo zvinonyanya kutaridzika kunyumwira. Uye zvingava zvakanaka kuudza mugadziri kuti: "Shamwari, pano iwe unotanga kuverenga nendekisi, wozoronga, wozocheka" - sekutonga, pane rekodhi rimwe chete.
Wese akanyora mibvunzo angangove akasangana neiyi pateni: "Ndipe odha yekupedzisira yeVasya, zuva rayo." Uye kana usina index nedeti, kana pasina zuva mune index yawakashandisa, ipapo nyatsotsika βrekeβ chairo .
Asi isu tinoziva kuti iyi i "rake" - saka wadii kuudza mugadziri wezvaanofanira kuita nekukurumidza. Saizvozvo, pakuvhura hurongwa izvozvi, mugadziri wedu anobva aona mufananidzo wakanaka une matipi, kwavanobva vamuudza kuti: "Une matambudziko pano neapo, asi anogadziriswa uku uye uko."
Nekuda kweizvozvo, huwandu hweruzivo hwaidiwa kugadzirisa matambudziko pakutanga uye ikozvino hwaderera zvakanyanya. Urwu ndirwo rudzi rwechombo chatinacho.
Source: www.habr.com