Raportul prezintă câteva abordări care permit monitorizați performanța interogărilor SQL atunci când există milioane de ele pe ziși există sute de servere PostgreSQL monitorizate.
Ce soluții tehnice ne permit să procesăm eficient un astfel de volum de informații și cum face asta viața unui dezvoltator obișnuit mai ușoară?
Cine este interesat? analiza problemelor specifice și diverse tehnici de optimizare Interogări SQL și rezolvarea problemelor tipice DBA în PostgreSQL - puteți, de asemenea pe această temă.
Numele meu este Kirill Borovikov, reprezint . Mai exact, sunt specializată în lucrul cu baze de date în cadrul companiei noastre.
Astăzi vă voi spune cum optimizăm interogările, când nu trebuie să „selegeți” performanța unei singure interogări, ci să rezolvați problema în masă. Când există milioane de solicitări și trebuie să găsești unele abordări ale soluției această mare problemă.
În general, Tensor pentru un milion dintre clienții noștri este : rețea socială corporativă, soluții pentru comunicare video, pentru fluxul de documente intern și extern, sisteme de contabilitate pentru contabilitate și depozite,... Adică o astfel de „mega-combină” pentru managementul integrat al afacerii, în care există peste 100 de diferite proiecte interne.
Pentru a ne asigura că toate funcționează și se dezvoltă normal, avem 10 centre de dezvoltare în toată țara, cu mai multe în ele 1000 de dezvoltatori.
Lucrăm cu PostgreSQL din 2008 și am acumulat o mare parte din ceea ce prelucrăm - date clienți, statistice, analitice, date din sistemele de informare externe - mai mult de 400TB. Există aproximativ 250 de servere numai în producție și, în total, sunt aproximativ 1000 de servere de baze de date pe care le monitorizăm.
SQL este un limbaj declarativ. Nu descrieți „cum” ar trebui să funcționeze ceva, ci „ce” doriți să obțineți. SGBD știe mai bine cum să facă un JOIN - cum să vă conectați tabelele, ce condiții să impuneți, ce va trece prin index, ce nu va...
Unele SGBD acceptă indicii: „Nu, conectați aceste două tabele într-o coadă de așteptare”, dar PostgreSQL nu poate face acest lucru. Aceasta este poziția conștientă a dezvoltatorilor de frunte: „Preferăm să terminăm optimizatorul de interogări decât să le permitem dezvoltatorilor să folosească un fel de indicii.”
Dar, în ciuda faptului că PostgreSQL nu permite „exterior” să se controleze, permite perfect vezi ce se întâmplă înăuntrul luicând executați interogarea și unde are probleme.
În general, cu ce probleme clasice vine de obicei un dezvoltator [la un DBA]? „Aici am îndeplinit cererea și totul este lent la noi, totul atârnă, se întâmplă ceva... Un fel de necaz!”
Motivele sunt aproape întotdeauna aceleași:
- algoritm de interogare ineficient
Dezvoltator: „Acum îi dau 10 tabele în SQL prin JOIN...” - și se așteaptă ca condițiile lui să fie în mod miraculos „dezlegate” și el va obține totul rapid. Dar miracolele nu se întâmplă și orice sistem cu o asemenea variabilitate (10 tabele într-un FROM) dă întotdeauna un fel de eroare. [] - statistici irelevante
Acest punct este foarte relevant în special pentru PostgreSQL, când ați „vărsat” un set mare de date pe server, ați făcut o solicitare și acesta vă „sexcanits” tableta. Pentru că ieri erau 10 înregistrări în el, iar astăzi sunt 10 milioane, dar PostgreSQL nu este încă conștient de acest lucru și trebuie să-i spunem despre asta. [] - „conectați” resursele
Ați instalat o bază de date mare și foarte încărcată pe un server slab care nu are suficientă performanță de disc, memorie sau procesor. Și asta-i tot... Undeva există un plafon de performanță deasupra căruia nu mai poți sări. - blocare
Acesta este un punct dificil, dar ele sunt cele mai relevante pentru diferite interogări de modificare (INSERT, UPDATE, DELETE) - acesta este un subiect mare separat.
Obținerea unui plan
...Și pentru orice altceva noi nevoie de un plan! Trebuie să vedem ce se întâmplă în interiorul serverului.
Un plan de execuție a interogării pentru PostgreSQL este un arbore al algoritmului de execuție a interogării în reprezentarea textului. Tocmai algoritmul este cel care, în urma analizei efectuate de planificator, s-a dovedit a fi cel mai eficient.
Fiecare nod de arbore este o operație: preluarea datelor dintr-un tabel sau index, construirea unui bitmap, unirea a două tabele, unirea, intersectarea sau excluderea selecțiilor. Executarea unei interogări implică parcurgerea nodurilor acestui arbore.
Pentru a obține planul de interogare, cel mai simplu mod este să executați instrucțiunea EXPLAIN. Pentru a obține toate atributele reale, adică pentru a executa efectiv o interogare pe bază - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
Partea proastă: când îl rulați, se întâmplă „aici și acum”, așa că este potrivit doar pentru depanare locală. Dacă luați un server foarte încărcat care se află sub un flux puternic de modificări de date și vedeți: „Oh! Aici avem o execuție lentăXia cerere." Acum o jumătate de oră, o oră - în timp ce rulați și primiți această solicitare din jurnale, aduceți-o înapoi pe server, întregul set de date și statisticile s-au schimbat. Îl rulați pentru a depana - și rulează rapid! Și nu poți înțelege de ce, de ce a fost încet.
Pentru a înțelege ce s-a întâmplat exact în momentul în care cererea a fost executată pe server, au scris oamenii deștepți . Este prezent în aproape toate cele mai comune distribuții PostgreSQL și poate fi activat pur și simplu în fișierul de configurare.
Dacă realizează că o anumită solicitare rulează mai mult decât limita pe care i-ai spus-o, o face „instantaneu” a planului acestei cereri și le scrie împreună în jurnal.
Totul pare să fie bine acum, mergem la jurnal și vedem acolo... [text footcloth]. Dar nu putem spune nimic despre asta, în afară de faptul că este un plan excelent pentru că a fost nevoie de 11 ms pentru a fi executat.
Totul pare să fie în regulă - dar nimic nu este clar ce s-a întâmplat de fapt. În afară de ora generală, nu vedem nimic. Pentru că a privi un astfel de „miel” de text simplu nu este în general vizual.
Dar chiar dacă nu este evident, chiar dacă este incomod, există probleme mai fundamentale:
- Nodul indică suma resurselor întregului subarbore sub el. Adică, nu puteți afla doar cât timp a fost petrecut cu această scanare a indexului, dacă există o condiție imbricată sub ea. Trebuie să ne uităm dinamic pentru a vedea dacă există „copii” și variabile condiționate, CTE-uri în interior - și să scădem toate acestea „în mintea noastră”.
- Al doilea punct: ora care este indicată pe nod este timpul de execuție cu un singur nod. Dacă acest nod a fost executat ca urmare, de exemplu, a unei bucle prin înregistrări de tabel de mai multe ori, atunci numărul de bucle - cicluri ale acestui nod - crește în plan. Dar timpul de execuție atomic în sine rămâne același în ceea ce privește planul. Adică, pentru a înțelege cât timp a fost efectuat acest nod în total, trebuie să înmulțiți un lucru cu altul - din nou, „în capul vostru”.
În astfel de situații, înțelegeți „Cine este cea mai slabă verigă?” aproape imposibil. Prin urmare, chiar și dezvoltatorii înșiși scriu în „manual” că „Înțelegerea unui plan este o artă care trebuie învățată, experimentată...”.
Dar avem 1000 de dezvoltatori și nu poți transmite această experiență fiecăruia dintre ei. Eu, tu, el știe, dar cineva de acolo nu mai știe. Poate că va învăța, sau poate nu, dar trebuie să lucreze acum - și de unde ar obține această experiență?
Planifică vizualizarea
Prin urmare, ne-am dat seama că pentru a face față acestor probleme, avem nevoie buna vizualizare a planului.
Am trecut mai întâi „prin piață” - să ne uităm pe internet pentru a vedea ce există.
Dar s-a dovedit că există foarte puține soluții relativ „vii” care sunt mai mult sau mai puțin în curs de dezvoltare - literalmente, doar una: de Hubert Lubaczewski. Când introduceți în câmpul „feed” o reprezentare text a planului, acesta vă arată un tabel cu datele analizate:
- timpul de procesare propriu al nodului
- timpul total pentru întregul subarbor
- numărul de înregistrări care au fost preluate care au fost așteptate statistic
- corpul nodului însuși
Acest serviciu are și capacitatea de a partaja o arhivă de link-uri. Ți-ai aruncat planul acolo și ai spus: „Hei, Vasya, iată un link, e ceva în neregulă acolo”.
Dar sunt și mici probleme.
În primul rând, o cantitate imensă de „copy-paste”. Luați o bucată din buștean, o înfigeți acolo și din nou, și din nou.
În al doilea rând, nicio analiză a cantității de date citite — aceleași buffere care ies EXPLAIN (ANALYZE, BUFFERS), nu-l vedem aici. Pur și simplu nu știe să le demonteze, să le înțeleagă și să lucreze cu ele. Când citiți o mulțime de date și realizați că este posibil să alocați greșit discul și memoria cache, aceste informații sunt foarte importante.
Al treilea punct negativ este dezvoltarea foarte slabă a acestui proiect. Comiterile sunt foarte mici, este bine dacă o dată la șase luni, iar codul este în Perl.
Dar acestea sunt toate „versuri”, am putea trăi cumva cu asta, dar există un lucru care ne-a îndepărtat foarte mult de acest serviciu. Acestea sunt erori în analiza Common Table Expression (CTE) și diferite noduri dinamice precum InitPlan/SubPlan.
Dacă credeți această imagine, atunci timpul total de execuție al fiecărui nod individual este mai mare decât timpul total de execuție al întregii cereri. E simplu - timpul de generare al acestui CTE nu a fost scăzut din nodul CTE Scan. Prin urmare, nu mai știm răspunsul corect la cât a durat scanarea CTE în sine.
Atunci ne-am dat seama că a sosit timpul să ne scriem pe al nostru – ura! Fiecare dezvoltator spune: „Acum vom scrie pe al nostru, va fi foarte ușor!”
Am luat o stivă tipică pentru serviciile web: un nucleu bazat pe Node.js + Express, am folosit Bootstrap și D3.js pentru diagrame frumoase. Și așteptările noastre au fost pe deplin justificate - am primit primul prototip în 2 săptămâni:
- analizator de plan personalizat
Adică, acum putem analiza orice plan din cele generate de PostgreSQL. - analiza corectă a nodurilor dinamice - CTE Scan, InitPlan, SubPlan
- analiza distribuției tampoanelor - unde paginile de date sunt citite din memorie, unde din memoria cache locală, unde de pe disc
- a primit claritate
Pentru a nu „sapa” toate acestea în jurnal, ci pentru a vedea „cea mai slabă verigă” imediat în imagine.
Avem ceva de genul acesta, cu evidențierea de sintaxă inclusă. Dar, de obicei, dezvoltatorii noștri nu mai lucrează cu o reprezentare completă a planului, ci cu una mai scurtă. Până la urmă, am analizat deja toate numerele și le-am aruncat în stânga și în dreapta, iar în mijloc am lăsat doar prima linie, ce fel de nod este: CTE Scan, CTE generation sau Seq Scan după vreun semn.
Aceasta este reprezentarea prescurtată pe care o numim șablon de plan.
Ce altceva ar fi convenabil? Ar fi convenabil să vedem ce parte din timpul nostru total este alocată nodului - și doar „lipiți-l” în lateral Graficul proporțiilor.
Arătăm spre nod și vedem - se dovedește că Seq Scan a durat mai puțin de un sfert din timpul total, iar restul de 3/4 a fost luat de CTE Scan. Groază! Aceasta este o mică notă despre „rata de foc” a scanării CTE dacă le utilizați în mod activ în interogările dumneavoastră. Nu sunt foarte rapide - sunt inferioare chiar și scanării obișnuite pe masă.
Dar, de obicei, astfel de diagrame sunt mai interesante, mai complexe, atunci când indicăm imediat un segment și vedem, de exemplu, că mai mult de jumătate din timp un Seq Scan „a mâncat”. Mai mult decât atât, înăuntru era un fel de filtru, multe înregistrări au fost aruncate conform lui... Puteți arunca direct această imagine dezvoltatorului și spune: „Vasya, totul este rău aici pentru tine! Descoperiți-vă, uite - ceva nu este în regulă!”
Desigur, au fost implicate niște „greble”.
Primul lucru pe care l-am întâlnit a fost problema rotunjirii. Timpul fiecărui nod individual din plan este indicat cu o precizie de 1 μs. Și când numărul de cicluri de noduri depășește, de exemplu, 1000 - după execuție, PostgreSQL a împărțit „cu precizie”, atunci când calculăm înapoi obținem timpul total „undeva între 0.95 ms și 1.05 ms”. Când numărul trece la microsecunde, este în regulă, dar când sunt deja [mili]secunde, trebuie să țineți cont de aceste informații atunci când „dezlegați” resursele de nodurile planului „cine a consumat cât de mult”.
Al doilea punct, mai complex, este distribuirea resurselor (acele buffere) între nodurile dinamice. Acest lucru ne-a costat primele 2 săptămâni ale prototipului plus alte 4 săptămâni.
Este destul de ușor să obțineți acest tip de problemă - facem un CTE și se presupune că citim ceva în el. De fapt, PostgreSQL este „inteligent” și nu va citi nimic direct acolo. Apoi luăm prima înregistrare din ea, iar la ea cea o sută întâi de la același CTE.
Ne uităm la plan și înțelegem - este ciudat, avem 3 buffere (pagini de date) „consumate” în Seq Scan, încă 1 în CTE Scan și încă 2 în a doua CTE Scan. Adică dacă însumăm totul pur și simplu, vom obține 6, dar de pe tabletă citim doar 3! CTE Scan nu citește nimic de nicăieri, dar funcționează direct cu memoria de proces. Adică, ceva este clar în neregulă aici!
De fapt, se dovedește că aici sunt toate acele 3 pagini de date care au fost solicitate de la Seq Scan, mai întâi 1 a cerut prima scanare CTE, apoi i-au fost citite a 1-a și încă 2. Adică un total de Au fost citite date 2 pagini, nu 3.
Și această imagine ne-a condus la înțelegerea că execuția unui plan nu mai este un arbore, ci pur și simplu un fel de grafic aciclic. Și am primit o diagramă ca aceasta, astfel încât să înțelegem „ce a venit de unde, în primul rând”. Adică, aici am creat un CTE de la pg_class și l-am cerut de două ori, iar aproape tot timpul nostru a fost petrecut pe ramură când l-am cerut a doua oară. Este clar că citirea celei de-a 2-a intrări este mult mai scumpă decât citirea primei intrări de pe tabletă.
Am expirat o vreme. Ei au spus: „Acum, Neo, știi kung fu! Acum experiența noastră este chiar pe ecranul tău. Acum îl poți folosi.”
Consolidarea jurnalelor
Cei 1000 de dezvoltatori ai noștri au răsuflat ușurați. Dar am înțeles că avem doar sute de servere „de luptă”, iar acest „copy-paste” din partea dezvoltatorilor nu este deloc convenabil. Ne-am dat seama că trebuie să-l colectăm singuri.
În general, există un modul standard care poate colecta statistici, cu toate acestea, trebuie să fie activat și în configurație - aceasta . Dar nu ni s-a potrivit.
În primul rând, atribuie acelorași interogări folosind scheme diferite în cadrul aceleiași baze de date diferite QueryIds. Adică dacă faci mai întâi SET search_path = '01'; SELECT * FROM user LIMIT 1;şi apoi SET search_path = '02'; și aceeași cerere, atunci statisticile acestui modul vor avea înregistrări diferite și nu voi putea colecta statistici generale în mod specific în contextul acestui profil de solicitare, fără a lua în considerare schemele.
Al doilea punct care ne-a împiedicat să-l folosim este lipsa de planuri. Adică nu există niciun plan, există doar cererea în sine. Vedem ce a încetinit, dar nu înțelegem de ce. Și aici revenim la problema unui set de date care se schimbă rapid.
Și ultimul moment - lipsa de „fapte”. Adică, nu puteți aborda o anumită instanță de execuție a unei interogări - nu există, există doar statistici agregate. Deși este posibil să lucrezi cu asta, este doar foarte dificil.
Prin urmare, am decis să luptăm cu copy-paste și am început să scriem colector.
Colectorul se conectează prin SSH, stabilește o conexiune sigură la server cu baza de date folosind un certificat și tail -F „se agață” de el în fișierul jurnal. Deci, în această sesiune obținem o „oglindă” completă a întregului fișier jurnal, pe care serverul îl generează. Încărcarea pe server în sine este minimă, deoarece nu analizăm nimic acolo, doar oglindim traficul.
Deoarece am început deja să scriem interfața în Node.js, am continuat să scriem colectorul în ea. Și această tehnologie s-a justificat, deoarece este foarte convenabil să utilizați JavaScript pentru a lucra cu date text slab formatate, care este jurnalul. Iar infrastructura Node.js în sine ca platformă de backend vă permite să lucrați ușor și convenabil cu conexiunile de rețea și, într-adevăr, cu orice flux de date.
În consecință, „întindem” două conexiuni: prima pentru a „asculta” jurnalul în sine și a-l lua la noi înșine, iar a doua pentru a întreba periodic baza. „Dar jurnalul arată că semnul cu oid 123 este blocat”, dar acest lucru nu înseamnă nimic pentru dezvoltator și ar fi bine să întrebați baza de date, „Ce este OID = 123 oricum?” Și astfel întrebăm periodic baza ce nu știm încă despre noi înșine.
„Există un singur lucru de care nu l-ai luat în considerare, există o specie de albine asemănătoare elefanților!...” Am început să dezvoltăm acest sistem când am vrut să monitorizăm 10 servere. Cea mai critică în înțelegerea noastră, unde au apărut unele probleme greu de rezolvat. Dar în primul trimestru am primit o sută pentru monitorizare - pentru că sistemul a funcționat, toată lumea și-a dorit, toată lumea a fost confortabil.
Toate acestea trebuie adunate, fluxul de date este mare și activ. De fapt, ceea ce monitorizăm, cu ce ne putem ocupa, este ceea ce folosim. De asemenea, folosim PostgreSQL ca stocare de date. Și nimic nu este mai rapid pentru a „turna” date în el decât operatorul COPY Nu încă.
Dar pur și simplu „turnarea” datelor nu este cu adevărat tehnologia noastră. Pentru că dacă aveți aproximativ 50 de solicitări pe secundă pe o sută de servere, atunci aceasta va genera 100-150 GB de jurnale pe zi. Prin urmare, a trebuit să „tăiem” cu grijă baza.
În primul rând, am făcut-o împărțirea pe zi, pentru că, în mare, pe nimeni nu este interesat de corelația dintre zile. Ce diferență are ceea ce ai avut ieri, dacă în seara asta ai lansat o nouă versiune a aplicației - și deja niște statistici noi.
În al doilea rând, am învățat (am fost forțați) foarte, foarte rapid de scris folosind COPY. Adică nu doar COPYpentru că el este mai rapid decât INSERT, și chiar mai repede.
Al treilea punct - trebuia abandonați declanșatoarele și, respectiv, cheile externe. Adică nu avem deloc integritate referenţială. Pentru că dacă aveți un tabel care are o pereche de FK și spuneți în structura bazei de date că „aici este o înregistrare de jurnal care este referită de FK, de exemplu, la un grup de înregistrări”, atunci când o inserați, PostgreSQL nu mai are nimic altceva decât cum să-l ia și să o facă sincer SELECT 1 FROM master_fk1_table WHERE ... cu identificatorul pe care încercați să-l introduceți - doar pentru a verifica dacă această înregistrare este prezentă acolo, că nu „despărțiți” această cheie străină odată cu introducerea dvs.
În loc de o înregistrare a tabelului țintă și a indecșilor acestuia, obținem avantajul suplimentar de a citi din toate tabelele la care se referă. Dar nu avem deloc nevoie de asta - sarcina noastră este să înregistrăm cât mai mult posibil și cât mai repede cu putință. Deci FK - jos!
Următorul punct este agregarea și hashing. Inițial, le-am implementat în baza de date - la urma urmei, este convenabil să o facem imediat, când ajunge o înregistrare, într-un fel de tabletă. „plus unu” chiar în trăgaci. Ei bine, este convenabil, dar același lucru rău - introduceți o înregistrare, dar sunteți forțat să citiți și să scrieți altceva dintr-un alt tabel. Mai mult decât atât, nu doar că citești și scrii, ci o faci și de fiecare dată.
Acum imaginați-vă că aveți un tabel în care pur și simplu numărați numărul de solicitări care au trecut printr-o anumită gazdă: +1, +1, +1, ..., +1. Și tu, în principiu, nu ai nevoie de asta - totul este posibil suma în memorie pe colector și trimiteți la baza de date dintr-o singură mișcare +10.
Da, în cazul unor probleme, integritatea dvs. logică se poate „destrama”, dar acesta este un caz aproape nerealist - pentru că aveți un server normal, are o baterie în controler, aveți un jurnal de tranzacții, un jurnal pe sistem de fișiere... În general, nu merită. Pierderea productivității pe care o obțineți din rularea declanșatoarelor/FK nu merită cheltuielile pe care le suportați.
Este la fel și cu hashingul. O anumită cerere zboară către tine, calculezi un anumit identificator din ea în baza de date, îl scrii în baza de date și apoi le spui tuturor. Totul este bine până când, în momentul înregistrării, vine la tine o a doua persoană care dorește să înregistreze același lucru - și te blochezi, iar acest lucru este deja rău. Prin urmare, dacă puteți transfera generarea unor ID-uri către client (față de baza de date), este mai bine să faceți acest lucru.
A fost perfect pentru noi să folosim MD5 din text - cerere, plan, șablon,... îl calculăm pe partea de colector și „turnăm” ID-ul gata făcut în baza de date. Lungimea MD5 și partiționarea zilnică ne permit să nu ne facem griji cu privire la posibilele coliziuni.
Dar pentru a înregistra toate acestea rapid, a trebuit să modificăm procedura de înregistrare în sine.
Cum scrieți de obicei datele? Avem un fel de set de date, îl împărțim în mai multe tabele, apoi îl COPIEM - mai întâi în primul, apoi în al doilea, în al treilea... Este incomod, pentru că se pare că scriem un flux de date în trei pași secvenţial. Neplăcut. Se poate face mai repede? Poate sa!
Pentru a face acest lucru, este suficient doar să descompuneți aceste fluxuri în paralel unele cu altele. Se dovedește că avem erori, solicitări, șabloane, blocări, ... zburând în fire separate - și scriem totul în paralel. Suficient pentru asta păstrați un canal COPY deschis în mod constant pentru fiecare tabel țintă individual.
Adică la colecționar întotdeauna există un flux, în care pot scrie datele de care am nevoie. Dar pentru ca baza de date să vadă aceste date și cineva să nu se blocheze în așteptarea ca aceste date să fie scrise, COPY trebuie întreruptă la anumite intervale. Pentru noi, cea mai eficientă perioadă a fost de aproximativ 100 ms - o închidem și o deschidem imediat din nou la aceeași masă. Și dacă nu avem suficient un flux în timpul anumitor vârfuri, atunci facem o grupare până la o anumită limită.
În plus, am aflat că pentru un astfel de profil de încărcare, orice agregare, atunci când înregistrările sunt colectate în loturi, este rea. Răul clasic este INSERT ... VALUES și alte 1000 de înregistrări. Pentru că în acel moment aveți un vârf de scriere pe suport și toți ceilalți care încearcă să scrie ceva pe disc vor aștepta.
Pentru a scăpa de astfel de anomalii, pur și simplu nu agregați nimic, nu tamponați deloc. Și dacă are loc tamponarea pe disc (din fericire, API-ul Stream din Node.js vă permite să aflați) - amânați această conexiune. Când primiți un eveniment că este din nou gratuit, scrieți-i din coada acumulată. Și în timp ce este ocupat, ia următorul gratuit din piscină și scrie-i.
Înainte de a introduce această abordare a înregistrării datelor, am avut operațiuni de scriere de aproximativ 4K și în acest fel am redus încărcarea de 4 ori. Acum au crescut de încă 6 ori datorită noilor baze de date monitorizate - până la 100MB/s. Și acum stocăm jurnalele din ultimele 3 luni într-un volum de aproximativ 10-15TB, sperând că în doar trei luni orice dezvoltator va putea rezolva orice problemă.
Înțelegem problemele
Dar pur și simplu colectarea tuturor acestor date este bună, utilă, relevantă, dar nu suficientă - trebuie înțeleasă. Pentru că acestea sunt milioane de planuri diferite pe zi.
Dar milioanele sunt de necontrolat, trebuie mai întâi să facem „mai mic”. Și, în primul rând, trebuie să decideți cum veți organiza acest lucru „mai mic”.
Am identificat trei puncte cheie:
- care a trimis această cerere
Adică din ce aplicație a „sosit”: interfață web, backend, sistem de plată sau altceva. - unde s-a întâmplat
Pe ce server anume? Pentru că dacă aveți mai multe servere sub o singură aplicație și dintr-o dată unul „devine prost” (pentru că „discul este putred”, „scurge de memorie”, o altă problemă), atunci trebuie să vă adresați în mod specific serverului. - ca problema s-a manifestat într-un fel sau altul
Pentru a înțelege „cine” ne-a trimis o solicitare, folosim un instrument standard - setarea unei variabile de sesiune: SET application_name = '{bl-host}:{bl-method}'; — trimitem numele gazdei logicii de business de la care provine cererea și numele metodei sau aplicației care a inițiat-o.
După ce am trecut „proprietarul” cererii, aceasta trebuie să fie scoasă în jurnal - pentru aceasta configuram variabila log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Pentru cei interesați, poate ce înseamnă totul. Se pare că vedem în jurnal:
- timp
- identificatori de proces și tranzacție
- numele bazei de date
- IP-ul persoanei care a trimis această solicitare
- și numele metodei
Apoi ne-am dat seama că nu este foarte interesant să ne uităm la corelația pentru o cerere între diferite servere. Nu se întâmplă adesea să ai o situație în care o aplicație se încurcă în mod egal ici și colo. Dar chiar dacă este același, uită-te la oricare dintre aceste servere.
Deci iată tăietura "un server - o zi" s-a dovedit a fi suficient pentru orice analiză.
Prima secțiune analitică este aceeași "probă" - o formă abreviată de prezentare a planului, curăţată de toţi indicatorii numerici. A doua tăietură este aplicația sau metoda, iar a treia tăietura este nodul de plan specific care ne-a creat probleme.
Când am trecut de la instanțe specifice la șabloane, am obținut două avantaje simultan:
- reducerea multiplă a numărului de obiecte pentru analiză
Trebuie să analizăm problema nu prin mii de interogări sau planuri, ci după zeci de șabloane. - cronologie
Adică, rezumând „faptele” într-o anumită secțiune, le puteți afișa aspectul în timpul zilei. Și aici puteți înțelege că, dacă aveți un fel de tipar care se întâmplă, de exemplu, o dată pe oră, dar ar trebui să se întâmple o dată pe zi, ar trebui să vă gândiți la ce a mers prost - cine a provocat-o și de ce, poate ar trebui să fie aici. nu ar trebui. Aceasta este o altă metodă de analiză nenumerică, pur vizuală.
Metodele rămase se bazează pe indicatorii pe care îi extragem din plan: de câte ori a apărut un astfel de model, timpul total și mediu, câte date au fost citite de pe disc și câte din memorie...
Pentru că, de exemplu, vii la pagina de analiză pentru gazdă, uite - ceva începe să se citească prea mult pe disc. Discul de pe server nu poate face față - cine citește din el?
Și puteți sorta după orice coloană și puteți decide cu ce vă veți ocupa chiar acum - încărcarea pe procesor sau pe disc, sau numărul total de solicitări... Am sortat, ne-am uitat la cele „de sus”, le-am reparat și a lansat o nouă versiune a aplicației.
Și imediat puteți vedea diferite aplicații care vin cu același șablon dintr-o solicitare ca SELECT * FROM users WHERE login = 'Vasya'. Frontend, backend, procesare... Și te întrebi de ce procesarea l-ar citi pe utilizator dacă nu interacționează cu el.
Modul opus este să vezi imediat din aplicație ce face. De exemplu, interfața este aceasta, aceasta, aceasta și aceasta o dată pe oră (cronologia ajută). Și întrebarea apare imediat: se pare că nu este treaba interfață să facă ceva o dată pe oră...
După ceva timp, ne-am dat seama că ne lipsesc agregatele statistici pe noduri de plan. Am izolat din planuri doar acele noduri care fac ceva cu datele tabelelor propriu-zise (le citesc/scriu prin index sau nu). De fapt, doar un aspect este adăugat în raport cu imaginea anterioară - câte înregistrări ne-a adus acest nod?, și câte au fost aruncate (Rânduri eliminate de filtru).
Nu aveți un index potrivit pe placă, îi faceți o cerere, trece peste index, intră în Seq Scan... ați filtrat toate înregistrările cu excepția uneia. De ce aveți nevoie de 100 de milioane de înregistrări filtrate pe zi? Nu este mai bine să ridicați indexul?
După ce am analizat toate planurile nod cu nod, ne-am dat seama că există unele structuri tipice în planuri care sunt foarte probabil să pară suspecte. Și ar fi frumos să îi spuneți dezvoltatorului: „Prietene, aici mai întâi citiți după index, apoi sortați și apoi tăiați” - de regulă, există o singură înregistrare.
Toți cei care au scris interogări au întâlnit probabil acest tipar: „Dați-mi ultima comandă pentru Vasya, data acesteia.” Și dacă nu aveți un index după dată sau nu există nicio dată în indexul pe care l-ați folosit, atunci veți calca exact pe aceeasi „grebla” .
Dar știm că aceasta este o „greblă” - așa că de ce să nu îi spuneți imediat dezvoltatorului ce ar trebui să facă. În consecință, atunci când deschide un plan acum, dezvoltatorul nostru vede imediat o imagine frumoasă cu sfaturi, în care îi spun imediat: „Ai probleme ici și colo, dar sunt rezolvate într-un fel și în altul.”
Ca urmare, cantitatea de experiență necesară pentru rezolvarea problemelor la început și acum a scăzut semnificativ. Acesta este genul de instrument pe care îl avem.
Sursa: www.habr.com