Predlagam, da preberete prepis poročila Vladimirja Sitnikova z začetka leta 2016 »PostgreSQL in JDBC iztisneta ves sok«
Dober večer Moje ime je Vladimir Sitnikov. Za NetCracker delam že 10 let. In večinoma me zanima produktivnost. Vse, kar je povezano z Javo, vse, kar je povezano s SQL, mi je všeč.
In danes bom govoril o tem, na kaj smo naleteli v podjetju, ko smo začeli uporabljati PostgreSQL kot strežnik baz podatkov. In večinoma delamo z Javo. Toda to, kar vam bom danes povedal, ni samo o Javi. Kot je pokazala praksa, se to dogaja tudi v drugih jezikih.
Pogovarjali se bomo:
- o vzorčenju podatkov.
- O shranjevanju podatkov.
- In tudi o uspešnosti.
- In o podvodnih grabljicah, ki so tam zakopane.
Začnimo s preprostim vprašanjem. Iz tabele izberemo eno vrstico glede na primarni ključ.
База находится на том же хосте. И все это хозяйство занимает 20 миллисекунд.
Teh 20 milisekund je veliko. Če imate 100 takšnih zahtevkov, potem porabite čas na sekundo za premikanje po teh zahtevah, torej izgubljamo čas.
Tega ne počnemo radi in poglejmo, kaj nam za to ponuja baza. Baza podatkov nam ponuja dve možnosti za izvajanje poizvedb.
Prva možnost je preprosta zahteva. Kaj je dobrega na tem? To, da vzamemo in pošljemo, in nič več.
Baza podatkov ima tudi napredno poizvedbo, ki je bolj zapletena, a bolj funkcionalna. Posebej lahko pošljete zahtevo za razčlenjevanje, izvajanje, vezavo spremenljivk itd.
Super razširjena poizvedba je nekaj, česar v tem poročilu ne bomo obravnavali. Mi morda želimo nekaj iz baze in obstaja lista želja, ki je v neki obliki oblikovana, torej to je tisto, kar želimo, ampak to je zdaj in v naslednjem letu nemogoče. Zato smo ga pravkar posneli in bomo šli naokrog in pretresali glavne ljudi.
Kar lahko storimo, je preprosta poizvedba in razširjena poizvedba.
Kaj je posebnega pri vsakem pristopu?
Preprosta poizvedba je dobra za enkratno izvedbo. Enkrat narejeno in pozabljeno. In težava je v tem, da ne podpira binarnega zapisa podatkov, torej ni primeren za nekatere visoko zmogljive sisteme.
Razširjena poizvedba – omogoča prihranek časa pri razčlenjevanju. To smo storili in začeli uporabljati. To nam je res, res pomagalo. Prihranki niso samo pri razčlenjevanju. Obstajajo prihranki pri prenosu podatkov. Prenos podatkov v binarni obliki je veliko bolj učinkovit.
Перейдем к практике. Вот так выглядит типичное приложение. Это может быть Java и т. д.
Ustvarili smo izjavo. Izvedel ukaz. Ustvarjeno blizu. Kje je tukaj napaka? V čem je problem? Brez težav. Tako piše v vseh knjigah. Tako naj se piše. Če želite največjo zmogljivost, napišite takole.
A praksa je pokazala, da to ne gre. Zakaj? Ker imamo "blizu" metodo. In ko to naredimo, se z vidika baze podatkov izkaže, da je to tako, kot bi kadilec delal z bazo podatkov. Rekli smo "PARSE EXECUTE DEALLOCATE".
Zakaj vse to dodatno ustvarjanje in razkladanje izjav? Nihče jih ne potrebuje. Toda v PreparedStatements se običajno zgodi, da ko jih zapremo, zaprejo vse v bazi podatkov. To ni tisto, kar si želimo.
Želimo, kot zdravi ljudje, delati z bazo. Svojo izjavo smo vzeli in pripravili enkrat, nato pa jo večkrat izvedemo. Pravzaprav so bile velikokrat – to je enkrat v celotnem življenju aplikacij – razčlenjene. In uporabljamo isti ID stavka na različnih REST-ih. To je naš cilj.
Kako lahko to dosežemo?
Je zelo preprosto - ni vam treba zapreti izjav. Zapišemo takole: "pripravi" "izvedi".
Če lansiramo kaj takega, potem je jasno, da se bo nekje nekaj prelilo. Če ni jasno, lahko poskusite. Napišimo merilo uspešnosti, ki uporablja to preprosto metodo. Ustvari izjavo. Zaženemo ga na neki različici gonilnika in ugotovimo, da se precej hitro zruši z izgubo vsega pomnilnika, ki ga je imel.
Jasno je, da se takšne napake zlahka popravijo. Ne bom govoril o njih. Povedal pa bom, da nova različica deluje veliko hitreje. Metoda je neumna, a vseeno.
Kako delati pravilno? Kaj moramo storiti za to?
V resnici aplikacije vedno zaprejo izjave. V vseh knjigah piše, da ga je treba zapreti, sicer bo spomin uhajal.
In PostgreSQL ne ve, kako predpomniti poizvedbe. Potrebno je, da vsaka seja ustvari ta predpomnilnik zase.
In tudi ne želimo izgubljati časa z razčlenjevanjem.
In kot ponavadi imamo dve možnosti.
Prva možnost je, da vzamemo in rečemo, da zavijemo vse v PgSQL. Tam je predpomnilnik. Predpomni vse. Odlično bo izpadlo. Videli smo to. Imamo 100500 zahtev. Ne deluje. Ne strinjamo se z ročnim spreminjanjem zahtev v postopke. ne ne
У нас есть второй вариант – взять и самим запилить. Открываем исходники, начинаем пилить. Пилим-пилим. Оказалось, что не так это сложно сделать.
To se je pojavilo avgusta 2015. Zdaj obstaja sodobnejša različica. In vse je super. Deluje tako dobro, da v aplikaciji ne spreminjamo ničesar. In celo nehali smo razmišljati v smeri PgSQL, to je bilo čisto dovolj, da smo vse režijske stroške zmanjšali skoraj na nič.
Skladno s tem se stavki, ki jih pripravi strežnik, aktivirajo ob 5. izvedbi, da se izognemo zapravljanju pomnilnika v bazi podatkov pri vsaki enkratni zahtevi.
Lahko se vprašate – kje so številke? Kaj dobiš? In tukaj ne bom dal številk, ker ima vsaka zahteva svojo.
Naše poizvedbe so bile takšne, da smo za razčlenjevanje poizvedb OLTP porabili približno 20 milisekund. Za izvajanje je bilo 0,5 milisekunde, za razčlenjevanje pa 20 milisekund. Zahteva – 10 KiB besedila, 170 vrstic načrta. To je zahteva OLTP. Zahteva 1, 5, 10 vrstic, včasih več.
Vendar sploh nismo želeli izgubiti 20 milisekund. Zmanjšali smo ga na 0. Vse je super.
Kaj lahko odneseš od tu? Če imate Javo, potem vzamete sodobno različico gonilnika in se veselite.
Če govorite drug jezik, pomislite - morda potrebujete tudi to? Ker z vidika končnega jezika, na primer, če imate PL 8 ali LibPQ, potem vam ni očitno, da ne porabite časa za izvajanje, ampak za razčlenjevanje, in to je vredno preveriti. kako Vse je brezplačno.
Le da so napake in nekatere posebnosti. In zdaj bomo govorili o njih. Največ bo o industrijski arheologiji, o tem, kaj smo našli, na kaj smo naleteli.
Če je zahteva generirana dinamično. Zgodi se. Nekdo zlepi nize skupaj, rezultat pa je poizvedba SQL.
Zakaj je slab? Hudo je, ker vsakič na koncu dobimo drugo vrvico.
In hashCode tega drugačnega niza je treba znova prebrati. To je v resnici naloga procesorja - iskanje dolgega besedila zahteve v celo obstoječem zgoščenem znesku ni tako preprosto. Zato je zaključek preprost - ne ustvarjajte zahtev. Shranite jih v eno spremenljivko. In veselite se.
Naslednji problem. Vrste podatkov so pomembne. Obstajajo ORM-ji, ki pravijo, da ni pomembno, kakšna NULL obstaja, naj bo kakšna. Če Int, potem rečemo setInt. In če je NULL, naj bo vedno VARCHAR. In kakšna je razlika na koncu, kaj je tam NULL? Baza podatkov bo sama razumela vse. In ta slika ne deluje.
V praksi podatkovne zbirke sploh ne skrbijo. Če ste prvič rekli, da je to številka, drugič pa, da je VARCHAR, potem ni mogoče ponovno uporabiti stavkov, pripravljenih s strežnikom. In v tem primeru moramo našo izjavo ponovno ustvariti.
Če izvajate isto poizvedbo, se prepričajte, da vrste podatkov v vašem stolpcu niso zamenjane. Paziti morate na NULL. To je pogosta napaka, ki smo jo imeli, ko smo začeli uporabljati PreparedStatements
V redu, vklopljeno. Mogoče so vzeli voznika. In produktivnost je padla. Stvari so se poslabšale.
Kako se to zgodi? Je to napaka ali funkcija? Na žalost ni bilo mogoče razumeti, ali je to napaka ali funkcija. Vendar obstaja zelo preprost scenarij za reprodukcijo te težave. Popolnoma nepričakovano nas je postavila v zasedo. In je sestavljen iz vzorčenja dobesedno iz ene mize. Takih prošenj smo seveda imeli več. Praviloma so vključili dve ali tri mize, vendar obstaja tak scenarij predvajanja. Vzemite katero koli različico iz svoje zbirke podatkov in jo predvajajte.
Bistvo je, da imamo dva stolpca, od katerih je vsak indeksiran. V enem stolpcu NULL je milijon vrstic. In drugi stolpec vsebuje samo 20 vrstic. Ko izvajamo brez vezanih spremenljivk, vse deluje dobro.
Če začnemo z izvajanjem z vezanimi spremenljivkami, tj. izvedemo "?" ali "1 USD" za našo zahtevo, kaj na koncu dobimo?
Prva izvedba je po pričakovanjih. Drugi je malo hitrejši. Nekaj je bilo predpomnjenega. Tretji, četrti, peti. Potem pok - in nekaj podobnega. In najhuje je, da se to zgodi na šesti usmrtitvi. Kdo je vedel, da je treba narediti natanko šest usmrtitev, da bi razumeli, kakšen je dejanski izvedbeni načrt?
kdo je kriv Kaj se je zgodilo? Baza vsebuje optimizacijo. In zdi se, da je optimiziran za generični primer. In zato na neki točki preide na generični načrt, ki se na žalost lahko izkaže za drugačnega. Lahko se izkaže, da je enako, lahko pa tudi drugače. In obstaja nekakšna mejna vrednost, ki vodi do tega vedenja.
Kaj lahko storite glede tega? Tukaj je seveda težje kar koli domnevati. Obstaja preprosta rešitev, ki jo uporabljamo. To je +0, OFFSET 0. Zagotovo poznate takšne rešitve. Samo vzamemo in zahtevi dodamo »+0« in vse je v redu. Ti bom pokazal kasneje.
In obstaja še ena možnost - natančneje si oglejte načrte. Razvijalec ne sme samo napisati zahteve, ampak tudi 6-krat reči "razloži analizo". Če je 5, ne bo šlo.
In obstaja še tretja možnost - napišite pismo pgsql-hekerjem. Napisal sem, vendar še ni jasno, ali je to napaka ali funkcija.
Medtem ko razmišljamo, ali je to napaka ali funkcija, jo popravimo. Vzemimo našo zahtevo in dodamo "+0". Vse je vredu. Dva simbola in sploh vam ni treba razmišljati, kako je ali kaj je. Zelo preprosto. Bazi podatkov smo preprosto prepovedali uporabo indeksa v tem stolpcu. Nimamo indeksa na stolpcu "+0" in to je to, baza ne uporablja indeksa, vse je v redu.
To je pravilo 6 razlage. Zdaj v trenutnih različicah morate to narediti 6-krat, če imate vezane spremenljivke. Če nimate vezanih spremenljivk, naredimo to. In na koncu je ravno ta zahteva tista, ki spodleti. To ni zapletena stvar.
Zdi se, koliko je mogoče? Hrošč tukaj, hrošč tam. Pravzaprav je napaka povsod.
Pa poglejmo pobliže. Na primer, imamo dve shemi. Shema A s tabelo S in diagram B s tabelo S. Poizvedba – izberite podatke iz tabele. Kaj bomo imeli v tem primeru? Imeli bomo napako. Vse našteto bomo imeli. Pravilo je - hrošč je povsod, imeli bomo vse našteto.
Zdaj se postavlja vprašanje: "Zakaj?" Zdi se, da obstaja dokumentacija, da če imamo shemo, potem obstaja spremenljivka "search_path", ki nam pove, kje naj iščemo tabelo. Zdi se, da obstaja spremenljivka.
В чем проблема? Проблема в том, что server-prepared statements не подозревают, что search_path может кто-то менять. Вот это значение остается как бы константным для базы данных. И какие-то части могут не подхватить новые значения.
Seveda je to odvisno od različice, na kateri testirate. Odvisno od tega, kako resno se vaše mize razlikujejo. In različica 9.1 bo preprosto izvedla stare poizvedbe. Nove različice lahko ujamejo napako in vam sporočijo, da imate napako.
Kako ga zdraviti? Obstaja preprost recept – ne delajte tega. Med izvajanjem aplikacije ni treba spreminjati search_path. Če spremenite, je bolje ustvariti novo povezavo.
Lahko razpravljate, tj. odpirate, razpravljate, dodajate. Mogoče lahko prepričamo razvijalce baz podatkov, da ko nekdo spremeni vrednost, mora baza podatkov odjemalcu o tem povedati: »Poglejte, vaša vrednost je bila tukaj posodobljena. Morda morate ponastaviti izjave in jih znova ustvariti?« Zdaj se baza obnaša prikrito in na noben način ne poroča, da so se izjave spremenile nekje v njej.
In še enkrat poudarjam - to je nekaj, kar ni značilno za Javo. Enako bomo videli v PL/pgSQL ena proti ena. Ampak tam se bo reproducirala.
Poskusimo še z izbiro podatkov. Izbiramo in izbiramo. Imamo tabelo z milijon vrsticami. Vsaka vrstica je kilobajt. Približno gigabajt podatkov. In imamo delovni pomnilnik v Java stroju 128 megabajtov.
Kot je priporočeno v vseh knjigah, uporabljamo obdelavo toka. To pomeni, da odpremo resultSet in malo po malo preberemo podatke od tam. Bo delovalo? Bo padlo iz spomina? Boš malo bral? Zaupajmo v bazo podatkov, zaupajmo v Postgres. Ne verjamemo. Bomo izgubili spomin? Kdo je doživel OutOfMemory? Komu ga je potem uspelo popraviti? Nekomu je uspelo popraviti.
Če imate milijon vrstic, ne morete kar izbirati. Zahtevan je OFFSET/LIMIT. Kdo je za to možnost? In kdo je za igranje z autoCommit?
Tukaj se, kot običajno, najbolj nepričakovana možnost izkaže za pravilno. In če nenadoma izklopite autoCommit, bo pomagalo. Zakaj? Znanost o tem ne ve.
Toda privzeto vsi odjemalci, ki se povezujejo z bazo podatkov Postgres, pridobijo celotne podatke. PgJDBC v tem pogledu ni izjema; izbere vse vrstice.
Obstaja različica teme FetchSize, tj. na ravni ločene izjave lahko rečete, da tukaj prosim izberite podatke za 10, 50. Vendar to ne deluje, dokler ne izklopite samodejne potrditve. Izklopljen autoCommit - začne delovati.
Toda pregledovanje kode in nastavitev setFetchSize povsod je neprijetno. Zato smo naredili nastavitev, ki bo povedala privzeto vrednost za celotno povezavo.
To smo rekli. Parameter je bil konfiguriran. In kaj smo dobili? Če izberemo majhne zneske, če na primer izberemo 10 vrstic naenkrat, potem imamo zelo velike režijske stroške. Zato je treba to vrednost nastaviti na približno sto.
V idealnem primeru bi se morali seveda še naučiti omejiti v bajtih, a recept je naslednji: nastavite defaultRowFetchSize na več kot sto in bodite srečni.
Preidimo na vnos podatkov. Vstavljanje je lažje, možnosti so različne. Na primer INSERT, VALUES. To je dobra možnost. Lahko rečete "INSERT SELECT". V praksi je to isto. Ni razlike v delovanju.
V knjigah piše, da morate izvesti stavek Batch, v knjigah piše, da lahko izvajate bolj zapletene ukaze z več oklepaji. In Postgres ima čudovito lastnost - lahko naredite COPY, torej naredite to hitreje.
Če ga izmerite, lahko spet pridete do nekaj zanimivih odkritij. Kako želimo, da to deluje? Ne želimo razčleniti in ne izvajati nepotrebnih ukazov.
V praksi nam TCP tega ne omogoča. Če je odjemalec zaposlen s pošiljanjem zahteve, baza podatkov ne bere zahtev, ko nam poskuša poslati odgovore. Končni rezultat je, da odjemalec čaka, da baza podatkov prebere zahtevo, baza podatkov pa čaka, da odjemalec prebere odgovor.
Zato je odjemalec prisiljen občasno pošiljati sinhronizacijski paket. Dodatne omrežne interakcije, dodatna izguba časa.
In več kot jih dodajamo, slabše je. Gonilnik je precej pesimističen in jih dodaja precej pogosto, približno enkrat na 200 vrstic, odvisno od velikosti vrstic itd.
Zgodi se, da popravite samo eno vrstico in vse se bo pospešilo 10-krat. Zgodi se. Zakaj? Kot običajno je bila taka konstanta že nekje uporabljena. In vrednost "128" je pomenila, da se ne uporablja seriranje.
Še dobro, da to ni bilo vključeno v uradno različico. Odkrito pred začetkom izdaje. Vsi pomeni, ki jih navajam, temeljijo na sodobnih različicah.
Poskusimo ga. InsertBatch merimo enostavno. InsertBatch merimo večkrat, torej isto stvar, vendar je veliko vrednosti. Težka poteza. Tega ne zmore vsak, vendar je tako preprosta poteza, veliko lažja kot KOPIRANJE.
Lahko naredite COPY.
In to lahko storite na strukturah. Navedite privzeti tip uporabnika, posredujte matriko in INSERT neposredno v tabelo.
Če odprete povezavo: pgjdbc/ubenchmsrk/InsertBatch.java, potem je ta koda na GitHubu. Tam lahko natančno vidite, katere zahteve so ustvarjene. Ni važno.
Začeli smo. In prva stvar, ki smo jo ugotovili, je bila, da je neuporaba serije preprosto nemogoča. Vse paketne možnosti so enake nič, kar pomeni, da je čas izvedbe v primerjavi z enkratno izvedbo praktično nič.
Мы вставляем данные. Весьма там простая таблица. Три колонки. И что мы здесь видим? Мы видим, что все эти три варианта примерно сравнимы. И COPY, конечно, лучше.
To je čas, ko vstavimo kose. Ko smo rekli, da ena vrednost VREDNOSTI, dve vrednosti VREDNOSTI, tri vrednosti VREDNOSTI ali smo jih navedli 10, ločenih z vejico. To je zdaj samo vodoravno. 1, 2, 4, 128. Vidi se, da se zaradi Batch Insert, ki je narisan v modri barvi, počuti veliko bolje. Se pravi, ko vstavite enega po enega ali celo če vstavite štiri naenkrat, postane dvakrat boljši, preprosto zato, ker smo v VREDNOSTI stlačili malo več. Manj operacij EXECUTE.
Uporaba COPY na majhnih količinah je izjemno neobetavna. Prvih dveh sploh nisem risal. Gredo v nebesa, torej te zelene številke za KOPIRANJE.
COPY uporabite, ko imate vsaj sto vrstic podatkov. Stroški odprtja te povezave so veliki. In, če sem iskren, nisem kopal v tej smeri. Optimiziral sem Batch, ne pa tudi COPY.
Kaj bomo naredili naslednje? Poskusili smo ga. Zavedamo se, da moramo uporabiti bodisi strukture bodisi pameten bacth, ki združuje več pomenov.
Kaj bi morali vzeti iz današnjega poročila?
- PreparedStatement je naše vse. To daje veliko za produktivnost. Proizvaja velik flop v mazilu.
- In EXPLAIN ANALIZE morate narediti 6-krat.
- Razredčiti moramo OFFSET 0 in trike, kot je +0, da popravimo preostali odstotek naših problematičnih poizvedb.
Vir: www.habr.com