Ju lutem shfletoni përmbledhjen e raportit të fillimit të vitit 2016 nga Vladimir Sitnikov "PostgreSQL dhe JDBC nxjerrim gjithçka nga to"
Mirëmëngjes! Emri im është Vladimir Sitnikov. Kam punuar për 10 vjet në kompaninë NetCracker. Kryesisht merrem me performancën. Gjithçka që ka të bëjë me Java-n, gjithçka që ka të bëjë me SQL - është ajo që dua.
Sot do të flas për sfidat me të cilat u përballëm në kompani kur filluam të përdorim PostgreSQL si server të bazës së të dhënave. Kryesisht punojmë me Java-n. Por ajo që do të ndaj sot nuk është e lidhur vetëm me Java-n. Siç tregojnë praktikat, kjo ndodh edhe në gjuhë të tjera.
Do të flasim për:
- shkarkimin e të dhënave.
- Ruajtjen e të dhënave.
- Dhe për performancën.
- Po ashtu dhe për pengesat që mund të hasim.
Le të fillojmë me një pyetje të thjeshtë. Ne marrim një rresht nga tabela sipas çelësit primar.
Baza ndodhet në të njëjtin host. Të gjitha këto marrin 20 milisekonda.
Këto 20 milisekonda janë shumë. Nëse keni 100 kërkesa të tilla, keni kaluar një moment të çmuar duke i procesuar ato, pra, po humbni kohë.
Ne nuk duam të bëjmë këtë dhe shohim çfarë na ofron baza për këtë. Ajo na ofron dy variante për të ekzekutuar kërkesat.
E para është një kërkesë e thjeshtë. Pse është e mirë? Sepse e marrim dhe e dërgojmë, dhe asgjë më shumë.
Baza ka gjithashtu një kërkesë të avancuar, që është më e sofistikuar por më funksionale. Mund të dërgoni veçmas kërkesat për analizim, ekzekutim, lidhjen e variablave etj.
Super extended query – kjo është diçka që nuk do ta mbulojmë në këtë raport. Mund të kemi disa kërkesa nga baza e të dhënave dhe kemi një listë të dëshirave që është formuar në një farë mënyre, domethënë këto janë gjërat që duam, por momentalisht nuk mundemi dhe as në vitin e ardhshëm. Prandaj, thjesht e kemi regjistruar dhe do të shkojmë të bisedojmë me njerëzit kryesorë.
Por ajo që mund të bëjmë është kërkesa e thjeshtë dhe kërkesa e avancuar.
Cili është karakteri i secilit qasje?
Kërkesa e thjeshtë është e mirë për ekzekutim të njëhershëm. E bëjmë një herë dhe harrojmë. Por problemi është se ajo nuk mbështet formatin binar të të dhënave, që do të thotë se për disa sisteme me performancë të lartë nuk është e përshtatshme.
Kërkesa e avancuar – ju lejon të kurseni kohë në analizim. Kjo është ajo që bëmë dhe filluam ta përdorim. Ndofta na ndihmoi shumë. Ajo ka jo vetëm kursime në analizim, por edhe në transferimin e të dhënave. Të dërgosh të dhëna në formatin binar është shumë më efektive.
Tani kalojmë në praktikë. Kështu duket një aplikacion tipik. Mund të jetë Java etj.
Kemi krijuar një statement. E kemi ekzekutuar urdhërin. Kemi krijuar një close. Ku është gabimi këtu? Nuk ka problem. Kështu shkruhet në të gjithë librat. Kështu është e duhur të shkruhet. Nëse dëshironi performancën maksimale, shkruani kështu.
Por praktika tregon se kjo nuk funksionon. Pse? Sepse kemi metodën "close". Dhe kur e bëjmë kështu, për bazën e të dhënave, kjo është si puna e një duhani me të dhënat. Ne thamë "PARSE EXECUTE DEALLOCATE".
Pse këto krijime dhe shkarkim statements? Askush nuk ka nevojë për to. 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 të punojmë si njerëz normalë me bazën. E përgatitim një herë statement-in tonë dhe më pas e ekzekutojmë shumë herë. Në të vërtetë shumë herë – kjo është një herë gjatë gjithë jetës së aplikacionit dhe përdorim të njëjtin id të statement për REST të ndryshme. Kjo është qëllimi ynë.
Si mund 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ë ketë mbingarkesë. Nëse nuk është e qartë, mund ta masim. Të bëjmë një benchmark, në të cilin kemi një metodë të tillë të thjeshtë. Krijojmë një statement. E ekzekutojmë në një version të caktuar të driver-it dhe gjejmë se ai bie mjaft shpejt me humbje të gjithë memories që kemi.
E qartë se këto gabime janë lehtësisht të zgjidhshme. Nuk do të flas për to. Por do të them se në versionin e ri funksionon shumë më shpejt. Metoda është e padobishme, por megjithatë.
Si të punojmë si duhet? Çfarë duhet të bëjmë për këtë?
Në të vërtetë aplikacionet gjithmonë e mbyllin statements. Në të gjithë librat shkruhet që të mbyllen, përndryshe do të ketë humbje të memories.
Dhe PostgreSQL nuk e di si të ndajë kërkesat. Çdo sesion duhet të krijojë vetë këtë cache.
Gjithashtu nuk duam të humbim kohë në analizim.
Si zakonisht ne kemi dy mundësi.
Mundësia e parë – të marrim dhe të themi se le të paketojmë gjithçka në PgSQL. Aty ka cache. Ai çdo gjë e mbështjell. Kjo do të ishte e shkëlqyer. E kemi parë. Kemi 100500 kërkesa. Nuk punon. Ne nuk pajtohemi - të kthejmë manualisht kërkesat në procedura. Jo, jo.
Ne kemi një opsion të dytë - ta bëjmë vetë. Hapur kodin burimor, fillojmë të përshtasim. Përshtasim-përshtasim. Doli se nuk është kaq e vështirë të bëhet.
Kjo u shfaq në gusht 2015. Tani ka një version më modern. Dhe gjithçka është në rregull. Punon kaq mirë, saqë ne nuk po bëjmë asnjë ndryshim në aplikacion. Dhe madje kemi ndaluar së menduari për PgSQL, dmth. kjo ishte mëse e mjaftueshme për të reduktuar shpenzimet praktike pothuajse në zero.
Për rrjedhojë, 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ë të pakonsoliduar.
Mund të pyesni - ku janë numrat? Çfarë merrni? Dhe këtu nuk mund të jap numra, sepse çdo kërkesë ka numrat e saj.
Këtu kërkesat tona ishin të tilla, që ne në kërkesat OLTP shpenzonim rreth 20 milisekonda për analizë. Kishte 0,5 milisekonda për ekzekutim, 20 milisekonda për analizë. 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 aspak nuk donim të shpenzonim 20 milisekonda. E kemi sjellë në 0. Gjithçka është në rregull.
Çfarë mund të nxirrni prej këtu? Nëse keni Java, merrni versionin e fundit të driver-it dhe gëzohuni.
Nëse keni ndonjë gjuhë tjetër, mendoni - ndoshta ju nevojitet gjithashtu? Sepse nga këndvështrimi i gjuhës përfundimtare, për shembull, nëse PL 8 ose keni LibPQ, nuk është e qartë se po shpenzoni kohë jo në ekzekutim, por në analizë dhe kjo duhet kontrolluar. Si? Gjithçka është falas.
Përveç faktit se ka gabime, disa veçori. Dhe për to do të flasim tani. Pjesa më e madhe do të jetë për arkeologjinë industriale, për atë që kemi gjetur, për çfarë kemi hasur.
Nëse kërkesa krijohet dinamikisht. Kjo ndodh. Disa përzgjidhin rreshtat, që ndodhet një kërkesë SQL.
Çfarë është e keqe me të? E keqe është se çdo herë kemi një rresht të ndryshëm në fund.
Dhe këtë rresht të ndryshëm duhet ta llogarisim përsëri hashCode. Kjo është me të vërtetë një detyrë e CPU-së - të gjejë tekstin e gjatë të kërkesës në një hash të pranishëm nuk është aq e lehtë. Prandaj, përfundimi është i thjeshtë - mos krijoni kërkesa. Ruani ato në një variabël të vetme. Dhe gëzohuni.
Problemi tjetër. Tipet e të dhënave janë të rëndësishme. Ndodhin ORM që thonë, se nuk ka rëndësi cila NULL, le të jetë ndonjëherë. Nëse është Int, ne themi setInt. Ndërsa nëse është NULL, le të jetë gjithmonë VARCHAR. Dhe çfarë rëndësie ka në fund të fundit cila është atje NULL? Baza e të dhënave do ta kuptojë gjithçka vetë. Dhe ky skenar nuk funksionon.
Në praktikë, baza e të dhënave nuk e ka aspak të qartë. Nëse për herë të parë thatë se keni një numër, dhe herën e dytë thatë se është VARCHAR, atëherë nuk është e mundur të rilagni Server-prepared statements. Dhe në këtë rast duhet të krijoni përsëri statement-in tuaj.
Nëse ekzekutoni të njëjtën kërkesë, tregoni kujdes që tipet e të dhënave në kolonë të mos ngatërrohen. Duhet të jeni vigjilent për NULL. Kjo është një gabim i zakonshëm që kemi pasur pasi filluam të përdorim PreparedStatements.
I mirë, e aktivizuam. Morëm, ndoshta, driver-in. Dhe performanca ra. Gjithçka u bë keq.
Si ndodh kjo? Është një bug apo një veçori? Fatkeqësisht, nuk arritëm të kuptojmë - është një bug apo një veçori. Por ka një skenar shumë të thjeshtë për të riprodhuar këtë problem. Ai na ka kapur papritur. Dhe përfshin një seleksion pikërisht nga një tabelë. Sigurisht, kemi pasur më shumë kërkesa të tilla. Ato zakonisht përfshinin dy-tre tabela, por ka një skenar për riprodhim. Merrni në bazën tuaj të çdo versioni dhe riprodhoni.
Kuptimi është se kemi dy kolona, secila e indeksuar. Në një kolonë ka një milion rreshta me vlerën NULL. Ndërsa në kolonën tjetër ka vetëm 20 rreshta. Kur e ekzekutojmë pa variablat e lidhur, gjithçka funksionon mirë.
Nëse fillojmë të ekzekutojmë me variablat e lidhur, dmth. ne ekzekutojmë shenjën "?" ose "$1" për kërkesën tonë, atëherë çfarë marrim në fund?
Ekzekutimi i parë - siç duhet. I dyti - pak më shpejt. Diçka u ruajt. Të tretat-të katërtat-të pestat. Pastaj, hop - dhe ashtu. Dhe më e keqja, kjo ndodh në ekzekutimin e gjashtë. Kush e dinte se duhet të bësh pikërisht gjashtë ekzekutime për të kuptuar se cili është plani i ekzekutimit?
Kush është fajtor? Çfarë ndodhi? Baza e të dhënave përmban optimizimin. Dhe ajo është siç duket optimizuar për rastin e përgjithshëm. Dhe, për rrjedhojë, duke filluar nga një pikë e caktuar, ajo kalon në planin e përgjithshëm, që, fatkeqësisht, mund të rezultojë të jetë ndryshe. Ai mund të rezultojë të jetë i njëjtë, ose mund të jetë ndryshe. Dhe atje ka një vlerë prag që sjell në këtë sjellje.
Çfarë mund të bëhet me këtë? Këtu, sigurisht, është më e komplikuar të supozosh diçka. Ka një zgjidhje të thjeshtë që ne përdorim. Është +0, OFFSET 0. Me siguri, ju i njihni këto zgjidhje. Thjesht e marrim dhe e shtojmë «+0» në kërkesë dhe gjithçka është në rregull. Do ta tregoj më vonë.
Dhe ka një variant tjetër – të shikoni më me kujdes planet. Zhvilluesi duhet jo vetëm të shkruajë kërkesën, por edhe të thotë 6 herë «explain analyze». Nëse 5, atëherë nuk do të funksionojë.
Dhe ka edhe një variant të tretë – të shkruani një letër në pgsql-hackers. Unë shkrova, por akoma nuk është e qartë – është një gabim apo një tipar.
Përsa kohë jemi duke menduar – është një gabim apo një tipar, le të eci përpara. Do të marrim kërkesën tonë dhe do të shtojmë «+0». Gjithçka është në rregull. Dy simbole dhe madje nuk është nevoja të mendojmë se si është pra dhe çfarë. Shumë e thjeshtë. Ne thjesht i ndaluam databazës të përdorë indeksin në këtë kolonë. S'kemi indeks në kolonën «+0» dhe çdo gjë është në rregull.
Kjo është rregulla e 6 explain’ave. Tani në versionet aktuale duhet të bëni 6 herë, nëse keni variabla të lidhur. Nëse s'keni variabla të lidhur, atëherë veprojmë kështu. Dhe në fund, pikërisht kjo kërkesë bie. Çështja nuk është e komplikuar.
Duket se, sa mund të jetë? Ka gabime këtu, ka gabime atje. Në realitet, gabimi është kudo.
Le të shohim edhe një herë. Për shembull, kemi dy skema. Skema A me tabelën Y dhe skema B me tabelën Y. Kërkesa është – zgjedhni të dhënat nga tabela. Çfarë do të kemi gjatë kësaj? Do të kemi një gabim. Do të kemi gjithçka të përmendur më lart. Rregulli është kështu – gabimi është kudo, do të kemi gjithçka të përmendur më lart.
Tani pyetja: «Pse?». Duket se, ka dokumentacion, që, nëse kemi një skemë, ka variablin «search_path», i cili tregon ku duhet të kërkohet tabela. Duket se, variabli është aty.
Cila është problemi? Problemi është se server-prepared statements nuk dyshojnë se dikush mund ta ndryshojë search_path. Ky vlerësim mbetet si një konstant për databazën. Dhe disa pjesë mund të mos kapin vlerat e reja.
Sigurisht, kjo varet nga versioni në të cilin po testoni. Varet se sa ndryshe janë tabelat tuaja. Dhe versioni 9.1 thjesht do të ekzekutojë kërkesat e vjetra. Versionet e reja mund të zbulojnë të fshehtat dhe të thonë se keni një gabim.
Si e trajtojmë këtë? Ka një recetë të thjeshtë – mos e bëni kështu. Mos e ndryshoni search_path në punën e aplikacionit. Nëse e ndryshoni, më mirë krijoni një lidhje të re.
Mund të diskutojmë, pra, hapni, diskutojmë, shtojmë. Ndoshta, dhe do të bindim zhvilluesit e databazës se, në rast se dikush ndryshon vlerën, databaza duhet t'i thotë klientit: «Shikoni, këtu vlera është përditësuar. Ndoshta, duhet të shfuqizoni dhe të krijoni përsëri statementet?». Tani databaza sillet fshehurazi dhe nuk njofton në asnjë mënyrë se diçka ka ndryshuar brenda declaration.
Dhe përsëri do të theksoj – kjo është diçka që nuk është tipike për Java. Ne do ta shohim të njëjtën gjë në PL/pgSQL një për një. Por atje do të ri-prodhohet.
Le të provojmë të zgjedhim të dhëna. Zgjedhim dhe zgjedhim. Kemi një tabelë me një milion rreshta. Çdo rresht është një kilobajt. Rreth një gigabajt të dhënash. Dhe kemi një memorie operative në makinë në Java prej 128 megabajt.
Ne, siç rekomandohet në të gjitha librat, përdorim procesimin e rrjedhës. Pra, hapim resultSet dhe lexojmë të dhënat ngadalë. A do të funksionojë kjo? A do të ra mbi memorie? A do të lexojë pak nga pak? Le të besojmë në databazën, besojmë në Postgres. Nuk besojmë. A do të na ra OutOFMemory? Kush ka rënë në OutOfMemory? Dhe kush është në gjendje ta rregullojë pas kësaj? Disa nga ata kanë arritur.
Nëse keni një milion rreshta, atëherë nuk mund të zgjedhni thjesht kështu. Duhet domosdoshmërisht OFFSET/LIMIT. Kush është për këtë variant? Dhe kush është për variantin, se duhet të luajmë me autoCommit?
Këtu, si zakonisht, varianti më i papritur rezulton të jetë i saktë. Dhe nëse ndonjëherë e fikni autoCommit, atëherë kjo do të ndihmojë. Pse kështu? Shkenca nuk e di këtë.
Por për default, të gjitha klientët që lidhen me databazën Postgres e zgjedhin të dhënat për tërë. PgJDBC në këtë aspekt nuk është përjashtim, zgjedh të gjitha rreshtat.
Ka një variacion mbi temën FetchSize, që do të thotë, mund të thuash në nivelin e një deklarate të veçantë, që 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ë kalosh nëpër kod dhe të vendosësh gjithandej setFetchSize – është jo komode. Prandaj ne bëmë një konfigurim, i cili për tërë lidhjen do të thotë vlerën e paracaktuar.
Tani e thamë këtë. E konfiguram parametrin. Dhe çfarë morëm? Nëse zgjedhim pak, nëse, për shembull, zgjedhim 10 rreshta, atëherë kemi shpenzime shumë të mëdha. Prandaj duhet të vendosni këtë vlerë rreth njëqind.
Në mënyrë ideale, sigurisht, të mësojmë të kufizojmë edhe në byte, por receta është e tillë: vendosim defaultRowFetchSize më shumë se njëqind dhe gëzohemi.
Le të kalojmë te futja 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 ta themi "INSERT SELECT". Në praktikë, kjo është e njëjta gjë. Nuk ka asnjë diferencë në performancë.
Libra thonë se duhen realizuar deklarata Batch, libra thonë se mund të kryhen komanda më të ndërlikuara me disa kllapë. Dhe në Postgres ka një funksion të shkëlqyer – mund të bësh COPY, pra, ta bësh më shpejt.
Nëse matim, mund të zbulojmë disa gjëra interesante. Si dëshirojmë që kjo të funksionojë? Duam të mos përpunojmë dhe të mos kryejmë komanda të panevojshme.
Në praktikë, TCP nuk na lejon ta bëjmë këtë. Nëse klienti është i zënë duke dërguar një kërkesë, baza e të dhënave, në përpjekje për të na dërguar përgjigje, nuk lexon kërkesat. Në fund, klienti po pret që baza e të dhënave të lexojë kërkesën, dhe baza e të dhënave po pret klientin të lexojë përgjigjen.
Dhe kështu, klienti është i detyruar të dërgojë periodikisht një paketë sinkronizimi. Ndërveprime të panevojshme në rrjet, humbje e kohës.
Dhe sa më shumë që i shtojmë, aq më keq bëhet. Driver-i është mjaft pesimist dhe i shton ato mjaft shpesh, rreth një herë në 200 rreshta, në varësi të madhësisë së rreshtave etj.
Ndonjëherë ndodh që ndryshon vetëm një rresht dhe gjithçka përshpejtohet 10 herë. Kjo ndodh. Përse? Si zakonisht, një konstante ishte përdorur diku. Dhe vlera "128" do të thotë – mos përdor batching.
Është mirë që kjo nuk e ndodhi në versionin zyrtar. E zbuluan para se të fillonin publikimin e versionit. Të gjitha vlerat që po i citoj, bazohen në versionet moderne.
Le të matim. Ne po masim InsertBatch të thjeshtë. Ne po masim InsertBatch të shumëfishtë, pra, të njëjtën gjë, por me shumë vlera. Një lëvizje e zgjuar. Nuk e dinë të gjithë si ta bëjnë, por është një lëvizje e thjeshtë, më e lehtë se COPY.
Mund të bësh COPY.
Dhe mund ta bësh këtë në struktura. Të shpallësh User default type, të kalosh një array dhe të INSERT direkt në tabelë.
Nëse e hapni lidhjen: pgjdbc/ubenchmsrk/InsertBatch.java, ky kod është në GitHub. Mund ta shihni konkretisht se cilat kërkesa aty gjenerohen. Nuk ka rëndësi.
E kemi nisur. Dhe e para që kuptuam, është se mos përdorimi i batch-it – thjesht nuk mund të bëhet. Të gjitha variantet e batching janë të barabarta me zero, pra, koha e ekzekutimit është pothuajse e barabartë me zero krahasuar me ekzekutimin e vetëm.
Ne po fusim të dhëna. Është një tabelë mjaft e thjeshtë. Tre kolona. Dhe çfarë shohim këtu? Shohim se të gjithë këto tre variante janë afërsisht të barabarta. Dhe COPY, sigurisht, është më e mirë.
Kjo është kur ne po fusim copa cope. Kur thamë, një vlerë VALUES, dy vlera VALUES, tri vlera VALUES, ose i kemi shtuar atje 10 me presje. Kjo është pikërisht tani horizontalisht. 1, 2, 4, 128. Është e dukshme se Batch Insert, që është vizatuar me blu, lehtësohet shumë nga kjo. Pra, kur e fusni një nga një ose madje kur e fusni katër, bëhet dy herë më mirë, thjesht nga fakti se kemi shtuar pak më shumë në VALUES. Më pak operacione EXECUTE.
Të përdorësh COPY për sasi të vogla – është jashtëzakonisht e paqartë. Unë nuk e kam vizatuar as për dy të parat. Ato shkojnë në qiell, pra, këto numra të gjelbërta për COPY.
COPY duhet përdorur kur ke të dhëna, 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 gërmuar në këtë drejtim. Unë kam optimizuar batch-in, COPY – jo.
Çfarë bëjmë më pas? E matëm. Kuptojmë se duhet të përdorim ose struktura, ose një batch të zgjuar, që bashkon disa vlera.
Çfarë duhet të nxjerrim nga raporti i sotëm?
- PreparedStatement – është gjithçka për ne. Kjo ofron shumë për performancën. Ajo sjell një sasi të madhe të prishjes.
- Dhe duhet bërë EXPLAIN ANALYZE 6 herë.
- Dhe duhet të përzihet OFFSET 0, dhe trika si +0 për të rregulluar përqindjen e mbetur nga kërkesat tona problematike.
Burimi: habr.com
