Jeg vil dele analysen av Vladimir Sitnikovs rapport fra begynnelsen av 2016 "PostgreSQL og JDBC, vi presser ut alt vi kan".
God dag! Jeg heter Vladimir Sitnikov. Jeg har jobbet i 10 Ă„r i NetCracker. Og jeg jobber hovedsakelig med ytelse. Alt som har med Java Ă„ gjĂžre, og alt som har med SQL Ă„ gjĂžre â det er det jeg elsker.
I dag vil jeg snakke om hva vi har stÞtt pÄ i selskapet nÄr vi begynte Ä bruke PostgreSQL som database-server. Og vi jobber hovedsakelig med Java. Men det jeg vil si i dag, gjelder ikke bare Java. Som praksisen har vist, oppstÄr det ogsÄ i andre sprÄk.
Vi skal snakke om:
- datauthenting.
- lagring av data.
- og ytelse.
- Og om feller som er der.
La oss starte med et enkelt spÞrsmÄl. Vi henter én rad fra tabellen basert pÄ primÊrnÞkkelen.
Databasen er pÄ den samme verten. Og alt dette tar 20 millisekunder.
Disse 20 millisekundene er veldig mye. Hvis du har 100 slike forespÞrselere, bruker du tid i sekunder pÄ Ä kjÞre disse forespÞrselene, det vil si at du kaster bort tid.
Vi liker ikke Ä gjÞre dette, og ser pÄ hva databasen tilbyr oss for Ä gjÞre det. Databasen gir oss to mÄter Ä utfÞre forespÞrselen pÄ.
Den fĂžrste mĂ„ten â en enkel forespĂžrsel. Hva er bra med dette? Jo, vi sender den og gjĂžr ingenting mer.
Databasen har ogsÄ en utvidet forespÞrsel, som er mer intelligent, men mer funksjonell. Du kan sende forespÞrselen pÄ parsing, utfÞrelse, binding av variabler osv. separat.
Super utvidet forespĂžrsel â dette er noe vi ikke vil dekke i denne presentasjonen. Vi Ăžnsker kanskje noen ting fra databasen, og det er en liste over Ăžnsker som har blitt dannet pĂ„ en eller annen mĂ„te, det vil si dette er det vi Ăžnsker, men som ikke er mulig akkurat nĂ„ og i lĂžpet av det nĂŠrmeste Ă„ret. SĂ„ vi har bare skrevet det ned og vil gĂ„ for Ă„ skape oppstyr blant hovedpersonene.
Det vi kan gjĂžre, er enkel forespĂžrsel og utvidet forespĂžrsel.
Hva er spesialiteten til hver tilnĂŠrming?
En enkel forespĂžrsel er godt egnet for engangsbruk. Du utfĂžrer den Ă©n gang og glemmer den. Problemet er at den ikke stĂžtter binĂŠrt dataformat, det vil si at den ikke er egnet for noen hĂžyytelsessystemer.
Utvidet forespĂžrsel â gir oss muligheten til Ă„ spare tid pĂ„ parsing. Dette er noe vi har implementert og begynt Ă„ bruke. Det har hjulpet oss enormt. Det er ikke bare tidsbesparelsen fra parsing. Det er ogsĂ„ besparelse pĂ„ datatransmisjon, da det er mye mer effektivt Ă„ overfĂžre data i binĂŠrt format.
La oss gÄ over til praksis. Slik ser en typisk applikasjon ut. Det kan vÊre Java osv.
Vi opprettet en statement. Vi utfÞrte kommandoen. Vi opprettet en close. Hvor ligger feilen her? Hva er problemet? Det er ingen problemer. Dette stÄr det i alle bÞkene. Dette mÄ man skrive. Hvis du Þnsker maksimal ytelse, sÄ skriv pÄ denne mÄten.
Men praksisen har vist at dette ikke fungerer. Hvorfor? Fordi vi har metoden «close». Og nÄr vi gjÞr det slik, blir det fra databasen sett pÄ som Ä vÊre en rÞyker som jobber med databasen. Vi sa «PARSE EXECUTE DEALLOCATE».
Hvorfor disse unÞdvendige opprettelsene og utlastingene av statements? De er ingen til hjelp. Men vanligvis skjer det slik i PreparedStatement, at nÄr vi stenger dem, sÄ stenger de alt i databasen. Dette er ikke det vi Þnsker.
Vi Ăžnsker Ă„ jobbe med databasen som friske mennesker. Vi forbereder statement Ă©n gang, og deretter bruker vi det mange ganger. Faktisk mange ganger â det er Ă©n gang i hele livssyklusen til applikasjonen at vi parser. Og pĂ„ de forskjellige REST-ene bruker vi den samme statement ID-en. Det er vĂ„rt mĂ„l.
Hvordan kan vi oppnÄ dette?
Veldig enkelt â vi trenger ikke Ă„ lukke statements. Vi skriver slik: «prepare» «execute».
Hvis vi kjÞrer dette, sÄ er det klart at vi et sted vil oppleve noe som overfyller. Hvis ikke det er klart, kan vi mÄle. La oss lage en benchmark, hvor en sÄ enkel metode er inkludert. Vi oppretter en statement. KjÞr den pÄ en viss versjon av driveren, og vi ser at den faller ganske raskt med tap av all minne som vi har tapt.
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 ubrukelig, men likevel.
Hvordan arbeide pÄ riktig mÄte? Hva mÄ vi gjÞre?
I virkeligheten lukker applikasjoner alltid statements. I alle bĂžker anbefales det Ă„ lukke dem, ellers vil minne lekke.
Og PostgreSQL kan ikke cache forespÞrslene. Hver sesjon mÄ opprette denne cachen for seg selv.
Og vi Þnsker heller ikke Ä kaste bort tid pÄ parsing.
Og som vanlig har vi to alternativer.
Den fÞrste lÞsningen er at vi tar og sier at vi skal pakke alt inn i PgSQL. Der finnes det cache. Det cacher alt. Det vil bli strÄlende. Vi har sett pÄ dette. Vi har 100500 forespÞrslene. Det fungerer ikke. Vi er ikke enige i Ä gjÞre forespÞrslene om til prosedyrer for hÄnd. Nei-nei.
Vi har en andre lĂžsning â ta og lage noe selv. Vi Ă„pner kildene og begynner Ă„ kode. Koder-koder. Det viste seg at det ikke var sĂ„ vanskelig Ă„ gjĂžre.
Dette dukket opp i august 2015. NÄ finnes det allerede en mer moderne versjon. Og alt er flott. Det fungerer sÄ bra at vi ikke endrer noe i applikasjonen. Vi har til og med sluttet Ä tenke pÄ PgSQL, det vil si at dette var mer enn tilstrekkelig til Ä redusere alle driftskostnader praktisk talt til null.
Dermed aktiveres server-forberedte utsagn ved den femte kjÞringen for Ä ikke kaste bort minne i databasen pÄ hver enkelt engangsforespÞrsel.
Man kan spĂžrre â hvor er tallene? Hva fĂ„r dere? Og her kan jeg ikke gi tall, for hver forespĂžrsel har sine egne tall.
VÄre forespÞrsel var slik at vi pÄ OLTP-forespÞrslene brukte omtrent 20 millisekunder pÄ parsing. Det var 0,5 millisekunder pÄ kjÞring, 20 millisekunder pÄ parsing. ForespÞrselen var 10 KiB med tekst, 170 linjer med plan. Dette er en OLTP-forespÞrsel. Den ber om 1, 5, 10 linjer, av og til mer.
Men vi Ăžnsket absolutt ikke Ă„ bruke 20 millisekunder. Vi klarte Ă„ redusere det til 0. Alt er flott.
Hva kan dere ta med dere herfra? Hvis dere bruker Java, sÄ tar dere den moderne versjonen av driveren og er fornÞyd.
Hvis dere bruker et annet sprĂ„k, sĂ„ tenk â kanskje dette ogsĂ„ er noe dere trenger? Fordi fra perspektivet til det endelige sprĂ„ket, for eksempel hvis det er PL 8 eller har dere LibPQ, sĂ„ er det ikke Ă„penbart at dere bruker tid ikke pĂ„ kjĂžringen, men pĂ„ parsing, og det er verdt Ă„ sjekke. Hvordan? Alt er gratis.
Unntatt at det er feil, noen spesielle forhold. Og om dem skal vi akkurat nÄ snakke. StÞrstedelen vil vÊre om industriell arkeologi, om det vi har funnet, hva vi har stÞtt pÄ.
Hvis forespĂžrselen genereres dynamisk. Slike ting skjer. Noen limer sammen strenger, og det blir en SQL-forespĂžrsel.
Hva er feil med det? Det er dÄrlig fordi vi hver gang ender opp med en annen streng.
Og denne varierte strengen mĂ„ hashCode beregnes pĂ„ nytt. Dette er virkelig en CPU-oppgave â Ă„ finne den lange forespĂžrselsteksten er ikke sĂ„ enkelt selv med eksisterende hash. SĂ„ konklusjonen er enkel â ikke generer forespĂžrslene. Oppbevar dem i en enkelt variabel. Og vĂŠr glad.
Neste problem. Datatyper er viktige. Det finnes ORM-er som sier at hvilken som helst NULL er greit. Hvis det er Int, sier vi setInt. Og hvis NULL, sÄ skal det alltid vÊre VARCHAR. Og hva gjÞr det vel til slutt hvilken NULL som er der? Databasen forstÄr dette selv.
I praksis er databasen fullstendig klar over dette. Hvis du fÞrst sa at dette er et tall, og etterpÄ sa at det er VARCHAR, sÄ er det umulig Ä gjenbruke server-forberedte setninger. I sÄ fall mÄ vi opprette setningen vÄr pÄ nytt.
Hvis du kjÞrer den samme forespÞrselen, sÄ vÊr oppmerksom pÄ at datatype i kolonnene ikke er forvirret. Du mÄ passe pÄ NULL. Dette er en vanlig feil vi hadde etter at vi begynte Ä bruke PreparedStatements.
Greit, vi skrudde det pÄ. Kanskje vi fikk driveren. Og ytelsen falt. Alt ble dÄrlig.
Hvordan kan dette skje? Er det en bug eller en funksjon? Dessverre er det ikke mulig Ă„ forstĂ„ â er dette en bug eller en funksjon. Men det finnes et ganske enkelt gjenskprehendingsscenario for dette problemet. Det angrep oss ganske uventet. Og det handler om en forespĂžrsel fra bokstavelig talt Ă©n tabell. Vi hadde selvfĂžlgelig flere slike forespĂžrslene. De inkluderte som regel to-tre tabeller, men det finnes dette scenariet. Ta databasen din i hvilken som helst versjon og gjenskap.
Poenget er at vi har to kolonner, hvor hver er indeksert. I den ene kolonnen finnes det en million rader med NULL-verdi. Og i den andre kolonnen ligger det bare 20 rader. NĂ„r vi kjĂžrer uten tilknyttede variabler, fungerer alt som det skal.
Hvis vi begynner Ä kjÞre med tilknyttede variabler, det vil si vi bruker tegn som «?» eller «$1» i forespÞrselen, hva fÄr vi da?
FĂžrste kjĂžring â som forventet. Andre â litt raskere. Noe ble cachet. Tredje-fjerde-femte. SĂ„, bang â og pĂ„ en eller annen mĂ„te sĂ„nn. Og det mest frustrerende er at dette skjer ved den sjette kjĂžringen. Hvem visste at man mĂ„tte gjĂžre akkurat seks kjĂžringer for Ă„ forstĂ„ hvilken utfĂžrelsesplan som faktisk gjelder?
Hvem har skylden? Hva skjedde? Databasen inneholder optimalisering. Og den er pÄ en mÄte optimalisert for et generisk tilfelle. Og, i sÄ fall, fra et visst punkt overgÄr den til den generiske planen, som dessverre kan vise seg Ä vÊre noe annet. Den kan vÊre lik, men kan ogsÄ vÊre noe annet. Og det er et terskelverdi som fÞrer til slik oppfÞrsel.
Hva kan vi gjÞre med dette? Her er det selvfÞlgelig mer komplisert Ä gjÞre antakelser. Det er en enkel lÞsning som vi bruker. Det er +0, OFFSET 0. Du kjenner sikkert slike lÞsninger. Vi tar bare og legger til «+0» i forespÞrselen, og sÄ gÄr det bra. Jeg skal vise det senere.
Og det er en annen mulighet â se mer nĂžye pĂ„ planene. Utvikleren mĂ„ ikke bare skrive forespĂžrselen, men ogsĂ„ si «explain analyze» seks ganger. Hvis det er fem, er det ikke tilstrekkelig.
Og det er en tredje mulighet â Ă„ skrive et brev til pgsql-hackers. Jeg skrev det, men det er fortsatt uklart om det er en feil eller en funksjon.
Mens vi tenker â er dette en feil eller en funksjon, la oss fikse det. Vi tar forespĂžrselen vĂ„r og legger til «+0». Det gĂ„r bra. To symboler og vi trenger ikke engang Ă„ tenke pĂ„ hvordan det fungerer. Veldig enkelt. Vi har bare nektet databasen Ă„ bruke indeksen for denne kolonnen. Vi har ikke indeksen for kolonnen «+0» og alt er bra.
Det er regelen om seks forklaringer. NÄ, i de nÄvÊrende versjonene mÄ du gjÞre det seks ganger hvis du har relaterte variabler. Hvis du ikke har relaterte variabler, gjÞr vi det slik. Og til slutt feiler akkurat denne forespÞrselen. Det er ikke sÄ vanskelig.
Det kunne virke som om det er nok nÄ? Her er en feil, der en feil. Det er faktisk feil overalt.
La oss se nĂŠrmere pĂ„ det. For eksempel har vi to skjemaer. Skjema A med tabellen Y og skjema B med tabellen Y. ForespĂžrselen â velg data fra tabellen. Hva skjer da? Vi fĂ„r en feil. Vi fĂ„r alt som er nevnt ovenfor. Regelen er â feil overalt, og vi fĂ„r alt nevnt ovenfor.
NÄ er spÞrsmÄlet: «Hvorfor?». Det kunne virke som om det finnes dokumentasjon som sier at hvis vi har et skjema, sÄ er det variabelen «search_path», som indikerer hvor vi skal lete etter tabellen. Det virker som om variabelen er der.
Hva er problemet? Problemet er at serverforberedte uttalelser ikke vet at «search_path» kan endres av noen. Denne verdien forblir som en konstant for databasen. Og noen deler kan kanskje ikke ta opp de nye verdiene.
SelvfÞlgelig avhenger det av versjonen du tester pÄ. Det kommer an pÄ hvor mye tabellene dine varierer. Og versjon 9.1 vil bare utfÞre de gamle forespÞrslene. Nye versjoner kan oppdage fellen og si at du har en feil.
Hvordan fikser vi dette? Det er en enkel oppskrift â ikke gjĂžr det. Ikke endre search_path i applikasjonens drift. Hvis du endrer det, er det bedre Ă„ opprette en ny tilkobling.
Vi kan diskutere, altsÄ Äpne, diskutere, skrive til. Kanskje vi kan overbevise databaseutviklerne om at nÄr noen endrer verdien, bÞr databasen si til klienten: «Se, verdien din har blitt oppdatert. Kanskje du bÞr tilbakestille utsagnene, opprette dem pÄ nytt?». NÄ oppfÞrer databasen seg skjult og informerer ikke om at det har skjedd endringer i utsagnene.
Og jeg vil igjen understreke â dette er ikke typisk for Java. Vi vil se det samme i PL/pgSQL en til en. Men der vil det bli gjenskapt.
La oss prÞve Ä velge mer data. Vi velger og velger. Vi har en tabell med en million rader. Hver rad er en kilobyte. Omtrent en gigabyte med data. Og vi har arbeidsminne pÄ Java-maskinen pÄ 128 megabyte.
Vi, som anbefalt i alle bÞker, bruker strÞmming. AltsÄ Äpner vi resultSet og leser data derfra litt etter litt. Vil dette fungere? Vil det ikke gÄ tom for minne? Vil det lese litt og litt? La oss stole pÄ databasen, la oss stole pÄ Postgres. Nei, vi stoler ikke. Vil vi fÄ OutOfMemory? Hvem har fÄtt OutOfMemory? Og hvem klarte Ä fikse det etterpÄ? Noen klarte Ä fikse det.
Hvis du har en million rader, kan du ikke bare velge pÄ den mÄten. Du mÄ absolutt bruke OFFSET/LIMIT. Hvem er for denne varianten? Og hvem er for varianten der man mÄ leke med autoCommit?
Her, som vanlig, viser det seg at det mest uventede alternativet er det rette. Og hvis du plutselig slÄr av autoCommit, vil det hjelpe. Hvorfor er det slik? Det er vitenskapen ukjent.
Men som standard velger alle klienter som kobler til Postgres-databasen dataene fullt ut. PgJDBC er ikke noe unntak i denne forbindelsen, den velger alle rader.
Det finnes en variant med FetchSize, altsĂ„ kan du pĂ„ nivĂ„et til et enkelt utsagn si at her, vĂŠr sĂ„ snill, velg data med 10, 50. Men det fungerer ikke fĂžr du slĂ„r av autoCommit. SlĂ„tt av autoCommit â det begynner Ă„ fungere.
Men det er upraktisk Ä gÄ gjennom koden og sette setFetchSize overalt. Derfor har vi laget en innstilling som angir standardverdien for hele forbindelsen.
SÄ vi sa dette. Vi har konfigurert parameteren. Og hva fikk vi ut av det? Hvis vi velger et lite antall, for eksempel 10 rader, sÄ har vi ganske store overhead-kostnader. Derfor mÄ denne verdien settes til omtrent hundre.
Ideelt sett skulle vi ogsÄ lÊre Ä begrense i byte, men oppskriften er slik: sett defaultRowFetchSize til mer enn hundre og vÊr glad.
La oss gÄ videre til datainnsending. Innsending er enklere, det finnes forskjellige alternativer. For eksempel, INSERT, VALUES. Det er et godt alternativ. Man kan ogsÄ si 'INSERT SELECT'. I praksis er det det samme. Det er ingen forskjell i ytelse.
BÞker sier at man mÄ utfÞre Batch-statement, bÞker sier at man kan utfÞre mer komplekse kommandoer med flere parenteser. Og i Postgres finnes det en fantastisk funksjon - man kan bruke COPY, det vil si Ä gjÞre dette raskere.
Hvis man mÄler, kan man igjen gjÞre noen interessante oppdagelser. Hvordan Þnsker vi at dette skal fungere? Vi Þnsker Ä unngÄ parsing og unÞdvendige kommandoer.
I praksis gir ikke TCP oss muligheten til dette. Hvis klienten er opptatt med Ä sende en forespÞrsel, leser ikke databasen forespÞrslene mens den prÞver Ä sende oss svar. Resultatet er at klienten venter pÄ databasen til den leser forespÞrselen, mens databasen venter pÄ klienten til han leser svaret.
Derfor er klienten tvunget til Ă„ sende pakker for synkronisering med jevne mellomrom. UnĂždvendige nettverksinteraksjoner, unĂždvendig tap av tid.
Og jo flere vi legger til, desto verre blir det. Driveren er ganske pessimistisk og legger dem til ganske ofte, omtrent hver 200. linje, avhengig av linjestĂžrrelsen, osv.
Noen ganger kan du justere bare én linje og fÄ ti ganger raskere ytelse. Dette skjer. Hvorfor? Som vanlig, var en slik konstant allerede brukt. Og verdien '128' betydde - ikke bruke batching.
Det er bra at dette ikke kom med i den offisielle versjonen. Vi oppdaget det fÞr vi begynte Ä utgi en ny versjon. Alle verdier jeg nevner, er basert pÄ moderne versjoner.
La oss mÄle. Vi mÄler enkel InsertBatch. Vi mÄler flere InsertBatch, det vil si det samme, men med mange values. En smart lÞsning. Ikke alle vet hvordan dette gjÞres, men det er en enkel lÞsning, mye enklere enn COPY.
Man kan bruke COPY.
Og dette kan gjĂžres med strukturer. Deklarer User default type, send inn et array og INSERT direkte i tabellen.
Hvis du Äpner linken: pgjdbc/ubenchmsrk/InsertBatch.java, sÄ finnes denne koden pÄ GitHub. Du kan se spesifikt hvilke forespÞrslser som genereres der. Det er ikke viktig.
Vi har startet. Og det fĂžrste vi forstod er at uten batch â det er rett og slett umulig. Alle batch-alternativer er lik null, dvs. kjĂžretiden er praktisk talt lik null sammenlignet med en enkelt utfĂžrelse.
Vi setter inn data. Det er en ganske enkel tabell. Tre kolonner. Og hva ser vi her? Vi ser at alle disse tre alternativene er omtrent sammenlignbare. Og COPY er selvfĂžlgelig best.
Dette er nÄr vi setter inn i biter. NÄr vi sa ett VALUES-verdi, to VALUES-verdier, tre VALUES-verdier, eller vi oppga 10 med komma. Dette er akkurat nÄ horisontalt. 1, 2, 4, 128. Det er tydelig at Batch Insert, som er tegnet med blÄtt, gjÞr det mye lettere. Det vil si, nÄr du setter inn én om gangen eller til og med nÄr du setter inn fire, sÄ blir det dobbelt sÄ bra, bare fordi vi klarte Ä stappe litt mer inn i VALUES. FÊrre EXECUTE-operasjoner.
à bruke COPY pÄ smÄ volum er ekstremt lite lovende. Jeg tegnet ikke engang de fÞrste to. De gÄr i skyene, dvs. disse grÞnne tallene for COPY.
COPY bÞr brukes nÄr du har datamengder som minimum er stÞrre enn hundre rader. Overhead ved Ä Äpne denne forbindelsen er stor. Og, Êrlig talt, jeg har ikke gravd i denne retningen. Jeg optimaliserte batchen, men COPY gjorde jeg ikke.
Hva gjÞr vi videre? Vi har mÄlt. Vi forstÄr at vi mÄ bruke enten strukturer eller det smart batching som kombinerer flere verdier.
Hva mÄ vi ta med oss fra dagens foredrag?
- PreparedStatement â det er vĂ„rt alt. Det gir oss mye for ytelsen. Det gir oss en stor krukke med tjĂŠre.
- Og vi mÄ gjÞre EXPLAIN ANALYZE 6 ganger.
- Og vi mÄ blande inn OFFSET 0, og triks som +0 for Ä rette opp den gjenvÊrende prosentandelen av vÄre problematiske spÞrringer.
Kilde: habr.com
Russian
Italian
German
Polish
Estonian
Albanian
Bulgarian
Croatian
Czech
Danish
Dutch
English
French
Hungarian
Latvian
Norwegian
Portuguese
Romanian
Slovak
Slovenian
Spanish
Swedish