Massiivne PostgreSQL päringute optimeerimine. Kirill Borovikov (Tensor)

Ettekandes esitatakse mõned lähenemised, mis võimaldavad jälgida SQL-päringute tulemuslikkust, kui neid on miljoneid päevas, samas kui kontrollitavaid PostgreSQL servereid on sadu.

Millised tehnilised lahendused võimaldavad meil tõhusalt töödelda nii suurt teabehulka ja kuidas see lihtsustab tavalise arendaja elu.

Kellele on huvitav konkreetselt probleemide analüüs ja erinevad tehnilised optimeerimise tehnikad SQL-päringutes ja tavaliste DBA-ülesannete lahendused PostgreSQL-is — saab ka tutvuda artiklite seeriaga sellel teemal.


Minu nimi on Kirill Borovikov, ma esindan ettevõtet "Tensor". Ma spetsialiseerun andmebaasidega töötamisele meie ettevõttes.

Täna räägin teile, kuidas me tegeleme päringute optimeerimisega, kui peate mitte "opereerima" ühe ainulaadse päringu jõudlust, vaid lahendama massiprobleemi. Kui päringute arv on miljon, ja peate leidma mingid lahenduste lähenemised selle suure probleemi lahendamiseks.

Tegelikult on "Tensor" miljoni meie kliendi jaoks SBiS — meie rakendus: ettevõtte sotsiaalne võrgustik, lahendused videokõnede jaoks, sise- ja välisdokumendihaldus, raamatupidamise ja lao juhtimissüsteemid,… Seega on see nagu "megakombain" kompleksseks ärijuhtimiseks, kus on üle 100 erineva sisemiselt projekti.

Kuna kõik need peavad korralikult töötama ja arenema — meil on 10 arenduskeskust kogu riigis, milles töötab üle 1000 arendaja.

Me töötame PostgreSQL-iga alates 2008. aastast ja oleme kogunud suure hulga andmeid — need on kliendiandmed, statistilised, analüütilised, andmed välisest teabe süsteemidest — üle 400TB.Ainuüksi tootmises on meil umbes 250 serverit, kokku jälgime andmebaasi servereid umbes 1000.

SQL on deklaratiivne keel. Te kirjeldate mitte seda, "kuidas" midagi peab töötama, vaid seda, "mida" soovite saada. Andmebaas teab paremini, kuidas teha JOIN — kuidas teie tabelid ühendada, milliseid tingimusi seada, mis läheb indeksi kaudu, mis mitte…

Mõned andmebaasisüsteemid võtavad vastu vihjeid: "Ei, ühenda need kaks tabelit sellise järjekorra järgi", kuid PostgreSQL ei oska nii teha. See on teadlik positsioon peamiste arendajate seas: "Paremini täiustame päringu optimeerijat, kui lubame arendajatel kasutada mingeid vihjeid."

Kuid hoolimata sellest, et PostgreSQL ei luba "väljast" end hallata, võimaldab see suurepäraselt näha, mis toimub "sees", kui te oma päringut täidate, ja kust võivad tekkida probleemid.

Üldiselt, milliste klassikaliste probleemidega tuleb arendaja [DBA-le] tavaliselt? "Siin me täitsime päringu ja meil on kõik aeglane, kõik on hangunud, midagi toimub... Tõeline häda!"

Põhjused on peaaegu alati samad:

• efektiivne päringu algoritm
  Arendaja: "Praegu liitun SQL-is 10 tabelit JOIN-iga..." – ja ootab, et tema tingimused imekombel tõhusalt "lahendatakse" ja ta saab kõik kiiresti. Kuid imesid ei juhtu ning iga süsteem sellise variatiivsuse (10 tabelit ühes FROM-is) tõttu annab alati mingi eksituse. [artikkel]
• aktuaalne statistika
  See hetk on eriti oluline just PostgreSQL-i puhul, kui olete suure andmestiku serverisse "laadinud", teete päringu – ja see "läbivaatab" tabelit. Kuna eile oli seal 10 kirjet, aga täna 10 miljonit, kuid PostgreSQL ei ole sellest veel teadlik ning peate seda talle näitama. [artikkel]
• "ressursside ummistus"
  Te olete pannud suure ja raskesti koormatud andmebaasi nõrgale serverile, millel ei jätku ketta, mälu ja protsessori jõudlust. Ja kõik... Kusagil on jõudluspiir, mille ületamine ei ole enam võimalik.
• lukud
  See on keeruline teema, kuid need on kõige aktuaalsemad erinevate muudetavate päringute (INSERT, UPDATE, DELETE) puhul – see on eraldi suur teema.

Kava saamine

… Ja kõigi ülejäänud jaoks on meil vajalik kava! Me peame nägema, mis toimub serveri sees.

PostgreSQL-i päringu täitmise plaan on päringu täitmise algoritmi puu tekstiline esitlemine. Just see algoritm, mis analüsaator on tunnustanud kõige tõhusamaks.

Iga puu sõlm on operation: andmete hankimine tabelist või indeksist, bitikaardi loomine, kahe tabeli ühendamine, ühendamine, lõikamine või valikute eraldamine. Päringu täitmine on selle puu sõlme kaudu minek.

Küsimise plaani saamiseks on kõige lihtsam viis täita käsk EXPLAIN. Et saada kõigi tegelike atribuutidega, st tegelikult päringut andmebaasis täita – EXPLAIN (ANALYZE, BUFFERS) SELECT ....

Halb sich anu, kui te seda teete, toimub see "siin ja praegu", seega sobib see ainult lokaalseks silumiseks. Kui võtate näiteks mõne suure koormusega serveri, mille all toimub suurt andmevoogu, ja näete: "Oih! Siin on meil aeglane täitminekone. küsitlus." Pool tundi, tund tagasi – kuni te jooksisite ja tõite selle päringu logidest, kandsite selle serverisse tagasi, on kogu teie andmekogum ja statistika muutunud. Teete seda silumiseks – ja see täitub kiiresti! Ja te ei saa aru "miks", miks oli aeglane.

Kuna mõista, mis juhtus täpselt sel hetkel, kui päring serveris täideti, on tarkade inimeste poolt kirjutatud moodul auto_explain. See on kohal praktiliselt kõigis levinud PostgreSQL jaotustes, ning seda saab lihtsalt aktiveerida seadistamisfailis.

Kui see mõistab, et mõni päring täitub kauem kui teatud piir, mille olete talle määranud, siis teeb ta "snapshot" selle päringu plaanist ja kirjutab selle logisse.

Nagu kõik on nüüd hästi, läheme logisse ja näeme seal… [portjanka teksti]. Kuid me ei saa sellest midagi öelda, peale selle, et see on suurepärane plaan, kuna see täitus 11 ms.

Nagu kõik on hästi – aga mitte midagi ei ole selge, mis tegelikult juhtus. Ainus, mida me näeme, on üleüldine aeg. Sest vaadata sellist "latukât" plain text on üldse mitte visuaalne.

Aga isegi kui see ei ole visuaalne, on seal ka tõsisemad probleemid:

• Sõlmes on näidatud ressursside summa kogu allpuu tema all. Seega on lihtsalt võimatu teada, kui palju aega konkreetse Index Scan'i jaoks kulus – seda ei saa teha, kui selle all on mõni sisemine tingimus. Peame dünaamiliselt vaatama, kas seal sees on "laste" ja tingimuslikke muutujaid, CTE – ja kõik need üles arvestama "meeles".
• Teine hetk: aeg, mis sõlmes on näidatud, on sõlme ainulaadne täitmis aeg.Kui see sõlm täideti näiteks tabeli ridade ringkäigu tulemusena mitu korda, siis plaanis suureneb loops – tsüklite arv selles sõlmes. Kuid selle ainulaadne täitmise aeg jääb plaanis endiseks. Seega, et mõista, kui kaua see sõlm kokkuvõttes täideti, tuleb üht korrutada teisega – jälle "meeles".

Sellest tulenevalt on peaaegu võimatu mõista, "Kes on kõige nõrgem lüli?" Seetõttu kirjutavad isegi arendajad oma "juhendis", et "Plani mõistmine on kunst, mida tuleb õppida, kogemus...".

Kuid meil on 1000 arendajat ja seda kogemust ei saa igaühele pähe panna. Mina, sina, tema - teavad, aga keegi seal kaugel - ei tea. Võib-olla ta õpib, aga võib-olla ei õpi, aga töötama peab ta kohe – kust küll võtta seda kogemust.

Plani visualiseerimine

Seetõttu mõistsime, et nende probleemide lahendamiseks on meil vaja head plaanivisualiseerimist. [artikkel]

Läksime kõigepealt „turule” - otsime internetist, mis olemas on.

Kuid selgus, et suhteliselt "elavaid" lahendusi, mis enam-vähem arenevad, on äärmiselt vähe - vaid üks: explain.depesz.com Hubert Lubaczewski poolt. Sa annad sisendiks tekstilise plaani, see näitab sulle tabelit analüüsitud andmetest:

• enda sõlme töötamise aeg
• koguaeg kogu alampuu puhul
• ekstraktitud kirje arv ja statistiliselt oodatud arv
• sõlme enda keha

Selle teenuse juures on ka võimalus jagada linkide arhiveeritud andmeid. Sa viskad sinna oma plaani ja ütled: "Hei, Vassja, siin on sulle link, seal on midagi valesti."

Kuid on ka mõned väikesed probleemid.

Esiteks, tohutu hulk "copy-paste'i". Sa võtad logi tükikese, lisad selle sisse ja jälle, ja jälle.

Teiseks, andmete lugemise arvu analüüsi ei ole - neid buffers, mida toob välja EXPLAIN (ANALYZE, BUFFERS), siin me ei näe. Ta lihtsalt ei oska neid analüüsida, mõista ja nendega töötada. Kui loed palju andmeid ja mõistad, et võid valele viisil andmed kettale ja mällu paigutada, on see teave väga oluline.

Kolmas negatiivne moment on selle projekti väga nõrk areng. Commitid on väga väikesed, hästi kui kaks korda aastas, ja kood on Perl'is.

Aga need on kõik "liirika", sellega saaks kuidagi elada, kuid on üks asi, mis tõukab meid sellest teenusest kaugele. Need on Common Table Expression (CTE) ja erinevate dünaamiliste sõlmede nagu InitPlan/SubPlan analüüsivead.

Kui usaldada seda pilti, siis on iga eraldiseisva sõlme koguaeg pikem kui kogu päringu koguaeg. Kõik on lihtne - CTE Scan sõlmedest ei lahutatud CTE genereerimise aega. Seetõttu ei tea me enam õiget vastust, kui kaua CTE skaneerimine aega võttis.

Siin mõistsime, et on aeg kirjutada oma — hurraa! Iga arendaja ütleb: «Nüüd kirjutame oma, super lihtne saab olema!»

Võtsime tüüpilise web-teenuste tehnoloogia: põhijoon on Node.js + Express, lisasime Bootstrap'i ja ilusate diagrammide jaoks D3.js. Ja meie ootused täitusid täiesti — esimese prototüübi saime kahe nädalaga:

• oma plaani parseri
  See tähendab, et nüüd saame analüüsida iga plaani, mida genereerib PostgreSQL.
• korrektne dünaamiliste sõlmede analüüs — CTE Scan, InitPlan, SubPlan
• pufferite jaotuse analüüs — kust loetakse andmete lehed mälust, kust kohalikust vahemälust, kust kettalt
• sai selgus
  Et mitte logides seda kõike «kaevata», vaid näha «nõrka kohta» kohe pildilt.

Saime umbes sellise pildi — kohe sünteksi esiletoomisega. Kuid tavaliselt töötavad meie arendajad juba mitte kogu plaani, vaid lühenenud versiooniga. Küll aga oleme kõik numbrid juba ära parseerinud ja paremale vasakule kõrvale pannud, ning keskele jätnud ainult esimese rea, mis sõlm see on: CTE Scan, CTE genereerimine või Seq Scan mingi tabeli kohta.

Seda lühendatud esitlemist nimetame plaani malliks.

Mis oleks veel mugav? Oleks mugav näha, milline osa millise sõlme kohta kogu ajast jaotub — ja lihtsalt «kleepisime» küljele torti diagrammina.

Suunates sõlmele näeme — meie Seq Scan on koguaeg kulutanud vähem kui veerandi, kuid ülejäänud 3/4 ajast on kulunud CTE Scan. Kohutav! See väike märk CTE Scan «kiirusest», kui te aktiivselt neid oma päringutes kasutate. Need ei ole eriti kiired — nad kaotavad isegi tavapärase tabeli skaneerimisele. [artikkel] [artikkel]

Kuid tavaliselt on sellised diagrammid huvitavamad ja keerukamad, kui suuname kohe segmenti ja näeme näiteks, et rohkem kui pool kogu ajast on «söötnud» mingi Seq Scan. Ja seal sees oli mingi Filter, hulk kirjeid visati selle kaudu… Selle pildi saab arendajale otse edasi saata ja öelda: «Vassilij, sul on siin tegelikult kõik halvasti! Uuri välja, vaata — midagi on vale!»

Muidugi ei saanud me ilma «aukudeta» hakkama.

Esimene probleem, millega kokku puutusime, oli ümarus. Iga plaani sõlme aeg on määratud täpsusega 1 μs. Ja kui sõlme tsüklite arv ületab näiteks 1000 — pärast PostgreSQL-i töötlemist jagatakse see 'täpsusega', mille tagajärjel saame koguaeg 'kuskil 0.95 ms ja 1.05 ms vahel'. Kui räägime mikrosekunditest, pole veel hullu, aga kui juba millisekunditest — siis tuleb plaani sõlmede ressursse 'lahendades' arvestada, kellel kui palju on kokku nõutud.

Teine, keerulisem, probleem on ressursside (neid samu pufferid) jaotamine dünaamilistes sõlmedes. See maksis meile prototüübi esimestel kahel nädalal veel lisaks 4 nädalat.

Sellise probleemi saamine on üsna lihtne — teeme CTE ja seal näiliselt loeme midagi. Tegelikult on PostgreSQL 'nutikas' ja ei loe seal otse midagi. Hiljem võtame sellest esimesse kirje, ja sellele — saja esimese samast CTE-st.

Vaadates plaani, mõistame — kummaline, meil oli 3 pufferit (andmelehti), mis 'kasutati' Seq Scanis, veel 1 CTE Scanis ja veel 2 teises CTE Scanis. Kui kõike kokku liita, siis saame 6, aga tabelist lugesime me vaid 3! CTE Scan ei loe ju midagi kuskilt, vaid töötab otse protsessi mäluga. Seega on siin ilmselgelt midagi valesti!

Tegelikult selgub, et need 3 andmelehte, mis olid nõutud Seq Scanis, taotles esiteks 1. CTE Scan ja siis 2., ning sellele lugesid nad veel 2. Seega loeti kokku 3 andmelehte, mitte 6.

Ja see pilt viis meid arusaamisele, et plaani täitmine ei ole enam puu, vaid lihtsalt mingi suvaline suunamata graaf. Ja meil on tekkinud umbes selline diagramm, et me mõistaksime 'kust ja kuidas teavet saime'. Siin loodime CTE pg_class-ist ja palusime seda kaks korda ning peaaegu kogu meie aeg kulus haru peal, kui küsisime seda teist korda. On selge, et 101. kirje lugemine on tunduvalt kallim kui lihtsalt 1. tabelist.

Me hingasime mõneks ajaks välja. Ütlesime: 'Nüüd, Neo, sa tead kung fu! Nüüd on meie kogemus sul otse ekraanil. Nüüd saad seda kasutada.' [artikkel]

Logide konsolideerimine

Meie 1000 arendajat hingasid kergendatult. Kuid me mõistsime, et meil on ainult sadu „tööservereid“ ja kogu see arendajate poolt tehtud „kopeerimine ja kleepimine“ pole sugugi mugav. Me otsustasime, et peame selle ise kokku panema.

Tegelikult on olemas ametlik moodul, mis oskab statistikat koguda, kuid seda tuleb samuti konfiguratsioonis aktiveerida — see on moodul pg_stat_statements. Kuid see meid ei rahuldanud.

Esiteks, sama päringutele erinevates skeemides ühe andmebaasi piires annab see erinevad QueryId. Nii et kui kõigepealt teha SET search_path = '01'; SELECT * FROM user LIMIT 1;, ja siis SET search_path = '02'; ja sama päring, siis selle mooduli statistikasse jäävad erinevad kirjed ning ma ei saa koguda üldist statistikat just selle päringuprofiili lõikes, skeeme arvesse võtmata.

Teine asi, mis takistas meid selle kasutamist — puuduvad plaanid.Nii et plaani pole, on ainult päring ise. Me näeme, mis aeglustas, kuid ei saa aru, miks. Ja siin tuleme tagasi kiiresti muutuva andmestiku probleemini.

Ja viimane asi — puuduvad „faktid“.Nii et ei saa pöörduda konkreetse päringu täitmise exemplaari poole — seda ei ole, on ainult koondstatistika. Sellega on kuigi võimalik töötada, lihtsalt väga keeruline.

Seetõttu otsustasime „kopeerimise ja kleepimisega“ võidelda ja hakkasime kirjutama kogujat..

Kogujaga ühendatakse SSH kaudu, „loob“ sertifikaadi abil turvalise ühenduse andmebaasi serveriga ja tail -F „kinni” logifailile. Nii et selles seansis saame täieliku „peegli“ kõigest logifailist,mida server genereerib. Serveri koormus on sel juhul minimaalne, kuna me seal midagi ei analüüsi, lihtsalt peegeldame liiklust.

Kuna olime juba hakanud kirjutama liidest Node.js-is, jätkasime koguja kirjutamist samal platvormil. Ja see tehnoloogia õigustas end, kuna halvasti vormindatud tekstiliste andmetega, millega logid on, töötamine JavaScriptiga on väga mugav. Ja Node.js infrastruktuur backend-platvormina võimaldab hõlpsalt ja mugavalt töötada võrguliideseid ja andmevooge.

Seega me "venitame" kaks ühendust: esimene, et "kuulata" logi ja see enda juurde tuua, ning teine, et perioodiliselt andmebaasilt küsida. "Logis on kirjas, et tabel oid 123 on blokeeritud," kuid arendajale ei ütle see midagi ja oleks hea küsida andmebaasilt: "Mis asi on OID = 123?" Nii küsime me perioodiliselt andmebaasilt seda, mida me veel ei tea.

"Ainult ühte asja sa ei arvestanud: on olemas elevantide sarnased mesilased!.." Me alustasime selle süsteemi arendamist, kui soovisime jälgida 10 serverit. Need olid meie arvates kõige kriitilisemad, kus esines probleeme, millega oli raske tegeleda. Kuid juba esimesel kvartalil saime jälgimiseks sada — sest süsteem "töötas", kõik soovisid seda, kõigile oli see mugav.

Kõik need andmed tuleb kokku liita, andmevoog on suur ja aktiivne. Tegelikult jälgime me seda, millega oskame tegeleda — ja kasutame seda. Andmete hoidmiseks kasutame samuti PostgreSQL-i. Sest pole midagi kiiremat, kui andmeid sinna "valada". COPY pole veel.

Kuid lihtsalt andmete "valamine" ei ole päris meie tehnoloogia. Sest kui teil on sajale serverile umbes 50k päringut sekundis, siis genereerib see teile 100-150GB logisid päevas. Seetõttu pidime andmebaasi ettevaatlikult "lõikama".

Esiteks tegime jaotuse päevade kaupa, sest suurem osa inimesi ei huvita ööpäevade vaheline korrelatsioon. Mis vahet seal on, mis sul eile oli, kui sa öösel väljastasid uue rakenduse versiooni — ja juba on mingid uued statistilised andmed.

Teiseks õppisime (olid sunnitud) väga-väga kiiresti kirjutama COPY. See tähendab, et mitte lihtsalt COPY, sest see on kiirem kui INSERT, vaid veelgi kiirem.

Kolmas punkt — pidime loobuma triggereist ja seega ka välismaistest võtmetest. See tähendab, et meil pole üldse viidatud terviklikkust. Sest kui teil on tabel, kus on paar FK-d, ja te ütlete andmebaasi struktuuris, et "siin on logikirje, mis viitab näiteks rühmale kirjed", siis kui te seda sisestate, ei jää PostgreSQL-il muud üle kui võtta ja ausalt teostada SELECT 1 FROM master_fk1_table WHERE ... selle identifikaatoriga, mida te üritate sisestada — lihtsalt selleks, et kontrollida, et see kirje seal on, et te ei "katke" oma sisestamisega seda välismaist võtit.

Me saame sihtmärkide tabelisse ja tema indeksitesse ühe kirje asemel veel lugedes kõigist tabelitest, millele see viitab. Ja me ei vaja seda - meie ülesanne on salvestada nii palju kui võimalik ja nii kiiresti kui võimalik võimalikult väikese koormusega. Seega FK - ära!

Järgmine punkt - agregatsioon ja hashimine. Alguses oli see meil realiseeritud andmebaasis - see on mugav, kui märge saabub, teha mingis tabelis „pluss üks“ otse triggeris. Hästi, mugav, kuid halvasti - te lisate ühe kirje ja peate lugema ja kirjutama veel midagi teisest tabelist. Lisaks sellele, et lugeda ja kirjutada - peate seda tegema iga kord.

Ja nüüd kujutage ette, et teil on tabel, kus te lihtsalt loendate konkreetse hosti kaudu läbitud päringute arvu: +1, +1, +1, ..., +1. Ja teil pole seda tõeliselt vaja - kõike seda saab mälus kollektoris kokku lugeda ja saata andmebaasi korraga +10.

Jah, teil võib mingi tõrke korral „laguneda“ loogiline terviklikkus, kuid see on praktiliselt ebatõenäoline juhtum - sest teil on korralik server, sellel on kontrolleris aku, teil on tehingute ajakiri, ajakiri failisüsteemis... Ühesõnaga, see ei ole seda väärt. See ei ole väärt jõudluse kadu, mille te saate triggerite/FK töötamise tõttu, nende kulude tõttu, mida te kannate.

Sama kehtib ka hashimise kohta. Teie poole tuleb mingi päring, millest te arvutate andmebaasis mingi identifikaatori, kirjutate selle andmebaasi ja ütlete kõigile seejärel. Kõik on hästi, kuni kirjutamise hetkel tuleb Teile teine soovija selle sama kirjutamiseks - ja teil tekib lukustus, mis juba on halb. Seetõttu, kui saate mõne ID genereerimise kliendile (andmebaasi suhtes) üle anda, on parem seda teha.

Meil on olnud ideaalne kasutada MD5 teksti - päring, plaan, mall,… Arvutame selle kollektoris ja „valame“ andmebaasi juba valmis ID. MD5 pikkus ja päevane osadus võimaldavad meil mitte muretseda võimalike konfliktide pärast.

Kuid et seda kõike kiiresti salvestada, pidime modifitseerima kirjutamisprotseduuri.

Kuidas andmeid tavaliselt kirjutatakse? Meil on mingi andmestik, me jaotame selle mitmeks tabeliks ja seejärel COPY — esmalt ühte, seejärel teise, kolmandasse... Ebamugav, kuna me näiliselt kirjutame ühe andmevoo järjestikku kolme sammu jooksul. Mitte just meeldiv. Kas saaks kuidagi kiiremini? Jah!

Selleks piisab lihtsalt, kui jaotada need vood paralleelselt üksteisega. Tulemuseks on see, et meil on eraldi voogudes vead, päringud, mallid, blokeeringud... — ja me kirjutame seda kõike paralleelselt. Selleks piisab hoida pidevalt avatud COPY-kanalit igale eraldi sihttabelile..

See tähendab, et kollektsionääril on alati voog,kuhu ma saan kirjutada mulle vajalikke andmeid. Kuid et andmebaas neid andmeid näeks ja keegi ei jääks blokeeringutesse ootama, kuni need andmed kirjutatakse, peab COPY-d katkestama teatud regulaarsusega.Meie jaoks osutus kõige tõhusamaks perioodiks umbes 100 ms — sulgeme ja kohe avame jälle sama tabeli peale. Ja kui meil ei piisa ühest voost teatud tipptundidel, siis teeme ummikseisu teatud piirini.

Lisaks selgus, et sellise koormusprofiili jaoks on iga agregatsioon, kui kirjed kogutakse pakettidesse - see on kuritegu. Klišee kuritegu on INSERT ... VALUES ja edasi 1000 kirjet. Sest selle hetkel tekib teil püsiva kirjutamise tipp ja kõik teised, kes üritavad midagi kettale kirjutada, peavad ootama.

Sellest anomaaliast vabanemiseks ärge agregige mitte midagi, ärge buferdage üldse. Ja kui kettale buferdamine siiski tekib (õnneks, Stream API Node.js-is võimaldab seda teada), siis lükake see ühendus edasi. Kui teil tuleb teade, et see on jälle vaba — kirjutage sellesse akumuleeritud järjekorrast. Ja seni, kuni see on hõivatud — võtke järgmisest vabast basseinist ja kirjutage sellesse.

Enne sellise lähenemise rakendamist andmete kirjutamisele oli meil umbes 4K kirjutamisoperatsiooni, kuid selle meetodiga vähendasime koormust nelja korra võrra. Nüüd oleme suuremaks kasvanud veel kuue korra uute jälgitavate aluste tõttu — kuni 100MB/s. Ja nüüd hoiame viimase kolme kuu logisid umbes 10–15TB mahus, lootes, et kolme kuu jooksul suudab iga arendaja lahendada iga probleemi.

Mõistame probleeme

Ainult kõigi nende andmete kogumine on hea, kasulik ja vajalik, kuid see ei ole piisav — neid peab mõistma. Sest see on miljonite erinevate plaanide hulk päevas.

Aga miljonid on üle jõu käiv, seega tuleb esmalt muuta need „väikesemaks”. Ja enne kõike tuleb otsustada, kuidas te selle „väikesuse” korraldate.

Oleme välja toonud kolm peamist punkti:

• kes selle päringu saatis
  St. millisest rakendusest see „tuli”: veebi liides, tagapind, maksesüsteem või midagi muud.
• kus kus see toimus
  Millisel konkreetsel serveril. Sest kui ühe rakenduse all on mitu serverit, ja äkki üks „toimetab aeglaselt” (sest „ketas on katki”, „mälu lekkib”, mingi muu probleem), siis tuleb adresseerida just sellele serverile.
• kuidas konkreetselt probleem ilmnes selles või teises plaanis

Kuna me peame selgitama „kes” saatis meile päringu, kasutame standardset tööriista — seansi muutuja seadistamist: SET application_name = '{bl-host}:{bl-method}'; — salvestame äriloogika hosti nime, kust päring tuleb, ja meetodi või rakenduse nime, mis selle algatas.

Pärast seda, kui oleme edastanud päringu „omaniku”, tuleb see logisse välja tuua — selleks konfigureerime muutuja log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Kellele see huvi pakub, võib vaadata juhendist, mida see kõik tähendab. Nii näeme logis:

• aega
• protsessi ja tehingu tuvastajad
• andmebaasi nime
• selle isiku IP, kes saatis selle päringu
• ja meetodi nime

Edasi liikudes mõistsime, et ühe päringu vahel erinevate serverite sünergiat pole just väga huvitav vaadata. Harva juhtub, et ühel rakendusel on siin-seal sama „probleem”. Kuid isegi kui see on sama — vaadake mõnda neist serveritest.

Nii et „ühe serveri — ühe päeva” lõike oli meile igasuguseks analüüsi jaoks piisav. Esimene analüütiline lõige on just see

„mall” — lühendatud kujul plaani esitus, kust on eemaldatud kõik numbrilised näitajad. Teine lõige — rakendus või meetod, ja kolmas — see konkreetne plaan, mis tekitas meile probleeme. Kui me liikuma hakkasime konkreetsetest eksemplaridest mallide juurde, saime kohe kaks eeliseid:

oluliselt vähenenud analüüsitavate objektide arv

• Problem ei pea enam olema tuhandete päringute või plaanide põhjal, vaid kümnete mallide põhjal.
  ajajoone
• ajalugu
  Kokkuvõttes saab „faktide“ üldistamisel mingisuguse lõike raames näidata nende esinemist päeva jooksul. Siin võite mõista, et kui teil toimub mingisugune muster näiteks korra tunnis, aga peaks olema korra päevas, tasub mõelda, mis valesti läks — kes ja miks selle käivitas, ehk ei peaks seda siin olema. See on veel üks mitte-numeeriline, visuaalne analüüsimeetod.

Ülejäänud meetodid põhinevad näitajatel, mida me plaanist välja loome: kui tihti selline muster esines, summaarne ja keskmine aeg, kui palju andmeid on kettalt välja loetud ja kui palju mälust...

Kuna näiteks tulete hosti analüüsile, vaatate — midagi liiga palju ketast lugema hakkas. Kett serveris ei suuda enam hakkama saada — aga kes seda loeb?

Ja te saate sorteerida igasuguste veergude järgi ja otsustada, millega soovite kohe tegeleda — kas protsessori või ketta koormusega, või üldise päringute arvuga... Sorteerisite, vaatasite „tippe“, parandasite — välja lastud uus rakenduse versioon.
[videolektuur]

Ja koheselt näete erinevaid rakendusi, mis kasutavad sama mustrit päringu tüübi SELECT * FROM users WHERE login = 'Vasya'. Fronteer, tagumine, töötlemine... Ja te mõtlete, miks töötlemine peaks kasutajat lugema, kui ta temaga ei suhtle.

Tagurpidi meetod — rakendusest kohe näha, mida see teeb. Näiteks, fronteer — see, see, see, ja veel see kord tunnis (just ajajoone abil aitab). Ja kohe tekib küsimus — ilmselt ei peaks fronteer tegema midagi kord tunnis...

Mõne aja pärast mõistsime, et meil puudub koondstatistika plaani sõlmede lõikes. Me eraldasime plaanidest ainult need sõlmed, mis tegelevad andmetega endi tabelite (loevad/kirjutavad neid indeksi järgi või mitte). Sisuliselt lisatakse eelnevale pildile vaid üks aspekt —kui palju kirjeid see sõlm meile tõi , ja kui palju eemaldati (Rows Removed by Filter).Teil pole tabeli jaoks sobivat indeksit, teete sellele päringu, see möödub indeksist, satub Seq Scan'i... kõik kirjed, välja arvatud üks, on filtreeritud. Ja milleks teile 100M filtreeritud kirjet ööpäevas, poleks parem indeks peale panna?

Teil pole sobivat indeksit tabelis, teete sellele päringu, see möödub indeksist, langeb Seq Scan… kõik kirjed, välja arvatud üks, olete filtreerinud. Aga miks vajate 100 miljonit filtreeritud kirjet ühe päeva jooksul, kas poleks parem indeks luua?

Analüüsinud kõiki sõlmpunkte, mõistsime, et plaanides on teatud tüüpilised struktuurid, mis tõenäoliselt näevad kahtlased välja. Ja oleks hea, kui arendajat oleks võimalik suunata: "Sõber, siin sa kõigepealt loed indeksi kaudu, seejärel sorteerid ja siis kärbid" – reeglina on seal üks kirje.

Kõik, kes on sellise mustriga päringute tegemisega kokku puutunud, on tõenäoliselt pidanud silmitsi seisma: "Anna mulle viimane tellimus Vassil, kuupäev". Kui sul pole kuupäeva indeksi, või kasutatud indeksis pole kuupäeva, siis astud just nende "kivideni".

Aga me ju teame, et need on "kivid" – siis miks mitte kohe arendajale öelda, mida tal võiks teha. Seega avades nüüd plaani, näeb meie arendaja kohe ilusat pilti vihjetega, kus talle öeldakse: "Sul on siin ja siin probleemid, ja need lahenevad nii ja naa."

Tulemuseks on koguse kogemus, mis oli vajalik probleemide lahendamiseks alguses ja praegu, vähenenud kümneid kordi. Selline tööriist meil ongi tekkinud.

Allikas: habr.com