Sawubona, izakhamuzi zaseKhabrovsk. Amakilasi eqembu lokuqala lesifundo aqala namuhla . Mayelana nalokhu, sithanda ukukutshela mayelana nokuthi i-webinar evuliwe kulesi sifundo yenzeka kanjani.
В sikhulume ngezinselelo zolwazi lwe-SQL ezibhekana nazo ngesikhathi samafu neKubernetes. Ngaso leso sikhathi, sibheke ukuthi isizindalwazi se-SQL sijwayela kanjani futhi siguquke ngaphansi kwethonya lalezi zinselele.
I-webinar yabanjwa , I-Google Cloud Practice Delivery Manager ku-EPAM Systems.
Lapho izihlahla zisezincane...
Okokuqala, ake sikhumbule ukuthi ukukhethwa kwe-DBMS kwaqala kanjani ekupheleni kwekhulu leminyaka elidlule. Kodwa-ke, lokhu ngeke kube nzima, ngoba ukukhethwa kwe-DBMS ngalezo zinsuku kwaqala futhi kwaphela Oracle.
Ngasekupheleni kweminyaka yama-90s nasekuqaleni kwawo-2, kwakungekho ukukhetha uma kuziwa kumininingwane yolwazi yezimboni. Yebo, bekukhona i-IBM DBXNUMX, i-Sybase nezinye izingosi zolwazi ezafika zadlula, kodwa ngokujwayelekile bezingabonakali kangako uma kuqhathaniswa nesizinda se-Oracle. Ngakho, amakhono onjiniyela bangalezo zikhathi ayeboshelwe ngandlela-thile ekukhetheni okwakukhona.
I-Oracle DBA bekufanele ikwazi uku:
- faka i-Oracle Server kusuka kukhithi yokusabalalisa;
- lungiselela i-Oracle Server:
- init.ora;
- umlaleli.ora;
- dala:
- izindawo zetafula;
- izikimu;
- abasebenzisi;
- yenza isipele futhi ubuyisele;
- ukwenza ukuqapha;
- bhekana nezicelo ezingafanelekile.
Ngaso leso sikhathi, kwakungekho mfuneko ekhethekile evela ku-Oracle DBA:
- ukwazi ukukhetha i-DBMS efanele noma obunye ubuchwepheshe bokugcina nokucubungula idatha;
- hlinzeka ngokutholakala okuphezulu kanye nokulinganisa okuvundlile (lokhu bekungeyona inkinga ye-DBA ngaso sonke isikhathi);
- ulwazi oluhle ngendawo yesifundo, ingqalasizinda, izakhiwo zohlelo lokusebenza, i-OS;
- layisha futhi ukhiphe idatha, hambisa idatha phakathi kwama-DBMS ahlukene.
Ngokuvamile, uma sikhuluma ngokukhetha ngalezo zinsuku, kufana nokukhetha esitolo saseSoviet ngasekupheleni kweminyaka yama-80s:
Isikhathi sethu
Kusukela ngaleso sikhathi, yebo, izihlahla ziye zakhula, umhlaba ushintshile, futhi kwaba into enjengale:
Imakethe ye-DBMS nayo isishintshile, njengoba kungabonakala ngokucacile embikweni wakamuva ovela ku-Gartner:
Futhi lapha kufanele kuqashelwe ukuthi amafu, okuthandwa kwawo kukhula, athathe indawo yawo. Uma sifunda umbiko ofanayo we-Gartner, sizobona iziphetho ezilandelayo:
- Amakhasimende amaningi asendleleni yokuhambisa izinhlelo zokusebenza emafini.
- Ubuchwepheshe obusha buqala ukuvela efwini futhi akulona iqiniso ukuthi bayoke bathuthele kwingqalasizinda engeyona eyefu.
- Imodeli yamanani okukhokha njengoba-uhamba isiphenduke insakavukela. Wonke umuntu ufuna ukukhokhela lokho akusebenzisayo kuphela, futhi lokhu akuyona ngisho inkambiso, kodwa isitatimende seqiniso nje.
Yini manje?
Namuhla sonke sisefwini. Futhi imibuzo esiphakamayo iyimibuzo yokuzikhethela. Futhi inkulu, noma sikhuluma kuphela ngokukhethwa kobuchwepheshe be-DBMS ngefomethi yasendaweni. Siphinde sibe nezinsizakalo eziphethwe kanye ne-SaaS. Ngakho, ukukhetha kuba nzima kakhulu njalo ngonyaka.
Kanye nemibuzo yokuzikhethela, kukhona futhi izici ezikhawulelayo:
- Intengo. Ubuchwepheshe obuningi busabiza imali;
- amakhono. Uma sikhuluma ngesofthiwe yamahhala, khona-ke umbuzo wamakhono uphakama, njengoba isofthiwe yamahhala idinga ikhono elanele kubantu abayisebenzisayo nabayisebenzisayo;
- ukusebenza. Akuzona zonke izinsizakalo ezitholakala efwini futhi ezakhiwe, ngisho, ngisho naku-Postgres efanayo, ezinezici ezifanayo ne-Postgres On-premises. Lesi yisici esibalulekile esidinga ukwaziwa futhi siqondwe. Ngaphezu kwalokho, lesi sici siba sibaluleke kakhulu kunolwazi lwamakhono athile afihliwe we-DBMS eyodwa.
Yini elindelwe manje ku-DA/DE:
- ukuqonda okuhle kwendawo yesifundo kanye nesakhiwo sohlelo lokusebenza;
- ikhono lokukhetha kahle ubuchwepheshe be-DBMS obufanele ngokucabangela umsebenzi owenziwayo;
- ikhono lokukhetha indlela efanelekile yokusebenzisa ubuchwepheshe obukhethiwe kumongo wemikhawulo ekhona;
- ikhono lokwenza ukudluliswa kwedatha nokuthutha;
- ikhono lokusebenzisa nokusebenzisa izixazululo ezikhethiwe.
Ngezansi isibonelo ngokusekelwe ku-GCP ibonisa ukuthi ukukhetha kobuchwepheshe obubodwa noma obunye bokusebenza ngedatha kusebenza kanjani kuye ngesakhiwo sayo:
Sicela uqaphele ukuthi i-PostgreSQL ayifakiwe ku-schema, futhi lokhu kungenxa yokuthi ifihliwe ngaphansi kwamagama I-Cloud SQL. Futhi uma sifika ku-Cloud SQL, sidinga ukwenza ukukhetha futhi:
Kumele kuqashelwe ukuthi le nketho ayihlali icacile, ngakho-ke abathuthukisi bohlelo lokusebenza bavame ukuqondiswa yi-intuition.
Inani:
- Lapho uqhubeka, umbuzo wokukhetha uba ngokucindezela kakhulu. Futhi ngisho noma ubheka kuphela i-GCP, izinsizakalo eziphethwe kanye ne-SaaS, khona-ke okunye okushiwo nge-RDBMS kuvela kuphela esinyathelweni sesi-4 (futhi kukhona i-Spanner eseduze). Futhi, ukukhetha kwe-PostgreSQL kuvela esinyathelweni sesi-5, futhi eduze kwayo kukhona ne-MySQL ne-SQL Server, okungukuthi. kuningi kwakho konke, kodwa kufanele ukhethe.
- Akumelwe sikhohlwe ngemingcele ngokumelene nesizinda sezilingo. Ngokuyisisekelo wonke umuntu ufuna i-Spanner, kodwa iyabiza. Njengomphumela, isicelo esijwayelekile sibukeka kanjena: "Sicela usenze i-Spanner kodwa ngentengo ye-Cloud SQL, ningochwepheshe!"
Kufanele senzeni?
Ngaphandle kokuthi siyiqiniso eliphelele, ake sisho lokhu okulandelayo:
Kudingeka sishintshe indlela yethu yokufunda:
- asikho isidingo sokufundisa indlela ama-DBA ayefundiswa ngayo ngaphambili;
- ulwazi lomkhiqizo owodwa alusanele;
- kodwa ukwazi inqwaba ezingeni elilodwa akunakwenzeka.
Akufanele wazi kuphela futhi hhayi ukuthi ungakanani umkhiqizo, kodwa:
- ukusetshenziswa kwesimo sokusetshenziswa kwayo;
- izindlela ezahlukene zokuthumela;
- izinzuzo kanye nebubi bendlela ngayinye;
- imikhiqizo efanayo nehlukile yokwenza ukukhetha unolwazi futhi ofanele futhi hhayi njalo uvuna umkhiqizo ojwayelekile.
Kudingeka futhi ukwazi ukuthutha idatha futhi uqonde izimiso eziyisisekelo zokuhlanganisa ne-ETL.
Icala langempela
Esikhathini esidlule, bekudingeka ukudala i-backend yesicelo seselula. Ngesikhathi umsebenzi uqala kuwo, i-backend yayisivele ithuthukisiwe futhi isilungele ukuqaliswa, futhi ithimba lezokuthuthukiswa lichithe cishe iminyaka emibili kule phrojekthi. Kwasethwa imisebenzi elandelayo:
- ukwakha i-CI/CD;
- buyekeza izakhiwo;
- konke kusebenze.
Uhlelo lokusebenza ngokwalo bekungama-microservices, futhi ikhodi ye-Python/Django yasungulwa kusukela ekuqaleni futhi ngokuqondile ku-GCP. Mayelana nezithameli eziqondiwe, kwacatshangwa ukuthi kuzoba nezifunda ezimbili - i-US ne-EU, futhi ithrafikhi yasatshalaliswa nge-balancer ye-Global Load. Yonke Imithwalo Yomsebenzi kanye nomsebenzi wokubala usebenze ku-Google Kubernetes Engine.
Mayelana nedatha, bekunezinhlaka ezi-3:
- Isitoreji Samafu;
- Isitolo sedatha;
- Cloud SQL (PostgreSQL).
Umuntu angase azibuze ukuthi kungani kukhethwe i-Cloud SQL? Ukukhuluma iqiniso, umbuzo onjalo ubangele uhlobo oluthile lokuphumula okungajwayelekile eminyakeni yamuva - kunomuzwa wokuthi abantu baye baba namahloni mayelana nemininingwane yolwazi, kodwa nokho bayaqhubeka beyisebenzisa ngenkuthalo ;-).
Ngokuqondene necala lethu, i-Cloud SQL yakhethwa ngenxa yezizathu ezilandelayo:
- Njengoba kushiwo, uhlelo lokusebenza lwakhiwe kusetshenziswa i-Django, futhi lunemodeli yokuqopha idatha eqhubekayo ukusuka kusizindalwazi se-SQL kuya ezintweni zePython (Django ORM).
- Uhlaka ngokwalo lusekele uhlu olunomkhawulo wama-DBMS:
- I-PostgreSQL;
- UMariaDB;
- I-MySQL;
- I-Oracle;
- I-SQLite.
Ngakho-ke, i-PostgreSQL yakhethwa kulolu hlu kunalokho ngendlela enembile (kahle, akuyona i-Oracle ukukhetha, ngempela).
Yini ebingekho:
- isicelo sasetshenziswa ezifundeni ezi-2 kuphela, kwathi esesithathu savela ezinhlelweni (e-Asia);
- Isizindalwazi sasisendaweni yaseNyakatho Melika (Iowa);
- ngasohlangothini lwekhasimende kwakukhona ukukhathazeka mayelana okungenzeka ukubambezeleka kokufinyelela kusuka eYurophu nase-Asia kanye iziphazamiso enkonzweni uma kwenzeka i-DBMS ingasebenzi.
Naphezu kweqiniso lokuthi i-Django ngokwayo ingasebenza nezinqolobane eziningana ngokuhambisana futhi ihlukanise ngokufunda nokubhala, kwakungekho ukubhala okungaka kuhlelo lokusebenza (ngaphezulu kuka-90% kufundwa). Futhi ngokujwayelekile, futhi ngokujwayelekile, uma kungenzeka ukwenza i-replica efundwayo yesisekelo esikhulu eYurophu nase-Asia, lokhu kungaba yisixazululo sokuvumelana. Awu, yini eyinkimbinkimbi ngakho?
Ubunzima ukuthi ikhasimende belingafuni ukuyeka ukusebenzisa amasevisi aphethwe kanye ne-Cloud SQL. Futhi amandla e-Cloud SQL okwamanje anomkhawulo. I-Cloud SQL isekela ukutholakala Okuphezulu (HA) kanye ne-Read Replica (RR), kodwa i-RR efanayo isekelwa endaweni eyodwa kuphela. Ngemva kokudala isizindalwazi esifundeni saseMelika, awukwazi ukwenza ikhophi efundwayo esifundeni saseYurophu usebenzisa i-Cloud SQL, nakuba i-Postgres ngokwayo ingakuvimbeli ekwenzeni lokhu. Ukuxhumana nabasebenzi bakwa-Google akuzange kuholele ndawo futhi kwaphetha ngezithembiso ngendlela yokuthi “siyazi inkinga futhi siyayilungisa, ngolunye usuku udaba luzoxazululeka.”
Uma sibala amakhono e-Cloud SQL kafushane, izobukeka kanjena:
1. Ukutholakala okuphezulu (HA):
- ngaphakathi kwesifunda esisodwa;
- ngokuphindaphinda kwediski;
- Izinjini ze-PostgreSQL azisetshenziswa;
- othomathikhi kanye nokulawula okwenziwa ngesandla kungenzeka - failover / failback;
- Uma ushintsha, i-DBMS ayitholakali imizuzu embalwa.
2. Funda iReplica (RR):
- ngaphakathi kwesifunda esisodwa;
- okubekwe eceleni okushisayo;
- Ukuphindaphinda kokusakaza kwe-PostgreSQL.
Ngaphezu kwalokho, njengoba kuyisiko, lapho ukhetha ubuchwepheshe uhlala ubhekene nothile imikhawulo:
- ikhasimende alizange lifune ukudala izinhlangano futhi lisebenzise i-IaaS, ngaphandle kwe-GKE;
- ikhasimende ngeke lithande ukuphakela i-PostgreSQL/MySQL;
- Hhayi-ke, ngokuvamile, i-Google Spanner ibizofaneleka impela ukube bekungeyona intengo yayo, nokho, i-Django ORM ayikwazi ukusebenza nayo, kepha kuyinto enhle.
Ngokucabangela isimo, ikhasimende lithole umbuzo wokulandelela: "Ungenza into efanayo ukuze kufane ne-Google Spanner, kodwa futhi isebenze ne-Django ORM?"
Inketho yesixazululo engunombolo 0
Into yokuqala efike engqondweni:
- hlala ngaphakathi kwe-CloudSQL;
- ngeke kube nokuphindaphinda okwakhelwe ngaphakathi phakathi kwezifunda nganoma yiluphi uhlobo;
- zama ukunamathisela ikhophi ku-Cloud SQL ekhona nge-PostgreSQL;
- vula isenzakalo se-PostgreSQL kwenye indawo futhi ngandlela thile, kodwa okungenani ungathinti inkosi.
Maye, kwavela ukuthi lokhu akunakwenziwa, ngoba akukho ukufinyelela kumsingathi (kuphrojekthi ehlukile ngokuphelele) - pg_hba nokunye, futhi akukho ukufinyelela ngaphansi kwe-superuser.
Inketho yesixazululo engunombolo 1
Ngemva kokucabangisisa okwengeziwe nokucabangela izimo zangaphambilini, isitimela somcabango sashintsha ngandlela-thile:
- Sisazama ukuhlala ngaphakathi kwe-CloudSQL, kodwa sishintshela ku-MySQL, ngoba i-Cloud SQL ye-MySQL inomphathi wangaphandle, othi:
- i-proxy ye-MySQL yangaphandle;
- kubukeka njengesibonelo se-MySQL;
- yakhelwe ukuthutha idatha esuka kwamanye amafu noma Ezindaweni.
Njengoba ukusetha ukuphindaphinda kwe-MySQL akudingi ukufinyelela kumsingathi, ngokomthetho konke kwasebenza, kodwa bekungazinzile futhi kungalungile. Futhi lapho siqhubeka, kwethusa ngokuphelele, ngoba sahambisa sonke isakhiwo nge-terraform, futhi kungazelelwe kwavela ukuthi inkosi yangaphandle ayizange isekelwe yi-terraform. Yebo, i-Google ine-CLI, kodwa ngesizathu esithile yonke into ibisebenza lapha njalo - ngezinye izikhathi iyadalwa, ngezinye izikhathi ayidalwa. Mhlawumbe ngenxa yokuthi i-CLI yasungulwa ukuze kuthuthwe idatha yangaphandle, hhayi ukuphindaphinda.
Empeleni, ngalesi sikhathi kwacaca ukuthi i-Cloud SQL ayifaneleki nhlobo. Njengoba besho, senza konke esasingakwenza.
Inketho yesixazululo engunombolo 2
Njengoba kungenzeki ukuhlala ngaphakathi kohlaka lwe-Cloud SQL, sizamile ukwenza izimfuneko zesixazululo sokuyekethisa. Izidingo zibe yilezi ezilandelayo:
- ukusebenza ku-Kubernetes, ukusetshenziswa okuphezulu kwezinsiza namakhono e-Kubernetes (DCS, ...) kanye ne-GCP (LB, ...);
- ukuntuleka kwe-ballast kusuka kunqwaba yezinto ezingadingekile efwini njenge-HA proxy;
- ikhono lokusebenzisa i-PostgreSQL noma i-MySQL esifundeni esikhulu se-HA; kwezinye izifunda - i-HA evela ku-RR yesifunda esikhulu kanye nekhophi yayo (yokwethembeka);
- multi master (bengingafuni ukuxhumana naye, kodwa bekungabalulekile kakhulu)
.
Ngenxa yalezi zimfuno, pI-DBMS efanelekile nezinketho zokubopha:
- I-MySQL Galera;
- I-CockroachDB;
- Amathuluzi we-PostgreSQL
:
- i-pgpool-II;
— Patroni.
I-MySQL Galera
Ubuchwepheshe be-MySQL Galera bathuthukiswa yi-Codership futhi iyi-plugin ye-InnoDB. Okukhethekile:
- amakhosi amaningi;
- ukuphindaphinda okuhambisanayo;
- ukufunda kunoma iyiphi i-node;
- ukuqopha kunoma iyiphi i-node;
- umshini we-HA owakhelwe ngaphakathi;
- Kukhona ishadi le-Helm elivela ku-Bitnami.
IphelaDB
Ngokusho kwencazelo, into iyibhomu ngokuphelele futhi iphrojekthi yomthombo ovulekile ebhalwe ku-Go. Umbambiqhaza omkhulu i-Cockroach Labs (eyasungulwa abantu abavela kwa-Google). Le DBMS ehlobene ekuqaleni yayiklanyelwe ukuthi isatshalaliswe (ngokukalwa okuvundlile ngaphandle kwebhokisi) futhi ibekezelela amaphutha. Ababhali bayo benkampani baveze inhloso “yokuhlanganisa ukunotha kokusebenza kwe-SQL nokufinyeleleka okuvundlile okujwayele izixazululo ze-NoSQL.”
Ibhonasi enhle ukusekelwa kwephrothokholi yokuxhuma kwe-post-gress.
I-Pgpool
Lesi isengezo ku-PostgreSQL, empeleni, ibhizinisi elisha elithatha konke ukuxhumana futhi likucubungule. Ine-balancer yomthwalo wayo kanye nomhlanganisi, onelayisensi ngaphansi kwelayisensi ye-BSD. Inikeza amathuba amaningi, kodwa ibukeka isabisa, ngoba ukuba khona kwebhizinisi elisha kungase kube umthombo wezinye izigigaba ezengeziwe.
Patroni
Lena into yokugcina amehlo ami awela kuyo, futhi, njengoba kwavela, hhayi ngeze. I-Patroni iyinsiza yomthombo ovulekile, okuyidaemon yePython ekuvumela ukuthi ugcine ngokuzenzakalelayo amaqoqo e-PostgreSQL anezinhlobo ezahlukene zokuphindaphinda nokushintsha indima okuzenzakalelayo. Le nto yabonakala ithakazelisa kakhulu, ngoba ihlangana kahle ne-cuber futhi ayethuli noma yiziphi izinhlangano ezintsha.
Ukhetheni ekugcineni?
Ukukhetha bekungelula:
- IphelaDB - umlilo, kodwa kumnyama;
- I-MySQL Galera - futhi akukubi, isetshenziswa ezindaweni eziningi, kodwa MySQL;
- I-Pgpool - izinhlangano eziningi ezingadingekile, ukuhlanganiswa kwefu kanye nama-K8;
- Patroni - ukuhlanganiswa okuhle kakhulu nama-K8, azikho izinhlaka ezingadingekile, kuhlangana kahle ne-GCP LB.
Ngakho, ukukhetha kwawela kuPatroni.
okutholakele
Isikhathi sokuhlanganisa kafushane. Yebo, umhlaba wengqalasizinda ye-IT ushintshe kakhulu, futhi lesi yisiqalo nje. Futhi uma ngaphambi kokuba amafu abe olunye uhlobo lwengqalasizinda, manje konke kuhlukile. Ngaphezu kwalokho, izinto ezintsha emafwini zihlala zivela, zizovela futhi, mhlawumbe, zizovela kuphela emafwini futhi kuphela lapho, ngemizamo yokuqalisa, zizodluliselwa ku-On-premises.
Ngokuqondene ne-SQL, i-SQL izophila. Lokhu kusho ukuthi udinga ukwazi i-PostgreSQL ne-MySQL futhi ukwazi ukusebenzisana nazo, kodwa okubaluleke nakakhulu ukuthi ukwazi ukuzisebenzisa ngendlela efanele.
Source: www.habr.com