Jeg foreslår at du leser utskriften av Vladimir Sitnikovs tidlige 2016-rapport "PostgreSQL og JDBC presser ut all saften"
God ettermiddag Mitt navn er Vladimir Sitnikov. Jeg har jobbet for NetCracker i 10 år. Og jeg er mest opptatt av produktivitet. Alt relatert til Java, alt relatert til SQL er det jeg elsker.
Og i dag skal jeg snakke om hva vi møtte i selskapet da vi begynte å bruke PostgreSQL som databaseserver. Og vi jobber mest med Java. Men det jeg skal fortelle deg i dag handler ikke bare om Java. Som praksis har vist, skjer dette også på andre språk.
Vi vil snakke:
- om dataprøvetaking.
- Om å lagre data.
- Og også om ytelse.
- Og om undervannsrivene som er gravlagt der.
La oss starte med et enkelt spørsmål. Vi velger én rad fra tabellen basert på primærnøkkelen.
Databasen ligger på samme vert. Og alt dette jordbruket tar 20 millisekunder.
Disse 20 millisekundene er mye. Hvis du har 100 slike forespørsler, bruker du tid per sekund på å bla gjennom disse forespørslene, det vil si at vi kaster bort tid.
Vi liker ikke å gjøre dette og ser på hva basen tilbyr oss for dette. Databasen tilbyr oss to alternativer for å utføre spørringer.
Det første alternativet er en enkel forespørsel. Hva er bra med det? Det at vi tar det og sender det, og ikke noe mer.
Databasen har også en avansert spørring, som er mer vanskelig, men mer funksjonell. Du kan separat sende en forespørsel om parsing, utførelse, variabel binding osv.
Superutvidet søk er noe vi ikke vil dekke i denne rapporten. Vi vil kanskje ha noe fra databasen og det er en ønskeliste som har blitt dannet i en eller annen form, dvs. dette er hva vi ønsker, men det er umulig nå og i neste år. Så vi har nettopp spilt det inn og vi skal gå rundt og riste hovedpersonene.
Og det vi kan gjøre er enkel spørring og utvidet spørring.
Hva er spesielt med hver tilnærming?
En enkel spørring er bra for engangsutførelse. En gang gjort og glemt. Og problemet er at det ikke støtter det binære dataformatet, det vil si at det ikke er egnet for noen høyytelsessystemer.
Utvidet spørring – lar deg spare tid på parsing. Dette er hva vi gjorde og begynte å bruke. Dette hjalp oss virkelig. Det er ikke bare besparelser på parsing. Det er besparelser på dataoverføring. Overføring av data i binært format er mye mer effektivt.
La oss gå videre til praksis. Slik ser en typisk applikasjon ut. Det kan være Java osv.
Vi laget uttalelse. Utførte kommandoen. Laget tett. Hvor er feilen her? Hva er problemet? Ikke noe problem. Dette er hva det står i alle bøkene. Slik skal det skrives. Hvis du vil ha maksimal ytelse, skriv slik.
Men praksis har vist at dette ikke fungerer. Hvorfor? Fordi vi har en "nær" metode. Og når vi gjør dette, fra databasens synspunkt viser det seg at det er som en røyker som jobber med en database. Vi sa "PARSE EXECUTE DEALLOCATE".
Hvorfor all denne ekstra opprettelsen og avlastningen av uttalelser? Ingen trenger dem. Men det som vanligvis skjer i PreparedStatements er at når vi lukker dem, lukker de alt på databasen. Det er ikke dette vi ønsker.
Vi ønsker, som friske mennesker, å jobbe med basen. Vi tok og forberedte uttalelsen en gang, så utfører vi den mange ganger. Faktisk har de blitt analysert mange ganger - dette er en gang i hele programmets levetid. Og vi bruker samme setnings-ID på forskjellige REST-er. Dette er målet vårt.
Hvordan kan vi oppnå dette?
Det er veldig enkelt - ingen grunn til å lukke uttalelser. Vi skriver det slik: "forbered" "utfør".
Hvis vi lanserer noe slikt, så er det klart at noe vil flyte over et sted. Hvis det ikke er klart, kan du prøve det. La oss skrive en benchmark som bruker denne enkle metoden. Lag en uttalelse. Vi starter den på en eller annen versjon av driveren og finner ut at den krasjer ganske raskt med tap av alt minnet den hadde.
Det er tydelig at slike feil lett kan rettes opp. Jeg vil ikke snakke om dem. Men jeg vil si at den nye versjonen fungerer mye raskere. Metoden er dum, men likevel.
Hvordan jobbe riktig? Hva må vi gjøre for dette?
I virkeligheten lukker søknader alltid uttalelser. I alle bøker sier de å lukke den, ellers vil minnet lekke.
Og PostgreSQL vet ikke hvordan man hurtigbufrer spørringer. Det er nødvendig at hver økt lager denne cachen for seg selv.
Og vi ønsker heller ikke å kaste bort tid på å analysere.
Og som vanlig har vi to alternativer.
Det første alternativet er at vi tar det og sier at la oss pakke alt inn i PgSQL. Det er en cache der. Den cacher alt. Det blir kjempebra. Vi så dette. Vi har 100500 XNUMX forespørsler. Virker ikke. Vi godtar ikke å gjøre forespørsler om til prosedyrer manuelt. Nei nei.
Vi har et annet alternativ - ta det og klipp det selv. Vi åpner kildene og begynner å kutte. Vi så og så. Det viste seg at det ikke er så vanskelig å gjøre.
Dette dukket opp i august 2015. Nå er det en mer moderne versjon. Og alt er flott. Det fungerer så bra at vi ikke endrer noe i søknaden. Og vi sluttet til og med å tenke i retning av PgSQL, det vil si at dette var ganske nok til at vi kunne redusere alle overheadkostnader til nesten null.
Følgelig aktiveres serverforberedte setninger ved den 5. utførelsen for å unngå å sløse med minne i databasen på hver engangsforespørsel.
Du kan spørre – hvor er tallene? Hva får du? Og her vil jeg ikke gi tall, fordi hver forespørsel har sin egen.
Spørringene våre var slik at vi brukte omtrent 20 millisekunder på å analysere OLTP-spørringer. Det var 0,5 millisekunder for utførelse, 20 millisekunder for parsing. Forespørsel – 10 KiB tekst, 170 linjer med plan. Dette er en OLTP-forespørsel. Den ber om 1, 5, 10 linjer, noen ganger mer.
Men vi ønsket ikke å kaste bort 20 millisekunder i det hele tatt. Vi reduserte til 0. Alt er flott.
Hva kan du ta med deg herfra? Hvis du har Java, så tar du den moderne versjonen av driveren og gleder deg.
Hvis du snakker et annet språk, så tenk – kanskje du trenger dette også? Fordi fra det endelige språkets synspunkt, for eksempel, hvis PL 8 eller du har LibPQ, så er det ikke åpenbart for deg at du bruker tid ikke på utførelse, på parsing, og dette er verdt å sjekke. Hvordan? Alt er gratis.
Bortsett fra at det er feil og noen særegenheter. Og vi skal snakke om dem akkurat nå. Det meste vil handle om industriell arkeologi, om hva vi fant, hva vi kom over.
Hvis forespørselen genereres dynamisk. Det skjer. Noen limer strengene sammen, noe som resulterer i en SQL-spørring.
Hvorfor er han dårlig? Det er ille fordi hver gang vi ender opp med en annen streng.
Og hashkoden til denne forskjellige strengen må leses på nytt. Dette er egentlig en CPU-oppgave - å finne en lang forespørselstekst i selv en eksisterende hash er ikke så lett. Derfor er konklusjonen enkel - ikke generer forespørsler. Lagre dem i én variabel. Og glede deg.
Neste problem. Datatyper er viktige. Det er ORMer som sier at det ikke spiller noen rolle hva slags NULL det er, la det være en slags. Hvis Int, så sier vi setInt. Og hvis NULL, så la det alltid være VARCHAR. Og hvilken forskjell gjør det til slutt hvilken NULL som er det? Databasen selv vil forstå alt. Og dette bildet fungerer ikke.
I praksis bryr ikke databasen seg i det hele tatt. Hvis du sa første gang at dette er et tall, og andre gang du sa at det er en VARCHAR, så er det umulig å gjenbruke Server-forberedte uttalelser. Og i dette tilfellet må vi gjenskape uttalelsen vår.
Hvis du kjører den samme spørringen, sørg for at datatypene i kolonnen ikke forveksles. Du må passe på NULL. Dette er en vanlig feil vi hadde etter at vi begynte å bruke PreparedStatements
Ok, slått på. Kanskje de tok sjåføren. Og produktiviteten falt. Ting ble dårlig.
Hvordan skjer dette? Er dette en feil eller en funksjon? Dessverre var det ikke mulig å forstå om dette er en feil eller en funksjon. Men det er et veldig enkelt scenario for å reprodusere dette problemet. Hun overfalt oss helt uventet. Og det består av prøvetaking bokstavelig talt fra en tabell. Vi hadde selvfølgelig flere slike forespørsler. Som regel inkluderte de to eller tre bord, men det er et slikt avspillingsscenario. Ta en hvilken som helst versjon fra databasen og spill den.
Poenget er at vi har to kolonner som hver er indeksert. Det er en million rader i én NULL-kolonne. Og den andre kolonnen inneholder bare 20 linjer. Når vi kjører uten bundne variabler, fungerer alt bra.
Hvis vi begynner å kjøre med bundne variabler, det vil si at vi kjører "?" eller "$1" for vår forespørsel, hva ender vi opp med å få?
Den første utførelsen er som forventet. Den andre er litt raskere. Noe ble bufret. Tredje, fjerde, femte. Så pang - og noe sånt. Og det verste er at dette skjer ved den sjette henrettelsen. Hvem visste at det var nødvendig å gjøre nøyaktig seks henrettelser for å forstå hva den faktiske henrettelsesplanen var?
Hvem er skyldig? Hva skjedde? Databasen inneholder optimalisering. Og det ser ut til å være optimalisert for det generiske tilfellet. Og følgelig, fra og med på et tidspunkt, bytter hun til en generisk plan, som dessverre kan vise seg å være annerledes. Det kan vise seg å være det samme, eller det kan være annerledes. Og det er en slags terskelverdi som fører til denne oppførselen.
Hva kan du gjøre med det? Her er det selvsagt vanskeligere å anta noe. Det er en enkel løsning vi bruker. Dette er +0, OFFSET 0. Du kjenner sikkert til slike løsninger. Vi bare tar det og legger til "+0" i forespørselen og alt er i orden. Jeg skal vise deg senere.
Og det er et annet alternativ - se på planene mer nøye. Utvikleren må ikke bare skrive en forespørsel, men også si «forklar analyser» 6 ganger. Hvis det er 5, vil det ikke fungere.
Og det er et tredje alternativ - skriv et brev til pgsql-hackere. Jeg skrev, men det er ennå ikke klart om dette er en feil eller en funksjon.
Mens vi tenker på om dette er en feil eller en funksjon, la oss fikse det. La oss ta forespørselen vår og legge til "+0". Alt er bra. To symboler og du trenger ikke engang tenke på hvordan det er eller hva det er. Veldig enkelt. Vi forbød ganske enkelt databasen å bruke en indeks på denne kolonnen. Vi har ikke en indeks på "+0"-kolonnen, og det er det, databasen bruker ikke indeksen, alt er i orden.
Dette er regelen med 6 forklar. Nå i gjeldende versjoner må du gjøre det 6 ganger hvis du har bundne variabler. Hvis du ikke har bundne variabler, er det dette vi gjør. Og til syvende og sist er det nettopp denne forespørselen som mislykkes. Det er ikke en vanskelig ting.
Det ser ut til, hvor mye er mulig? En feil her, en feil der. Faktisk er feilen overalt.
La oss ta en nærmere titt. For eksempel har vi to skjemaer. Skjema A med tabell S og diagram B med tabell S. Spørring – velg data fra en tabell. Hva vil vi ha i dette tilfellet? Vi vil ha en feil. Vi vil ha alt det ovennevnte. Regelen er - en feil er overalt, vi vil ha alt det ovennevnte.
Nå er spørsmålet: "Hvorfor?" Det ser ut til at det er dokumentasjon på at hvis vi har et skjema, så er det en "search_path"-variabel som forteller oss hvor vi skal lete etter tabellen. Det ser ut til at det er en variabel.
Hva er problemet? Problemet er at serverforberedte uttalelser ikke mistenker at search_path kan endres av noen. Denne verdien forblir så å si konstant for databasen. Og enkelte deler får kanskje ikke nye betydninger.
Dette avhenger selvfølgelig av versjonen du tester på. Avhenger av hvor alvorlig tabellene dine er forskjellige. Og versjon 9.1 vil ganske enkelt utføre de gamle spørringene. Nye versjoner kan fange feilen og fortelle deg at du har en feil.
Hvordan behandle det? Det er en enkel oppskrift - ikke gjør det. Det er ikke nødvendig å endre search_path mens applikasjonen kjører. Hvis du endrer, er det bedre å opprette en ny tilkobling.
Du kan diskutere, dvs. åpne, diskutere, legge til. Kanskje vi kan overbevise databaseutviklerne om at når noen endrer en verdi, bør databasen fortelle klienten om dette: «Se, verdien din har blitt oppdatert her. Kanskje du må tilbakestille utsagnene og gjenskape dem?» Nå oppfører databasen seg hemmelig og rapporterer ikke på noen måte at utsagnene har endret seg et sted inne.
Og jeg vil understreke igjen - dette er noe som ikke er typisk for Java. Vi vil se det samme i PL/pgSQL en til en. Men det vil bli gjengitt der.
La oss prøve litt mer datavalg. Vi velger og velger. Vi har en tabell med en million rader. Hver linje er en kilobyte. Omtrent en gigabyte med data. Og vi har et arbeidsminne i Java-maskinen på 128 megabyte.
Vi, som anbefalt i alle bøker, bruker strømbehandling. Det vil si at vi åpner resultSet og leser dataene derfra litt etter litt. Vil det fungere? Vil det falle fra hukommelsen? Vil du lese litt? La oss stole på databasen, la oss stole på Postgres. Vi tror det ikke. Vil vi falle ut av minnet? Hvem opplevde OutOfMemory? Hvem klarte å fikse det etter det? Noen klarte å fikse det.
Hvis du har en million rader, kan du ikke bare velge og vrake. OFFSET/LIMIT kreves. Hvem er for dette alternativet? Og hvem er for å spille med autoCommit?
Her, som vanlig, viser det mest uventede alternativet seg å være riktig. Og hvis du plutselig slår av autoCommit, vil det hjelpe. Hvorfor det? Vitenskapen vet ikke om dette.
Men som standard henter alle klienter som kobler til en Postgres-database alle dataene. PgJDBC er intet unntak i denne forbindelse; den velger alle rader.
Det er en variant av FetchSize-temaet, det vil si at du kan si på nivået til en egen setning at her, velg data med 10, 50. Men dette fungerer ikke før du slår av autoCommit. Slått av autoCommit - det begynner å fungere.
Men å gå gjennom koden og sette setFetchSize overalt er upraktisk. Derfor har vi laget en innstilling som vil si standardverdien for hele tilkoblingen.
Det var det vi sa. Parameteren er konfigurert. Og hva fikk vi? Velger vi små beløp, hvis vi for eksempel velger 10 rader om gangen, så har vi veldig store overheadkostnader. Derfor bør denne verdien settes til omtrent hundre.
Ideelt sett må du selvfølgelig fortsatt lære å begrense det i byte, men oppskriften er denne: sett defaultRowFetchSize til mer enn hundre og vær fornøyd.
La oss gå videre til å sette inn data. Innsetting er enklere, det er forskjellige alternativer. For eksempel INSERT, VALUES. Dette er et godt alternativ. Du kan si "INSERT SELECT". I praksis er det det samme. Det er ingen forskjell i ytelse.
Bøker sier at du må utføre en batch-setning, bøker sier at du kan utføre mer komplekse kommandoer med flere parenteser. Og Postgres har en fantastisk funksjon - du kan kopiere, dvs. gjøre det raskere.
Hvis du måler det, kan du igjen gjøre noen interessante funn. Hvordan vil vi at dette skal fungere? Vi ønsker ikke å analysere og ikke utføre unødvendige kommandoer.
I praksis tillater ikke TCP oss å gjøre dette. Hvis klienten er opptatt med å sende en forespørsel, leser ikke databasen forespørslene i forsøk på å sende oss svar. Sluttresultatet er at klienten venter på at databasen skal lese forespørselen, og databasen venter på at klienten leser svaret.
Og derfor er klienten tvunget til å sende en synkroniseringspakke med jevne mellomrom. Ekstra nettverksinteraksjoner, ekstra bortkastet tid.
Og jo flere vi legger dem til, jo verre blir det. Driveren er ganske pessimistisk og legger dem til ganske ofte, omtrent en gang hver 200. linje, avhengig av størrelsen på linjene osv.
Det hender at du korrigerer bare en linje og alt vil øke hastigheten 10 ganger. Det skjer. Hvorfor? Som vanlig er en konstant som denne allerede brukt et sted. Og verdien "128" betydde ikke å bruke batching.
Det er bra at dette ikke var inkludert i den offisielle versjonen. Oppdaget før utgivelsen begynte. Alle betydningene jeg gir er basert på moderne versjoner.
La oss prøve det. Vi måler InsertBatch enkelt. Vi måler InsertBatch flere ganger, det vil si det samme, men det er mange verdier. Vanskelig trekk. Ikke alle kan gjøre dette, men det er et så enkelt trekk, mye enklere enn COPY.
Du kan kopiere.
Og du kan gjøre dette på strukturer. Deklarer brukerens standardtype, pass array og INSERT direkte til tabellen.
Hvis du åpner lenken: pgjdbc/ubenchmsrk/InsertBatch.java, så er denne koden på GitHub. Du kan se spesifikt hvilke forespørsler som genereres der. Det spiller ingen rolle.
Vi lanserte. Og det første vi innså var at det å ikke bruke batch rett og slett er umulig. Alle batchalternativer er null, det vil si at utførelsestiden er praktisk talt null sammenlignet med en engangsutførelse.
Vi legger inn data. Det er et veldig enkelt bord. Tre kolonner. Og hva ser vi her? Vi ser at alle disse tre alternativene er omtrent sammenlignbare. Og COPY er selvfølgelig bedre.
Det er da vi setter inn biter. Da vi sa at én VALUES-verdi, to VALUES-verdier, tre VALUES-verdier, eller vi indikerte 10 av dem atskilt med komma. Dette er bare horisontalt nå. 1, 2, 4, 128. Det kan sees at batch-innlegget, som er tegnet i blått, får ham til å føle seg mye bedre. Det vil si at når du setter inn en om gangen eller til og med når du setter inn fire om gangen, blir det dobbelt så bra, rett og slett fordi vi stappet litt mer inn i VERDIER. Færre UTFØR operasjoner.
Å bruke COPY på små volumer er ekstremt lite lovende. Jeg tegnet ikke engang på de to første. De går til himmelen, altså disse grønne tallene for COPY.
COPY bør brukes når du har minst hundre rader med data. Kostnadene ved å åpne denne forbindelsen er store. Og for å være ærlig, jeg gravde ikke i denne retningen. Jeg optimaliserte Batch, men ikke COPY.
Hva gjør vi videre? Vi prøvde den. Vi forstår at vi må bruke enten strukturer eller en smart batch som kombinerer flere betydninger.
Hva bør du ta med deg fra dagens rapport?
- PreparedStatement er vårt alt. Dette gir mye for produktiviteten. Det gir en stor flopp i salven.
- Og du må gjøre EXPLAIN ANALYSE 6 ganger.
- Og vi må fortynne OFFSET 0, og triks som +0 for å korrigere den gjenværende prosentandelen av våre problematiske spørringer.
Kilde: www.habr.com