Unë ju sugjeroj të lexoni transkriptin e raportit të fillimit të 2016 të Vladimir Sitnikov "PostgreSQL dhe JDBC po shtrydhin të gjithë lëngun"
Mirembrema Emri im është Vladimir Sitnikov. Unë kam 10 vjet që punoj për NetCracker. Dhe unë jam kryesisht pas produktivitetit. Gjithçka që lidhet me Java, gjithçka që lidhet me SQL është ajo që unë dua.
Dhe sot do të flas për atë që kemi hasur në kompani kur filluam të përdorim PostgreSQL si një server të bazës së të dhënave. Dhe ne kryesisht punojmë me Java. Por ajo që do t'ju them sot nuk ka të bëjë vetëm me Java-n. Siç ka treguar praktika, kjo ndodh edhe në gjuhë të tjera.
Ne do të flasim:
- rreth kampionimit të të dhënave.
- Rreth ruajtjes së të dhënave.
- Dhe gjithashtu për performancën.
- Dhe për grabujët nënujore që janë varrosur atje.
Le të fillojmë me një pyetje të thjeshtë. Ne zgjedhim një rresht nga tabela bazuar në çelësin primar.
Baza e të dhënave ndodhet në të njëjtin host. Dhe e gjithë kjo bujqësi zgjat 20 milisekonda.
Këto 20 milisekonda janë shumë. Nëse keni 100 kërkesa të tilla, atëherë kaloni kohë në sekondë duke lëvizur nëpër këto kërkesa, dmth ne po humbim kohë.
Ne nuk na pëlqen ta bëjmë këtë dhe të shikojmë se çfarë na ofron baza për këtë. Baza e të dhënave na ofron dy opsione për ekzekutimin e pyetjeve.
Opsioni i parë është një kërkesë e thjeshtë. Çfarë është e mirë për të? Fakti që e marrim dhe e dërgojmë dhe asgjë më shumë.
Baza e të dhënave ka gjithashtu një pyetje të avancuar, e cila është më e ndërlikuar, por më funksionale. Ju mund të dërgoni veçmas një kërkesë për analizë, ekzekutim, lidhje të ndryshoreve, etj.
Pyetja super e zgjeruar është diçka që nuk do ta mbulojmë në raportin aktual. Ne, ndoshta, duam diçka nga baza e të dhënave dhe ekziston një listë dëshirash që është formuar në një formë, pra kjo është ajo që duam, por është e pamundur tani dhe vitin e ardhshëm. Kështu që ne sapo e regjistruam dhe do të shkojmë duke tundur njerëzit kryesorë.
Dhe ajo që mund të bëjmë është një pyetje e thjeshtë dhe një pyetje e zgjeruar.
Çfarë është e veçantë për çdo qasje?
Një pyetje e thjeshtë është e mirë për ekzekutim një herë. Pasi bëhet dhe harrohet. Dhe problemi është se nuk mbështet formatin binar të të dhënave, d.m.th. nuk është i përshtatshëm për disa sisteme me performancë të lartë.
Pyetje e zgjeruar – ju lejon të kurseni kohë në analizë. Kjo është ajo që ne bëmë dhe filluam të përdorim. Kjo me të vërtetë, me të vërtetë na ndihmoi. Nuk ka vetëm kursime në analizë. Ka kursime në transferimin e të dhënave. Transferimi i të dhënave në format binar është shumë më efikas.
Le të kalojmë në praktikë. Kështu duket një aplikacion tipik. Mund të jetë Java, etj.
Ne krijuam deklaratë. Ekzekutoi komandën. Krijuar afër. Ku eshte gabimi ketu? Cili është problemi? Nuk ka problem. Kështu thuhet në të gjithë librat. Kështu duhet të shkruhet. Nëse dëshironi performancë maksimale, shkruani kështu.
Por praktika ka treguar se kjo nuk funksionon. Pse? Sepse ne kemi një metodë "të ngushtë". Dhe kur e bëjmë këtë, nga pikëpamja e bazës së të dhënave rezulton se është si një duhanpirës që punon me një bazë të dhënash. Thamë "PARSE EKZEKUTOHET DEALLOCATE".
Pse gjithë ky krijim dhe shkarkim shtesë i deklaratave? Askush nuk ka nevojë për to. Por ajo që zakonisht ndodh në PreparedStatements është se kur i mbyllim ato, ata mbyllin gjithçka në bazën e të dhënave. Kjo nuk është ajo që duam.
Ne duam, si njerëz të shëndetshëm, të punojmë me bazën. Ne e morëm dhe e përgatitëm deklaratën tonë një herë, pastaj e ekzekutojmë shumë herë. Në fakt, shumë herë - kjo është një herë në të gjithë jetën e aplikacioneve - ato janë analizuar. Dhe ne përdorim të njëjtën id të deklaratës në REST të ndryshme. Ky është qëllimi ynë.
Si mund ta arrijmë këtë?
Është shumë e thjeshtë - nuk ka nevojë të mbyllni deklaratat. E shkruajmë kështu: "përgatit" "ekzekuto".
Nëse nisim diçka të tillë, atëherë është e qartë se diçka do të vërshojë diku. Nëse nuk është e qartë, mund ta provoni. Le të shkruajmë një pikë referimi që përdor këtë metodë të thjeshtë. Krijo një deklaratë. Ne e lëshojmë atë në disa versione të shoferit dhe zbulojmë se ai rrëzohet mjaft shpejt me humbjen e të gjithë kujtesës që kishte.
Është e qartë se gabime të tilla korrigjohen lehtësisht. Nuk do të flas për to. Por unë do të them që versioni i ri funksionon shumë më shpejt. Metoda është budalla, por gjithsesi.
Si të punoni saktë? Çfarë duhet të bëjmë për këtë?
Në realitet, aplikacionet mbyllin gjithmonë deklaratat. Në të gjithë librat thonë mbyllet, përndryshe do të rrjedh kujtesa.
Dhe PostgreSQL nuk di se si të ruajë pyetjet në memorie. Është e nevojshme që çdo seancë të krijojë këtë cache për vete.
Dhe ne nuk duam të humbim kohë as në analizë.
Dhe si zakonisht kemi dy opsione.
Opsioni i parë është që ta marrim dhe të themi se le të mbështjellim gjithçka në PgSQL. Ka një cache atje. Ajo ruan gjithçka. Do të dalë e shkëlqyeshme. Ne e pamë këtë. Kemi 100500 kërkesa. Nuk punon. Ne nuk pranojmë t'i kthejmë kërkesat në procedura manualisht. Jo jo.
Ne kemi një mundësi të dytë - ta marrim dhe ta presim vetë. Hapim burimet dhe fillojmë të presim. Ne pamë dhe pamë. Doli se nuk është aq e vështirë për të bërë.
Kjo u shfaq në gusht 2015. Tani ka një version më modern. Dhe gjithçka është e mrekullueshme. Punon aq mirë sa nuk ndryshojmë asgjë në aplikacion. Dhe ne madje ndaluam së menduari në drejtimin e PgSQL, d.m.th., kjo ishte mjaft e mjaftueshme që ne të reduktonim të gjitha kostot e përgjithshme në pothuajse zero.
Prandaj, deklaratat e përgatitura nga serveri aktivizohen në ekzekutimin e 5-të për të shmangur humbjen e kujtesës në bazën e të dhënave për çdo kërkesë një herë.
Ju mund të pyesni - ku janë numrat? Çfarë po merrni? Dhe këtu nuk do të jap numra, sepse çdo kërkesë ka të sajën.
Pyetjet tona ishin të tilla që shpenzuam rreth 20 milisekonda për analizimin e pyetjeve OLTP. Kishte 0,5 milisekonda për ekzekutim, 20 milisekonda për analizë. Kërkesë – 10 KiB tekst, 170 rreshta plani. Kjo është një kërkesë OLTP. Kërkon 1, 5, 10 rreshta, ndonjëherë më shumë.
Por ne nuk donim të humbnim 20 milisekonda fare. Ne e reduktuam atë në 0. Gjithçka është e mrekullueshme.
Çfarë mund të hiqni nga këtu? Nëse keni Java, atëherë merrni versionin modern të shoferit dhe gëzoheni.
Nëse flisni një gjuhë tjetër, atëherë mendoni - ndoshta ju duhet edhe kjo? Sepse nga pikëpamja e gjuhës përfundimtare, për shembull, nëse PL 8 ose keni LibPQ, atëherë nuk është e qartë për ju që po kaloni kohë jo në ekzekutim, në analizë, dhe kjo ia vlen të kontrollohet. Si? Gjithçka është falas.
Përveçse ka gabime dhe disa veçori. Dhe ne do të flasim për to tani. Pjesa më e madhe do të jetë për arkeologjinë industriale, për atë që gjetëm, atë që hasëm.
Nëse kërkesa gjenerohet në mënyrë dinamike. Ndodh. Dikush i ngjit vargjet së bashku, duke rezultuar në një pyetje SQL.
Pse është i keq? Është keq sepse çdo herë përfundojmë me një varg të ndryshëm.
Dhe hashCode i këtij vargu të ndryshëm duhet të lexohet përsëri. Kjo është me të vërtetë një detyrë CPU - gjetja e një teksti të gjatë kërkese edhe në hash-in ekzistues nuk është aq e lehtë. Prandaj, përfundimi është i thjeshtë - mos krijoni kërkesa. Ruani ato në një variabël. Dhe gëzohu.
Problemi i radhës. Llojet e të dhënave janë të rëndësishme. Ka ORM që thonë se nuk ka rëndësi se çfarë lloj NULL ka, le të ketë një lloj. Nëse Int, atëherë themi setInt. Dhe nëse NULL, atëherë le të jetë gjithmonë VARCHAR. Dhe çfarë ndryshimi ka në fund se çfarë NULL është atje? Vetë baza e të dhënave do të kuptojë gjithçka. Dhe kjo foto nuk funksionon.
Në praktikë, bazës së të dhënave nuk i intereson fare. Nëse herën e parë thatë se ky është një numër, dhe herën e dytë thatë se është VARCHAR, atëherë është e pamundur të ripërdorësh deklaratat e përgatitura nga serveri. Dhe në këtë rast, ne duhet të rikrijojmë deklaratën tonë.
Nëse po ekzekutoni të njëjtin pyetje, sigurohuni që llojet e të dhënave në kolonën tuaj të mos ngatërrohen. Duhet të keni kujdes për NULL. Ky është një gabim i zakonshëm që kemi pasur pasi kemi filluar të përdorim PreparedStatements
Në rregull, i ndezur. Ndoshta e kanë marrë shoferin. Dhe produktiviteti ra. Gjërat u bënë keq.
Si ndodh kjo? A është ky një gabim apo një veçori? Fatkeqësisht, nuk ishte e mundur të kuptohej nëse ky është një gabim apo një veçori. Por ekziston një skenar shumë i thjeshtë për riprodhimin e këtij problemi. Ajo na zuri pritë krejtësisht e papritur. Dhe konsiston në marrjen e mostrave fjalë për fjalë nga një tabelë. Natyrisht, ne kishim më shumë kërkesa të tilla. Si rregull, ato përfshinin dy ose tre tabela, por ekziston një skenar i tillë i riprodhimit. Merrni çdo version nga databaza juaj dhe luani atë.
Çështja është se ne kemi dy kolona, secila prej të cilave është e indeksuar. Ka një milion rreshta në një kolonë NULL. Dhe kolona e dytë përmban vetëm 20 rreshta. Kur ekzekutojmë pa variabla të lidhur, gjithçka funksionon mirë.
Nëse fillojmë të ekzekutojmë me variabla të lidhur, pra ekzekutojmë "?" ose "$1" për kërkesën tonë, çfarë do të marrim në fund?
Ekzekutimi i parë është ashtu siç pritej. E dyta është pak më e shpejtë. Diçka u ruajt në memorie. E treta, e katërta, e pesta. Pastaj zhurmë - dhe diçka e tillë. Dhe më e keqja është se kjo ndodh në ekzekutimin e gjashtë. Kush e dinte se ishte e nevojshme të bëheshin saktësisht gjashtë ekzekutime për të kuptuar se cili ishte plani aktual i ekzekutimit?
Kush është fajtor? Cfare ndodhi? Baza e të dhënave përmban optimizim. Dhe duket se është optimizuar për rastin e përgjithshëm. Dhe, në përputhje me rrethanat, duke filluar nga një moment, ajo kalon në një plan gjenerik, i cili, për fat të keq, mund të rezultojë i ndryshëm. Mund të rezultojë e njëjtë, ose mund të jetë ndryshe. Dhe ka një lloj vlere pragu që çon në këtë sjellje.
Çfarë mund të bëni për këtë? Këtu, natyrisht, është më e vështirë të supozohet diçka. Ekziston një zgjidhje e thjeshtë që ne përdorim. Kjo është +0, OFFSET 0. Me siguri ju i njihni zgjidhje të tilla. Ne thjesht e marrim atë dhe shtojmë "+0" në kërkesë dhe gjithçka është në rregull. Unë do t'ju tregoj më vonë.
Dhe ekziston një mundësi tjetër - shikoni planet me më shumë kujdes. Zhvilluesi jo vetëm që duhet të shkruajë një kërkesë, por edhe të thotë "shpjego analizo" 6 herë. Nëse është 5, nuk do të funksionojë.
Dhe ekziston një opsion i tretë - shkruani një letër pgsql-hakerëve. Kam shkruar, megjithatë, nuk është ende e qartë nëse ky është një gabim apo një veçori.
Ndërsa ne po mendojmë nëse ky është një gabim apo një veçori, le ta rregullojmë atë. Le të marrim kërkesën tonë dhe të shtojmë "+0". Cdo gje eshte ne rregull. Dy simbole dhe as nuk duhet të mendoni se si është apo çfarë është. Shume e thjeshte. Ne thjesht e ndaluam bazën e të dhënave të përdorë një indeks në këtë kolonë. Ne nuk kemi një indeks në kolonën "+0" dhe kaq, baza e të dhënave nuk e përdor indeksin, gjithçka është në rregull.
Ky është rregulli i 6 shpjegimit. Tani në versionet aktuale ju duhet ta bëni atë 6 herë nëse keni variabla të lidhur. Nëse nuk keni variabla të lidhur, kjo është ajo që ne bëjmë. Dhe në fund është pikërisht kjo kërkesë që dështon. Nuk është një gjë e ndërlikuar.
Do të duket, sa është e mundur? Një bug këtu, një defekt atje. Në fakt, defekti është kudo.
Le të hedhim një vështrim më të afërt. Për shembull, ne kemi dy skema. Skema A me tabelën S dhe diagrami B me tabelën S. Pyetje – zgjidhni të dhënat nga një tabelë. Çfarë do të kemi në këtë rast? Do të kemi një gabim. Do të kemi të gjitha sa më sipër. Rregulli është - një gabim është kudo, ne do të kemi të gjitha sa më sipër.
Tani pyetja është: "Pse?" Duket se ka dokumentacion që nëse kemi një skemë, atëherë ekziston një variabël "search_path" që na tregon se ku të kërkojmë tabelën. Duket se ka një variabël.
Cili është problemi? Problemi është se deklaratat e përgatitura nga serveri nuk dyshojnë se search_path mund të ndryshohet nga dikush. Kjo vlerë mbetet, si të thuash, konstante për bazën e të dhënave. Dhe disa pjesë mund të mos marrin kuptime të reja.
Sigurisht, kjo varet nga versioni në të cilin po testoni. Varet se sa seriozisht ndryshojnë tabelat tuaja. Dhe versioni 9.1 thjesht do të ekzekutojë pyetjet e vjetra. Versionet e reja mund të kapin defektin dhe t'ju thonë se keni një defekt.
Si ta trajtojmë atë? Ekziston një recetë e thjeshtë - mos e bëni. Nuk ka nevojë të ndryshoni rrugën e kërkimit ndërsa aplikacioni është duke u ekzekutuar. Nëse ndryshoni, është më mirë të krijoni një lidhje të re.
Mund të diskutoni, d.m.th të hapni, diskutoni, shtoni. Ndoshta ne mund t'i bindim zhvilluesit e bazës së të dhënave që kur dikush ndryshon një vlerë, baza e të dhënave duhet t'i tregojë klientit për këtë: "Shiko, vlera jote është përditësuar këtu. Ndoshta ju duhet të rivendosni deklaratat dhe t'i rikrijoni ato?” Tani baza e të dhënave sillet fshehurazi dhe nuk raporton në asnjë mënyrë që deklaratat kanë ndryshuar diku brenda.
Dhe do të theksoj përsëri - kjo është diçka që nuk është tipike për Java. Ne do të shohim të njëjtën gjë në PL/pgSQL një me një. Por ajo do të riprodhohet atje.
Le të provojmë disa zgjedhje të tjera të dhënash. Ne zgjedhim dhe zgjedhim. Ne kemi një tabelë me një milion rreshta. Çdo rresht është një kilobajt. Përafërsisht një gigabajt të dhënash. Dhe ne kemi një memorie pune në makinën Java prej 128 megabajt.
Ne, siç rekomandohet në të gjithë librat, përdorim përpunimin e transmetimit. Kjo do të thotë, ne hapim ResultSet dhe lexojmë të dhënat nga atje pak nga pak. A do të funksionojë? A do të bjerë nga kujtesa? Do të lexoni pak? Le të besojmë në bazën e të dhënave, le të besojmë në Postgres. Nuk e besojmë. A do të biem jashtë Kujtesës? Kush e përjetoi OutOfMemory? Kush arriti ta rregullonte pas kësaj? Dikush arriti ta rregullonte.
Nëse keni një milion rreshta, nuk mund të zgjidhni dhe zgjidhni. Kërkohet OFFSET/LIMIT. Kush është për këtë opsion? Dhe kush është në favor të lojës me autoCommit?
Këtu, si zakonisht, opsioni më i papritur rezulton të jetë i saktë. Dhe nëse papritmas fikni AutoCommit, do t'ju ndihmojë. Pse eshte ajo? Shkenca nuk di për këtë.
Por si parazgjedhje, të gjithë klientët që lidhen me një bazë të dhënash Postgres marrin të gjitha të dhënat. PgJDBC nuk bën përjashtim në këtë drejtim; ai zgjedh të gjitha rreshtat.
Ekziston një ndryshim në temën FetchSize, d.m.th. mund të thoni në nivelin e një deklarate të veçantë se këtu, ju lutemi zgjidhni të dhënat me 10, 50. Por kjo nuk funksionon derisa të fikni AutoCommit. U çaktivizua AutoCommit - fillon të funksionojë.
Por kalimi i kodit dhe vendosja e setFetchSize kudo është e papërshtatshme. Prandaj, kemi bërë një cilësim që do të thotë vlerën e paracaktuar për të gjithë lidhjen.
Kështu thamë. Parametri është konfiguruar. Dhe çfarë morëm? Nëse zgjedhim sasi të vogla, nëse, për shembull, zgjedhim 10 rreshta në të njëjtën kohë, atëherë kemi kosto të përgjithshme shumë të mëdha. Prandaj, kjo vlerë duhet të vendoset në rreth njëqind.
Në mënyrë ideale, sigurisht, duhet të mësoni ende se si ta kufizoni atë në bajt, por receta është kjo: vendosni defaultRowFetchSize në më shumë se njëqind dhe jini të lumtur.
Le të kalojmë në futjen e të dhënave. Futja është më e lehtë, ka mundësi të ndryshme. Për shembull, INSERT, VALUES. Ky është një opsion i mirë. Mund të thoni "INSERT SELECT". Në praktikë është e njëjta gjë. Nuk ka asnjë ndryshim në performancë.
Librat thonë se ju duhet të ekzekutoni një deklaratë Batch, librat thonë se mund të ekzekutoni komanda më komplekse me disa kllapa. Dhe Postgres ka një veçori të mrekullueshme - ju mund të bëni COPY, d.m.th. ta bëni atë më shpejt.
Nëse e matni, përsëri mund të bëni disa zbulime interesante. Si duam që kjo të funksionojë? Ne duam të mos analizojmë dhe të mos ekzekutojmë komanda të panevojshme.
Në praktikë, TCP nuk na lejon ta bëjmë këtë. Nëse klienti është i zënë me dërgimin e një kërkese, atëherë baza e të dhënave nuk i lexon kërkesat në përpjekje për të na dërguar përgjigje. Rezultati përfundimtar është se klienti pret që baza e të dhënave të lexojë kërkesën, dhe baza e të dhënave pret që klienti të lexojë përgjigjen.
Dhe për këtë arsye klienti detyrohet të dërgojë periodikisht një paketë sinkronizimi. Ndërveprime shtesë në rrjet, humbje shtesë kohe.
Dhe sa më shumë t'i shtojmë, aq më keq bëhet. Shoferi është mjaft pesimist dhe i shton mjaft shpesh, rreth një herë në 200 rreshta, në varësi të madhësisë së linjave, etj.
Ndodh që korrigjoni vetëm një rresht dhe gjithçka do të përshpejtohet 10 herë. Ndodh. Pse? Si zakonisht, një konstante si kjo tashmë është përdorur diku. Dhe vlera "128" nënkuptonte të mos përdorej grumbullim.
Është mirë që kjo nuk u përfshi në versionin zyrtar. U zbulua para fillimit të lëshimit. Të gjitha kuptimet që unë jap bazohen në versione moderne.
Le ta provojmë. Ne matim InsertBatch thjeshtë. Ne matim InsertBatch shumë herë, pra të njëjtën gjë, por ka shumë vlera. Lëvizje e ndërlikuar. Jo të gjithë mund ta bëjnë këtë, por është një veprim kaq i thjeshtë, shumë më i lehtë se KOPJIMI.
Ju mund të bëni KOPJ.
Dhe këtë mund ta bëni në struktura. Deklaroni llojin e paracaktuar të përdoruesit, kaloni grupin dhe INSERT direkt në tabelë.
Nëse hapni lidhjen: pgjdbc/ubenchmsrk/InsertBatch.java, atëherë ky kod është në GitHub. Ju mund të shihni konkretisht se çfarë kërkesa krijohen atje. Nuk ka rëndësi.
Ne nisëm. Dhe gjëja e parë që kuptuam ishte se mos përdorimi i grupit është thjesht i pamundur. Të gjitha opsionet e grumbullimit janë zero, d.m.th. koha e ekzekutimit është praktikisht zero në krahasim me një ekzekutim një herë.
Ne fusim të dhëna. Është një tabelë shumë e thjeshtë. Tre kolona. Dhe çfarë shohim këtu? Ne shohim se të tre këto opsione janë afërsisht të krahasueshme. Dhe COPY është, natyrisht, më i mirë.
Kjo është kur ne futim copa. Kur thonim se një vlerë VALUES, dy vlera VALUES, tre vlera VALUES, ose treguam 10 prej tyre të ndara me presje. Kjo është vetëm horizontale tani. 1, 2, 4, 128. Mund të shihet se Batch Insert, i cili është vizatuar në ngjyrë blu, e bën atë të ndihet shumë më mirë. Kjo do të thotë, kur futni një nga një ose edhe kur futni katër në një kohë, ai bëhet dy herë më i mirë, thjesht sepse kemi ngjeshur pak më shumë në VLERA. Më pak operacione EXECUTE.
Përdorimi i COPY në vëllime të vogla nuk është jashtëzakonisht premtues. Nuk kam vizatuar as në dy të parat. Ata shkojnë në parajsë, domethënë, këta numra të gjelbër për KOPJE.
COPY duhet të përdoret kur keni të paktën njëqind rreshta të dhënash. Kostoja e hapjes së kësaj lidhjeje është e madhe. Dhe, të jem i sinqertë, nuk gërmova në këtë drejtim. Kam optimizuar Batch, por jo KOPJO.
Çfarë të bëjmë më pas? Ne e provuam atë. Ne e kuptojmë se duhet të përdorim ose struktura ose një bath të zgjuar që ndërthur disa kuptime.
Çfarë duhet të hiqni nga raporti i sotëm?
- PreparedStatement është gjithçka jonë. Kjo jep shumë për produktivitetin. Ajo prodhon një rënie të madhe në pomadë.
- Dhe ju duhet të bëni ANALIZË EXPLAIN 6 herë.
- Dhe ne duhet të hollojmë OFFSET 0, dhe truket si +0 në mënyrë që të korrigjojmë përqindjen e mbetur të pyetjeve tona problematike.
Burimi: www.habr.com