Ju lutem shqyrtoni shpjegimin e raportit të fillimit të vitit 2016 nga Vladimir Sitnikov "PostgreSQL dhe JDBC shfrytëzojmë të gjitha burimet"
Mirëdita! Emri im është Vladimir Sitnikov. Punoj 10 vjet në kompaninë NetCracker. Kryesisht merrem me performancën. Çdo gjë që ka të bëjë me Java, çdo gjë që ka të bëjë me SQL – kjo është ajo që kam pasion.
Dhe sot do t'ju flas për atë që na ndodhi në kompani kur filluam të përdornim PostgreSQL si server bazash të dhënash. Ne zakonisht punojmë me Java. Por ajo që do të flas sot, nuk lidhet vetëm me Java. Siç ka treguar praktika, kjo ndodh edhe në gjuhë të tjera.
Ne do të flasim për:
- selekcionin e të dhënave.
- Ruajtjen e të dhënave.
- Po ashtu për performancën.
- Dhe për pengesat që janë fshehur atje.
Le të fillojmë me një pyetje të thjeshtë. Ne zgjedhim një rresht nga tabela sipas çelësit primar.
Baza ndodhet në të njëjtin host. Dhe gjithë ky proces zë 20 milisekonda.
Këto 20 milisekonda – janë shumë. Nëse keni 100 pyetje të tilla, shpenzoni kohë në sekonda për të rrotulluar këto kërkesa, dmth, po e humbni kohën pa nevojë.
Ne nuk duam ta bëjmë këtë dhe shikojmë se çfarë na ofron baza për këtë. Baza na ofron dy alternativa për ekzekutimin e kërkesave.
Alternativa e parë është kërkesa e thjeshtë. Çfarë është e mirë në këtë? Sepse ne e marrim dhe e dërgojmë, dhe nuk bëjmë asgjë më shumë.
Baza ka gjithashtu një kërkesë të zgjeruar, e cila është më e mençur, por më funksionale. Mund të dërgoni veçmas kërkesa për analizë, ekzekutim, lidhjen e variablave, etj.
Super extended query – është ajo që ne nuk do ta mbulojmë në këtë raport. Ndoshta kemi disa dëshira nga baza e të dhënave dhe ekziston një listë dëshirash e formuar në njëfarë forme, dmth, kjo është ajo që ne dëshirojmë, por nuk është e mundur tani dhe në vitin e ardhshëm. Prandaj, thjesht e shkruam dhe do të shkojmë të trondisim njerëzit kryesorë.
Ajo që mund të bëjmë është kërkesa e thjeshtë dhe kërkesa e zgjeruar.
Cila është veçoria e çdo qasjeje?
Kërkesa e thjeshtë është e mirë për ekzekutimin një herë. E realizove një herë dhe e harrove. Problemi është se ajo nuk mbështet formatin binar të të dhënave, dmth, për disa sisteme me performancë të lartë, ajo nuk është e përshtatshme.
Kërkesa e zgjeruar – lejon të kursejmë kohë gjatë procesit të parse-ing. Kjo është ajo që bëmë dhe filluam ta përdorim. Na ka ndihmuar jashtëzakonisht. Aty ka jo vetëm kursim në parse-ing. Ka edhe kursim në transmetimin e të dhënave. Të transmetosh të dhënat në format binar është shumë më efikase.
Të kalojmë në praktikë. Këtu është si duket një aplikacion tipik. Mund të jetë Java etj.
Krijuam një statement. E ekzekutuam komandën. Krijuam një close. Ku është gabimi këtu? Çfarë është problemi? Nuk ka probleme. Kështu shkruhet në të gjitha librat. Kështu duhet të shkruhet. Nëse doni maksimumin e performancës, shkruani kështu.
Por praktika tregoi se kjo nuk funksionon. Pse? Sepse kemi metodën "close". Dhe kur e bëjmë kështu, nga këndvështrimi i bazës së të dhënave, kjo është si puna e një duhanpirësi me bazën e të dhënave. Ne thamë "PARSE EXECUTE DEALLOCATE".
Pse këto krijime dhe shkarkime të tepruara të statements? Askush nuk i nevojitet. Por zakonisht në PreparedStatement ndodh që kur i mbyllim, ato 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. Një herë e morëm dhe e përgatitëm statement-in tonë, pastaj e ekzekutojmë shumë herë. Në të vërtetë shumë herë – është një herë për gjithë jetën e aplikacionit që e kemi parse. Dhe në REST të ndryshme përdorim të njëjtin statement id. Kjo është qëllimi ynë.
Si ta arrijmë këtë?
Shumë thjeshtë – nuk duhet të mbyllim statements. Shkruajmë kështu: "prepare" "execute".
Nëse e ekzekutojmë këtë, është e qartë se diku do të mbushet. Nëse nuk është e qartë, mund të matim. Merrni dhe shkruani një benchmark, ku është një metodë e tillë e thjeshtë. Krijojmë një statement. E ekzekutojmë në ndonjë version të driver-it dhe kuptojmë se ai rrëzohet mjaft shpejt me humbjen e të gjithë memories që kemi sjellë.
E qartë se këto gabime janë lehtësisht të korrigjueshme. Nuk do të flas për to. Por do të them se në versionin e ri punon shumë më shpejt. Metoda është e pavlefshme, por megjithatë.
Si të punojmë si duhet? Çfarë duhet të bëjmë për këtë?
Në realitet, aplikacionet gjithmonë mbyllin statements. Në të gjitha librat shkruhen që duhet mbyllur, në të kundërt do të ketë rrjedhje të memories.
Dhe PostgreSQL nuk di të cache-ojë kërkesat. Duhet që çdo sesion të krijojë vetë këtë cache.
Dhe nuk duam të kalojmë kohë në parse-ing gjithashtu.
Dhe si zakonisht kemi dy mundësi.
Opsioni i parë – ne marrim dhe themi, le të konvertojmë gjithçka në PgSQL. Aty ka cache. Ai gjithçka e cache. Do të del shkëlqyer. Ne e shikuam këtë. Kemi 100500 kërkesa. Nuk funksionon. Nuk jemi dakord – të transformojmë kërkesat në procedura me dorë. Jo jo.
Kemi një opsion të dytë – të marrim dhe ta bëjmë vetë. Hapim kodin burimor, fillojmë të punojmë. Punohet-punohet. Doli se nuk është aq e vështirë ta bësh.
Kjo u shfaq në gusht 2015. Tani kemi një version më modern. Dhe gjithçka është shkëlqyer. Funksionon kaq mirë, saqë nuk bëjmë asnjë ndryshim në aplikacion. Madje kemi ndaluar së menduari për PgSQL, dmth na mjafton kjo për të ulur praktiksht të gjitha shpenzimet.
Përkatësisht, Server-prepared statements aktivizohet në ekzekutimin e pestë për të mos shpenzuar memorie në bazën e të dhënave për çdo kërkesë njëherore.
Mund të pyesni – ku janë numrat? Çfarë po merrni? Dhe këtu nuk mund të jap numra, sepse për çdo kërkesë, ata janë të ndryshëm.
Kërkesat tona ishin të tilla që ne në kërkesat OLTP shpenzonim diku 20 milisekonda për parsing. Kishte 0,5 milisekonda për ekzekutim, 20 milisekonda për parsing. Kërkesa – 10 KiB tekst, 170 rreshta plani. Kjo është një kërkesë OLTP. Ajo kërkon 1, 5, 10 rreshta, ndonjëherë më shumë.
Por ne nuk donim të shpenzonim 20 milisekonda. Ne e kemi ulur atë në 0. Gjithçka është shkëlqyer.
Çfarë mund të nxirrni prej këtu? Nese keni Java, atëherë merrni versionin modern të driver-it dhe gëzohuni.
Nëse keni ndonjë gjuhë tjetër, atëherë mendoni – ndoshta kjo ju nevojitet gjithashtu? Sepse nga pikëpamja e gjuhës përfundimtare, për shembull, nëse është PL 8 ose nëse keni LibPQ, nuk është e qartë se po humbni kohë jo për ekzekutim, por për parsing dhe kjo ia vlen ta verifikoni. Si? Të gjitha janë falas.
Përveç faktit që ka gabime, disa veçori. Dhe për to do të flasim tani. Pjesa më e madhe do të jetë për arkeologjinë industrisë, për atë që kemi gjetur, për çfarë na është ndodhur.
Nëse kërkesa gjenerohet dinamike. Kjo ndodh. Disa bashkangjisin vargje, kështu që del një kërkesë SQL.
Çfarë është e keqe me të? Është e keqe sepse çdo herë kemi në fund një varg të ndryshëm.
Dhe duhet ri-të llogarisni hashCode për këtë varg të ndryshëm. Kjo është vërtet një detyrë CPU – të gjeni tekstin e gjatë të kërkesës në një hash ekzistues nuk është aq e lehtë. Prandaj, përfundimi është i thjeshtë – mos gjeneroni kërkesa. Ruani ato në një variabël të vetme. Dhe gëzohuni.
Problemi tjetër. Llojet e të dhënave janë të rëndësishme. Ka ORM që thonë se nuk ka rëndësi cili NULL, le të jetë ndonjëherë. Nëse është Int, atëherë ne themi setInt. Ndërsa nëse është NULL, le të jetë gjithmonë VARCHAR. Dhe çfarë rëndësie ka në fund të fundit cili atje është NULL? Baza e të dhënave do ta kuptojë gjithçka vetë. Dhe kjo skicë nuk funksionon.
Në praktikë, baza e të dhënave nuk i bie fare në sy kjo. Nëse herën e parë thatë se është numër, dhe herën e dytë thatë se është VARCHAR, atëherë është e pamundur të ri-përdorni deklaratat e përgatitura nga serveri. Dhe në këtë rast, duhet të krijoni nga e para deklaratën tonë.
Nëse po ekzekutoni të njëjtën kërkesë, atëherë qëndroni të sigurt për të mos ngatërruar llojet e të dhënave në kolonën tuaj. Duhet të monitoroni NULL. Kjo është një gabim i zakonshëm që kemi pasur pas fillimit të përdorimit të PreparedStatements.
Mirë, e aktivizuam. Morëm, ndoshta, drejtuesin. Dhe performanca ra. Çdo gjë u bë keq.
Si ndodh kjo? Është një gabim apo një karakteristikë? Fatkeqësisht, nuk arritëm të kuptojmë – është një gabim apo një karakteristikë. Por ka një skenar të thjeshtë për të përsëritur këtë problem. Ai na ka përndjekur krejt papritur. Dhe përfshin marrjen e të dhënave vetëm nga një tabelë. Sigurisht, na ka pasur edhe kërkesa të tjera. Ato zakonisht përfshinin dy-tre tabela, por ka një skenar të tillë për të përsëritur. Merrni ndonjë version të bazës suaj dhe e përsërisni.
Kuptimi është se kemi dy kolona, secila prej tyre e indeksuar. Në një kolonë ka një milion rreshta me vlerë NULL. Ndërsa në kolonën tjetër ndodhen vetëm 20 rreshta. Kur e ekzekutojmë pa variabla të lidhura, çdo gjë funksionon mirë.
Nëse fillojmë të ekzekutojmë me variabla të lidhura, domethënë, ne ekzekutojmë shenjën "?" ose "$1" për kërkesën tonë, atëherë çfarë marrim përfundimisht?
Ekzekutimi i parë – siç është e duhura. I dyti – pak më i shpejtë. Disa shkalla u shpëtuar. I treti-katërti-pestë. Pastaj ndodhi - dhe filloi kështu. Dhe më e keqja, është se ndodh në ekzekutimin e gjashtë. Kush e dinte se duhet të bëjmë pikërisht gjashtë ekzekutime për të kuptuar se cili është plani i shkak të vërtetë?
Kush faji? Çfarë ndodhi? Baza e të dhënave përmban optimizim. Dhe ajo është si optimizuar për rastin generic. Dhe, përkatësisht, duke filluar nga një moment, ajo kalon në planin generic, i cili, fatkeqësisht, mund të rezultojë të jetë ndryshe. Ai mund të mbetet i njëjtë, por gjithashtu mund të jetë ndryshe. Dhe ka një vlerë pragore, që çon në këtë sjellje.
Çfarë mund të bëjmë me këtë? Këtu, sigurisht, është më e komplikuar të parashikohet. Ka një zgjidhje të thjeshtë që ne përdorim. Kjo është +0, OFFSET 0. Sigurisht që ju i dini këto zgjidhje. Thjesht e marrim dhe e shtojmë '+0' në kërkesë dhe gjithçka shkon mirë. Do e tregoj më vonë.
Dhe ka një tjetër variant – të shikoni më me kujdes planet. Zhvilluesi duhet të shkruajë jo vetëm kërkesën, por edhe të thotë 'explain analyze' 6 herë. Nëse thotë 5, atëherë nuk qëndron.
Dhe ka edhe një variant të tretë – të shkruajmë një letër në pgsql-hackers. E kam bërë, megjithatë, ende nuk është e qartë – është një gabim apo një karakteristikë.
Ndërkohë që mendojmë – a është një gabim apo një karakteristikë, le të e rregullojmë. Do e marrim kërkesën tonë dhe do i shtojmë '+0'. Gjithçka shkon mirë. Dy simbole dhe nuk duhet të mendojmë se si është. Shumë e thjeshtë. Ne thjesht e ndaluam bazën e të dhënave që të përdorë indeksin për këtë kolonë. Nuk kemi indeks për kolonën '+0' dhe gjithçka shkon mirë.
Ky është rregulli i 6 explain’ëve. Tani në versionet aktuale duhet të bëjmë 6 herë, nëse keni variabla të lidhura. Nëse nuk keni variabla të lidhura, atëherë ne veprojmë kështu. Dhe në fund, pikërisht kjo kërkesë bie. Nuk është ndonjë gjë e komplikuar.
Siç duket, sa mund të ndodhin? Ka një gabim këtu, një gabim atje. Realisht ka gabim gjithandej.
Le të shikojmë edhe njëherë. Për shembull, kemi dy skema. Schema A me tabelën Y dhe schema B me tabelën Y. Kërkesa është – përzgjidh të dhënat nga tabela. Çfarë do të ndodhë? Do të kemi një gabim. Do të kemi gjithçka të përmendur më sipër. Rregulli është i tillë – gabim në çdo vend, do të kemi gjithçka të përmendur më sipër.
Tani pyetja: 'Pse?'. Siç duket, ka dokumentacion, që thotë se, nëse kemi skemë, ekziston variabla 'search_path', e cila tregon se ku duhet të kërkohet tabela. Siç duket, variabla ekziston.
Cila është problemo? Problemi qëndron në faktin se server-prepared statements nuk njoftojnë që search_path mund të ndryshohet nga dikush. Ky vlerë mbetet si një konstant për bazën e të dhënave. Dhe disa pjesë mund të mos kapin vlerat e reja.
Natyrisht, kjo varet nga versioni me të cilin po testoni. Varet nga sa seriozisht ndryshojnë tabelat tuaja. Versioni 9.1 do të ekzekutojë thirrjet e vjetra. Versionet e reja mund të zbulojnë mashtrimin dhe të thonë se keni një gabim.
Si ta trajtojmë këtë? Ka një recetë të thjeshtë – mos e bëni kështu. Mos e ndryshoni search_path gjatë punës së aplikacionit. Nëse e ndryshoni, është më mirë të krijoni një lidhje të re.
Mund të diskutojmë, dmth. të hapim, të diskutojmë, të shkruajmë më shumë. Ndoshta do t'i bindim zhvilluesit e bazës së të dhënave që në rast se dikush ndryshon vlerën, baza e të dhënave duhet ta informojë klientin: "Shikoni, këtu ju është rinovuar një vlerë. Ndoshta duhet të ri-inizoni deklaratat?". Tani, baza e të dhënave vepron fshehuras dhe nuk njofton asgjë për ndryshimin e ndodhur brenda deklaratave.
Dhe përsëri do ta theksoj – kjo është diçka që nuk është tipike për Java. Ne do ta shohim të njëjtën gjë në PL/pgSQL për një të njëjtë. Por aty do të riprodhohet.
Le të provojmë të zgjedhim të dhëna përsëri. Po zgjedhim. Kemi një tabelë me një milion rreshta. Secili rresht një kilobajt. Afërsisht një gigabajt të dhënë. Dhe kemi një kujtesë operative në makinën Java me 128 megabajt.
Ne, siç rekomandohet në të gjitha librat, përdorim përpunimin në rrjedhë. Domethënë, hapim resultSet dhe lexojmë të dhënat nga aty pak-pak. A do të funksionojë kjo? A do të bjerë për shkak të memories? A do të lexojë pak-pak? Le të besojmë në bazën e të dhënave, le të besojmë në Postgres. Nuk besojmë. A do të bie OutOfMemory? Kush ka rënë nga OutOfMemory? Dhe kush arriti ta riparojë pas kësaj? Disa arritën ta riparojnë.
Nëse keni një milion rreshta, nuk mund të zgjidhni thjesht ashtu. Duhet patjetër OFFSET/LIMIT. Kush është për këtë variant? Dhe kush është për variantin që duhet të luajmë me autoCommit?
Këtu, si zakonisht, varianti më befasues rezulton të jetë i saktë. Dhe nëse ndonjëherë e fikni autoCommit, do të ndihmojë. Pse kështu? Shkenca nuk e di këtë.
Por në mënyrë standart, të gjithë klientët, që lidhen me bazën e të dhënave Postgres, zgjedhin të dhënat e plota. PgJDBC në këtë aspekt nuk është përjashtim, zgjedh të gjithë rreshtat.
Ka një variacion mbi temën FetchSize, domethënë, mund të thuash në nivelin e një deklarate të veçantë se këtu, ju lutem, zgjidhni të dhënat me 10, 50. Por kjo nuk funksionon derisa të fikni autoCommit. E fikët autoCommit – fillon të funksionojë.
Por të ecur në kod dhe për të vendosur setFetchSize kudo është e pakëndshme. Prandaj ne bëmë një konfigurim që do të thotë vlera e parazgjedhur për të gjithë lidhjen.
Ja, ne e thamë këtë. E konfiguram parametrin. Dhe çfarë arritëm? Nëse zgjedhim pak, për shembull, 10 rreshta, atëherë kemi shpenzime shumë të mëdha. Prandaj duhet ta vendosim këtë vlerë afër njëqind.
Idealisht, padyshim, do të mësojmë të kufizojmë në byte, por receta është kështu: vendosim defaultRowFetchSize më shumë se njëqind dhe gëzohemi.
Le të kalojmë në futjen e të dhënave. Futja është më e lehtë, ka variante të ndryshme. Për shembull, INSERT, VALUES. Ky është një variant i mirë. Mund të flasim për "INSERT SELECT". Në praktikë, është të njëjtën gjë. Nuk ka asnjë ndryshim në performancë.
Libra thonë se duhet të kryejmë Batch statement, libra thonë se mund të kryejmë komanda më komplekse me disa kushte. Dhe në Postgres ka një funksion të shkëlqyer - mund të bëjmë COPY, dmth. ta bëjmë këtë më shpejt.
Nëse e matim, mund të bëjmë disa zbulime interesante. Si duam që kjo të funksionojë? Duam të mos analizojmë dhe të mos kryejmë komanda të tepërta.
Në praktikë, TCP nuk na lejon të bëjmë kështu. Nëse klienti është i zënë duke dërguar një kërkesë, atëherë baza e të dhënave nuk lexon kërkesat, duke u përpjekur të na dërgojë përgjigje. Në fund, klienti pret bazën e të dhënave derisa ajo të lexojë kërkesën, ndërsa baza e të dhënave pret klientin derisa ai të lexojë përgjigjen.
Dhe kështu klienti është i detyruar të dërgojë periudhikisht një paketë sinkronizimi. Ndërveprime të panevojshme rrjetërore, humbje e kohës.
Dhe sa më shumë t'i shtojmë, aq më keq bëhet. Driver-i është shumë pesimist dhe i shton ato mjaft shpesh, rreth një herë në 200 rreshta, në varësi të madhësisë së rreshtave etj.
Ndonjëherë, kur korrigjon vetëm një rresht, çdo gjë përshpejtohet dhjetë herë. Kjo ndodh. Pse? Siç është zakonisht, një konstante diku është përdorur. Dhe vlera "128" nënkupton - mos përdorni batching.
Është mirë që kjo nuk shkoi në versionin zyrtar. E zbuluam para se të fillonim versionin e lëshimit. Të gjitha vlerat që po i përmend janë të bazuara në versionet moderne.
Le ta matim. Ne po matim InsertBatch të thjeshtë. Po matim InsertBatch të shumtë, dmth. e njëjta gjë, por shumë vlera. Një lëvizje e mençur. Nuk të gjithë e bëjnë kështu, por kjo është një lëvizje e thjeshtë, shumë më e lehtë se COPY.
Mund të bëjmë COPY.
Dhe mund të bëhet në struktura. Deklaroni llojin default të User, dërgoni një array dhe bëni INSERT direkt në tabelë.
Nëse hapni lidhjen: pgjdbc/ubenchmsrk/InsertBatch.java, ky kod është në GitHub. Mund të shihni konkretisht se cilat kërkesa gjenerohen aty. Nuk ka rëndësi.
E kemi nisur. Dhe gjëja e parë që kuptuam është se mos përdorimi i batch – thjesht nuk është e mundur. Të gjitha variantet e batching janë zero, dmth koha e ekzekutimit është praktikisht zero krahasuar me ekzekutimin e një herë.
Po insertojmë të dhëna. Ka një tabelë mjaft të thjeshtë. Tre kolona. Dhe çfarë shohim këtu? Shohim se të gjitha këto tre variante janë përafërsisht të krahasueshme. Dhe COPY, sigurisht, është më e mira.
Ky është kur ne insertojmë në copa. Kur thonim se një vlerë VALUES, dy vlera VALUES, tre vlera VALUES ose ne i kemi ato 10 me një presje. Kjo është pikërisht tani horizontalisht. 1, 2, 4, 128. Është e dukshme se Batch Insert, e cila është vizatuar me blu, ndikon shumë lehtësisht. Domethënë, kur insertoni njëri pas tjetrit ose madje edhe kur insertoni katër, bëhet dy herë më mirë, thjesht sepse kemi futur pak më shumë në VALUES. Më pak operacione EXECUTE.
Përdorimi i COPY për volume të vogla – është jashtëzakonisht të paarsyeshëm. Deri në dy të parat madje nuk i kam vizatuar. Ata shkojnë në qiell, dmth këto numra të gjelbërta për COPY.
COPY duhet të përdoret kur volumi i të dhënave është të paktën më shumë se njëqind rreshta. Shpenzimet për hapjen e kësaj lidhjeje janë të mëdha. Dhe, sinqerisht, nuk kam kërkuar shumë në këtë drejtim. E kam optimizuar batch, COPY – jo.
Çfarë bëjmë më tej? E matëm. Kuptojmë se duhet të përdorim ose struktura, ose një batch të mençur që bashkon disa vlera.
Çfarë duhet të nxirret nga referati i sotëm?
- PreparedStatement – është gjithçka për ne. Kjo sjell shumë për performancën. Ajo sjell një gropë të madhe katran.
- Dhe duhet të bëni EXPLAIN ANALYZE 6 herë.
- Dhe duhet të hollojmë OFFSET 0, dhe truket si +0 për të rregulluar përqindjen e mbetur nga kërkesat tona problematike.
Burimi: habr.com
