Ang ulat ay naglalahad ng ilang paraan na nagbibigay-daan subaybayan ang pagganap ng mga query sa SQL kapag mayroong milyon-milyong mga ito bawat araw, at mayroong daan-daang sinusubaybayang PostgreSQL server.
Anong mga teknikal na solusyon ang nagpapahintulot sa amin na mahusay na maproseso ang ganoong dami ng impormasyon, at paano nito ginagawang mas madali ang buhay ng isang ordinaryong developer?
Sino ang interesado? pagsusuri ng mga partikular na problema at iba't ibang mga diskarte sa pag-optimize Mga query sa SQL at paglutas ng mga karaniwang problema sa DBA sa PostgreSQL - magagawa mo rin sa paksang ito.
Ang pangalan ko ay Kirill Borovikov, kinakatawan ko . Sa partikular, dalubhasa ako sa pagtatrabaho sa mga database sa aming kumpanya.
Ngayon sasabihin ko sa iyo kung paano namin ino-optimize ang mga query, kapag hindi mo kailangang "paghiwalayin" ang pagganap ng isang query, ngunit lutasin ang problema nang maramihan. Kapag mayroong milyun-milyong mga kahilingan, at kailangan mong maghanap ng ilan mga diskarte sa solusyon itong malaking problema.
Sa pangkalahatan, ang Tensor para sa isang milyon ng aming mga kliyente ay : corporate social network, mga solusyon para sa komunikasyon sa video, para sa panloob at panlabas na daloy ng dokumento, mga sistema ng accounting para sa accounting at mga bodega,... Iyon ay, tulad ng isang "mega-combine" para sa pinagsamang pamamahala ng negosyo, kung saan mayroong higit sa 100 iba't ibang mga panloob na proyekto.
Upang matiyak na lahat sila ay gumagana at umuunlad nang normal, mayroon tayong 10 development center sa buong bansa, na may higit pa sa mga ito 1000 na mga developer.
Nagtatrabaho kami sa PostgreSQL mula noong 2008 at nakaipon ng malaking halaga ng aming pinoproseso - data ng kliyente, istatistika, analytical, data mula sa mga external na sistema ng impormasyon - higit sa 400TB. Mayroong humigit-kumulang 250 server sa produksyon lamang, at sa kabuuan ay may humigit-kumulang 1000 database server na aming sinusubaybayan.
Ang SQL ay isang deklaratibong wika. Inilalarawan mo hindi "kung paano" dapat gumana ang isang bagay, ngunit "ano" ang gusto mong makamit. Mas alam ng DBMS kung paano gumawa ng JOIN - kung paano ikonekta ang iyong mga talahanayan, anong mga kundisyon ang ipapataw, kung ano ang dadaan sa index, kung ano ang hindi...
Ang ilang mga DBMS ay tumatanggap ng mga pahiwatig: "Hindi, ikonekta ang dalawang talahanayan na ito sa isang queue," ngunit hindi ito magagawa ng PostgreSQL. Ito ang sinasadyang posisyon ng mga nangungunang developer: "Mas gugustuhin naming tapusin ang query optimizer kaysa payagan ang mga developer na gumamit ng ilang uri ng mga pahiwatig."
Ngunit, sa kabila ng katotohanan na hindi pinapayagan ng PostgreSQL ang "labas" na kontrolin ang sarili nito, perpektong pinapayagan nito tingnan kung ano ang nangyayari sa loob niyakapag pinatakbo mo ang iyong query, at kung saan ito nagkakaproblema.
Sa pangkalahatan, anong mga klasikong problema ang kadalasang kasama ng isang developer [sa isang DBA]? βDito namin tinupad ang kahilingan, at mabagal ang lahat sa amin, lahat ay nakabitin, may nangyayari... Ilang uri ng gulo!β
Ang mga dahilan ay halos palaging pareho:
- hindi mahusay na algorithm ng query
Developer: βNgayon binibigyan ko siya ng 10 table sa SQL sa pamamagitan ng JOIN...β - at inaasahan na ang kanyang mga kundisyon ay mahimalang mabisang "makakatali" at makukuha niya ang lahat nang mabilis. Ngunit ang mga himala ay hindi nangyayari, at anumang sistema na may ganitong pagkakaiba-iba (10 mga talahanayan sa isang MULA) ay palaging nagbibigay ng ilang uri ng pagkakamali. [] - hindi nauugnay na istatistika
Ang puntong ito ay partikular na may kaugnayan para sa PostgreSQL, kapag ikaw ay "nagbuhos" ng isang malaking dataset sa server, gumawa ng isang kahilingan, at ito ay "sexcanits" sa iyong tablet. Dahil kahapon ay mayroong 10 mga talaan dito, at ngayon ay mayroong 10 milyon, ngunit ang PostgreSQL ay hindi pa alam tungkol dito, at kailangan nating sabihin ito tungkol dito. [] - "plug" sa mga mapagkukunan
Nag-install ka ng malaki at mabigat na na-load na database sa isang mahinang server na walang sapat na disk, memorya, o pagganap ng processor. At iyon lang... Sa isang lugar ay may performance ceiling sa itaas kung saan hindi ka na makatalon. - hinaharang
Ito ay isang mahirap na punto, ngunit ang mga ito ay pinaka-kaugnay para sa iba't ibang pagbabago ng mga query (INSERT, UPDATE, DELETE) - ito ay isang hiwalay na malaking paksa.
Pagkuha ng plano
...At para sa lahat ng iba pa namin kailangan ng plano! Kailangan nating makita kung ano ang nangyayari sa loob ng server.
Ang isang query execution plan para sa PostgreSQL ay isang puno ng query execution algorithm sa text representation. Ito ay tiyak na ang algorithm na, bilang isang resulta ng pagsusuri ng tagaplano, ay natagpuan na ang pinaka-epektibo.
Ang bawat tree node ay isang operasyon: pagkuha ng data mula sa isang talahanayan o index, pagbuo ng isang bitmap, pagsali sa dalawang talahanayan, pagsali, pag-intersecting, o pagbubukod ng mga seleksyon. Ang pagsasagawa ng query ay nagsasangkot ng paglalakad sa mga node ng punong ito.
Upang makuha ang query plan, ang pinakamadaling paraan ay ang pagsasagawa ng statement EXPLAIN. Upang makuha ang lahat ng tunay na katangian, iyon ay, upang aktwal na magsagawa ng isang query sa base - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
Ang masamang bahagi: kapag pinatakbo mo ito, nangyayari ito "dito at ngayon", kaya angkop lamang ito para sa lokal na pag-debug. Kung kukuha ka ng napaka-load na server na nasa ilalim ng malakas na daloy ng mga pagbabago sa data, at makikita mo ang: βOh! Dito mayroon kaming mabagal na pagpapatupadkamping hiling." Kalahating oras, isang oras ang nakalipas - habang tumatakbo ka at kinukuha ang kahilingang ito mula sa mga log, ibinabalik ito sa server, nagbago ang iyong buong dataset at istatistika. Patakbuhin mo ito para i-debug - at mabilis itong tumakbo! At hindi mo maintindihan kung bakit, bakit ito ay dahan dahan
Upang maunawaan kung ano ang eksaktong nangyari sa sandaling ang kahilingan ay naisakatuparan sa server, sumulat ang mga matatalinong tao . Ito ay naroroon sa halos lahat ng pinakakaraniwang distribusyon ng PostgreSQL, at maaari lamang i-activate sa config file.
Kung napagtanto nito na ang ilang kahilingan ay tumatakbo nang mas mahaba kaysa sa limitasyong sinabi mo dito, ginagawa nito "snapshot" ng plano ng kahilingang ito at isinusulat ang mga ito nang magkasama sa log.
Mukhang maayos na ang lahat ngayon, pumunta kami sa troso at tingnan doon... [text footcloth]. Ngunit wala kaming masasabi tungkol dito, maliban sa katotohanan na ito ay isang mahusay na plano dahil tumagal ito ng 11ms upang maisagawa.
Mukhang maayos ang lahat - ngunit walang malinaw kung ano talaga ang nangyari. Bukod sa pangkalahatang oras, wala talaga kaming nakikita. Dahil ang pagtingin sa gayong "tupa" ng payak na teksto ay karaniwang hindi nakikita.
Ngunit kahit na ito ay hindi halata, kahit na ito ay hindi maginhawa, may mga mas pangunahing problema:
- Ang node ay nagpapahiwatig kabuuan ng mga mapagkukunan ng buong subtree sa ilalim niya. Ibig sabihin, hindi mo lang malalaman kung gaano karaming oras ang ginugol sa partikular na Index Scan kung mayroong ilang nested na kondisyon sa ilalim nito. Dapat nating dynamic na tingnan kung mayroong "mga bata" at conditional variable, CTE sa loob - at ibawas ang lahat ng ito "sa ating mga isip".
- Pangalawang punto: ang oras na ipinahiwatig sa node ay single node execution time. Kung ang node na ito ay naisakatuparan bilang isang resulta ng, halimbawa, ang isang loop sa pamamagitan ng talaan ng talahanayan ng ilang beses, ang bilang ng mga loopβmga cycle ng node na itoβay tataas sa plano. Ngunit ang atomic execution time mismo ay nananatiling pareho sa mga tuntunin ng plano. Iyon ay, upang maunawaan kung gaano katagal ginawa ang node na ito sa kabuuan, kailangan mong i-multiply ang isang bagay sa isa pa - muli, "sa iyong ulo."
Sa ganitong mga sitwasyon, unawain ang "Sino ang pinakamahinang link?" halos imposible. Samakatuwid, kahit na ang mga developer mismo ay sumulat sa "manual" na "Ang pag-unawa sa isang plano ay isang sining na dapat matutunan, maranasan...".
Ngunit mayroon kaming 1000 developer, at hindi mo maiparating ang karanasang ito sa bawat isa sa kanila. Ako, ikaw, alam niya, ngunit hindi na alam ng isang tao doon. Marahil ay matututo siya, o maaaring hindi, ngunit kailangan niyang magtrabaho ngayon - at saan niya makukuha ang karanasang ito?
Plano ng visualization
Samakatuwid, napagtanto namin na upang harapin ang mga problemang ito, kailangan namin magandang visualization ng plano.
Una kaming nagpunta sa "sa merkado" - tingnan natin sa Internet upang makita kung ano ang mayroon.
Ngunit lumabas na kakaunti ang medyo "live" na mga solusyon na higit pa o hindi gaanong umuunlad - literal, isa lamang: ni Hubert Lubaczewski. Kapag ipinasok mo ang field na "feed" ng isang text na representasyon ng plano, ipinapakita nito sa iyo ang isang talahanayan na may na-parse na data:
- sariling oras ng pagproseso ng node
- kabuuang oras para sa buong subtree
- bilang ng mga rekord na nakuha na inaasahan ayon sa istatistika
- ang katawan ng node mismo
Ang serbisyong ito ay mayroon ding kakayahang magbahagi ng archive ng mga link. Inihagis mo ang iyong plano doon at sinabing: "Hoy, Vasya, narito ang isang link, may mali doon."
Ngunit mayroon ding maliliit na problema.
Una, isang malaking halaga ng "copy-paste". Kumuha ka ng isang piraso ng log, idikit ito doon, at muli, at muli.
Pangalawa, ang walang pagsusuri sa dami ng data na nabasa β ang parehong mga buffer na nag-output EXPLAIN (ANALYZE, BUFFERS), hindi natin nakikita dito. Hindi niya lang alam kung paano i-disassemble ang mga ito, unawain ang mga ito at makipagtulungan sa kanila. Kapag nagbabasa ka ng maraming data at napagtanto mo na maaari mong mali ang pamamahagi ng disk at memory cache, ang impormasyong ito ay napakahalaga.
Ang ikatlong negatibong punto ay ang mahinang pag-unlad ng proyektong ito. Ang mga commit ay napakaliit, mabuti kung isang beses bawat anim na buwan, at ang code ay nasa Perl.
Ngunit ito ay ang lahat ng "liriko", maaari naming mabuhay sa anumang paraan, ngunit may isang bagay na lubos na nagpapalayo sa amin mula sa serbisyong ito. Ito ay mga error sa pagsusuri ng Common Table Expression (CTE) at iba't ibang mga dynamic na node tulad ng InitPlan/SubPlan.
Kung naniniwala ka sa larawang ito, ang kabuuang oras ng pagpapatupad ng bawat indibidwal na node ay mas malaki kaysa sa kabuuang oras ng pagpapatupad ng buong kahilingan. Ito ay simple - ang oras ng pagbuo ng CTE na ito ay hindi ibinawas sa CTE Scan node. Samakatuwid, hindi na namin alam ang tamang sagot kung gaano katagal ang CTE scan mismo.
Pagkatapos ay napagtanto namin na oras na upang magsulat ng aming sarili - hurray! Sinasabi ng bawat developer: "Ngayon, isusulat namin ang sarili namin, magiging napakadali!"
Kumuha kami ng stack na tipikal para sa mga serbisyo sa web: isang core batay sa Node.js + Express, ginamit ang Bootstrap at D3.js para sa magagandang diagram. At ang aming mga inaasahan ay ganap na nabigyang-katwiran - natanggap namin ang unang prototype sa loob ng 2 linggo:
- custom na parser ng plano
Ibig sabihin, ngayon ay maaari na nating i-parse ang anumang plano mula sa mga nabuo ng PostgreSQL. - tamang pagsusuri ng mga dynamic na node - CTE Scan, InitPlan, SubPlan
- pagsusuri ng pamamahagi ng mga buffer - kung saan ang mga pahina ng data ay binabasa mula sa memorya, kung saan mula sa lokal na cache, kung saan mula sa disk
- nakakuha ng kalinawan
Upang hindi "hukayin" ang lahat ng ito sa log, ngunit upang makita ang "pinakamahinang link" kaagad sa larawan.
May nakuha kaming ganito, kasama ang syntax highlighting. Ngunit kadalasan ang aming mga developer ay hindi na gumagana sa isang kumpletong representasyon ng plano, ngunit sa isang mas maikli. Pagkatapos ng lahat, na-parse na namin ang lahat ng mga numero at inihagis ang mga ito sa kaliwa at kanan, at sa gitna ay iniwan lamang namin ang unang linya, kung anong uri ng node ito: CTE Scan, CTE generation o Seq Scan ayon sa ilang palatandaan.
Ito ang pinaikling representasyon na tinatawag natin template ng plano.
Ano pa ang magiging maginhawa? Magiging maginhawa upang makita kung anong bahagi ng ating kabuuang oras ang inilalaan kung saang node - at "idikit" lang ito sa gilid pie chart.
Itinuro namin ang node at nakita - lumalabas na ang Seq Scan ay tumagal ng mas mababa sa isang-kapat ng kabuuang oras, at ang natitirang 3/4 ay kinuha ng CTE Scan. Horror! Ito ay isang maliit na tala tungkol sa "rate ng apoy" ng CTE Scan kung aktibong ginagamit mo ang mga ito sa iyong mga query. Ang mga ito ay hindi masyadong mabilis - sila ay mas mababa kahit na sa regular na pag-scan sa talahanayan.
Ngunit kadalasan ang gayong mga diagram ay mas kawili-wili, mas kumplikado, kapag agad nating itinuro ang isang segment at nakita, halimbawa, na higit sa kalahati ng oras ang ilang Seq Scan ay "kumain". Bukod dito, mayroong ilang uri ng Filter sa loob, maraming mga rekord ang itinapon ayon dito... Maaari mong direktang ihagis ang larawang ito sa developer at sabihin: "Vasya, lahat ay masama dito para sa iyo! Isipin mo, tingnan mo - may mali!"
Naturally, may ilang "rakes" na kasangkot.
Ang una naming narating ay ang problema sa pag-ikot. Ang oras ng bawat indibidwal na node sa plano ay ipinahiwatig na may katumpakan na 1 ΞΌs. At kapag ang bilang ng mga cycle ng node ay lumampas, halimbawa, 1000 - pagkatapos ng pagpapatupad, hinati ng PostgreSQL "sa loob ng katumpakan", kung gayon kapag nagkalkula pabalik makuha namin ang kabuuang oras "sa isang lugar sa pagitan ng 0.95ms at 1.05ms". Kapag ang bilang ay napunta sa microseconds, ayos lang, ngunit kapag ito ay [milli]segundo na, kailangan mong isaalang-alang ang impormasyong ito kapag "nagkakalag" ng mga mapagkukunan sa mga node ng "sino ang kumonsumo ng magkano" na plano.
Ang pangalawang punto, mas kumplikado, ay ang pamamahagi ng mga mapagkukunan (mga buffer) sa mga dynamic na node. Nagkakahalaga ito sa amin ng unang 2 linggo ng prototype kasama ang isa pang 4 na linggo.
Napakadaling makuha ang ganitong uri ng problema - gumagawa kami ng CTE at parang may binabasa dito. Sa katunayan, ang PostgreSQL ay "matalino" at hindi direktang magbabasa ng kahit ano doon. Pagkatapos ay kinuha namin ang unang tala mula dito, at dito ang isang daan at una mula sa parehong CTE.
Tinitingnan namin ang plano at naiintindihan namin - ito ay kakaiba, mayroon kaming 3 buffer (mga pahina ng data) na "naubos" sa Seq Scan, 1 pa sa CTE Scan, at 2 pa sa pangalawang CTE Scan. Ibig sabihin, kung susumahin lang natin ang lahat, makakakuha tayo ng 6, ngunit mula sa tablet ay 3 lang ang nabasa natin! Ang CTE Scan ay hindi nagbabasa ng kahit ano mula saanman, ngunit gumagana nang direkta sa memorya ng proseso. Ibig sabihin, malinaw na may mali dito!
Sa katunayan, ito ay lumabas na lahat ng 3 pahina ng data na hiniling mula sa Seq Scan, unang 1 ay humiling ng 1st CTE Scan, at pagkatapos ay ang 2nd, at 2 pa ay binasa sa kanya. Ibig sabihin, isang kabuuang 3 pahina ang binasang data, hindi 6.
At ang larawang ito ay humantong sa amin sa pag-unawa na ang pagpapatupad ng isang plano ay hindi na isang puno, ngunit isang uri lamang ng acyclic graph. At nakakuha kami ng isang diagram na tulad nito, upang maunawaan namin "kung saan nanggaling sa unang lugar." Ibig sabihin, dito kami gumawa ng CTE mula sa pg_class, at hiniling ito ng dalawang beses, at halos lahat ng oras namin ay ginugol sa sangay nang hilingin namin ito sa pangalawang pagkakataon. Malinaw na ang pagbabasa ng 2st entry ay mas mahal kaysa sa pagbabasa lamang ng 101st entry mula sa tablet.
Napabuntong hininga kami saglit. Sabi nila: βNgayon, Neo, alam mo na kung fu! Ngayon ang aming karanasan ay nasa iyong screen. Ngayon ay magagamit mo na ito."
Pagsasama-sama ng log
Nakahinga ng maluwag ang aming 1000 developer. Ngunit naunawaan namin na mayroon lamang kaming daan-daang mga server ng "labanan", at ang lahat ng "kopya-paste" na ito sa bahagi ng mga developer ay hindi maginhawa. Napagtanto namin na kailangan naming kolektahin ito mismo.
Sa pangkalahatan, mayroong isang karaniwang module na maaaring mangolekta ng mga istatistika, gayunpaman, kailangan din itong i-activate sa config - ito . Ngunit hindi siya nababagay sa amin.
Una, nagtatalaga ito sa parehong mga query gamit ang iba't ibang mga scheme sa loob ng parehong database iba't ibang QueryId. Iyon ay, kung gagawin mo muna SET search_path = '01'; SELECT * FROM user LIMIT 1;at pagkatapos ay SET search_path = '02'; at ang parehong kahilingan, kung gayon ang mga istatistika ng modyul na ito ay magkakaroon ng magkakaibang mga tala, at hindi ako makakakolekta ng mga pangkalahatang istatistika na partikular sa konteksto ng profile ng kahilingang ito, nang hindi isinasaalang-alang ang mga scheme.
Ang pangalawang punto na pumigil sa amin mula sa paggamit nito ay kakulangan ng mga plano. Ibig sabihin, walang plano, meron lang mismong hiling. Nakikita namin kung ano ang bumagal, ngunit hindi namin maintindihan kung bakit. At dito bumalik tayo sa problema ng isang mabilis na pagbabago ng dataset.
At ang huling sandali - kakulangan ng "katotohanan". Iyon ay, hindi mo matutugunan ang isang partikular na halimbawa ng pagpapatupad ng query - wala, mayroon lamang pinagsama-samang mga istatistika. Bagaman posible na magtrabaho kasama ito, ito ay napakahirap.
Samakatuwid, nagpasya kaming labanan ang copy-paste at nagsimulang magsulat kolektor.
Ang kolektor ay kumokonekta sa pamamagitan ng SSH, nagtatatag ng isang secure na koneksyon sa server gamit ang database gamit ang isang sertipiko, at tail -F "kumakapit" dito sa log file. Kaya sa session na ito nakakakuha kami ng kumpletong "salamin" ng buong log file, na binubuo ng server. Ang load sa server mismo ay minimal, dahil wala kaming na-parse doon, sinasalamin lang namin ang trapiko.
Dahil sinimulan na naming isulat ang interface sa Node.js, ipinagpatuloy namin ang pagsulat ng kolektor dito. At ang teknolohiyang ito ay nabigyang-katwiran ang sarili nito, dahil napaka-maginhawang gumamit ng JavaScript upang gumana sa mahinang na-format na data ng teksto, na siyang log. At ang mismong imprastraktura ng Node.js bilang isang backend na platform ay nagbibigay-daan sa iyo na madali at maginhawang magtrabaho sa mga koneksyon sa network, at sa katunayan sa anumang mga stream ng data.
Alinsunod dito, "inaunat" namin ang dalawang koneksyon: ang una ay "makinig" sa log mismo at dalhin ito sa ating sarili, at ang pangalawa na pana-panahong magtanong sa base. "Ngunit ipinapakita ng log na ang sign na may oid 123 ay naka-block," ngunit wala itong ibig sabihin sa developer, at magandang itanong sa database, "Ano ang OID = 123 pa rin?" At kaya pana-panahon naming tinatanong ang base kung ano ang hindi pa namin alam tungkol sa aming sarili.
"May isang bagay lang na hindi mo isinasaalang-alang, mayroong isang uri ng mga bubuyog na parang elepante!.." Sinimulan naming i-develop ang system na ito noong gusto naming subaybayan ang 10 server. Ang pinaka-kritikal sa aming pag-unawa, kung saan lumitaw ang ilang mga problema na mahirap harapin. Ngunit noong unang quarter, nakatanggap kami ng isang daan para sa pagsubaybay - dahil gumagana ang sistema, gusto ng lahat, komportable ang lahat.
Ang lahat ng ito ay kailangang idagdag, ang daloy ng data ay malaki at aktibo. Sa katunayan, kung ano ang aming sinusubaybayan, kung ano ang maaari naming harapin, ay kung ano ang ginagamit namin. Ginagamit din namin ang PostgreSQL bilang isang imbakan ng data. At walang mas mabilis na "ibuhos" ang data dito kaysa sa operator COPY Hindi pa.
Ngunit ang simpleng "pagbuhos" ng data ay hindi talaga ang aming teknolohiya. Dahil kung mayroon kang humigit-kumulang 50k na kahilingan bawat segundo sa isang daang server, bubuo ito ng 100-150GB ng mga log bawat araw. Samakatuwid, kailangan naming maingat na "i-cut" ang base.
Una, ginawa namin paghahati sa araw, dahil, sa pangkalahatan, walang interesado sa ugnayan sa pagitan ng mga araw. Ano ang pagkakaiba nito kung ano ang mayroon ka kahapon, kung ngayong gabi ay inilunsad mo ang isang bagong bersyon ng application - at mayroon nang ilang mga bagong istatistika.
Pangalawa, natuto tayo (napilitan) napaka, napakabilis na magsulat gamit COPY. Ibig sabihin, hindi lang COPYdahil mas mabilis siya kaysa INSERT, at mas mabilis pa.
Ang ikatlong punto - kailangan kong gawin abandunahin ang mga trigger, ayon sa pagkakabanggit, at mga foreign key. Ibig sabihin, wala kaming referential integrity sa lahat. Dahil kung mayroon kang isang talahanayan na mayroong isang pares ng mga FK, at sasabihin mo sa istraktura ng database na "narito ang isang talaan ng log na isinangguni ng FK, halimbawa, sa isang pangkat ng mga talaan," pagkatapos ay kapag ipinasok mo ito, ang PostgreSQL wala nang natitira kundi kung paano ito kunin at gawin ng tapat SELECT 1 FROM master_fk1_table WHERE ... gamit ang identifier na sinusubukan mong ipasok - para lang matiyak na ang record na ito ay naroroon, na hindi mo "masira" ang Foreign Key na ito sa iyong pagpapasok.
Sa halip na isang tala sa target na talahanayan at mga index nito, makukuha namin ang karagdagang benepisyo ng pagbabasa mula sa lahat ng mga talahanayan na tinutukoy nito. Ngunit hindi namin ito kailangan - ang aming gawain ay mag-record hangga't maaari at sa lalong madaling panahon na may pinakamaliit na pag-load. Kaya FK - pababa!
Ang susunod na punto ay ang pagsasama-sama at pag-hash. Sa una, ipinatupad namin ang mga ito sa database - pagkatapos ng lahat, ito ay maginhawa upang kaagad, kapag may dumating na tala, gawin ito sa ilang uri ng tablet "plus one" sa mismong trigger. Well, ito ay maginhawa, ngunit ang parehong masamang bagay - nagpasok ka ng isang tala, ngunit napipilitang magbasa at magsulat ng ibang bagay mula sa isa pang talahanayan. Bukod dito, hindi ka lang nagbabasa at nagsusulat, ginagawa mo rin ito sa bawat oras.
Ngayon isipin na mayroon kang isang talahanayan kung saan binibilang mo lang ang bilang ng mga kahilingan na dumaan sa isang partikular na host: +1, +1, +1, ..., +1. At ikaw, sa prinsipyo, ay hindi kailangan nito - posible ang lahat kabuuan sa memorya sa kolektor at ipadala sa database nang sabay-sabay +10.
Oo, sa kaso ng ilang mga problema, ang iyong lohikal na integridad ay maaaring "mabagsak", ngunit ito ay isang halos hindi makatotohanang kaso - dahil mayroon kang isang normal na server, mayroon itong baterya sa controller, mayroon kang isang log ng transaksyon, isang log sa file system... Sa pangkalahatan, hindi ito katumbas ng halaga. Ang pagkawala ng produktibidad na nakukuha mo mula sa pagpapatakbo ng mga trigger/FK ay hindi katumbas ng gastos na iyong natamo.
Ito ay pareho sa hashing. Ang isang tiyak na kahilingan ay lilipad sa iyo, kinakalkula mo ang isang tiyak na identifier mula dito sa database, isulat ito sa database at pagkatapos ay sabihin ito sa lahat. Maayos ang lahat hanggang, sa oras ng pagre-record, may darating sa iyo na pangalawang tao na gustong i-record ang parehong bagay - at ma-block ka, at masama na ito. Samakatuwid, kung maaari mong ilipat ang henerasyon ng ilang mga ID sa kliyente (kamag-anak sa database), mas mahusay na gawin ito.
Perpekto lang para sa amin na gumamit ng MD5 mula sa teksto - kahilingan, plano, template,... Kinakalkula namin ito sa panig ng kolektor, at "ibuhos" ang handa na ID sa database. Ang haba ng MD5 at pang-araw-araw na partitioning ay nagbibigay-daan sa amin na huwag mag-alala tungkol sa mga posibleng banggaan.
Ngunit upang maitala ang lahat ng ito nang mabilis, kailangan naming baguhin ang mismong pamamaraan ng pag-record.
Paano mo karaniwang isinusulat ang data? Mayroon kaming ilang uri ng dataset, hinati namin ito sa ilang mga talahanayan, at pagkatapos ay Kopyahin ito - una sa una, pagkatapos ay sa pangalawa, sa pangatlo... Ito ay hindi maginhawa, dahil tila nagsusulat kami ng isang stream ng data sa tatlong hakbang. sunud-sunod. Hindi kanais-nais. Maaari ba itong gawin nang mas mabilis? Pwede!
Upang gawin ito, sapat lamang na mabulok ang mga daloy na ito nang kahanay sa bawat isa. Lumalabas na mayroon kaming mga error, kahilingan, template, blocking, ... lumilipad sa magkahiwalay na mga thread - at isinulat namin ang lahat ng ito nang magkatulad. Sapat na para dito panatilihing palaging bukas ang isang COPY channel para sa bawat indibidwal na target na talahanayan.
Ibig sabihin, sa kolektor laging may batis, kung saan maaari kong isulat ang data na kailangan ko. Ngunit upang makita ng database ang data na ito, at ang isang tao ay hindi makaalis sa paghihintay na maisulat ang data na ito, Dapat maputol ang COPY sa ilang partikular na agwat. Para sa amin, ang pinakamabisang panahon ay humigit-kumulang 100ms - isinara namin ito at agad na binuksan muli sa parehong mesa. At kung wala kaming sapat na isang daloy sa ilang mga peak, pagkatapos ay gagawin namin ang pooling hanggang sa isang tiyak na limitasyon.
Bukod pa rito, nalaman namin na para sa naturang load profile, ang anumang pagsasama-sama, kapag ang mga talaan ay kinolekta sa mga batch, ay masama. Ang klasikong kasamaan ay INSERT ... VALUES at karagdagang 1000 talaan. Dahil sa puntong iyon mayroon kang isang peak ng pagsulat sa media, at lahat ng iba na sumusubok na magsulat ng isang bagay sa disk ay naghihintay.
Upang maalis ang gayong mga anomalya, huwag lang pagsama-samahin ang anuman, huwag mag-buffer. At kung ang buffering sa disk ay nangyari (sa kabutihang palad, ang Stream API sa Node.js ay nagbibigay-daan sa iyo upang malaman) - ipagpaliban ang koneksyon na ito. Kapag nakatanggap ka ng isang kaganapan na ito ay libre muli, sumulat dito mula sa naipon na pila. At habang abala ito, kunin ang susunod na libre mula sa pool at sumulat dito.
Bago ipakilala ang diskarteng ito sa pag-record ng data, nagkaroon kami ng humigit-kumulang 4K na write ops, at sa paraang ito binawasan namin ang load ng 4 na beses. Ngayon sila ay lumago ng isa pang 6 na beses dahil sa mga bagong sinusubaybayang database - hanggang sa 100MB/s. At ngayon, nag-iimbak kami ng mga log sa huling 3 buwan sa dami na humigit-kumulang 10-15TB, umaasa na sa loob lamang ng tatlong buwan ay malulutas ng sinumang developer ang anumang problema.
Naiintindihan namin ang mga problema
Ngunit ang simpleng pagkolekta ng lahat ng data na ito ay mabuti, kapaki-pakinabang, may kaugnayan, ngunit hindi sapat - kailangan itong maunawaan. Dahil ito ay milyon-milyong iba't ibang mga plano bawat araw.
Ngunit milyon-milyon ang hindi mapangasiwaan, kailangan muna nating gawin ang "mas maliit". At, una sa lahat, kailangan mong magpasya kung paano mo ayusin ang "mas maliit" na bagay na ito.
Natukoy namin ang tatlong pangunahing punto:
- sino ipinadala ang kahilingang ito
Iyon ay, mula sa anong application ito "dumating": web interface, backend, sistema ng pagbabayad o iba pa. - saan nangyari ito
Sa anong partikular na server? Dahil kung mayroon kang ilang mga server sa ilalim ng isang application, at biglang ang isa ay "naging hangal" (dahil ang "disk ay bulok", "ang memorya ay tumagas", ilang iba pang problema), pagkatapos ay kailangan mong partikular na tugunan ang server. - bilang ang problema ay nagpakita mismo sa isang paraan o iba pa
Upang maunawaan ang "sino" ang nagpadala sa amin ng kahilingan, gumagamit kami ng karaniwang tool - pagtatakda ng variable ng session: SET application_name = '{bl-host}:{bl-method}'; β ipinapadala namin ang pangalan ng business logic host kung saan nagmumula ang kahilingan, at ang pangalan ng paraan o application na nagpasimula nito.
Matapos naming maipasa ang "may-ari" ng kahilingan, dapat itong maging output sa log - para dito i-configure namin ang variable log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Para sa mga interesado, siguro ano ang ibig sabihin ng lahat. Lumalabas na nakikita natin sa log:
- oras
- mga pagkakakilanlan ng proseso at transaksyon
- pangalan ng database
- IP ng taong nagpadala ng kahilingang ito
- at pangalan ng pamamaraan
Pagkatapos ay napagtanto namin na hindi masyadong kawili-wiling tingnan ang ugnayan para sa isang kahilingan sa pagitan ng iba't ibang mga server. Hindi madalas na mayroon kang isang sitwasyon kung saan ang isang application ay pantay-pantay na nabubulok dito at doon. Ngunit kahit na ito ay pareho, tingnan ang alinman sa mga server na ito.
Kaya narito ang hiwa "isang server - isang araw" ito ay naging sapat para sa amin para sa anumang pagsusuri.
Ang unang seksyon ng analitikal ay pareho "sample" - isang pinaikling anyo ng pagtatanghal ng plano, na-clear sa lahat ng mga numerical indicator. Ang pangalawang hiwa ay ang aplikasyon o pamamaraan, at ang pangatlong hiwa ay ang partikular na node ng plano na nagdulot sa amin ng mga problema.
Noong lumipat kami mula sa mga partikular na pagkakataon patungo sa mga template, nakakuha kami ng dalawang pakinabang nang sabay-sabay:
- maramihang pagbawas sa bilang ng mga bagay para sa pagsusuri
Kailangan nating suriin ang problema hindi na sa pamamagitan ng libu-libong query o plano, ngunit sa pamamagitan ng dose-dosenang mga template. - timeline
Iyon ay, sa pamamagitan ng pagbubuod ng "mga katotohanan" sa loob ng isang partikular na seksyon, maaari mong ipakita ang kanilang hitsura sa araw. At dito mo mauunawaan na kung mayroon kang isang uri ng pattern na nangyayari, halimbawa, isang beses sa isang oras, ngunit dapat itong mangyari isang beses sa isang araw, dapat mong isipin kung ano ang naging mali - sino ang naging sanhi nito at bakit, marahil ito ay dapat na narito. hindi dapat. Ito ay isa pang non-numerical, puro visual, na paraan ng pagsusuri.
Ang natitirang mga pamamaraan ay batay sa mga tagapagpahiwatig na kinuha namin mula sa plano: kung gaano karaming beses naganap ang gayong pattern, ang kabuuan at average na oras, kung gaano karaming data ang nabasa mula sa disk, at kung magkano ang mula sa memorya...
Dahil, halimbawa, pumunta ka sa pahina ng analytics para sa host, tingnan - may nagsisimula nang magbasa nang labis sa disk. Ang disk sa server ay hindi maaaring hawakan ito - sino ang nagbabasa mula dito?
At maaari kang mag-uri-uri ayon sa anumang hanay at magpasya kung ano ang haharapin mo ngayon - ang pag-load sa processor o ang disk, o ang kabuuang bilang ng mga kahilingan... Inayos namin ito, tiningnan ang mga "nangungunang", inayos ito at naglunsad ng bagong bersyon ng application.
At kaagad na makikita mo ang iba't ibang mga application na kasama ng parehong template mula sa isang kahilingan tulad ng SELECT * FROM users WHERE login = 'Vasya'. Frontend, backend, processing... At nagtataka ka kung bakit babasahin ng processing ang user kung hindi siya nakikipag-ugnayan sa kanya.
Ang kabaligtaran na paraan ay upang makita kaagad mula sa application kung ano ang ginagawa nito. Halimbawa, ang frontend ay ito, ito, ito, at ito minsan sa isang oras (nakakatulong ang timeline). At agad na bumangon ang tanong: parang hindi trabaho ng frontend na gumawa ng isang bagay minsan sa isang oras...
Pagkaraan ng ilang oras, napagtanto namin na kulang kami sa pinagsama-samang mga istatistika ayon sa mga node ng plano. Ibinukod namin mula sa mga plano ang mga node lamang na gumagawa ng isang bagay sa data ng mga talahanayan mismo (basahin/isulat ang mga ito sa pamamagitan ng index o hindi). Sa katunayan, isang aspeto lamang ang idinagdag na may kaugnayan sa nakaraang larawan - ilang record ang dinala sa amin ng node na ito?, at kung ilan ang itinapon (Rows Inalis ng Filter).
Wala kang angkop na index sa plato, hilingin mo ito, lumipad ito sa index, nahuhulog sa Seq Scan... na-filter mo ang lahat ng mga tala maliban sa isa. Bakit kailangan mo ng 100M na na-filter na talaan bawat araw? Hindi ba mas magandang i-roll up ang index?
Nang masuri ang lahat ng mga plano sa bawat node, napagtanto namin na may ilang mga tipikal na istruktura sa mga plano na malamang na magmukhang kahina-hinala. At masarap sabihin sa nag-develop: "Kaibigan, narito ka munang magbasa ayon sa index, pagkatapos ay pag-uri-uriin, at pagkatapos ay putulin" - bilang isang panuntunan, mayroong isang tala.
Lahat ng sumulat ng mga query ay malamang na nakatagpo ng pattern na ito: "Ibigay sa akin ang huling order para sa Vasya, ang petsa nito." At kung wala kang index ayon sa petsa, o walang petsa sa index na ginamit mo, pagkatapos ay hakbang sa eksaktong parehong "rake" .
Ngunit alam namin na ito ay isang "rake" - kaya bakit hindi agad sabihin sa developer kung ano ang dapat niyang gawin. Alinsunod dito, sa pagbubukas ng isang plano ngayon, ang aming developer ay agad na nakakita ng isang magandang larawan na may mga tip, kung saan agad nilang sinabi sa kanya: "Mayroon kang mga problema dito at doon, ngunit ang mga ito ay nalutas sa ganitong paraan at sa ganoong paraan."
Bilang resulta, ang dami ng karanasan na kailangan upang malutas ang mga problema sa simula at ngayon ay bumaba nang malaki. Ito ang uri ng tool na mayroon tayo.
Pinagmulan: www.habr.com