Predlažem da pročitate transkript izvještaja Vladimira Sitnikova iz ranog 2016. godine „PostgreSQL i JDBC istiskuju sav sok“
Dobar dan Moje ime je Vladimir Sitnikov. Za NetCracker radim 10 godina. I uglavnom se bavim produktivnošću. Sve što se odnosi na Javu, sve što se odnosi na SQL je ono što volim.
A danas ću govoriti o tome sa čime smo se susreli u kompaniji kada smo počeli da koristimo PostgreSQL kao server baze podataka. I uglavnom radimo sa Javom. Ali ono što ću vam danas reći nije samo o Javi. Kao što je praksa pokazala, to se dešava i u drugim jezicima.
razgovarat ćemo:
- o uzorkovanju podataka.
- O čuvanju podataka.
- I o performansama.
- I o podvodnim grabljama koje su tu zakopane.
Počnimo s jednostavnim pitanjem. Odabiremo jedan red iz tabele na osnovu primarnog ključa.
Baza podataka se nalazi na istom hostu. A sav ovaj uzgoj traje 20 milisekundi.
Ovih 20 milisekundi je puno. Ako imate 100 takvih zahtjeva, onda trošite vrijeme u sekundi na skrolovanje ovih zahtjeva, odnosno gubimo vrijeme.
Ne volimo ovo da radimo i gledamo šta nam baza nudi za to. Baza podataka nam nudi dvije opcije za izvršavanje upita.
Prva opcija je jednostavan zahtjev. Šta je tu dobro? Činjenica da mi to uzmemo i pošaljemo, i ništa više.
Baza podataka takođe ima napredni upit, koji je komplikovaniji, ali funkcionalniji. Možete zasebno poslati zahtjev za raščlanjivanje, izvršenje, povezivanje varijable, itd.
Super prošireni upit je nešto što nećemo pokrivati u ovom izvještaju. Mi, možda, želimo nešto iz baze i postoji lista želja koja je u nekom obliku formirana, odnosno to je ono što želimo, ali to je nemoguće sada i u narednoj godini. Tako da smo upravo snimili i idemo okolo drmajući glavne ljude.
A ono što možemo učiniti je jednostavan upit i prošireni upit.
Šta je posebno kod svakog pristupa?
Jednostavan upit je dobar za jednokratno izvršavanje. Jednom gotovo i zaboravljeno. A problem je što ne podržava binarni format podataka, odnosno nije pogodan za neke sisteme visokih performansi.
Prošireni upit – omogućava vam da uštedite vrijeme na raščlanjivanju. Ovo smo uradili i počeli da koristimo. Ovo nam je zaista pomoglo. Ne štedi se samo na raščlanjivanju. Postoje uštede na prijenosu podataka. Prijenos podataka u binarnom formatu je mnogo efikasniji.
Pređimo na praksu. Ovako izgleda tipična aplikacija. To može biti Java, itd.
Napravili smo izjavu. Izvršio naredbu. Created close. Gdje je tu greška? Šta je problem? Nema problema. To piše u svim knjigama. Ovako to treba pisati. Ako želite maksimalan učinak, pišite ovako.
Ali praksa je pokazala da to ne funkcionira. Zašto? Zato što imamo "bliski" metod. A kada to učinimo, sa stanovišta baze podataka ispada da je to kao pušač koji radi sa bazom podataka. Rekli smo "PARSE EXECUTE DEALLOCATE".
Čemu svo ovo dodatno kreiranje i rasterećenje izjava? Nikome nisu potrebni. Ali ono što se obično dešava u PreparedStatements je da kada ih zatvorimo, oni zatvaraju sve u bazi podataka. Ovo nije ono što želimo.
Želimo, kao zdravi ljudi, da radimo sa bazom. Jednom smo uzeli i pripremili našu izjavu, a onda je izvršimo mnogo puta. U stvari, mnogo puta - ovo je jednom u čitavom životu aplikacija - one su raščlanjene. I koristimo isti ID naredbe na različitim REST-ovima. Ovo 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. Hajde da napišemo benchmark koji koristi ovu jednostavnu metodu. Napravite izjavu. Pokrećemo ga na nekoj verziji drajvera i otkrijemo da se prilično brzo ruši uz gubitak sve memorije koju je imao.
Jasno je da se takve greš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? Šta treba da uradimo za ovo?
U stvarnosti, aplikacije uvijek zatvaraju izjave. U svim knjigama kažu da ga zatvorite, inače će pamćenje iscuriti.
A PostgreSQL ne zna kako keširati upite. Neophodno je da svaka sesija kreira ovu keš memoriju za sebe.
I ne želimo gubiti vrijeme na raščlanjivanje.
I kao i obično imamo dvije opcije.
Prva opcija je da uzmemo i kažemo da umotamo sve u PgSQL. Tamo postoji cache. Sprema sve. Ispast će odlično. Videli smo ovo. Imamo 100500 zahtjeva. Ne radi. Ne slažemo se da ručno pretvaramo zahtjeve u procedure. Ne ne.
Imamo drugu opciju - uzmemo i sami ga isečemo. Otvaramo izvore i počinjemo rezati. Videli smo i videli. Ispostavilo se da to nije tako teško učiniti.
Ovo se pojavilo u avgustu 2015. Sada postoji modernija verzija. I sve je super. Toliko dobro funkcionira da ništa ne mijenjamo u aplikaciji. I čak smo prestali razmišljati u smjeru PgSQL-a, odnosno to nam je bilo sasvim dovoljno da sve režijske troškove svedemo na gotovo nulu.
Shodno tome, naredbe pripremljene na serveru se aktiviraju pri 5. izvršenju kako bi se izbjeglo trošenje memorije u bazi podataka na svaki jednokratni zahtjev.
Možete pitati – gdje su brojevi? šta dobijaš? I ovdje neću davati brojeve, jer svaki zahtjev ima svoje.
Naši upiti su bili takvi da smo potrošili oko 20 milisekundi na raščlanjivanje OLTP upita. Bilo je 0,5 milisekundi za izvršenje, 20 milisekundi za raščlanjivanje. Zahtjev – 10 KiB teksta, 170 redova plana. Ovo je OLTP zahtjev. Zahtijeva 1, 5, 10 redova, ponekad i više.
Ali uopšte nismo hteli da gubimo 20 milisekundi. Smanjili smo je na 0. Sve je super.
Šta možete ponijeti odavde? Ako imate Javu, onda uzmite modernu verziju drajvera i radujte se.
Ako govorite drugim jezikom, onda razmislite – možda vam i ovo treba? Jer sa stanovišta finalnog jezika, na primjer, ako PL 8 ili imate LibPQ, onda vam nije očito da trošite vrijeme ne na izvršenje, na raščlanjivanje, i to vrijedi provjeriti. Kako? Sve je besplatno.
Osim što ima grešaka i nekih posebnosti. A o njima ćemo upravo sada. Najviše će biti o industrijskoj arheologiji, o onome što smo pronašli, na šta smo naišli.
Ako je zahtjev generiran dinamički. Dešava se. Neko zalijepi nizove zajedno, što rezultira SQL upitom.
Zašto je loš? Loše je jer svaki put završimo sa drugačijim nizom.
I hashCode ovog različitog niza treba ponovo pročitati. Ovo je zaista CPU zadatak - pronalaženje dugačkog teksta zahtjeva čak ni u postojećem hash-u nije tako lako. Stoga je zaključak jednostavan - nemojte generirati zahtjeve. Čuvajte ih u jednoj varijabli. I radujte se.
Sledeći problem. Tipovi podataka su važni. Postoje ORM-ovi koji kažu da nije važno kakav NULL postoji, neka postoji neka vrsta. Ako je Int, onda kažemo setInt. A ako je NULL, onda neka uvijek bude VARCHAR. I kakva je razlika na kraju šta je NULL? Sama baza podataka će sve razumjeti. A ova slika ne radi.
U praksi, bazu podataka uopšte nije briga. Ako ste prvi put rekli da je ovo broj, a drugi put ste rekli da je VARCHAR, onda je nemoguće ponovo koristiti iskaze pripremljene na serveru. I u ovom slučaju, moramo ponovo kreirati našu izjavu.
Ako izvršavate isti upit, uvjerite se da tipovi podataka u vašoj koloni nisu zbunjeni. Morate paziti na NULL. Ovo je uobičajena greška koju smo imali nakon što smo počeli koristiti PreparedStatements
Ok, uključeno. Možda su uzeli vozača. I produktivnost je opala. Stvari su postale loše.
Kako se to događa? Je li ovo greška ili karakteristika? Nažalost, nije bilo moguće razumjeti da li je ovo greška ili karakteristika. Ali postoji vrlo jednostavan scenarij za reprodukciju ovog problema. Potpuno neočekivano nas je zasjedala. I sastoji se od uzorkovanja bukvalno iz jedne tabele. Mi smo, naravno, imali više takvih zahtjeva. U pravilu su uključivale dva ili tri stola, ali postoji i takav scenario reprodukcije. Uzmite bilo koju verziju iz svoje baze podataka i igrajte je.
Poenta je da imamo dvije kolone, od kojih je svaka indeksirana. Postoji milion redova u jednoj NULL koloni. A druga kolona sadrži samo 20 redova. Kada izvršavamo bez vezanih varijabli, sve radi dobro.
Ako počnemo izvršavati s vezanim varijablama, tj. izvršimo "?" ili “$1” za naš zahtjev, šta ćemo na kraju dobiti?
Prvo izvršenje je prema očekivanjima. Drugi je malo brži. Nešto je keširano. Treće, četvrto, peto. Onda prasak - i tako nešto. A najgore je što se to dešava pri šestom pogubljenju. Ko je znao da je potrebno izvršiti tačno šest egzekucija da bi se shvatilo šta je zapravo plan izvršenja?
ko je kriv? Šta se desilo? Baza podataka sadrži optimizaciju. I čini se da je optimiziran za generički slučaj. I, shodno 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 drugačije. I postoji neka vrsta vrijednosti praga koja vodi do ovakvog ponašanja.
Šta možete učiniti povodom toga? Ovdje je, naravno, teže bilo šta 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. Pokazaću ti kasnije.
A postoji još jedna opcija - pažljivije pogledajte planove. Programer mora ne samo da napiše zahtjev, već i da izgovori „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 da li je ovo greška ili karakteristika.
Dok razmišljamo da li je ovo greška ili karakteristika, popravimo to. Uzmimo naš zahtjev i dodajmo "+0". Sve je uredu. Dva simbola i ne morate ni da razmišljate kako je ili šta je. Veoma jednostavno. Jednostavno smo zabranili bazi podataka da koristi indeks na ovoj koloni. Nemamo indeks u koloni “+0” i to je to, baza podataka ne koristi indeks, sve je u redu.
Ovo je pravilo od 6 objašnjenja. Sada u trenutnim verzijama morate to učiniti 6 puta ako imate vezane varijable. Ako nemate vezane varijable, to je ono što radimo. I na kraju, upravo taj zahtjev ne uspijeva. To nije škakljiva stvar.
Čini se, koliko je moguće? Buba ovdje, buba tamo. Zapravo, greška je svuda.
Pogledajmo izbliza. Na primjer, imamo dvije sheme. Šema A sa tabelom S i dijagram B sa tabelom S. Upit – odabir podataka iz tabele. Šta ćemo imati u ovom slučaju? Imaćemo grešku. Imaćemo sve navedeno. Pravilo je - greška je svuda, imaćemo sve navedeno.
Sada je pitanje: "Zašto?" Čini se da postoji dokumentacija da ako imamo šemu, onda postoji varijabla "search_path" koja nam govori gdje da tražimo tabelu. Čini se da postoji varijabla.
Šta je problem? Problem je u tome što iskazi pripremljeni na serveru ne sumnjaju da netko može promijeniti stazu pretraživanja. Ova vrijednost ostaje, takoreći, konstantna za bazu podataka. A neki dijelovi možda neće dobiti nova značenja.
Naravno, to ovisi o verziji na kojoj testirate. Zavisi od toga koliko se vaše tablice ozbiljno razlikuju. A verzija 9.1 će jednostavno izvršiti stare zahtjeve. Nove verzije mogu uhvatiti grešku i reći vam da imate grešku.
Kako to liječiti? Postoji jednostavan recept - nemojte to raditi. Nema potrebe mijenjati search_path dok je aplikacija pokrenuta. Ako se promijenite, bolje je stvoriti novu vezu.
Možete diskutovati, odnosno otvarati, raspravljati, dodavati. Možda možemo uvjeriti programere baze podataka da kada neko promijeni vrijednost, baza podataka treba reći klijentu o tome: „Vidi, tvoja vrijednost je ažurirana ovdje. Možda trebate resetirati izjave i ponovo ih kreirati?” Sada se baza podataka ponaša tajno i ni na koji način ne javlja da su se iskazi promijenili negdje unutra.
I opet ću naglasiti - to je nešto što nije tipično za Javu. Istu stvar ćemo vidjeti u PL/pgSQL jedan na jedan. Ali tamo će se reproducirati.
Pokušajmo još s odabirom podataka. Mi biramo i biramo. Imamo tabelu sa milion redova. Svaki red je kilobajt. Otprilike gigabajt podataka. I imamo radnu memoriju u Java mašini od 128 megabajta.
Mi, kao što se preporučuje u svim knjigama, koristimo stream obradu. To jest, otvaramo set rezultata i čitamo podatke malo po malo. Hoće li uspjeti? Hoće li ispasti iz sjećanja? Hoćeš li malo čitati? Vjerujmo bazi podataka, vjerujmo Postgresu. Ne vjerujemo. Hoćemo li ispasti iz OFMemory? Ko je iskusio OutOfMemory? Ko je to nakon toga uspio popraviti? Neko je to uspio popraviti.
Ako imate milion redova, ne možete samo birati. OFFSET/LIMIT je obavezan. Ko je za ovu opciju? A ko je za igranje sa autoCommit-om?
Ovdje se, kao i obično, ispostavilo da je najneočekivanija opcija ispravna. A ako iznenada isključite autoCommit, pomoći će. Žašto je to? Nauka ne zna za ovo.
Ali prema zadanim postavkama, svi klijenti koji se povezuju na Postgres bazu podataka preuzimaju cjelokupne podatke. PgJDBC nije izuzetak u ovom pogledu; on bira sve redove.
Postoji varijacija na temu FetchSize, tj. možete reći na nivou zasebne izjave da ovdje, molimo vas da odaberete podatke za 10, 50. Ali ovo ne funkcionira dok ne isključite autoCommit. Isključen autoCommit - počinje raditi.
Ali prolazak kroz kod i postavljanje setFetchSize svuda je nezgodno. Stoga smo napravili postavku koja će reći zadanu vrijednost za cijelu vezu.
To smo rekli. Parametar je konfiguriran. I šta smo dobili? Ako odaberemo male količine, ako, na primjer, odaberemo 10 redova odjednom, onda imamo vrlo velike režijske troškove. Stoga ovu vrijednost treba postaviti na oko stotinu.
U idealnom slučaju, naravno, još uvijek morate naučiti kako ga ograničiti u bajtovima, ali recept je sljedeći: postavite defaultRowFetchSize na više od sto i budite sretni.
Pređimo na umetanje podataka. Umetanje je lakše, postoje različite opcije. Na primjer, INSERT, VALUES. Ovo je dobra opcija. Možete reći “INSERT SELECT”. U praksi je ista stvar. Nema razlike u performansama.
Knjige kažu da morate izvršiti naredbu Batch, knjige kažu da možete izvršiti složenije komande sa nekoliko zagrada. A Postgres ima divnu osobinu - možete da uradite COPY, tj. uradite to brže.
Ako ga izmjerite, opet možete doći do zanimljivih otkrića. Kako želimo da ovo funkcionira? Želimo da ne analiziramo i ne izvršavamo nepotrebne komande.
U praksi, TCP nam to ne dozvoljava. 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.
I stoga je klijent primoran da povremeno šalje paket za sinhronizaciju. Dodatne mrežne interakcije, dodatno gubljenje vremena.
I što ih više dodamo, sve je gore. Vozač je prilično pesimističan i dodaje ih prilično često, otprilike jednom na svakih 200 redova, ovisno o veličini redova itd.
Dešava se da ispravite samo jednu liniju i sve će se ubrzati 10 puta. Dešava se. Zašto? Kao i obično, ovakva konstanta je već negdje korištena. A vrijednost “128” značila je da se ne koristi batching.
Dobro je što ovo nije uključeno u zvaničnu verziju. Otkriveno prije početka objavljivanja. Sva značenja koja dajem zasnovana su na modernim verzijama.
Hajde da probamo. Mi mjerimo InsertBatch jednostavno. InsertBatch mjerimo više puta, tj. istu stvar, ali ima mnogo vrijednosti. Tricky potez. Ne može svako ovo da uradi, ali to je tako jednostavan potez, mnogo lakši od COPY.
Možete uraditi COPY.
I to možete učiniti na strukturama. Deklarirajte zadani tip korisnika, proslijedite niz i INSERT direktno u tablicu.
Ako otvorite vezu: pgjdbc/ubenchmsrk/InsertBatch.java, onda je ovaj kod na GitHubu. Tamo možete vidjeti konkretno koji zahtjevi se generiraju. Nije bitno.
Pokrenuli smo. I prva stvar koju smo shvatili je da je nekorištenje serije jednostavno nemoguće. Sve opcije batchinga su nule, tj. vrijeme izvršenja je praktički nula u odnosu na jednokratno izvršenje.
Ubacujemo podatke. To je vrlo jednostavan stol. Tri kolone. I šta vidimo ovdje? Vidimo da su sve tri ove opcije otprilike uporedive. A COPY je, naravno, bolji.
Ovo je kada ubacujemo komade. Kada smo rekli da je jedna VRIJEDNOST, dvije VRIJEDNOSTI, tri VRIJEDNOSTI, ili smo naznačili 10 njih odvojenih zarezom. Ovo je sada samo horizontalno. 1, 2, 4, 128. Vidi se da mu se puno bolje osjeća Batch Insert, koji je nacrtan plavom bojom. Odnosno, kada ubacite jedan po jedan ili čak kada umetnete četiri odjednom, postaje duplo bolje, jednostavno zato što smo malo više ugurali u VRIJEDNOSTI. Manje EXECUTE operacija.
Korištenje COPY na malim količinama je izuzetno neobećavajuće. Nisam čak ni crtao na prva dva. Oni idu u raj, odnosno ovi zeleni brojevi za COPY.
COPY treba koristiti kada imate najmanje stotinu redova podataka. Troškovi otvaranja ove veze su veliki. I, da budem iskren, nisam kopao u ovom pravcu. Optimizirao sam Batch, ali ne i COPY.
Šta ćemo dalje? Probali smo. Razumijemo da trebamo koristiti ili strukture ili pametan bat koji kombinuje nekoliko značenja.
Šta biste trebali uzeti iz današnjeg izvještaja?
- PreparedStatement je naše sve. Ovo daje mnogo za produktivnost. To stvara veliki pad u masti.
- I treba da uradite ANALIZU OBJAŠNJENJA 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