Vladimir Sitnikov-en 2016 hasierako txostenaren transkripzioa irakurtzea gomendatzen dizut "PostgreSQL eta JDBC zuku guztia ateratzen ari dira"
Arratsalde on Nire izena Vladimir Sitnikov da. 10 urte daramatzat NetCracker-en lanean. Eta produktibitatean ari naiz gehienbat. Javarekin zerikusia duen guztia, SQLrekin zerikusia duen guztia da maite dudana.
Eta gaur PostgreSQL datu-base zerbitzari gisa erabiltzen hasi ginenean enpresan topatu genuenari buruz hitz egingo dut. Eta batez ere Javarekin lan egiten dugu. Baina gaur esango dizudana ez da Javari buruz soilik. Praktikak erakutsi duenez, beste hizkuntza batzuetan ere gertatzen da.
Hitz egingo dugu:
- datuen laginketari buruz.
- Datuak gordetzeari buruz.
- Baita errendimenduari buruz ere.
- Eta bertan lurperatuta dauden urpeko arrastei buruz.
Has gaitezen galdera sinple batekin. Taularen errenkada bat hautatzen dugu lehen gakoaren arabera.
Datu-basea ostalari berean dago. Eta laborantza horrek guztiak 20 milisegundo behar ditu.
20 milisegundo hauek asko dira. Horrelako 100 eskaera badituzu, segundoko denbora ematen duzu eskaera hauek mugitzen, hau da, denbora galtzen ari gara.
Ez zaigu gustatzen hau egitea eta begira zer eskaintzen digun oinarriak horretarako. Datu-baseak bi aukera eskaintzen dizkigu kontsultak egiteko.
Lehenengo aukera eskaera sinple bat da. Zer da ona? Hartu eta bidaltzen dugula, eta kito.
Datu-baseak kontsulta aurreratu bat ere badu, zailagoa, baina funtzionalagoa. Analisia, exekuzioa, aldagaiak lotzeko, etab. eskaera bana bidali dezakezu.
Kontsulta super hedatua oraingo txostenean landuko ez dugun zerbait da. Guk, beharbada, datu-basetik zerbait nahi dugu eta badago nolabait eratu den nahi zerrenda bat, hau da, hau da nahi duguna, baina ezinezkoa da orain eta hurrengo urtean. Beraz, grabatu besterik ez dugu egin eta jende nagusia astintzen ibiliko gara.
Eta egin dezakeguna kontsulta sinplea eta kontsulta hedatua da.
Zer du berezi ikuspegi bakoitzak?
Kontsulta sinple bat ona da behin-behineko exekuziorako. Behin eginda eta ahaztuta. Eta arazoa da ez duela datu bitar formatua onartzen, hau da, ez da egokia errendimendu handiko sistema batzuetarako.
Kontsulta hedatua: analisian denbora aurrezteko aukera ematen du. Hauxe egin genuen eta erabiltzen hasi ginen. Honek benetan, benetan lagundu digu. Analisian ez dago aurrezten soilik. Datuen transferentzian aurrezten da. Datuak formatu bitarrean transferitzea askoz eraginkorragoa da.
Goazen praktikara. Hau da aplikazio tipiko baten itxura. Java izan daiteke, etab.
Adierazpena sortu dugu. Agindua exekutatu du. Gertu sortua. Non dago hemen akatsa? Zein da arazoa? Arazorik ez. Hauxe dio liburu guztietan. Horrela idatzi behar da. Errendimendu handiena nahi baduzu, idatzi honela.
Baina praktikak erakutsi du horrek ez duela funtzionatzen. Zergatik? Metodo βhurbilβ dugulako. Eta hau egiten dugunean, datu-basearen ikuspuntutik datu-base batekin lan egiten duen erretzaile bat bezalakoa dela ematen du. "PARSE EXECUTE DEALLOCATE" esan genuen.
Zergatik adierazpenen sortze eta deskarga gehigarri hori guztia? Inork ez ditu behar. Baina PreparedStatements-en gertatu ohi dena da ixten ditugunean datu-basean dagoen guztia ixten dutela. Hau ez da nahi duguna.
Guk, pertsona osasuntsuak bezala, oinarriarekin lan egin nahi dugu. Behin hartu eta prestatu genuen gure adierazpena, gero askotan exekutatzen dugu. Izan ere, askotan - hau aplikazioen bizitza osoan behin da - analizatu egin dira. Eta adierazpen-id bera erabiltzen dugu REST desberdinetan. Hau da gure helburua.
Nola lor dezakegu hori?
Oso erraza da: ez dago adierazpenak itxi beharrik. Honela idazten dugu: βprestatuβ βexekutatuβ.
Horrelako zerbait abian jartzen badugu, argi dago zerbait gainezka egingo duela nonbait. Argi ez badago, probatu dezakezu. Idatz dezagun metodo sinple hau erabiltzen duen erreferentzia. Sortu adierazpen bat. Gidariaren bertsio batzuetan abiarazten dugu eta zeukan memoria guztia galduta nahiko azkar huts egiten duela ikusten dugu.
Argi dago horrelako akatsak erraz zuzentzen direla. Ez dut horietaz hitz egingo. Baina bertsio berriak askoz azkarrago funtzionatzen duela esango dut. Metodoa astakeria da, baina hala ere.
Nola egin behar den lan? Zer egin behar dugu horretarako?
Egia esan, aplikazioek beti ixten dituzte adierazpenak. Liburu guztietan ixteko esaten dute, bestela memoria galduko da.
Eta PostgreSQL-k ez daki kontsultak cachean gordetzen. Beharrezkoa da saio bakoitzak bere katxe hori sortzea.
Eta ez dugu denborarik galdu nahi analisian ere.
Eta ohi bezala bi aukera ditugu.
Lehenengo aukera da hartu eta esan dezagun dena PgSQL-n biltzeko. Cache bat dago hor. Dena gordetzen du. Handia aterako da. Hau ikusi genuen. 100500 eskaera ditugu. Ez dabil. Ez dugu onartzen eskaerak eskuz prozedura bihurtzea. Ez ez.
Bigarren aukera dugu: hartu eta geuk moztu. Iturriak ireki eta mozten hasten gara. Ikusi eta ikusi genuen. Agertu zen ez dela hain zaila egitea.
Hau 2015eko abuztuan agertu zen. Orain bertsio modernoagoa dago. Eta dena bikaina da. Hain ondo funtzionatzen du, ez dugu aplikazioan ezer aldatzen. Eta PgSQL-ren norabidean pentsatzeari ere utzi genion, hau da, nahikoa zen kostu orokorrak ia zerora murrizteko.
Horren arabera, zerbitzariak prestatutako instrukzioak 5. exekuzioan aktibatzen dira, behin-behineko eskaera bakoitzean datu-basean memoria alferrik gal ez dadin.
Galdetuko duzu: non daude zenbakiak? Zer lortzen duzu? Eta hemen ez dut zenbakirik emango, eskaera bakoitzak berea duelako.
Gure kontsultak OLTP kontsultak analizatzen 20 milisegundo inguru eman genituen. 0,5 milisegundo zegoen exekutatzeko, 20 milisegundo analizatzeko. Eskaera - 10 KiB testu, 170 plano-lerro. Hau OLTP eskaera bat da. 1, 5, 10 lerro eskatzen ditu, batzuetan gehiago.
Baina ez genuen 20 milisegundo batere alferrik galdu nahi. 0ra murriztu dugu. Dena bikaina da.
Zer eraman dezakezu hemendik? Java baduzu, gidariaren bertsio modernoa hartu eta pozten zara.
Beste hizkuntza bat hitz egiten baduzu, pentsa: agian hau ere behar duzu? Zeren azken hizkuntzaren ikuspuntutik, adibidez, PL 8 edo LibPQ baduzu, orduan ez zaizu nabaritzen denbora ez exekuzioan, analisian ematen ari zarenik, eta hori egiaztatzea merezi du. Nola? Dena doakoa da.
Akatsak eta berezitasun batzuk daudela izan ezik. Eta horietaz hitz egingo dugu oraintxe bertan. Gehiena arkeologia industrialari buruzkoa izango da, aurkitu dugunaz, topatu dugunaz.
Eskaera dinamikoki sortzen bada. Gertatzen da. Norbaitek kateak itsatsi egiten ditu, SQL kontsulta bat sortuz.
Zergatik da txarra? Txarra da, aldi bakoitzean kate ezberdin batekin bukatzen dugulako.
Eta kate ezberdin honen hashCode berriro irakurri behar da. Hau benetan CPU zeregina da - lehendik dagoen hash batean ere eskaera testu luze bat aurkitzea ez da hain erraza. Hori dela eta, ondorioa erraza da: ez sortu eskaerak. Gorde itzazu aldagai batean. Eta poztu.
Hurrengo arazoa. Datu motak garrantzitsuak dira. Badaude ORM-ak esaten dutena berdin dela zer NULL mota dagoen, izan dadila nolabaiteko. Int bada, setInt esaten dugu. Eta NULL bada, bedi beti VARCHAR. Eta zer desberdintasun du azkenean NULL dagoenak? Datu-baseak berak dena ulertuko du. Eta argazki honek ez du funtzionatzen.
Praktikan, datu-baseari ez zaio batere axola. Lehenengo aldiz zenbaki bat dela esan baduzu eta bigarrenean VARCHAR dela esan baduzu, ezinezkoa da Zerbitzariak prestatutako adierazpenak berrerabiltzea. Eta kasu honetan, gure adierazpena berriro sortu behar dugu.
Kontsulta bera exekutatzen ari bazara, ziurtatu zure zutabeko datu-motak ez daudela nahasirik. Kontuz ibili behar duzu NULL-ekin. PreparedStatements erabiltzen hasi ondoren izan dugun errore arrunta da
Ados, piztuta. Agian gidaria hartu zuten. Eta produktibitatea jaitsi egin zen. Gauzak txarto egin ziren.
Nola gertatzen da hau? Hau akats bat edo ezaugarri bat al da? Zoritxarrez, ezin izan da ulertu hau akats bat edo funtzio bat den. Baina arazo hau erreproduzitzeko eszenatoki oso sinplea dago. Erabat ustekabean segada eman zigun. Eta literalki mahai batetik laginak hartzean datza. Guk, noski, horrelako eskaera gehiago genituen. Oro har, bizpahiru mahai sartzen zituzten, baina badago erreprodukzio-eszenatoki bat. Hartu zure datu-baseko edozein bertsio eta erreproduzitu.
Kontua da bi zutabe ditugula, eta horietako bakoitza indexatuta dago. Milioi bat errenkada daude NULL zutabe batean. Eta bigarren zutabeak 20 lerro baino ez ditu. Lotutako aldagairik gabe exekutatzen dugunean, dena ondo funtzionatzen du.
Lotutako aldagaiekin exekutatzen hasten bagara, hau da, "?" edo β$1β gure eskaerarako, zer lortzen dugu azkenean?
Lehen exekuzioa espero zen bezala da. Bigarrena pixka bat azkarragoa da. Zerbait gorde zen cachean. Hirugarren, laugarren, bosgarren. Orduan kolpatu... eta horrelako zerbait. Eta okerrena da hori seigarren exekuzioan gertatzen dela. Nork zekien zehatz-mehatz sei exekuzio egitea beharrezkoa zela benetako exekuzio plana zein zen ulertzeko?
Nor da erruduna? Zer gertatu da? Datu-baseak optimizazioa dauka. Eta badirudi kasu generikorako optimizatuta dagoela. Eta, horren arabera, uneren batean hasita, plan generiko batera aldatzen da, eta hori, zoritxarrez, desberdina izan daiteke. Baliteke berdina izatea, edo desberdina izan daiteke. Eta bada portaera hori ekartzen duen atalase-balioren bat.
Zer egin dezakezu horri buruz? Hemen, noski, zailagoa da ezer bere gain hartzea. Erabiltzen dugun irtenbide sinple bat dago. Hau +0 da, OFFSET 0. Ziur horrelako irtenbideak ezagutzen dituzula. Hartu eta eskaerari "+0" gehitzen diogu eta dena ondo dago. Gero erakutsiko dizut.
Eta beste aukera bat dago: begiratu planak arretaz. Garatzaileak eskaera bat idatzi ez ezik, "azaldu aztertu" ere esan behar du 6 aldiz. 5 bada, ez du funtzionatuko.
Eta hirugarren aukera bat dago: idatzi gutun bat pgsql-hackers-i. Idatzi nuen, hala ere, oraindik ez dago argi hau akats bat edo ezaugarri bat den.
Hau akats bat edo funtzio bat den pentsatzen ari garen bitartean, konpon dezagun. Har dezagun gure eskaera eta gehitu "+0". Dena ondo dago. Bi sinbolo eta ez duzu nola den edo zer den pentsatu behar. Oso sinplea. Datu-baseari zutabe honetako indize bat erabiltzea debekatu diogu. "+0" zutabean ez dugu indizerik eta kitto, datu-baseak ez du indizea erabiltzen, dena ondo dago.
Hau da 6ko araua azaldu. Orain egungo bertsioetan 6 aldiz egin behar duzu aldagai lotuak badituzu. Lotutako aldagairik ez baduzu, hau da guk egiten duguna. Eta azkenean eskaera hori da huts egiten duena. Ez da gauza delikatua.
Badirudi, zenbat da posible? Akats bat hemen, akats bat hor. Egia esan, akatsa nonahi dago.
Ikus dezagun hurbilagotik. Adibidez, bi eskema ditugu. A eskema S taularekin eta B diagrama S taularekin. Kontsulta: hautatu taula bateko datuak. Zer izango dugu kasu honetan? Errore bat izango dugu. Aurreko guztia izango dugu. Araua hauxe da: akats bat nonahi dago, goiko guztia izango dugu.
Orain galdera hau da: "Zergatik?" Badirudi dokumentazioa badagoela eskema bat badugu, orduan "search_path" aldagai bat dagoela taula non bilatu behar dugun esaten diguna. Badirudi aldagai bat dagoela.
Zein da arazoa? Arazoa da zerbitzariak prestatutako adierazpenek ez dutela susmatzen bilaketa_bidea norbaitek alda dezakeenik. Balio hori konstante izaten jarraitzen du datu-basearentzat. Eta zati batzuek ez dute esanahi berririk jaso.
Noski, hori probatzen ari zaren bertsioaren araberakoa da. Zure mahaiak zenbateraino diren desberdinen araberakoa da. Eta 9.1 bertsioak kontsulta zaharrak exekutatuko ditu. Bertsio berriek akatsa atzeman dezakete eta akats bat duzula esango dizute.
Nola tratatu? Errezeta sinple bat dago - ez egin. Ez dago bilaketa_bidea aldatu beharrik aplikazioa exekutatzen ari den bitartean. Aldatzen baduzu, hobe da konexio berri bat sortzea.
Eztabaida dezakezu, hau da, ireki, eztabaidatu, gehitu. Agian datu-baseen garatzaileak konbentzitu ditzakegu norbaitek balio bat aldatzen duenean datu-baseak bezeroari hau esan behar diola: βBegira, zure balioa hemen eguneratu da. Agian adierazpenak berrezarri eta birsortu behar dituzu?" Orain datu-baseak ezkutuan jokatzen du eta ez du inola ere jakinarazi adierazpenak barruan nonbait aldatu direnik.
Eta berriro azpimarratuko dut - Javarako ohikoa ez den zerbait da. Gauza bera ikusiko dugu PL/pgSQL-n banan-banan. Baina bertan erreproduzituko da.
Saia gaitezen datuen aukeraketa gehiago. Guk aukeratu eta aukeratzen dugu. Milioi bat errenkada dituen taula dugu. Lerro bakoitza kilobyte bat da. Gigabyte bat datu gutxi gorabehera. Eta lan memoria bat dugu Java makinan 128 megabytekoa.
Guk, liburu guztietan gomendatzen den moduan, korronteen prozesamendua erabiltzen dugu. Hau da, resultSet ireki eta hortik datuak irakurtzen ditugu pixkanaka. Funtzionatuko al du? Oroimenetik eroriko al da? Irakurriko al duzu pixka bat? Fidatu gaitezen datu-basean, fidatu gaitezen Postgres-en. Ez dugu sinesten. Erori egingo al gara oroimenez? Nork bizi izan du OutOfMemory? Nork lortu zuen hori konpontzea ondoren? Norbaitek konpontzea lortu zuen.
Milioi bat errenkada badituzu, ezin duzu aukeratu eta aukeratu. OFFSET/LIMIT beharrezkoa da. Nor da aukera honetarako? Eta nor da autoCommit-ekin jokatzearen alde?
Hemen, ohi bezala, ustekabeko aukera zuzena izaten da. Eta bat-batean autoCommit desaktibatzen baduzu, lagunduko dizu. Zergatik da hori? Zientziak ez daki honen berri.
Baina lehenespenez, Postgres datu-base batera konektatzen diren bezero guztiek datu guztiak eskuratzen dituzte. PgJDBC ez da salbuespena zentzu honetan; errenkada guztiak hautatzen ditu.
FetchSize gaian aldakuntza bat dago, hau da, adierazpen bereizi baten mailan esan dezakezu hemen, mesedez hautatu datuak 10, 50. Baina honek ez du funtzionatzen autoCommit desaktibatzen duzun arte. AutoCommit desaktibatuta - funtzionatzen hasten da.
Baina kodea zeharkatu eta setFetchSize nonahi ezartzea deserosoa da. Hori dela eta, konexio osorako balio lehenetsia esango duen ezarpen bat egin dugu.
Horixe esan genuen. Parametroa konfiguratu da. Eta zer lortu dugu? Kopuru txikiak hautatzen baditugu, adibidez, aldi berean 10 errenkada hautatzen baditugu, gastu orokorrak oso handiak ditugu. Beraz, balio hori ehun inguru ezarri behar da.
Egokiena, noski, oraindik nola mugatu bytetan ikasi behar duzu, baina errezeta hau da: ezarri defaultRowFetchSize ehun baino gehiagotan eta pozik egon.
Goazen datuak txertatzen. Sartzea errazagoa da, aukera desberdinak daude. Adibidez, txertatu, BALIOAK. Aukera ona da hau. "TXERTATU HAUTATU" esan dezakezu. Praktikan gauza bera da. Errendimenduan ez dago alderik.
Liburuek diote Batch instrukzio bat exekutatu behar duzula, liburuek komando konplexuagoak exekutatu ditzakezula hainbat parentesirekin. Eta Postgres-ek ezaugarri zoragarri bat du: COPY egin dezakezu, hau da, azkarrago egin dezakezu.
Neurtzen baduzu, berriz ere aurkikuntza interesgarri batzuk egin ditzakezu. Nola nahi dugu hau funtzionatzea? Alferrikako komandoak ez analizatu eta ez exekutatu nahi ditugu.
Praktikan, TCPk ez digu hori egiten uzten. Bezeroa eskaera bat bidaltzen lanpetuta badago, datu-baseak ez ditu eskaerak irakurtzen erantzunak bidaltzeko saiakeretan. Azken emaitza da bezeroak datu-baseak eskaera irakurri arte itxaroten duela, eta datu-baseak bezeroak erantzuna irakurri arte itxaroten duela.
Eta horregatik bezeroa behartuta dago aldian-aldian sinkronizazio pakete bat bidaltzera. Sare interakzio gehigarriak, denbora galtze gehigarria.
Eta zenbat eta gehiago gehitu, orduan eta okerrago. Gidaria nahiko ezkorra da eta maiz gehitzen ditu, 200 lerroan behin gutxi gorabehera, lerroen tamainaren arabera, etab.
Gertatzen da lerro bakarra zuzentzen duzula eta dena 10 aldiz bizkortuko dela. Gertatzen da. Zergatik? Ohi bezala, horrelako konstante bat erabili da jada nonbait. Eta "128" balioak batching ez erabiltzea esan nahi zuen.
Ona da hau bertsio ofizialean sartu ez izana. Askapena hasi aurretik aurkitu zuten. Ematen ditudan esanahi guztiak bertsio modernoetan oinarritzen dira.
Proba dezagun. InsertBatch sinplea neurtzen dugu. InsertBatch hainbat aldiz neurtzen dugu, hau da, gauza bera, baina balio asko daude. Mugimendu zaila. Denek ezin dute hori egin, baina mugimendu sinplea da, COPY baino askoz errazagoa.
COPY egin dezakezu.
Eta hori egituretan egin dezakezu. Adierazi Erabiltzaile mota lehenetsia, pasa array eta txertatu zuzenean taulara.
Esteka irekitzen baduzu: pgjdbc/ubenchmsrk/InsertBatch.java, kode hau GitHub-en dago. Zehazki ikus dezakezu bertan zer eskaerak sortzen diren. Berdin du.
martxan jarri genuen. Eta konturatu ginen lehenengo gauza batch ez erabiltzea besterik gabe ezinezkoa dela izan zen. Batching aukera guztiak zero dira, hau da, exekuzio-denbora ia zero da exekuzio bakarreko batekin alderatuta.
Datuak txertatzen ditugu. Oso mahai sinplea da. Hiru zutabe. Eta zer ikusten dugu hemen? Hiru aukera hauek gutxi gorabehera konparagarriak direla ikusten dugu. Eta COPY, noski, hobea da.
Hau da piezak sartzen ditugu. BALIOA balio bat, bi BALIO balio, hiru BALIO balio hori esan dugunean, edo horietako 10 komaz bereizita adierazi ditugu. Hau horizontala besterik ez da orain. 1, 2, 4, 128. Ikusten da urdinez marraztuta dagoen Batch Insert-ak askoz hobeto sentiarazten duela. Hau da, banan-banan sartzen dituzunean edo lau aldi berean sartzen dituzunean ere, bi aldiz ona bihurtzen da, besterik gabe, apur bat gehiago sartu dugulako BALIOAK. EXECUTE eragiketa gutxiago.
COPY bolumen txikietan erabiltzea oso itxaropentsua da. Lehenengo bietan ez nuen berdindu ere egin. Zerura doaz, hau da, COPY egiteko zenbaki berde hauek.
COPY erabili behar da gutxienez ehun datu errenkada dituzunean. Konexio hau irekitzearen gainkostua handia da. Eta, egia esateko, ez nuen norabide horretan zulatu. Batch optimizatu dut, baina ez COPY.
Zer egingo dugu gero? Probatu genuen. Ulertzen dugu egiturak edo hainbat esanahi uztartzen dituen batth adimentsu bat erabili behar dugula.
Zer kendu behar zenioke gaurko txostenetik?
- PreparedStatement gure guztia da. Horrek asko ematen dio produktibitateari. Ukenduan flop handia sortzen du.
- Eta AZALDU AZALTZEA 6 aldiz egin behar duzu.
- Eta OFFSET 0 eta +0 bezalako trikimailuak diluitu behar ditugu gure kontsulta problematikoen gainerako ehunekoa zuzentzeko.
Iturria: www.habr.com