Predlažem da pročitate prijepis izvješća Vladimira Sitnikova s početka 2016. „PostgreSQL i JDBC cijede sav sok“
Dobar dan Moje ime je Vladimir Sitnikov. Za NetCracker radim 10 godina. I uglavnom sam u produktivnosti. Sve vezano uz Javu, sve vezano uz SQL je ono što volim.
A danas ću govoriti o tome s čime smo se susreli u tvrtki kada smo počeli koristiti PostgreSQL kao poslužitelj baze podataka. I uglavnom radimo s Javom. Ali ono što ću vam danas reći nije samo o Javi. Kao što je praksa pokazala, to se događa iu drugim jezicima.
Pričat ćemo:
- o uzorkovanju podataka.
- O spremanju podataka.
- I također o izvedbi.
- I o podvodnim grabljama koje su tamo zakopane.
Počnimo s jednostavnim pitanjem. Odaberemo jedan red iz tablice na temelju primarnog ključa.
Baza podataka nalazi se na istom hostu. A sva ova obrada traje 20 milisekundi.
Ovih 20 milisekundi je puno. Ako imate 100 takvih zahtjeva, onda trošite vrijeme u sekundi skrolajući po tim zahtjevima, tj. gubimo vrijeme.
Ne volimo to raditi i pogledajte što nam baza nudi za to. Baza nam nudi dvije opcije za izvršavanje upita.
Prva opcija je jednostavan zahtjev. Što je tu dobro? To što uzmemo i pošaljemo, i ništa više.
Baza podataka također ima napredni upit, koji je zahtjevniji, ali funkcionalniji. Možete zasebno poslati zahtjev za raščlanjivanje, izvršenje, vezanje varijable itd.
Super prošireni upit nešto je što nećemo obraditi u ovom izvješću. Mi, možda, želimo nešto iz baze podataka i postoji lista želja koja je formirana u nekom obliku, odnosno to je ono što želimo, ali to je nemoguće sada iu idućoj godini. Dakle, upravo smo to snimili i ići ćemo okolo tresti glavne ljude.
A ono što možemo učiniti je jednostavan upit i prošireni upit.
Što je posebno u svakom pristupu?
Jednostavan upit je dobar za jednokratno izvršavanje. Jednom učinjeno i zaboravljeno. A problem je što ne podržava binarni format podataka, tj. nije prikladan za neke sustave visokih performansi.
Prošireni upit – omogućuje uštedu vremena na raščlanjivanju. To smo mi napravili i počeli koristiti. Ovo nam je stvarno pomoglo. Nema ušteda samo na raščlanjivanju. Postoje uštede na prijenosu podataka. Prijenos podataka u binarnom formatu puno je učinkovitiji.
Prijeđimo na praksu. Ovako izgleda tipična aplikacija. To može biti Java itd.
Napravili smo izjavu. Izvršio naredbu. Stvoreno blizu. Gdje je tu greška? U čemu je problem? Nema problema. Tako piše u svim knjigama. Ovako treba pisati. Ako želite maksimalne performanse, pišite ovako.
Ali praksa je pokazala da to ne ide. Zašto? Zato što imamo "blizu" metodu. I kada to učinimo, sa stajališta baze podataka ispada da je to kao da pušač radi s bazom podataka. Rekli smo "PARSE EXECUTE DEALLOCATE".
Čemu sve ovo dodatno stvaranje i istovar izjava? Nikome ne trebaju. Ali ono što se obično događa u PreparedStatements je da kada ih zatvorimo, oni zatvore sve u bazi podataka. Ovo nije ono što želimo.
Želimo, kao zdravi ljudi, raditi s bazom. Izjavu smo uzeli i pripremili jednom, a onda je izvršavamo više puta. Zapravo, mnogo su puta - ovo je jednom u cijelom životu aplikacija - analizirane. I koristimo isti ID izjave na različitim REST-ovima. To je naš cilj.
Kako to možemo postići?
Vrlo je jednostavno - nema potrebe za zatvaranjem izjava. Pišemo to ovako: “pripremiti” “izvršiti”.
Ako ovako nešto pokrenemo, onda je jasno da će negdje nešto preliti. Ako nije jasno, možete ga isprobati. Napišimo mjerilo koje koristi ovu jednostavnu metodu. Stvorite izjavu. Pokrenemo ga na nekoj verziji upravljačkog programa i ustanovimo da se prilično brzo ruši uz gubitak sve memorije koju je imao.
Jasno je da se takve pogreške lako ispravljaju. Neću o njima. Ali reći ću da nova verzija radi mnogo brže. Metoda je glupa, ali ipak.
Kako ispravno raditi? Što trebamo učiniti za ovo?
U stvarnosti, aplikacije uvijek zatvaraju izjave. U svim knjigama kažu da je treba zatvoriti, inače će memorija procuriti.
A PostgreSQL ne zna kako keširati upite. Neophodno je da svaka sesija kreira ovu predmemoriju za sebe.
A ne želimo ni gubiti vrijeme na raščlanjivanje.
I kao i obično imamo dvije mogućnosti.
Prva opcija je da uzmemo i kažemo da sve omotamo u PgSQL. Tamo je cache. Predmemorira sve. Ispast će super. Vidjeli smo ovo. Imamo 100500 zahtjeva. Ne radi. Ne pristajemo na ručno pretvaranje zahtjeva u procedure. Ne ne.
Imamo drugu opciju - uzmite ga i izrežite sami. Otvaramo izvore i počinjemo rezati. Vidjeli smo i vidjeli. Ispostavilo se da to nije tako teško učiniti.
Ovo se pojavilo u kolovozu 2015. Sada postoji modernija verzija. I sve je super. Toliko dobro radi da ništa ne mijenjamo u aplikaciji. Čak smo i prestali razmišljati u smjeru PgSQL-a, tj. ovo nam je bilo sasvim dovoljno da sve režijske troškove svedemo gotovo na nulu.
Sukladno tome, iskazi pripremljeni od strane poslužitelja aktiviraju se pri 5. izvršavanju kako bi se izbjeglo gubljenje memorije u bazi podataka na svaki jednokratni zahtjev.
Možete se zapitati – gdje su brojevi? Što dobivaš? I ovdje neću navoditi brojke, jer svaki zahtjev ima svoj.
Naši su upiti bili takvi da smo potrošili oko 20 milisekundi na analizu OLTP upita. Bilo je 0,5 milisekundi za izvršenje, 20 milisekundi za raščlanjivanje. Zahtjev – 10 KiB teksta, 170 redaka plana. Ovo je OLTP zahtjev. Zahtijeva 1, 5, 10 redaka, ponekad i više.
Ali uopće nismo htjeli izgubiti 20 milisekundi. Sveli smo na 0. Sve je super.
Što možete ponijeti odavde? Ako imate Javu, onda uzmete modernu verziju drajvera i radujete se.
Ako govorite drugim jezikom, razmislite - možda vam i ovo treba? Jer s gledišta konačnog jezika, na primjer, ako imate PL 8 ili LibPQ, onda vam nije očito da trošite vrijeme ne na izvođenje, na raščlanjivanje, i to vrijedi provjeriti. Kako? Sve je besplatno.
Osim što ima grešaka i nekih osobitosti. I sada ćemo razgovarati o njima. Najviše će biti o industrijskoj arheologiji, o onome što smo pronašli, na što smo naišli.
Ako se zahtjev generira dinamički. Događa se. Netko spaja nizove zajedno, što rezultira SQL upitom.
Zašto je loš? Loše je jer svaki put završimo s drugim nizom.
I hashCode ovog različitog niza treba ponovno pročitati. Ovo je zapravo CPU zadatak - pronalaženje dugog teksta zahtjeva čak ni u postojećem hash-u nije tako jednostavno. Stoga je zaključak jednostavan – ne generirajte zahtjeve. Pohranite ih u jednu varijablu. I radujte se.
Sljedeći problem. Vrste podataka su važne. Postoje ORM-ovi koji kažu da nije bitno kakva NULL postoji, neka postoji neka vrsta. Ako je Int, onda kažemo setInt. A ako je NULL, neka uvijek bude VARCHAR. I kakva je razlika na kraju što je tu NULL? Sama baza podataka će sve razumjeti. I ova slika ne radi.
U praksi, baza podataka uopće ne mari. Ako ste prvi put rekli da je ovo broj, a drugi put da je to VARCHAR, tada je nemoguće ponovno upotrijebiti iskaze pripremljene na poslužitelju. I u ovom slučaju, moramo ponovno stvoriti našu izjavu.
Ako izvršavate isti upit, provjerite da tipovi podataka u vašem stupcu nisu pobrkani. Morate paziti na NULL. Ovo je uobičajena pogreška koju smo imali nakon što smo počeli koristiti PreparedStatements
U redu, uključeno. Možda su uzeli vozača. I produktivnost je pala. Stvari su postale loše.
Kako se to događa? Je li to greška ili značajka? Nažalost, nije bilo moguće razumjeti je li to greška ili značajka. Ali postoji vrlo jednostavan scenarij za reprodukciju ovog problema. Sasvim neočekivano nas je dočekala u zasjedi. A sastoji se od uzorkovanja doslovno iz jedne tablice. Imali smo, naravno, još takvih zahtjeva. U pravilu su uključivali dva ili tri stola, ali postoji takav scenarij reprodukcije. Uzmite bilo koju verziju iz svoje baze podataka i igrajte je.
Poanta je da imamo dva stupca, od kojih je svaki indeksiran. Postoji milijun redaka u jednom stupcu NULL. A drugi stupac sadrži samo 20 redaka. Kada izvršavamo bez vezanih varijabli, sve radi dobro.
Ako počnemo izvršavati s vezanim varijablama, tj. izvršavamo "?" ili "$1" za naš zahtjev, što na kraju dobivamo?
Prva egzekucija je očekivana. Drugi je malo brži. Nešto je spremljeno u predmemoriju. Treći, četvrti, peti. Onda prasak - i tako nešto. A najgore je što se to događa na šestoj ovrsi. Tko je znao da je potrebno napraviti točno šest egzekucija da bi se shvatilo kakav je stvarni plan egzekucije?
Tko je kriv? Što se dogodilo? Baza podataka sadrži optimizaciju. I čini se da je optimiziran za generički slučaj. I, sukladno tome, počevši od nekog trenutka, ona prelazi na generički plan, koji se, nažalost, može pokazati drugačijim. Može se pokazati da je isto, a može biti i drugačije. I postoji neka vrsta granične vrijednosti koja dovodi do ovakvog ponašanja.
Što možete učiniti u vezi s tim? Ovdje je, naravno, teže bilo što pretpostaviti. Postoji jednostavno rješenje koje koristimo. Ovo je +0, OFFSET 0. Sigurno znate takva rješenja. Samo uzmemo i dodamo "+0" zahtjevu i sve je u redu. pokazat ću ti kasnije.
A postoji još jedna opcija - pažljivije pogledajte planove. Programer mora ne samo napisati zahtjev, već i reći "objasni analizu" 6 puta. Ako je 5, neće raditi.
A postoji i treća opcija - napišite pismo pgsql-hackerima. Napisao sam, međutim, još nije jasno je li to greška ili značajka.
Dok razmišljamo je li ovo greška ili značajka, popravimo to. Uzmimo naš zahtjev i dodamo "+0". Sve je u redu. Dva simbola i ne morate ni razmišljati o tome kako je ili što je. Jako jednostavno. Jednostavno smo zabranili bazi podataka korištenje indeksa na ovom stupcu. Nemamo indeks na stupcu "+0" i to je to, baza ne koristi indeks, sve je u redu.
Ovo je pravilo 6 objasniti. Sada u trenutnim verzijama to morate učiniti 6 puta ako imate vezane varijable. Ako nemate vezane varijable, ovo je ono što mi radimo. I na kraju upravo taj zahtjev propada. Nije to škakljivo.
Čini se, koliko je moguće? Buba ovdje, buba ondje. Zapravo, greška je posvuda.
Pogledajmo pobliže. Na primjer, imamo dvije sheme. Shema A s tablicom S i dijagram B s tablicom S. Upit – odaberite podatke iz tablice. Što ćemo imati u ovom slučaju? Imat ćemo grešku. Imat ćemo sve navedeno. Pravilo je - bug je posvuda, imat ćemo sve navedeno.
Sada se postavlja pitanje: "Zašto?" Čini se da postoji dokumentacija da ako imamo shemu, onda postoji varijabla "search_path" koja nam govori gdje da tražimo tablicu. Čini se da postoji varijabla.
U čemu je problem? Problem je u tome što izjave koje je pripremio poslužitelj ne sumnjaju da search_path netko može promijeniti. Ova vrijednost ostaje konstantna za bazu podataka. A neki dijelovi možda neće poprimiti nova značenja.
Naravno, to ovisi o verziji na kojoj testirate. Ovisi o tome koliko se vaše tablice ozbiljno razlikuju. A verzija 9.1 jednostavno će izvršiti stare upite. Nove verzije mogu uhvatiti grešku i reći vam da imate grešku.
Kako to liječiti? Postoji jednostavan recept – nemojte to činiti. Nema potrebe mijenjati search_path dok je aplikacija pokrenuta. Ako promijenite, bolje je stvoriti novu vezu.
Možete raspravljati, tj. otvarati, raspravljati, dodavati. Možda možemo uvjeriti programere baze podataka da kada netko promijeni vrijednost, baza podataka treba reći klijentu o ovome: "Gledajte, vaša vrijednost je ažurirana ovdje. Možda trebate resetirati izjave i ponovno ih stvoriti?" Sada se baza ponaša tajno i ni na koji način ne javlja da su se izjave promijenile negdje unutra.
I opet ću naglasiti - ovo je nešto što nije tipično za Javu. Vidjet ćemo istu stvar u PL/pgSQL jedan na jedan. Ali tamo će se reproducirati.
Pokušajmo s odabirom više podataka. Biramo i biramo. Imamo tablicu s milijun redaka. Svaki redak je kilobajt. Otprilike gigabajt podataka. A imamo radnu memoriju u Java stroju od 128 megabajta.
Mi, kao što je preporučeno u svim knjigama, koristimo obradu toka. Odnosno, otvaramo resultSet i od tamo malo po malo čitamo podatke. Hoće li uspjeti? Hoće li ispasti iz sjećanja? Hoćeš li malo čitati? Vjerujmo u bazu podataka, vjerujmo u Postgres. Ne vjerujemo. Hoćemo li izgubiti pamćenje? Tko je iskusio OutOfMemory? Tko je to nakon toga uspio popraviti? Netko je to uspio popraviti.
Ako imate milijun redaka, ne možete samo birati. OFFSET/LIMIT je obavezan. Tko je za ovu opciju? A tko je za igranje s autoCommitom?
Ovdje se, kao i obično, najneočekivanija opcija pokazuje točnom. A ako iznenada isključite autoCommit, to će pomoći. Zašto je to? Znanost o tome ne zna.
Ali prema zadanim postavkama svi klijenti koji se povezuju s Postgres bazom podataka dohvaćaju cjelokupne podatke. PgJDBC nije iznimka u ovom pogledu; odabire sve retke.
Postoji varijacija teme FetchSize, tj. možete reći na razini zasebne izjave da ovdje odaberite podatke prema 10, 50. Ali to ne radi dok ne isključite autoCommit. Isključio autoCommit - počinje raditi.
Ali pregledavanje koda i postavljanje setFetchSize posvuda je nezgodno. Stoga smo napravili postavku koja će reći zadanu vrijednost za cijelu vezu.
To smo rekli. Parametar je konfiguriran. I što smo dobili? Ako biramo male iznose, ako npr. biramo 10 redova odjednom, onda imamo jako velike režijske troškove. Stoga bi ovu vrijednost trebalo postaviti na oko stotinu.
U idealnom slučaju, naravno, još morate naučiti kako ga ograničiti u bajtovima, ali recept je sljedeći: postavite defaultRowFetchSize na više od sto i budite sretni.
Prijeđimo na umetanje podataka. Umetanje je lakše, postoje različite mogućnosti. Na primjer, INSERT, VALUES. Ovo je dobra opcija. Možete reći "INSERT SELECT". U praksi je to ista stvar. Nema razlike u performansama.
Knjige kažu da trebate izvršiti Batch naredbu, knjige kažu da možete izvršiti složenije naredbe s nekoliko zagrada. A Postgres ima divnu značajku - možete raditi COPY, tj. raditi to brže.
Ako ga izmjerite, opet možete doći do zanimljivih otkrića. Kako želimo da ovo funkcionira? Ne želimo analizirati i ne izvršavati nepotrebne naredbe.
U praksi nam TCP to ne dopušta. Ako je klijent zauzet slanjem zahtjeva, tada baza podataka ne čita zahtjeve u pokušaju da nam pošalje odgovore. Krajnji rezultat je da klijent čeka da baza podataka pročita zahtjev, a baza podataka čeka da klijent pročita odgovor.
Stoga je klijent prisiljen povremeno slati sinkronizacijski paket. Dodatne mrežne interakcije, dodatni gubitak vremena.
I što ih više dodajemo, to je gore. Driver je prilično pesimističan i dodaje ih prilično često, otprilike jednom svakih 200 redaka, ovisno o veličini redaka itd.
Dogodi se da ispravite samo jedan red i sve se ubrza 10 puta. Događa se. Zašto? Kao i obično, ovakva konstanta je već negdje korištena. A vrijednost "128" značila je da se ne koristi grupiranje.
Dobro je da ovo nije uvršteno u službenu verziju. Otkriveno prije početka izdavanja. Sva značenja koja dajem temelje se na modernim verzijama.
Hajdemo ga isprobati. InsertBatch jednostavno mjerimo. Mjerimo InsertBatch više puta, tj. istu stvar, ali postoji mnogo vrijednosti. Lukav potez. Ne može svatko to učiniti, ali to je tako jednostavan potez, mnogo lakši od KOPIRANJA.
Možete učiniti COPY.
A to možete učiniti na strukturama. Deklarirajte zadani tip korisnika, proslijedite polje i INSERT izravno u tablicu.
Ako otvorite link: pgjdbc/ubenchmsrk/InsertBatch.java, onda je ovaj kod na GitHubu. Tamo možete vidjeti konkretno koji su zahtjevi generirani. Nema veze.
Pokrenuli smo. I prvo što smo shvatili je da je jednostavno nemoguće ne koristiti seriju. Sve opcije skupljanja su nula, tj. vrijeme izvršenja je praktički jednako nuli u usporedbi s jednokratnim izvršenjem.
Unosimo podatke. To je vrlo jednostavan stol. Tri stupca. I što vidimo ovdje? Vidimo da su sve tri opcije ugrubo usporedive. A COPY je, naravno, bolji.
Ovo je kada ubacujemo dijelove. Kada smo rekli da jedna vrijednost VRIJEDNOSTI, dvije vrijednosti VRIJEDNOSTI, tri vrijednosti VRIJEDNOSTI, ili smo naznačili njih 10 odvojenih zarezom. Ovo je sada samo horizontalno. 1, 2, 4, 128. Vidi se da mu je Batch Insert, koji je nacrtan plavom bojom, puno bolji. Odnosno, kada umetnete jedan po jedan ili čak kada umetnete četiri odjednom, postaje duplo bolji, jednostavno zato što smo malo više strpali u VRIJEDNOSTI. Manje operacija EXECUTE.
Korištenje COPY-a na malim količinama je krajnje neobećavajuće. Na prva dva nisam ni crtao. Idu u nebo, odnosno ove zelene brojke za KOPIR.
COPY treba koristiti kada imate najmanje stotinu redaka podataka. Troškovi otvaranja ove veze su veliki. I, da budem iskren, nisam kopao u ovom smjeru. Optimizirao sam Batch, ali ne i COPY.
Što ćemo dalje? Isprobali smo ga. Razumijemo da trebamo koristiti ili strukture ili pametan bacth koji kombinira nekoliko značenja.
Što biste trebali izdvojiti iz današnjeg izvješća?
- PreparedStatement je naše sve. To daje puno za produktivnost. To stvara veliki promašaj.
- I trebate napraviti EXPLAIN ANALIZE 6 puta.
- I trebamo razrijediti OFFSET 0 i trikove poput +0 kako bismo ispravili preostali postotak naših problematičnih upita.
Izvor: www.habr.com