Jag föreslÄr att ni bekantar er med transkriptet av Vladimir Sitnikovs presentation frÄn början av 2016, "PostgreSQL och JDBC: Utnyttja all potential"
God dag! Jag heter Vladimir Sitnikov. Jag har arbetat i 10 Är pÄ NetCracker. Jag fokuserar frÀmst pÄ prestanda. Allt som har att göra med Java och SQL Àr det jag brinner för.
Idag kommer jag att berÀtta om de utmaningar vi stött pÄ nÀr vi började anvÀnda PostgreSQL som databasserver. Vi arbetar frÀmst med Java, men det jag kommer att tala om idag gÀller inte bara Java. Som erfarenhet visat sig, uppstÄr detta Àven i andra sprÄk.
Vi kommer att tala om:
- dataval.
- databevarande.
- och Àven prestanda.
- Och om de fallgropar som finns dÀr.
LÄt oss börja med en simpel frÄga. Vi hÀmtar en rad frÄn tabellen med hjÀlp av det primÀra nyckeln.
Datan finns pÄ samma vÀrd. Och allt detta tar 20 millisekunder.
De 20 millisekunderna Àr mycket. Om ni har 100 sÄdana förfrÄgningar, slösar ni tid i en sekund för att bearbeta dessa, det vill sÀga ni slösar tid.
Vi gillar inte att göra det och kollar vad databasen erbjuder för det. Den erbjuder oss tvÄ alternativ för att köra förfrÄgningar.
Det första alternativet Àr en enkel förfrÄgan. Vad Àr bra med den? Jo, vi tar bara den och skickar den, och inget mer.
Databasen har ocksÄ en utökad förfrÄgan, som Àr mer komplicerad men ocksÄ mer funktionell. Man kan skicka förfrÄgningar för parsing, exekvering, bindning av variabler etc. separat.
Superutökad förfrĂ„gan â detta Ă€r nĂ„got vi inte kommer att tĂ€cka i den aktuella presentationen. Kanske har vi nĂ„gra önskemĂ„l frĂ„n databasen, och det finns en lista av önskemĂ„l som Ă€r pĂ„ nĂ„got sĂ€tt formulerad, alltsĂ„ det vi vill ha, men som för nĂ€rvarande och under det nĂ€rmaste Ă„ret Ă€r omöjligt. SĂ„ vi har helt enkelt bara skrivit ner det och kommer att gĂ„ och be grundlĂ€ggande personer att ta en titt.
SÄ vad vi kan göra Àr enkel förfrÄgan och utökad förfrÄgan.
Vad Àr kÀnnetecknet för varje metod?
En enkel förfrÄgan Àr bra att anvÀnda för engÄngsupplÀgg. Man utför den och glömmer den. Problemet Àr att den inte stöder binÀrt dataformat, vilket innebÀr att den inte passar för vissa högpresterande system.
Utvidet spĂžrring â lar deg spare tid pĂ„ parsing. Dette er hva vi har gjort og begynt Ă„ bruke. Det har vĂŠrt ekstremt til stor hjelp for oss. Det er ikke bare besparelse pĂ„ parsing. Det er ogsĂ„ besparelser pĂ„ dataoverfĂžring. Ă overfĂžre data i binĂŠrt format er mye mer effektivt.
La oss gÄ over til praksis. Slik ser en typisk applikasjon ut. Dette kan vÊre Java osv.
Vi opprettet en statement. UtfÞrte kommandoen. Opprettet close. Hvor ligger feilen her? Hva er problemet? Det er ingen problemer. SÄnn stÄr det skrevet i alle bÞker. Det mÄ skrives slik. Hvis du vil ha maksimal ytelse, skriv sÄnn.
Men praksis har vist at dette ikke fungerer. Hvorfor? Fordi vi har metoden «close». Og nÄr vi gjÞr det pÄ denne mÄten, blir det, sett fra databasen, som en rÞyker som jobber med databasen. Vi sa «PARSE EXECUTE DEALLOCATE».
Hvorfor disse unÞdvendige opprettelsene og lasting av statements? De er ingen til hjelp. Men vanligvis skjer det sÄnn med PreparedStatement, nÄr vi lukker dem, stenger de alt i databasen. Dette er ikke det vi vil.
Vi vil, som friske mennesker, jobbe med databasen. FÞrst forbereder vi statement vÄrt once, deretter utfÞrer vi det mange ganger. Egentlig mange ganger betyr én gang i lÞpet av applikasjonens liv. Og ved ulike REST bruker vi den samme statement ID-en. Dette er vÄrt mÄl.
Hvordan kan vi oppnÄ dette?
Det er veldig enkelt â ikke lukk statements. Skriv slik: «prepare» «execute».
Hvis vi kjÞrer noe sÄnt, vil det vÊre klart at et eller annet vil gÄ over styr et sted. Hvis det ikke er klart, kan vi mÄle. La oss lage et benchmark hvor denne enkle metoden blir testet. Vi oppretter en statement. KjÞrer med en bestemt versjon av driveren og fÄr vite at den faller ganske raskt med tap av all minnet som vi hadde.
Det er klart at slike feil lett kan rettes. Jeg skal ikke snakke om dem. Men jeg vil si at i den nye versjonen fungerer det mye raskere. Metoden er tÄpelig, men likevel.
Hvordan jobbe riktig? Hva mÄ vi gjÞre for dette?
I virkeligheten lukker applikasjoner alltid statements. I alle bÞker stÄr det at de mÄ lukkes, ellers vil minnet lekke.
Og PostgreSQL kan ikke cache forespÞrslene. Hver sesjon mÄ opprette denne cachen for seg selv.
Og vi vil heller ikke bruke tid pÄ parsing.
Og som vanlig har vi to alternativer.
Det första alternativet Ă€r att vi sĂ€ger att lĂ„t oss slĂ„ in allt i PgSQL. DĂ€r finns det cache. Den cachar allt. Det kommer att bli bra. Vi har sett pĂ„ det. Vi har 100500 förfrĂ„gningar. Det fungerar inte. Vi Ă€r inte överens â vi vill inte omvandla förfrĂ„gningar till procedurer manuellt. Nej-nej.
Vi har ett andra alternativ â att sjĂ€lva bygga det. Vi öppnar kĂ€llkoden, börjar bygga. Bygger och bygger. Det visade sig inte vara sĂ„ svĂ„rt.
Detta dök upp i augusti 2015. Nu finns det en mer modern version. Och allt Àr bra. Det fungerar sÄ bra att vi inte Àndrar nÄgot i applikationen. Och vi har till och med slutat tÀnka i termer av PgSQL, det vill sÀga, detta var tillrÀckligt för att vi skulle kunna minska alla kostnader praktiskt taget till noll.
DÀrför aktiveras Server-prepared statements vid den femte körningen för att inte slösa minne i databasen pÄ varje engÄngsförfrÄgan.
Man kan frĂ„ga â var Ă€r siffrorna? Vad fĂ„r ni? Och hĂ€r ger jag inga siffror, eftersom de varierar för varje förfrĂ„gan.
Vi hade förfrĂ„gningar dĂ€r vi pĂ„ OLTP-förfrĂ„gningar spenderade cirka 20 millisekunder pĂ„ parsing. Det var 0,5 millisekunder för körning, 20 millisekunder för parsing. FörfrĂ„gan â 10 KiB text, 170 rader av planen. Det Ă€r en OLTP-förfrĂ„gan. Den frĂ„gar 1, 5, 10 rader, ibland fler.
Men vi ville absolut inte spendera 20 millisekunder. Vi fick det till 0. Allt Àr bra.
Vad kan ni ta med er hÀrifrÄn? Om ni har Java, sÄ tar ni den moderna versionen av drivrutinen och Àr glada.
Om ni har nĂ„got annat sprĂ„k, sĂ„ övervĂ€g â kanske detta ocksĂ„ Ă€r nĂ„got ni behöver? För ur slutkundens synvinkel, till exempel, om ni har PL 8 eller LibPQ, sĂ„ Ă€r det inte uppenbart att ni slösar tid inte pĂ„ körning, utan pĂ„ parsing, och detta Ă€r vĂ€rt att kontrollera. Hur? Allt Ă€r gratis.
Med undantag för att det finns fel, vissa egenskaper. Och om dem ska vi just nu prata. Största delen kommer att handla om industriell arkeologi, om vad vi har funnit, vad vi har stött pÄ.
Om förfrÄgan genereras dynamiskt. SÄnt hÀnder. NÄgon klipper ihop strÀngar, vilket ger en SQL-förfrÄgan.
Vad Àr dÄ dÄligt med det? Det Àr dÄligt pÄ sÄ sÀtt att vi varje gÄng fÄr en annan strÀng.
Och denna olika rad behöver rĂ€kna hashCode igen. Det Ă€r verkligen en CPU-uppgift â att hitta en lĂ„ng förfrĂ„gan i en redan befintlig hash Ă€r inte sĂ„ enkelt. SĂ„ slutsatsen Ă€r enkel â generera inte förfrĂ„gningar. Lagra dem i en och samma variabel. Och var glad.
NÀsta problem. Datatyper Àr viktiga. Det finns ORM:er som sÀger att det inte spelar nÄgon roll vilken NULL det Àr, det kan vara vilken slags som helst. Om det Àr Int, sÄ sÀger vi setInt. Och om det Àr NULL, lÄt det alltid vara VARCHAR. Vad spelar det egentligen för roll vilken NULL som Àr dÀr? Databasen kommer att förstÄ allt sjÀlv. Och den bilden fungerar inte.
I praktiken bryr sig inte databasen det minsta. Om du första gÄngen sa att det hÀr Àr ett nummer, och andra gÄngen sa att det Àr VARCHAR, dÄ gÄr det inte att ÄteranvÀnda Server-prepared statements. I sÄ fall mÄste vi Äterskapa vÄr statement.
Om du kör samma förfrÄgan, se till att datatyperna i kolumnen inte blandas ihop. Det Àr viktigt att hÄlla koll pÄ NULL. Det Àr ett vanligt fel som vi hade efter att vi började anvÀnda PreparedStatements.
Bra, vi slog pÄ. Vi tog kanske en drivrutin. Och prestandan sjönk. Allt blev dÄligt.
Hur kan det hĂ€nda? Ăr det en bugg eller en funktion? TyvĂ€rr lyckades vi inte förstĂ„ â Ă€r det en bugg eller en funktion? Men det finns ett ganska enkelt sĂ€tt att reproducera detta problem. Det överraskade oss helt. Det handlar om att hĂ€mta data frĂ„n bokstavligen en tabell. Vi hade förstĂ„s fler sĂ„dana förfrĂ„gningar. De inkluderade vanligtvis tvĂ„-tre tabeller, men det finns ett sĂ„dant reproduktionsscenario. Ta vilken version som helst av din databas och upprepa.
PoÀngen Àr att vi har tvÄ kolumner, varav varje Àr indexerad. I den ena kolumnen ligger en miljon rader med vÀrdet NULL. I den andra kolumnen finns bara 20 rader. NÀr vi kör utan kopplade variabler fungerar allt bra.
Om vi börjar köra med kopplade variabler, det vill sĂ€ga att vi anvĂ€nder tecknet â?â eller â$1â för vĂ„r förfrĂ„gan, vad fĂ„r vi dĂ„?
Första körningen â som vanligt. Andra â lite snabbare. NĂ„got har cachats. Tredje-fjĂ€rde-femte. Sedan plötsligt â och sĂ„ hĂ€r. Och det vĂ€rsta Ă€r att det hĂ€nder vid sjĂ€tte körningen. Vem visste att man behövde göra just sex körningar för att förstĂ„ vilken plan som verkligen gĂ€ller?
Vem bÀr ansvaret? Vad hÀnde? Databasen innehÄller optimering. Och den Àr slags optimerad för det generiska fallet. Och dÀrför, frÄn ett visst tillfÀlle, övergÄr den till den generiska planen som tyvÀrr kan vara en annan. Den kan vara densamma, men den kan ocksÄ vara en annan. Och det finns ett tröskelvÀrde som leder till ett sÄdant beteende.
Vad kan vi göra Ät detta? HÀr Àr det nÄgot mer komplicerat att spekulera. Det finns en enkel lösning som vi anvÀnder. Det Àr +0, OFFSET 0. Ni kÀnner sÀkert till sÄdana lösningar. Vi lÀgger helt enkelt till «+0» i förfrÄgan och allt Àr bra. Jag visar det senare.
Och det finns ocksÄ ett alternativ - att titta noggrannare pÄ planerna. Utvecklaren mÄste inte bara skriva förfrÄgan utan ocksÄ sÀga «explain analyze» sex gÄnger. Om det Àr fem gÄnger, sÄ passar det inte.
Och det finns ocksÄ det tredje alternativet - att skriva ett brev till pgsql-hackers. Jag har skrivit, men det Àr fortfarande oklart - Àr det en bugg eller en funktion?
Medan vi tÀnker - Àr det en bugg eller en funktion, lÄt oss fixa det. Vi tar vÄr förfrÄgan och lÀgger till «+0». Allt Àr bra. TvÄ tecken och man behöver inte ens tÀnka pÄ hur det gÄr till. VÀldigt enkelt. Vi har helt enkelt förbjudit databasen frÄn att anvÀnda indexet för den hÀr kolumnen. Vi har inget index för kolumnen «+0» och allt, databasen anvÀnder inte indexet, allt Àr bra.
Det hÀr Àr regeln för sex «explain». Just nu i de aktuella versionerna mÄste man göra det sex gÄnger om man har relaterade variabler. Om man inte har relaterade variabler gör vi sÄ hÀr. Och vi fÄr i slutÀndan just den hÀr förfrÄgan att falla. Det Àr inte komplicerat.
Det verkar som om hur mycket kan man? HÀr Àr en bugg, dÀr Àr en bugg. Verkligen buggar överallt.
LÄt oss titta pÄ en gÄng till. Till exempel, vi har tvÄ scheman. Schema A med tabellen Ы och schema B med tabellen Ы. FörfrÄgan - att hÀmta data frÄn tabellen. Vad kommer vi att fÄ? Vi kommer att fÄ ett fel. Vi kommer att ha allt ovanstÄende. Regeln Àr sÄ hÀr - bugg överallt, vi kommer att ha allt ovanstÄende.
Nu frÄgan: «Varför?». Det verkar som om det finns dokumentation, att om vi har ett schema, sÄ finns det variabeln «search_path» som sÀger var man ska leta efter tabellen. Det verkar som om variabeln finns.
Vad Àr problemet? Problemet Àr att serverberedda uttalanden inte misstÀnker att nÄgon kan Àndra «search_path». Det hÀr vÀrdet förblir som en konstant för databasen. Och vissa delar kanske inte fÄnga de nya vÀrdena.
SjÀlvklart beror det pÄ vilken version du testar. Det beror pÄ hur mycket dina tabeller skiljer sig Ät. Version 9.1 kommer helt enkelt att köra de gamla frÄgorna. Nyare versioner kan upptÀcka misstaget och sÀga att du har ett fel.
Hur Ă„tgĂ€rdar man detta? Det finns ett enkelt recept â gör inte sĂ„. Ăndra inte search_path i applikationens arbete. Om du Ă€ndrar det Ă€r det bĂ€ttre att skapa en ny anslutning.
Vi kan diskutera det, det vill sÀga öppna upp, diskutera, fylla pÄ. Kanske kan vi övertyga databasutvecklarna om att nÀr nÄgon förÀndrar ett vÀrde, bör databasen informera klienten: 'Titta, det finns ett nytt vÀrde hÀr. Kanske behöver du ÄterstÀlla statements, Äterskapa dem?'. Just nu beter sig databasen pÄ ett dolt sÀtt och meddelar ingenting om att statements har förÀndrats nÄgonstans inuti.
Och jag ska Ă„terigen betona â detta Ă€r inte typiskt för Java. Vi kommer att se samma sak i PL/pgSQL direkt ett till ett. Men dĂ€r kommer det att reproduceras.
LÄt oss försöka hÀmta data igen. Vi hÀmtar och hÀmtar. Vi har en tabell med en miljon rader. Varje rad Àr ett kilobyte. UngefÀr en gigabyte data. Och vi har en arbetsminne i Java-maskinen pÄ 128 megabyte.
Vi, som rekommenderas i alla böcker, anvÀnder strömmande behandling. Det vill sÀga, vi öppnar resultSet och lÀser data dÀrifrÄn lite i taget. Kommer detta att fungera? Kommer det att krascha av minnesbrist? Kommer det att lÀsa lite i taget? LÄt oss ha tro pÄ databasen, pÄ Postgres. Vi tror inte. Ska vi fÄ OutOfMemory? Vem har fÄtt OutOfMemory? Och vem lyckades fixa det? NÄgon lyckades reda ut det.
Om du har en miljon rader, kan du inte bara hÀmta dem. Du mÄste definitivt anvÀnda OFFSET/LIMIT. Vem Àr för det alternativet? Och vem tycker att man ska leka med autoCommit?
HÀr visar det sig som vanligt att det ovÀntade alternativet Àr det rÀtta. Och om du plötsligt stÀnger av autoCommit sÄ hjÀlper det. Varför Àr det sÄ? Vetenskapen vet inte.
Men som standard hÀmtar alla klienter som ansluter till databasen Postgres all data. PgJDBC Àr inget undantag, det hÀmtar alla rader.
Det finns en variation pĂ„ temat FetchSize, det vill sĂ€ga man kan pĂ„ nivĂ„ med ett enskilt statement ange att hĂ€r, snĂ€lla hĂ€mta data i omgĂ„ngar om 10, 50. Men det fungerar inte förrĂ€n du stĂ€nger av autoCommit. Har du stĂ€ngt av autoCommit â börjar det fungera.
Men att gÄ igenom koden och sÀtta setFetchSize överallt Àr obekvÀmt. DÀrför har vi gjort en instÀllning som anger standardvÀrdet för hela anslutningen.
SÄ, vi har sagt detta. Vi har stÀllt in parametern. Och vad har vi fÄtt? Om vi vÀljer pÄ lÄga nivÄer, till exempel, om vi vÀljer 10 rader, har vi ganska stora overhead-kostnader. DÀrför bör detta vÀrde sÀttas till cirka hundra.
Idealiskt sett, förstÄs, kan vi Àven lÀra oss att begrÀnsa i byte, men receptet Àr följande: sÀtt defaultRowFetchSize över hundra och var glad.
LÄt oss gÄ vidare till datainsÀttning. InsÀttning Àr enklare, det finns olika alternativ. Till exempel, INSERT, VALUES. Det Àr ett bra alternativ. Man kan sÀga "INSERT SELECT". I praktiken Àr det samma sak. Det finns ingen skillnad i prestanda.
Böcker sĂ€ger att man mĂ„ste utföra Batch-statements, böcker sĂ€ger att man kan utföra mer komplexa kommandon med flera parenteser. Och i Postgres finns det en fantastisk funktion â man kan göra COPY, dvs. göra detta snabbare.
Om vi mÀter, kan vi Äterigen göra flera intressanta upptÀckter. Hur vill vi att detta ska fungera? Vi vill inte parsa och inte utföra onödiga kommandon.
I praktiken tillÄter TCP oss inte att göra sÄ. Om klienten Àr upptagen med att skicka en förfrÄgan, lÀser inte databasen förfrÄgningarna nÀr den försöker skicka tillbaka svaren. I slutÀndan vÀntar klienten pÄ databasen medan den lÀser förfrÄgan, och databasen vÀntar pÄ klienten medan den lÀser svaret.
Och dÀrför tvingas klienten periodiskt skicka en synkroniseringspaket. Onödig nÀtverksinteraktion, onödig tidsförlust.
Och ju fler vi lÀgger till, desto sÀmre blir det. Drivern Àr ganska pessimistisk och lÀgger till dem ganska ofta, ungefÀr var 200:e rad, beroende pÄ radens storlek osv.
Det hĂ€nder att om du bara Ă€ndrar en rad sĂ„ ökar allt med tio gĂ„nger. SĂ„dana saker hĂ€nder. Varför? Som vanligt var en konstant nĂ„gonstans redan anvĂ€nd. Och vĂ€rdet "128" innebar â att inte anvĂ€nda batching.
Det Àr bra att detta inte kom med i den officiella versionen. Vi upptÀckte det innan vi började slÀppa en version. Alla vÀrden jag nÀmner baseras pÄ moderna versioner.
LÄt oss mÀta. Vi mÀter enkel InsertBatch. Vi mÀter multipel InsertBatch, dvs. samma sak, men med mÄnga values. En smart strategi. Inte alla kan göra det, men det Àr en enkel strategi, mycket enklare Àn COPY.
Man kan göra COPY.
Och detta kan göras med strukturer. Deklarera User standardtyp, överför en array och sÀtt in direkt i tabellen.
Om du öppnar lÀnken: pgjdbc/ubenchmsrk/InsertBatch.java, sÄ finns denna kod pÄ GitHub. Du kan se specifikt vilka frÄgor som genereras dÀr. Det Àr inte det viktiga.
Vi har startat. Och det första vi insĂ„g Ă€r att det Ă€r helt omöjligt att inte anvĂ€nda batch â alla alternativ till batching Ă€r likvĂ€rdiga med noll, det vill sĂ€ga körningstiden Ă€r praktiskt taget noll jĂ€mfört med att köra en gĂ„ng.
Vi sÀtter in data. Det Àr en ganska enkel tabell. Tre kolumner. Och vad ser vi hÀr? Vi ser att alla dessa tre alternativ Àr ungefÀr jÀmförbara. Och COPY Àr sjÀlvklart bÀttre.
Det Àr nÀr vi sÀtter in bit för bit. NÀr vi sa att ett vÀrde VALUES, tvÄ vÀrden VALUES, tre vÀrden VALUES eller vi angav dem dÀr 10 med kommatecken. Det Àr just nu Ät sidan. 1, 2, 4, 128. Det Àr tydligt att Batch Insert, som Àr ritat i blÄtt, lÀttas av detta. Det vill sÀga, nÀr du sÀtter in en och en, eller till och med nÀr du sÀtter in fyra, sÄ blir det i tvÄ gÄnger bÀttre, bara för att vi tryckte lite mer i VALUES. FÀrre EXECUTE-operationer.
Att anvÀnda COPY för smÄ volymer Àr extremt orealistiskt. Jag ritade inte ens pÄ de tvÄ första. De gÄr till himlen, det vill sÀga dessa gröna siffror för COPY.
COPY bör anvĂ€ndas nĂ€r du har Ă„tminstone mer Ă€n hundra rader. Ăverheaden för att öppna denna anslutning Ă€r stor. Och, Ă€rligt talat, har jag inte grĂ€vt i den riktningen. Jag har optimerat batch, COPY â nej.
Vad gör vi hÀrnÀst? Vi har mÀtt. Vi förstÄr att vi bör anvÀnda antingen strukturer eller en smart batch som kombinerar flera vÀrden.
Vad mÄste vi ta med oss frÄn dagens förelÀsning?
- PreparedStatement â detta Ă€r allt för oss. Det ger mycket för prestanda. Det ger en stor spann med tjĂ€ra.
- Och vi mÄste göra EXPLAIN ANALYZE 6 gÄnger.
- Och vi mÄste spÀda ut OFFSET 0, och trick som +0 för att rÀtta till den kvarvarande procenten av vÄra problematiska frÄgor.
KĂ€lla: habr.com
