Vă sugerez să citiți transcrierea raportului lui Vladimir Sitnikov de la începutul anului 2016 „PostgreSQL și JDBC stoarce toată sucul”
Bună ziua Numele meu este Vladimir Sitnikov. Lucrez pentru NetCracker de 10 ani. Și mă interesează mai mult productivitatea. Tot ce ține de Java, tot ce ține de SQL este ceea ce îmi place.
Și astăzi voi vorbi despre ce am întâlnit în companie când am început să folosim PostgreSQL ca server de baze de date. Și lucrăm în mare parte cu Java. Dar ceea ce o să vă spun astăzi nu este doar despre Java. După cum a arătat practica, acest lucru se întâmplă și în alte limbi.
Vom vorbi:
- despre eșantionarea datelor.
- Despre salvarea datelor.
- Și, de asemenea, despre performanță.
- Și despre greblele subacvatice care sunt îngropate acolo.
Să începem cu o întrebare simplă. Selectăm un rând din tabel pe baza cheii primare.
Baza de date se află pe aceeași gazdă. Și toată această agricultură durează 20 de milisecunde.
Aceste 20 de milisecunde sunt multe. Dacă aveți 100 de astfel de solicitări, atunci petreceți timp pe secundă parcurgând aceste solicitări, adică pierdem timpul.
Nu ne place să facem asta și să ne uităm la ce ne oferă baza pentru asta. Baza de date ne oferă două opțiuni pentru executarea interogărilor.
Prima opțiune este o cerere simplă. Ce e bun la asta? Faptul că o luăm și o trimitem și nimic mai mult.
Baza de date are și o interogare avansată, care este mai complicată, dar mai funcțională. Puteți trimite separat o solicitare de analizare, execuție, legare de variabile etc.
Interogarea super extinsă este ceva pe care nu îl vom acoperi în raportul actual. Noi, poate, vrem ceva din baza de date și există o listă de dorințe care s-a format într-o anumită formă, adică asta ne dorim, dar este imposibil acum și în anul următor. Așa că tocmai am înregistrat-o și vom merge în jur zguduind oamenii principali.
Și ceea ce putem face este o interogare simplă și o interogare extinsă.
Ce este special la fiecare abordare?
O interogare simplă este bună pentru o execuție unică. Odată făcut și uitat. Și problema este că nu acceptă formatul de date binare, adică nu este potrivit pentru unele sisteme de înaltă performanță.
Interogare extinsă – vă permite să economisiți timp la analizare. Asta am făcut și am început să folosim. Acest lucru ne-a ajutat cu adevărat. Nu există doar economii la analizare. Există economii la transferul de date. Transferul datelor în format binar este mult mai eficient.
Să trecem la practică. Așa arată o aplicație tipică. Ar putea fi Java etc.
Am creat declarație. A executat comanda. Creat aproape. Unde este greșeala aici? Care este problema? Nici o problemă. Asta scrie în toate cărțile. Așa ar trebui scris. Daca vrei performanta maxima scrie asa.
Dar practica a arătat că acest lucru nu funcționează. De ce? Pentru că avem o metodă „în apropiere”. Și când facem asta, din punctul de vedere al bazei de date, se dovedește că este ca un fumător care lucrează cu o bază de date. Am spus „PARSE EXECUTE DEALLOCATE”.
De ce toată această creație suplimentară și descărcare de declarații? Nimeni nu are nevoie de ei. Dar ceea ce se întâmplă de obicei în PreparedStatements este că atunci când le închidem, ele închid totul din baza de date. Nu asta ne dorim.
Ne dorim, ca și oamenii sănătoși, să lucrăm cu baza. Ne-am luat și ne-am pregătit declarația o dată, apoi o executăm de mai multe ori. De fapt, de multe ori - aceasta este o dată în întreaga viață a aplicațiilor - au fost analizate. Și folosim același id de instrucțiune pe REST-uri diferite. Acesta este scopul nostru.
Cum putem realiza acest lucru?
Este foarte simplu - nu este nevoie să închideți declarațiile. O scriem astfel: „pregătește” „execută”.
Dacă lansăm așa ceva, atunci este clar că ceva se va revărsa undeva. Dacă nu este clar, îl poți încerca. Să scriem un benchmark care folosește această metodă simplă. Creați o declarație. Îl lansăm pe o anumită versiune a driverului și constatăm că se blochează destul de repede odată cu pierderea întregii memorie pe care o avea.
Este clar că astfel de erori sunt ușor de corectat. Nu voi vorbi despre ele. Dar voi spune că noua versiune funcționează mult mai rapid. Metoda este stupidă, dar totuși.
Cum să lucrezi corect? Ce trebuie să facem pentru asta?
În realitate, aplicațiile închid întotdeauna declarațiile. În toate cărțile se spune să se închidă, altfel se va scurge memoria.
Și PostgreSQL nu știe cum să pună în cache interogările. Este necesar ca fiecare sesiune să creeze acest cache pentru sine.
Și nici nu vrem să pierdem timpul cu analizarea.
Și, ca de obicei, avem două opțiuni.
Prima opțiune este să o luăm și să spunem că să încapsulăm totul în PgSQL. Există un cache acolo. Memorează totul în cache. Va iesi grozav. Am văzut asta. Avem 100500 de cereri. Nu funcționează. Nu suntem de acord să transformăm cererile în proceduri manual. Nu Nu.
Avem o a doua opțiune - luăm-o și tăiem-o singuri. Deschidem sursele și începem să tăiem. Am văzut și am văzut. S-a dovedit că nu este atât de greu de făcut.
Aceasta a apărut în august 2015. Acum există o versiune mai modernă. Și totul este grozav. Funcționează atât de bine încât nu schimbăm nimic în aplicație. Și chiar am încetat să ne gândim în direcția PgSQL, adică acest lucru a fost suficient pentru a reduce toate costurile generale la aproape zero.
În consecință, instrucțiunile pregătite de server sunt activate la a 5-a execuție pentru a evita risipa de memorie în baza de date la fiecare solicitare unică.
Vă puteți întreba – unde sunt numerele? Ce primesti? Și aici nu voi da numere, pentru că fiecare cerere are propria sa.
Interogările noastre au fost de așa natură încât am petrecut aproximativ 20 de milisecunde analizând interogările OLTP. Au fost 0,5 milisecunde pentru execuție, 20 de milisecunde pentru analizare. Solicitare – 10 KiB de text, 170 de linii de plan. Aceasta este o solicitare OLTP. Solicită 1, 5, 10 linii, uneori mai multe.
Dar nu am vrut să pierdem deloc 20 de milisecunde. L-am redus la 0. Totul e grozav.
Ce poți lua de aici? Dacă aveți Java, atunci luați versiunea modernă a driverului și vă bucurați.
Dacă vorbiți o altă limbă, atunci gândiți-vă - poate aveți nevoie și de asta? Pentru că din punctul de vedere al limbajului final, de exemplu, dacă PL 8 sau aveți LibPQ, atunci nu vă este evident că petreceți timp nu pe execuție, pe analiză, iar acest lucru merită verificat. Cum? Totul este gratuit.
Doar că există erori și unele particularități. Și vom vorbi despre ele chiar acum. Cea mai mare parte va fi despre arheologia industrială, despre ce am găsit, despre ce am întâlnit.
Dacă cererea este generată dinamic. S-a întâmplat. Cineva lipește șirurile împreună, rezultând o interogare SQL.
De ce este rău? Este rău pentru că de fiecare dată ajungem cu un șir diferit.
Și codul hash al acestui șir diferit trebuie citit din nou. Aceasta este într-adevăr o sarcină CPU - găsirea unui text lung de solicitare chiar și într-un hash existent nu este atât de ușoară. Prin urmare, concluzia este simplă - nu generați solicitări. Stocați-le într-o singură variabilă. Și bucurați-vă.
Următoarea problemă. Tipurile de date sunt importante. Există ORM-uri care spun că nu contează ce fel de NULL există, să fie un fel. Dacă Int, atunci spunem setInt. Și dacă NULL, atunci să fie întotdeauna VARCHAR. Și ce diferență face până la urmă ce NULL este acolo? Baza de date în sine va înțelege totul. Și această imagine nu funcționează.
În practică, bazei de date nu-i pasă deloc. Dacă ați spus prima dată că acesta este un număr și a doua oară ați spus că este un VARCHAR, atunci este imposibil să reutilizați instrucțiunile pregătite de server. Și în acest caz, trebuie să ne recreăm declarația.
Dacă executați aceeași interogare, asigurați-vă că tipurile de date din coloana dvs. nu sunt confuze. Trebuie să fii atent la NULL. Aceasta este o eroare comună pe care am avut-o după ce am început să folosim PreparedStatements
Bine, pornit. Poate că au luat șoferul. Și productivitatea a scăzut. Lucrurile s-au înrăutățit.
Cum se întâmplă asta? Este aceasta o eroare sau o caracteristică? Din păcate, nu a fost posibil să înțelegem dacă acesta este o eroare sau o caracteristică. Dar există un scenariu foarte simplu pentru reproducerea acestei probleme. Ea ne-a ținut într-o ambuscadă complet neașteptat. Și constă în eșantionarea literalmente dintr-o singură masă. Noi, desigur, am avut mai multe astfel de solicitări. De regulă, au inclus două sau trei mese, dar există un astfel de scenariu de redare. Luați orice versiune din baza de date și jucați-o.
Ideea este că avem două coloane, fiecare dintre acestea fiind indexată. Există un milion de rânduri într-o coloană NULL. Și a doua coloană conține doar 20 de rânduri. Când executăm fără variabile legate, totul funcționează bine.
Dacă începem să executăm cu variabile legate, adică executăm "?" sau „$1” pentru cererea noastră, ce obținem în final?
Prima execuție este conform așteptărilor. Al doilea este un pic mai rapid. Ceva a fost stocat în cache. Al treilea, al patrulea, al cincilea. Apoi bang - și ceva de genul ăsta. Și cel mai rău lucru este că asta se întâmplă la a șasea execuție. Cine a știut că este necesar să se facă exact șase execuții pentru a înțelege care este planul real de execuție?
Cine este vinovat? Ce s-a întâmplat? Baza de date conține optimizare. Și pare a fi optimizat pentru cazul generic. Și, în consecință, începând de la un moment dat, ea trece la un plan generic, care, din păcate, se poate dovedi a fi diferit. Se poate dovedi a fi la fel sau poate fi diferit. Și există un fel de valoare de prag care duce la acest comportament.
Ce poți face în privința asta? Aici, desigur, este mai greu să presupunem ceva. Există o soluție simplă pe care o folosim. Acesta este +0, OFFSET 0. Cu siguranță știți astfel de soluții. Pur și simplu o luăm și adăugăm „+0” la cerere și totul este în regulă. Îți voi arăta mai târziu.
Și există o altă opțiune - uitați-vă la planuri mai atent. Dezvoltatorul nu trebuie doar să scrie o solicitare, ci și să spună „explicați analiza” de 6 ori. Dacă este 5, nu va funcționa.
Și există o a treia opțiune - scrieți o scrisoare către pgsql-hackers. Am scris, totuși, nu este încă clar dacă acesta este un bug sau o caracteristică.
În timp ce ne gândim dacă acesta este o eroare sau o caracteristică, haideți să o reparăm. Să luăm cererea noastră și să adăugăm „+0”. Totul e bine. Două simboluri și nici nu trebuie să te gândești cum este sau ce este. Foarte simplu. Pur și simplu am interzis bazei de date să folosească un index pe această coloană. Nu avem un index pe coloana „+0” și gata, baza de date nu folosește indexul, totul este în regulă.
Aceasta este regula celor 6 explicații. Acum, în versiunile curente, trebuie să o faceți de 6 ori dacă aveți variabile legate. Dacă nu aveți variabile legate, aceasta este ceea ce facem. Și până la urmă tocmai această cerere este cea care eșuează. Nu este un lucru complicat.
S-ar părea, cât de mult este posibil? Un bug aici, un bug acolo. De fapt, bug-ul este peste tot.
Să aruncăm o privire mai atentă. De exemplu, avem două scheme. Schema A cu tabelul S și diagrama B cu tabelul S. Interogare – selectați datele dintr-un tabel. Ce vom avea în acest caz? Vom avea o eroare. Vom avea toate cele de mai sus. Regula este - un bug este peste tot, vom avea toate cele de mai sus.
Acum întrebarea este: „De ce?” S-ar părea că există documentație că, dacă avem o schemă, atunci există o variabilă „search_path” care ne spune unde să căutăm tabelul. S-ar părea că există o variabilă.
Care este problema? Problema este că declarațiile pregătite de server nu bănuiesc că search_path poate fi schimbată de cineva. Această valoare rămâne, parcă, constantă pentru baza de date. Și este posibil ca unele părți să nu prindă semnificații noi.
Desigur, asta depinde de versiunea pe care testați. Depinde de cât de serios diferă mesele. Iar versiunea 9.1 va executa pur și simplu vechile interogări. Noile versiuni pot prinde eroarea și vă pot spune că aveți o eroare.
Cum să o tratezi? Există o rețetă simplă - nu o face. Nu este nevoie să schimbați search_path în timp ce aplicația rulează. Dacă schimbați, este mai bine să creați o nouă conexiune.
Puteți discuta, adică deschideți, discutați, adăugați. Poate îi putem convinge pe dezvoltatorii bazei de date că atunci când cineva schimbă o valoare, baza de date ar trebui să-i spună clientului despre asta: „Uite, valoarea ta a fost actualizată aici. Poate că trebuie să resetați declarațiile și să le recreați?” Acum baza de date se comportă în secret și nu raportează în niciun fel că declarațiile s-au schimbat undeva în interior.
Și voi sublinia din nou - acesta este ceva care nu este tipic pentru Java. Vom vedea același lucru în PL/pgSQL unu la unu. Dar acolo va fi reprodus.
Să încercăm mai multe selecții de date. Alegem și alegem. Avem un tabel cu un milion de rânduri. Fiecare linie este un kilobyte. Aproximativ un gigabyte de date. Și avem o memorie de lucru în mașina Java de 128 de megaocteți.
Noi, așa cum se recomandă în toate cărțile, folosim procesarea fluxului. Adică deschidem resultSet și citim datele de acolo puțin câte puțin. Va funcționa? Va cădea din memorie? Vei citi puțin? Să avem încredere în baza de date, să avem încredere în Postgres. Nu credem. Vom cădea în afara memoriei? Cine a experimentat OutOfMemory? Cine a reusit sa o repare dupa aceea? Cineva a reușit să o repare.
Dacă aveți un milion de rânduri, nu puteți pur și simplu să alegeți. OFFSET/LIMIT este necesar. Cine este pentru această variantă? Și cine este în favoarea jocului cu autoCommit?
Aici, ca de obicei, cea mai neașteptată opțiune se dovedește a fi corectă. Și dacă dezactivați brusc autoCommit, vă va ajuta. De ce este asta? Știința nu știe despre asta.
Dar în mod implicit, toți clienții care se conectează la o bază de date Postgres preiau toate datele. PgJDBC nu face excepție în acest sens; selectează toate rândurile.
Există o variație a temei FetchSize, adică puteți spune la nivelul unei declarații separate că aici, vă rugăm să selectați datele cu 10, 50. Dar acest lucru nu funcționează până când dezactivați autoCommit. AutoCommit dezactivat - începe să funcționeze.
Dar parcurgerea codului și setarea setFetchSize peste tot este incomod. Prin urmare, am făcut o setare care va spune valoarea implicită pentru întreaga conexiune.
Asta am spus noi. Parametrul a fost configurat. Și ce am primit? Dacă selectăm sume mici, dacă, de exemplu, selectăm 10 rânduri odată, atunci avem costuri generale foarte mari. Prin urmare, această valoare ar trebui setată la aproximativ o sută.
În mod ideal, desigur, mai trebuie să înveți cum să-l limitezi în octeți, dar rețeta este următoarea: setează defaultRowFetchSize la mai mult de o sută și fii fericit.
Să trecem la inserarea datelor. Inserarea este mai ușoară, există diferite opțiuni. De exemplu, INSERT, VALUES. Aceasta este o opțiune bună. Puteți spune „INSERT SELECT”. În practică este același lucru. Nu există nicio diferență de performanță.
Cărțile spun că trebuie să executați o instrucțiune Batch, cărțile spun că puteți executa comenzi mai complexe cu mai multe paranteze. Și Postgres are o caracteristică minunată - puteți face COPY, adică o faceți mai repede.
Dacă o măsori, poți face din nou câteva descoperiri interesante. Cum vrem să funcționeze asta? Dorim să nu analizăm și să nu executăm comenzi inutile.
În practică, TCP nu ne permite să facem acest lucru. Dacă clientul este ocupat cu trimiterea unei cereri, atunci baza de date nu citește cererile în încercarea de a ne trimite răspunsuri. Rezultatul final este că clientul așteaptă ca baza de date să citească cererea, iar baza de date așteaptă ca clientul să citească răspunsul.
Și, prin urmare, clientul este forțat să trimită periodic un pachet de sincronizare. Interacțiuni suplimentare în rețea, pierdere suplimentară de timp.
Și cu cât le adăugăm mai multe, cu atât devine mai rău. Șoferul este destul de pesimist și le adaugă destul de des, cam o dată la 200 de linii, în funcție de dimensiunea liniilor etc.
Se întâmplă să corectezi o singură linie și totul se va accelera de 10 ori. S-a întâmplat. De ce? Ca de obicei, o constantă ca aceasta a fost deja folosită undeva. Și valoarea „128” a însemnat să nu folosească loturi.
E bine că asta nu a fost inclus în versiunea oficială. Descoperit înainte de a începe lansarea. Toate semnificațiile pe care le dau sunt bazate pe versiuni moderne.
Hai să-l încercăm. Măsurăm InsertBatch simplu. Măsurăm InsertBatch de mai multe ori, adică același lucru, dar există multe valori. Mișcare dificilă. Nu toată lumea poate face acest lucru, dar este o mișcare atât de simplă, mult mai ușoară decât COPIE.
Puteți face COPIE.
Și puteți face acest lucru pe structuri. Declarați tipul implicit de utilizator, treceți matrice și INSERT direct în tabel.
Dacă deschideți linkul: pgjdbc/ubenchmsrk/InsertBatch.java, atunci acest cod este pe GitHub. Puteți vedea în mod specific ce solicitări sunt generate acolo. Nu contează.
Ne-am lansat. Și primul lucru pe care ne-am dat seama a fost că a nu folosi lotul este pur și simplu imposibil. Toate opțiunile de loturi sunt zero, adică timpul de execuție este practic zero în comparație cu o execuție unică.
Inserăm date. Este o masă foarte simplă. Trei coloane. Și ce vedem aici? Vedem că toate aceste trei opțiuni sunt aproximativ comparabile. Și COPY este, desigur, mai bine.
Acesta este momentul în care introducem bucăți. Când am spus că o valoare VALUES, două valori VALORI, trei valori VALORI, sau am indicat 10 dintre ele separate prin virgulă. Acesta este doar orizontal acum. 1, 2, 4, 128. Se poate observa că Batch Insert, care este desenat cu albastru, îl face să se simtă mult mai bine. Adică atunci când introduci câte unul sau chiar când introduci câte patru, devine de două ori mai bun, pur și simplu pentru că ne-am înghesuit puțin mai mult în VALORI. Mai puține operațiuni EXECUTE.
Utilizarea COPY pe volume mici este extrem de nepromițătoare. Nici nu am desenat pe primele două. Se duc în rai, adică aceste numere verzi pentru COPIE.
COPY ar trebui folosit atunci când aveți cel puțin o sută de rânduri de date. Suprafața de deschidere a acestei conexiuni este mare. Și, să fiu sincer, nu am săpat în această direcție. Am optimizat Lot, dar nu COPY.
Ce facem mai departe? Am încercat-o. Înțelegem că trebuie să folosim fie structuri, fie o baie inteligentă care combină mai multe sensuri.
Ce ar trebui să scoți din raportul de astăzi?
- PreparedStatement este totul pentru noi. Acest lucru dă mult pentru productivitate. Produce un eșec mare în unguent.
- Și trebuie să faceți EXPLICARE ANALIZA de 6 ori.
- Și trebuie să diluăm OFFSET 0 și trucuri precum +0 pentru a corecta procentul rămas din interogările noastre problematice.
Sursa: www.habr.com