Jag föreslår att du läser utskriften av Vladimir Sitnikovs rapport från början av 2016 "PostgreSQL och JDBC pressar ut all juice"
God eftermiddag Jag heter Vladimir Sitnikov. Jag har arbetat för NetCracker i 10 år. Och jag ägnar mig mest åt produktivitet. Allt relaterat till Java, allt relaterat till SQL är det jag älskar.
Och idag ska jag prata om vad vi stötte på i företaget när vi började använda PostgreSQL som databasserver. Och vi jobbar mest med Java. Men det jag ska berätta idag handlar inte bara om Java. Som praxis har visat förekommer detta även på andra språk.
Vi ska prata:
- om datasampling.
- Om att spara data.
- Och även om prestanda.
- Och om undervattenskrattor som ligger begravda där.
Låt oss börja med en enkel fråga. Vi väljer en rad från tabellen baserat på primärnyckeln.
Databasen finns på samma värd. Och allt detta jordbruk tar 20 millisekunder.
Dessa 20 millisekunder är mycket. Om du har 100 sådana förfrågningar, spenderar du tid per sekund på att scrolla igenom dessa förfrågningar, det vill säga vi slösar tid.
Vi gillar inte att göra det här och tittar på vad basen erbjuder oss för detta. Databasen erbjuder oss två alternativ för att utföra frågor.
Det första alternativet är en enkel begäran. Vad är bra med det? Det faktum att vi tar det och skickar det, och inget mer.
Databasen har också en avancerad fråga, som är mer knepig, men mer funktionell. Du kan separat skicka en begäran om analys, exekvering, variabelbindning, etc.
Superförlängd fråga är något som vi inte kommer att täcka i den aktuella rapporten. Vi kanske vill ha något från databasen och det finns en önskelista som har formats i någon form, det vill säga det är vad vi vill, men det är omöjligt nu och under nästa år. Så vi har precis spelat in det och vi ska gå runt och skaka huvudpersonerna.
Och vad vi kan göra är enkel fråga och utökad fråga.
Vad är speciellt med varje tillvägagångssätt?
En enkel fråga är bra för engångsutförande. En gång gjort och glömd. Och problemet är att det inte stöder det binära dataformatet, dvs det är inte lämpligt för vissa högpresterande system.
Utökad fråga – låter dig spara tid vid analys. Detta är vad vi gjorde och började använda. Det här hjälpte oss verkligen. Det finns inte bara besparingar på att analysera. Det finns besparingar på dataöverföring. Att överföra data i binärt format är mycket effektivare.
Låt oss gå vidare till praktiken. Så här ser en typisk applikation ut. Det kan vara Java osv.
Vi skapade ett uttalande. Utförde kommandot. Skapat nära. Var är felet här? Vad är problemet? Inga problem. Så står det i alla böckerna. Så här ska det skrivas. Om du vill ha maximal prestanda, skriv så här.
Men praxis har visat att detta inte fungerar. Varför? För vi har en "nära" metod. Och när vi gör detta, ur databassynpunkt visar det sig att det är som en rökare som arbetar med en databas. Vi sa "PARSE UTFÖR DEALLOCATE".
Varför allt detta extra skapande och avlastning av uttalanden? Ingen behöver dem. Men det som vanligtvis händer i PreparedStatements är att när vi stänger dem stänger de allt på databasen. Det här är inte vad vi vill.
Vi vill, som friska människor, arbeta med basen. Vi tog och förberedde vårt uttalande en gång, sedan utför vi det många gånger. Faktum är att många gånger - det här är en gång i hela ansökningarnas liv - har de analyserats. Och vi använder samma sats-id på olika REST. Detta är vårt mål.
Hur kan vi uppnå detta?
Det är väldigt enkelt - du behöver inte avsluta uttalanden. Vi skriver det så här: "förbereda" "köra".
Om vi lanserar något sånt här är det klart att något kommer att svämma över någonstans. Om det inte är tydligt kan du prova det. Låt oss skriva ett riktmärke som använder denna enkla metod. Skapa ett uttalande. Vi startar den på någon version av drivrutinen och upptäcker att den kraschar ganska snabbt med förlust av allt minne som den hade.
Det är tydligt att sådana fel lätt kan korrigeras. Jag ska inte prata om dem. Men jag kommer att säga att den nya versionen fungerar mycket snabbare. Metoden är dum, men ändå.
Hur fungerar man korrekt? Vad behöver vi göra för detta?
I verkligheten stänger ansökningar alltid uttalanden. I alla böcker säger man att man ska stänga den, annars läcker minnet.
Och PostgreSQL vet inte hur man cachelagrar frågor. Det är nödvändigt att varje session skapar denna cache för sig själv.
Och vi vill inte slösa tid på att analysera heller.
Och som vanligt har vi två alternativ.
Det första alternativet är att vi tar det och säger att låt oss slå in allt i PgSQL. Det finns en cache där. Den cachar allt. Det kommer att bli jättebra. Vi såg det här. Vi har 100500 XNUMX förfrågningar. Fungerar inte. Vi går inte med på att förvandla förfrågningar till procedurer manuellt. Nej nej.
Vi har ett andra alternativ - ta det och skär det själva. Vi öppnar källorna och börjar klippa. Vi såg och såg. Det visade sig att det inte är så svårt att göra.
Detta dök upp i augusti 2015. Nu finns det en modernare version. Och allt är jättebra. Det fungerar så bra att vi inte ändrar något i ansökan. Och vi slutade till och med tänka i PgSQL-riktningen, det vill säga detta räckte för oss att minska alla omkostnader till nästan noll.
Följaktligen aktiveras serverförberedda uttalanden vid den 5:e körningen för att undvika att slösa minne i databasen vid varje engångsförfrågan.
Man kan fråga sig – var är siffrorna? Vad får du? Och här kommer jag inte att ge siffror, eftersom varje begäran har sin egen.
Våra frågor var sådana att vi spenderade cirka 20 millisekunder på att analysera OLTP-frågor. Det var 0,5 millisekunder för exekvering, 20 millisekunder för att analysera. Begäran – 10 KiB text, 170 rader plan. Detta är en OLTP-förfrågan. Den kräver 1, 5, 10 rader, ibland mer.
Men vi ville inte slösa bort 20 millisekunder alls. Vi minskade den till 0. Allt är bra.
Vad kan du ta med dig härifrån? Om du har Java, så tar du den moderna versionen av drivrutinen och gläds.
Om du talar ett annat språk, tänk då - kanske du behöver detta också? För ur det slutliga språkets synvinkel, till exempel om PL 8 eller du har LibPQ, så är det inte uppenbart för dig att du spenderar tid inte på exekvering, på att analysera, och detta är värt att kontrollera. Hur? Allt är gratis.
Förutom att det finns fel och en del egenheter. Och vi ska prata om dem just nu. Det mesta kommer att handla om industriell arkeologi, om vad vi hittade, vad vi stötte på.
Om begäran genereras dynamiskt. Det händer. Någon limmar ihop strängarna, vilket resulterar i en SQL-fråga.
Varför är han dålig? Det är dåligt eftersom vi varje gång får en annan sträng.
Och hashkoden för denna olika sträng måste läsas igen. Det här är verkligen en CPU-uppgift - att hitta en lång begärantext även i en befintlig hash är inte så lätt. Därför är slutsatsen enkel - generera inte förfrågningar. Lagra dem i en variabel. Och glädjas.
Nästa problem. Datatyper är viktiga. Det finns ORMs som säger att det inte spelar någon roll vilken typ av NULL det finns, låt det finnas någon sort. Om Int, då säger vi setInt. Och om NULL, låt det alltid vara VARCHAR. Och vilken skillnad gör det i slutändan vilken NULL som finns? Databasen själv kommer att förstå allt. Och den här bilden fungerar inte.
I praktiken bryr sig databasen inte alls. Om du första gången sa att detta är ett nummer, och andra gången du sa att det är en VARCHAR, så är det omöjligt att återanvända serverförberedda uttalanden. Och i det här fallet måste vi återskapa vårt uttalande.
Om du kör samma fråga, se till att datatyperna i din kolumn inte förväxlas. Du måste se upp för NULL. Detta är ett vanligt fel som vi hade efter att vi började använda PreparedStatements
Okej, påslagen. Kanske tog de föraren. Och produktiviteten sjönk. Saker och ting blev dåliga.
Hur går det till? Är detta en bugg eller en funktion? Tyvärr gick det inte att förstå om detta är en bugg eller en funktion. Men det finns ett mycket enkelt scenario för att reproducera detta problem. Hon överföll oss helt oväntat. Och det består av provtagning bokstavligen från en tabell. Vi hade naturligtvis fler sådana önskemål. Som regel inkluderade de två eller tre bord, men det finns ett sådant uppspelningsscenario. Ta valfri version från din databas och spela den.
Poängen är att vi har två kolumner som var och en är indexerad. Det finns en miljon rader i en NULL-kolumn. Och den andra kolumnen innehåller bara 20 rader. När vi kör utan bundna variabler fungerar allt bra.
Om vi börjar köra med bundna variabler, dvs vi kör "?" eller "$1" för vår begäran, vad får vi till slut?
Det första utförandet är som förväntat. Den andra är lite snabbare. Något cacheades. Tredje, fjärde, femte. Sen pang - och något sånt. Och det värsta är att detta händer vid den sjätte avrättningen. Vem visste att det var nödvändigt att göra exakt sex avrättningar för att förstå vad den faktiska avrättningsplanen var?
Vem är skyldig? Vad hände? Databasen innehåller optimering. Och det verkar vara optimerat för det generiska fallet. Och följaktligen, från och med någon gång, byter hon till en generisk plan, som tyvärr kan visa sig vara annorlunda. Det kan visa sig vara detsamma, eller så kan det vara annorlunda. Och det finns något slags tröskelvärde som leder till detta beteende.
Vad kan du göra åt det? Här är det förstås svårare att anta något. Det finns en enkel lösning som vi använder. Detta är +0, OFFSET 0. Du känner säkert till sådana lösningar. Vi tar bara det och lägger till "+0" till begäran och allt är bra. Jag ska visa dig senare.
Och det finns ett annat alternativ - titta på planerna mer noggrant. Utvecklaren måste inte bara skriva en förfrågan, utan också säga "förklara analysera" 6 gånger. Om det är 5 så fungerar det inte.
Och det finns ett tredje alternativ - skriv ett brev till pgsql-hackers. Jag skrev, men det är ännu inte klart om detta är en bugg eller en funktion.
Medan vi funderar på om detta är en bugg eller en funktion, låt oss fixa det. Låt oss ta vår begäran och lägga till "+0". Allt är bra. Två symboler och du behöver inte ens tänka på hur det är eller vad det är. Väldigt enkelt. Vi förbjöd helt enkelt databasen att använda ett index i denna kolumn. Vi har inget index i kolumnen "+0" och det är det, databasen använder inte indexet, allt är bra.
Detta är regeln för 6 förklara. Nu i nuvarande versioner måste du göra det 6 gånger om du har bundna variabler. Om du inte har bundna variabler är det detta vi gör. Och i slutändan är det just denna begäran som misslyckas. Det är ingen knepig sak.
Det verkar, hur mycket är möjligt? En bugg här, en bugg där. Egentligen finns buggen överallt.
Låt oss ta en närmare titt. Vi har till exempel två scheman. Schema A med tabell S och diagram B med tabell S. Fråga – välj data från en tabell. Vad kommer vi att ha i det här fallet? Vi kommer att ha ett fel. Vi kommer att ha allt ovanstående. Regeln är - en bugg finns överallt, vi kommer att ha allt ovanstående.
Nu är frågan: "Varför?" Det verkar som att det finns dokumentation om att om vi har ett schema så finns det en "search_path"-variabel som talar om för oss var vi ska leta efter tabellen. Det verkar som om det finns en variabel.
Vad är problemet? Problemet är att serverförberedda uttalanden inte misstänker att sökväg kan ändras av någon. Detta värde förblir så att säga konstant för databasen. Och vissa delar kanske inte får nya betydelser.
Detta beror naturligtvis på vilken version du testar på. Beror på hur allvarligt dina tabeller skiljer sig åt. Och version 9.1 kommer helt enkelt att exekvera de gamla förfrågningarna. Nya versioner kan fånga buggen och berätta att du har en bugg.
Hur ska man behandla det? Det finns ett enkelt recept - gör det inte. Det finns ingen anledning att ändra sökväg medan programmet körs. Om du ändrar dig är det bättre att skapa en ny anslutning.
Du kan diskutera, d.v.s. öppna, diskutera, lägga till. Kanske kan vi övertyga databasutvecklarna om att när någon ändrar ett värde, bör databasen berätta för klienten om detta: ”Titta, ditt värde har uppdaterats här. Kanske måste du återställa påståendena och återskapa dem?” Nu beter sig databasen i hemlighet och rapporterar inte på något sätt att uttalandena har ändrats någonstans där inne.
Och jag kommer att betona igen - detta är något som inte är typiskt för Java. Vi kommer att se samma sak i PL/pgSQL en till en. Men det kommer att återges där.
Låt oss prova lite mer dataurval. Vi väljer och väljer. Vi har en tabell med en miljon rader. Varje rad är en kilobyte. Ungefär en gigabyte data. Och vi har ett arbetsminne i Java-maskinen på 128 megabyte.
Vi, som rekommenderas i alla böcker, använder strömbehandling. Det vill säga, vi öppnar resultSet och läser data därifrån lite i taget. Kommer det att fungera? Kommer det att falla ur minnet? Kommer du läsa lite? Låt oss lita på databasen, låt oss lita på Postgres. Vi tror inte på det. Kommer vi att falla ur minnet? Vem upplevde OutOfMemory? Vem lyckades fixa det efter det? Någon lyckades fixa det.
Om du har en miljon rader kan du inte bara välja och vraka. OFFSET/LIMIT krävs. Vem är för detta alternativ? Och vem är för att spela med autoCommit?
Här visar sig som vanligt det mest oväntade alternativet vara korrekt. Och om du plötsligt stänger av autoCommit så hjälper det. Varför är det så? Vetenskapen vet inte om detta.
Men som standard hämtar alla klienter som ansluter till en Postgres-databas hela data. PgJDBC är inget undantag i detta avseende, den väljer alla rader.
Det finns en variant på temat FetchSize, det vill säga du kan säga på nivån för ett separat uttalande att här, välj data med 10, 50. Men detta fungerar inte förrän du stänger av autoCommit. Stängt av autoCommit - det börjar fungera.
Men att gå igenom koden och ställa in setFetchSize överallt är obekvämt. Därför gjorde vi en inställning som kommer att säga standardvärdet för hela anslutningen.
Det var vad vi sa. Parametern har konfigurerats. Och vad fick vi? Om vi väljer små belopp, om vi till exempel väljer 10 rader åt gången, så har vi väldigt stora omkostnader. Därför bör detta värde sättas till cirka hundra.
Helst måste du naturligtvis fortfarande lära dig att begränsa det i byte, men receptet är detta: ställ in defaultRowFetchSize till mer än hundra och var nöjd.
Låt oss gå vidare till att infoga data. Insättning är lättare, det finns olika alternativ. Till exempel, INSERT, VALUES. Detta är ett bra alternativ. Du kan säga "INSERT SELECT". I praktiken är det samma sak. Det är ingen skillnad i prestanda.
Böcker säger att du måste köra en Batch-sats, böcker säger att du kan utföra mer komplexa kommandon med flera parenteser. Och Postgres har en underbar funktion - du kan göra COPY, dvs göra det snabbare.
Om du mäter det kan du återigen göra några intressanta upptäckter. Hur vill vi att det här ska fungera? Vi vill inte analysera och inte köra onödiga kommandon.
I praktiken tillåter inte TCP oss att göra detta. Om klienten är upptagen med att skicka en förfrågan läser databasen inte förfrågningarna i försök att skicka svar till oss. Slutresultatet är att klienten väntar på att databasen ska läsa begäran, och databasen väntar på att klienten ska läsa svaret.
Och därför tvingas klienten att regelbundet skicka ett synkroniseringspaket. Extra nätverksinteraktioner, extra slöseri med tid.
Och ju fler vi lägger till dem, desto värre blir det. Föraren är ganska pessimistisk och lägger till dem ganska ofta, ungefär en gång var 200:e rad, beroende på storleken på raderna osv.
Det händer att du korrigerar bara en rad och allt kommer att snabba upp 10 gånger. Det händer. Varför? Som vanligt har en sådan här konstant redan använts någonstans. Och värdet "128" menade att inte använda batchning.
Det är bra att detta inte fanns med i den officiella versionen. Upptäcktes innan releasen började. Alla betydelser jag ger är baserade på moderna versioner.
Låt oss prova det. Vi mäter InsertBatch enkelt. Vi mäter InsertBatch flera gånger, det vill säga samma sak, men det finns många värden. Knepigt drag. Alla kan inte göra detta, men det är ett så enkelt drag, mycket enklare än COPY.
Du kan göra COPY.
Och du kan göra detta på strukturer. Deklarera användarens standardtyp, skicka array och INSERT direkt till tabellen.
Om du öppnar länken: pgjdbc/ubenchmsrk/InsertBatch.java, så finns den här koden på GitHub. Du kan se specifikt vilka förfrågningar som genereras där. Det spelar ingen roll.
Vi lanserade. Och det första vi insåg var att det helt enkelt är omöjligt att använda batch. Alla batchningsalternativ är noll, det vill säga exekveringstiden är praktiskt taget noll jämfört med en engångsexekvering.
Vi lägger in data. Det är ett väldigt enkelt bord. Tre kolumner. Och vad ser vi här? Vi ser att alla dessa tre alternativ är ungefär jämförbara. Och COPY är förstås bättre.
Det är då vi sätter in bitar. När vi sa att ett VALUES-värde, två VALUES-värden, tre VALUES-värden, eller så angav vi 10 av dem åtskilda med kommatecken. Det här är bara horisontellt nu. 1, 2, 4, 128. Det kan ses att Batch Insert, som är ritat i blått, får honom att må mycket bättre. Dvs när man sätter in en i taget eller till och med när man sätter in fyra åt gången så blir det dubbelt så bra, helt enkelt för att vi proppade lite mer i VÄRDEN. Färre UTFÖR operationer.
Att använda COPY på små volymer är extremt föga lovande. 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 minst hundra rader med data. Omkostnaderna för att öppna denna anslutning är stora. Och, för att vara ärlig, grävde jag inte i den här riktningen. Jag optimerade Batch, men inte COPY.
Vad gör vi härnäst? Vi provade den. Vi förstår att vi måste använda antingen strukturer eller en smart metod som kombinerar flera betydelser.
Vad ska du ta med dig från dagens rapport?
- PreparedStatement är vårt allt. Detta ger mycket för produktiviteten. Det ger en stor flopp i salvan.
- Och du måste göra EXPLAIN ANALYSE 6 gånger.
- Och vi måste späda OFFSET 0, och tricks som +0 för att korrigera den återstående andelen av våra problematiska frågor.
Källa: will.com