Ik stel voor dat u het transcript leest van het rapport van Vladimir Sitnikov uit begin 2016: “PostgreSQL en JDBC persen al het sap eruit”
Goedemiddag Mijn naam is Vladimir Sitnikov. Ik werk al 10 jaar voor NetCracker. En ik houd vooral van productiviteit. Alles wat met Java te maken heeft, alles wat met SQL te maken heeft, is waar ik van hou.
En vandaag zal ik het hebben over wat we tegenkwamen in het bedrijf toen we PostgreSQL als databaseserver gingen gebruiken. En we werken vooral met Java. Maar wat ik je vandaag ga vertellen gaat niet alleen over Java. Zoals de praktijk leert, komt dit ook in andere talen voor.
We zullen praten:
- over gegevensbemonstering.
- Over het opslaan van gegevens.
- En ook over prestaties.
- En over de onderwaterharken die daar begraven liggen.
Laten we beginnen met een simpele vraag. We selecteren één rij uit de tabel op basis van de primaire sleutel.
De database bevindt zich op dezelfde host. En al dit boerenwerk duurt 20 milliseconden.
Deze 20 milliseconden zijn veel. Als u 100 van dergelijke verzoeken heeft, besteedt u tijd per seconde aan het scrollen door deze verzoeken, d.w.z. we verspillen tijd.
Wij doen dit niet graag en kijken wat de basis ons hiervoor biedt. De database biedt ons twee opties voor het uitvoeren van queries.
De eerste optie is een eenvoudig verzoek. Wat is er goed aan? Het feit dat we het nemen en verzenden, en meer niet.
De database heeft ook een geavanceerde query, die lastiger, maar functioneler is. U kunt afzonderlijk een verzoek verzenden voor parsering, uitvoering, variabele binding, enz.
Super uitgebreide zoekopdracht is iets dat we in het huidige rapport niet zullen behandelen. We willen misschien iets uit de database en er is een wensenlijst die in een of andere vorm is gevormd, d.w.z. dit is wat we willen, maar het is nu en volgend jaar onmogelijk. Dus we hebben het zojuist opgenomen en we gaan de belangrijkste mensen schudden.
En wat we kunnen doen is een eenvoudige vraag en een uitgebreide vraag.
Wat is er bijzonder aan elke aanpak?
Een eenvoudige query is goed voor eenmalige uitvoering. Eenmaal gedaan en vergeten. En het probleem is dat het het binaire gegevensformaat niet ondersteunt, dat wil zeggen dat het niet geschikt is voor sommige krachtige systemen.
Uitgebreide zoekopdracht – hiermee kunt u tijd besparen bij het parseren. Dit is wat we deden en gingen gebruiken. Dit heeft ons echt heel erg geholpen. Er zijn niet alleen besparingen op het parseren. Er zijn besparingen op gegevensoverdracht. Het overbrengen van gegevens in binair formaat is veel efficiënter.
Laten we verder gaan met oefenen. Zo ziet een typische toepassing eruit. Het zou Java kunnen zijn, enz.
We hebben een verklaring gemaakt. Voerde de opdracht uit. Dicht gemaakt. Waar zit hier de fout? Wat is het probleem? Geen probleem. Dit staat in alle boeken. Dit is hoe het geschreven moet worden. Als je maximale prestaties wilt, schrijf dan zo.
Maar de praktijk leert dat dit niet werkt. Waarom? Omdat we een "close" -methode hebben. En als we dit doen, blijkt vanuit database-oogpunt dat het net een roker is die met een database werkt. We zeiden "PARSE EXECUTE DEALLOCATE".
Waarom al dit extra aanmaken en lossen van verklaringen? Niemand heeft ze nodig. Maar wat meestal gebeurt in PreparedStatements is dat wanneer we ze sluiten, ze alles in de database sluiten. Dit is niet wat wij willen.
Wij willen, net als gezonde mensen, werken met de basis. We hebben onze verklaring één keer opgesteld en opgesteld, en daarna voeren we deze vele malen uit. Sterker nog, vele malen (dit is één keer in de hele levensduur van applicaties) zijn ze ontleed. En we gebruiken dezelfde instructie-ID op verschillende REST's. Dit is ons doel.
Hoe kunnen we dit bereiken?
Het is heel eenvoudig: het is niet nodig om verklaringen af te sluiten. We schrijven het als volgt: “voorbereiden” “uitvoeren”.
Als we zoiets lanceren, dan is het duidelijk dat er ergens iets zal overstromen. Als het niet duidelijk is, kun je het proberen. Laten we een benchmark schrijven die deze eenvoudige methode gebruikt. Maak een verklaring. We starten het op een bepaalde versie van het stuurprogramma en ontdekken dat het vrij snel crasht, waarbij al het geheugen dat het had verloren gaat.
Het is duidelijk dat dergelijke fouten gemakkelijk kunnen worden gecorrigeerd. Ik zal niet over hen praten. Maar ik zal zeggen dat de nieuwe versie veel sneller werkt. De methode is stom, maar toch.
Hoe correct werken? Wat moeten we hiervoor doen?
In werkelijkheid sluiten applicaties altijd verklaringen af. In alle boeken staat dat je het moet sluiten, anders lekt het geheugen.
En PostgreSQL weet niet hoe query's in de cache moeten worden opgeslagen. Het is noodzakelijk dat elke sessie deze cache voor zichzelf aanmaakt.
En we willen ook geen tijd verspillen aan het parseren.
En zoals gewoonlijk hebben we twee opties.
De eerste optie is dat we het nemen en zeggen: laten we alles in PgSQL verpakken. Er is daar een cache. Het cachet alles. Het zal geweldig blijken. Wij hebben dit gezien. We hebben 100500 verzoeken. Werkt niet. Wij gaan er niet mee akkoord om verzoeken handmatig in procedures om te zetten. Nee nee.
We hebben een tweede optie: neem het en snijd het zelf. We openen de bronnen en beginnen met snijden. Wij zagen en zagen. Het bleek dat het niet zo moeilijk is om te doen.
Dit verscheen in augustus 2015. Nu is er een modernere versie. En alles is geweldig. Het werkt zo goed dat we niets veranderen aan de applicatie. En we zijn zelfs gestopt met denken in de richting van PgSQL, d.w.z. dit was voor ons voldoende om alle overheadkosten tot bijna nul terug te brengen.
Dienovereenkomstig worden door de server voorbereide instructies geactiveerd bij de vijfde uitvoering om te voorkomen dat er geheugen in de database wordt verspild bij elk eenmalig verzoek.
U vraagt zich misschien af: waar zijn de cijfers? Wat krijg je? En hier zal ik geen cijfers geven, omdat elk verzoek zijn eigen verzoek heeft.
Onze query's waren zodanig dat we ongeveer 20 milliseconden besteedden aan het parseren van OLTP-query's. Er was 0,5 milliseconden voor uitvoering en 20 milliseconden voor parseren. Verzoek – 10 KiB tekst, 170 regels plan. Dit is een OLTP-verzoek. Het vraagt om 1, 5, 10 regels, soms meer.
Maar we wilden helemaal geen 20 milliseconden verspillen. Wij hebben dit teruggebracht naar 0. Alles is geweldig.
Wat kun je hieruit meenemen? Als je Java hebt, neem dan de moderne versie van het stuurprogramma en verheug je.
Als je een andere taal spreekt, denk dan: misschien heb je dit ook nodig? Omdat vanuit het oogpunt van de uiteindelijke taal, bijvoorbeeld als PL 8 of je LibPQ hebt, het voor jou niet duidelijk is dat je geen tijd besteedt aan uitvoering, aan parseren, en dit is de moeite waard om te controleren. Hoe? Alles is gratis.
Behalve dat er fouten en enkele eigenaardigheden zijn. En we zullen er nu over praten. Het grootste deel zal gaan over industriële archeologie, over wat we hebben gevonden, wat we zijn tegengekomen.
Als het verzoek dynamisch wordt gegenereerd. Het gebeurt. Iemand lijmt de touwtjes aan elkaar, wat resulteert in een SQL-query.
Waarom is hij slecht? Het is slecht, want elke keer eindigen we met een andere reeks.
En de hashCode van deze andere string moet opnieuw worden gelezen. Dit is echt een CPU-taak - het vinden van een lange verzoektekst in zelfs een bestaande hash is niet zo eenvoudig. Daarom is de conclusie eenvoudig: genereer geen verzoeken. Bewaar ze in één variabele. En verheug je.
Volgende probleem. Gegevenstypen zijn belangrijk. Er zijn ORM's die zeggen dat het niet uitmaakt wat voor soort NULL er is, laat er een soort zijn. Als Int, dan zeggen we setInt. En als NULL is, laat het dan altijd VARCHAR zijn. En wat maakt het uiteindelijk uit wat NULL is? De database zelf zal alles begrijpen. En deze foto werkt niet.
In de praktijk maakt het de database helemaal niets uit. Als u de eerste keer zei dat dit een getal is en de tweede keer dat het een VARCHAR is, dan is het onmogelijk om door de server voorbereide instructies opnieuw te gebruiken. En in dit geval moeten we onze verklaring opnieuw opstellen.
Als u dezelfde query uitvoert, zorg er dan voor dat de gegevenstypen in uw kolom niet met elkaar worden verward. Je moet oppassen voor NULL. Dit is een veel voorkomende fout die we tegenkwamen nadat we PreparedStatements gingen gebruiken
Oké, ingeschakeld. Misschien hebben ze de chauffeur meegenomen. En de productiviteit daalde. Het ging slecht.
Hoe gebeurde dit? Is dit een bug of een feature? Helaas was het niet mogelijk om te begrijpen of dit een bug of een functie is. Maar er is een heel eenvoudig scenario om dit probleem te reproduceren. Ze heeft ons totaal onverwacht in een hinderlaag gelokt. En het bestaat uit het letterlijk bemonsteren van één tafel. Natuurlijk hadden we meer van dergelijke verzoeken. In de regel bevatten ze twee of drie tabellen, maar er is zo'n afspeelscenario. Neem een willekeurige versie uit uw database en speel deze af.
Het punt is dat we twee kolommen hebben, die elk geïndexeerd zijn. Er zijn een miljoen rijen in één NULL-kolom. En de tweede kolom bevat slechts 20 regels. Wanneer we uitvoeren zonder gebonden variabelen, werkt alles goed.
Als we beginnen met het uitvoeren van gebonden variabelen, d.w.z. we voeren het "?" of “$1” voor ons verzoek, wat krijgen we uiteindelijk?
De eerste uitvoering verloopt zoals verwacht. De tweede is iets sneller. Er is iets in de cache opgeslagen. Derde, vierde, vijfde. Dan knallen - en zoiets. En het ergste is dat dit gebeurt bij de zesde executie. Wie wist dat het nodig was om precies zes executies uit te voeren om te begrijpen wat het feitelijke executieplan was?
Wie is schuldig? Wat is er gebeurd? De database bevat optimalisatie. En het lijkt geoptimaliseerd te zijn voor het algemene geval. En daarom schakelt ze vanaf een gegeven moment over op een generiek plan, dat helaas anders kan blijken te zijn. Het kan hetzelfde blijken te zijn, maar het kan ook anders zijn. En er is een soort drempelwaarde die tot dit gedrag leidt.
Wat kun je eraan doen? Hier is het natuurlijk moeilijker om iets aan te nemen. Er is een eenvoudige oplossing die wij gebruiken. Dit is +0, OFFSET 0. Dergelijke oplossingen ken je vast wel. We nemen het gewoon en voegen “+0” toe aan het verzoek en alles is in orde. Ik laat het je later zien.
En er is nog een andere optie: bekijk de plannen zorgvuldiger. De ontwikkelaar moet niet alleen een verzoek schrijven, maar ook 6 keer “leg analyse uit” zeggen. Als het er 5 zijn, zal het niet werken.
En er is een derde optie: schrijf een brief aan pgsql-hackers. Ik schreef echter dat het nog niet duidelijk is of dit een bug of een functie is.
Terwijl we erover nadenken of dit een bug of een functie is, gaan we dit oplossen. Laten we ons verzoek aannemen en "+0" toevoegen. Alles is in orde. Twee symbolen en je hoeft niet eens na te denken over hoe het is of wat het is. Erg makkelijk. We hebben de database eenvoudigweg verboden een index voor deze kolom te gebruiken. We hebben geen index in de “+0”-kolom en dat is alles, de database gebruikt de index niet, alles is in orde.
Dit is de regel van 6 uitleg. In de huidige versies moet je dit nu zes keer doen als je gebonden variabelen hebt. Als u geen gebonden variabelen heeft, is dit wat we doen. En uiteindelijk is het juist dit verzoek dat mislukt. Het is geen lastige zaak.
Het lijkt erop, hoeveel is mogelijk? Een bug hier, een bug daar. Eigenlijk is de bug overal.
Laten we dat eens van dichterbij bekijken. We hebben bijvoorbeeld twee schema's. Schema A met tafel S en diagram B met tafel S. Query – selecteer gegevens uit een tabel. Wat zullen we in dit geval hebben? We zullen een fout hebben. We zullen al het bovenstaande hebben. De regel is: er is overal een bug, we hebben al het bovenstaande.
Nu is de vraag: “Waarom?” Het lijkt erop dat er documentatie is dat als we een schema hebben, er een variabele "search_path" is die ons vertelt waar we naar de tabel moeten zoeken. Het lijkt erop dat er een variabele is.
Wat is het probleem? Het probleem is dat door de server voorbereide instructies niet vermoeden dat zoekpad door iemand kan worden gewijzigd. Deze waarde blijft als het ware constant voor de database. En sommige delen krijgen mogelijk geen nieuwe betekenissen.
Dit is uiteraard afhankelijk van de versie waarop u test. Hangt af van hoe ernstig uw tabellen verschillen. En versie 9.1 zal eenvoudigweg de oude verzoeken uitvoeren. Nieuwe versies kunnen de bug opvangen en u vertellen dat u een bug heeft.
Hoe het te behandelen? Er is een eenvoudig recept: doe het niet. Het is niet nodig om zoekpad te wijzigen terwijl de toepassing actief is. Als u wijzigt, kunt u beter een nieuwe verbinding maken.
Je kunt discussiëren, d.w.z. openen, bespreken, toevoegen. Misschien kunnen we de databaseontwikkelaars ervan overtuigen dat wanneer iemand een waarde verandert, de database de klant hierover moet vertellen: “Kijk, jouw waarde is hier bijgewerkt. Misschien moet u de verklaringen opnieuw instellen en opnieuw aanmaken? Nu gedraagt de database zich in het geheim en rapporteert op geen enkele manier dat de uitspraken ergens binnenin zijn veranderd.
En ik zal nogmaals benadrukken: dit is iets dat niet typisch is voor Java. Hetzelfde zullen we één op één zien in PL/pgSQL. Maar het zal daar worden herhaald.
Laten we wat meer gegevensselectie proberen. Wij kiezen en kiezen. We hebben een tabel met een miljoen rijen. Elke regel is een kilobyte. Ongeveer een gigabyte aan data. En we hebben een werkgeheugen in de Java-machine van 128 megabytes.
Wij gebruiken, zoals aanbevolen in alle boeken, streamverwerking. Dat wil zeggen, we openen resultSet en lezen de gegevens beetje bij beetje. Zal het werken? Zal het uit het geheugen verdwijnen? Lees je een beetje? Laten we vertrouwen op de database, laten we vertrouwen op Postgres. Wij geloven het niet. Zullen we OutOFMemory vallen? Wie heeft OutOfMemory ervaren? Wie heeft het daarna kunnen repareren? Iemand heeft het kunnen repareren.
Als je een miljoen rijen hebt, kun je niet zomaar kiezen. OFFSET/LIMIT is vereist. Wie is voor deze optie? En wie is er voorstander van om met autoCommit te spelen?
Hier blijkt, zoals gewoonlijk, de meest onverwachte optie de juiste te zijn. En als u autoCommit plotseling uitschakelt, zal het helpen. Waarom is dat? De wetenschap weet hier niets van.
Maar standaard halen alle clients die verbinding maken met een Postgres-database de volledige gegevens op. PgJDBC is in dit opzicht geen uitzondering; het selecteert alle rijen.
Er is een variatie op het FetchSize-thema, d.w.z. u kunt op het niveau van een afzonderlijke verklaring zeggen dat u hier gegevens moet selecteren met een getal van 10, 50. Maar dit werkt pas als u autoCommit uitschakelt. AutoCommit uitgeschakeld - het begint te werken.
Maar het is lastig om de code door te nemen en overal setFetchSize in te stellen. Daarom hebben we een instelling gemaakt die de standaardwaarde voor de gehele verbinding aangeeft.
Dat is wat we zeiden. De parameter is geconfigureerd. En wat hebben we gekregen? Als we kleine bedragen selecteren, als we bijvoorbeeld 10 rijen tegelijk selecteren, hebben we zeer grote overheadkosten. Daarom moet deze waarde op ongeveer honderd worden ingesteld.
Idealiter moet je natuurlijk nog leren hoe je het in bytes kunt beperken, maar het recept is dit: stel defaultRowFetchSize in op meer dan honderd en wees tevreden.
Laten we verder gaan met het invoegen van gegevens. Het inbrengen is eenvoudiger, er zijn verschillende mogelijkheden. Bijvoorbeeld INSERT, WAARDEN. Dit is een goede optie. U kunt zeggen “INSERT SELECT”. In de praktijk is het hetzelfde. Er is geen verschil in prestatie.
Boeken zeggen dat je een Batch-instructie moet uitvoeren, boeken zeggen dat je complexere opdrachten met meerdere haakjes kunt uitvoeren. En Postgres heeft een prachtige functie: je kunt KOPIËREN, dat wil zeggen, sneller.
Als je het meet, kun je opnieuw een aantal interessante ontdekkingen doen. Hoe willen we dat dit werkt? We willen niet parseren en geen onnodige opdrachten uitvoeren.
In de praktijk staat TCP ons dit niet toe. Als de klant bezig is met het verzenden van een verzoek, leest de database de verzoeken niet bij pogingen om ons antwoorden te sturen. Het eindresultaat is dat de client wacht tot de database het verzoek heeft gelezen, en de database wacht tot de client het antwoord heeft gelezen.
En daarom wordt de client gedwongen periodiek een synchronisatiepakket te verzenden. Extra netwerkinteracties, extra tijdverspilling.
En hoe meer we ze toevoegen, hoe erger het wordt. De chauffeur is nogal pessimistisch en voegt ze vrij vaak toe, ongeveer eens per 200 regels, afhankelijk van de grootte van de regels, enz.
Het komt voor dat je slechts één regel corrigeert en alles wordt 10 keer sneller. Het gebeurt. Waarom? Zoals gewoonlijk is zo'n constante al ergens gebruikt. En de waarde "128" betekende dat er geen batching werd gebruikt.
Het is goed dat dit niet in de officiële versie is opgenomen. Ontdekt voordat de release begon. Alle betekenissen die ik geef zijn gebaseerd op moderne versies.
Laten we het proberen. Wij meten InsertBatch eenvoudig. We meten InsertBatch meerdere keren, dat wil zeggen hetzelfde, maar er zijn veel waarden. Lastige zet. Niet iedereen kan dit doen, maar het is zo'n eenvoudige zet, veel gemakkelijker dan KOPIËREN.
U kunt KOPIËREN.
En je kunt dit op structuren doen. Declareer het standaardtype Gebruiker, geef array door en INSERT rechtstreeks naar de tabel.
Als je de link opent: pgjdbc/ubenchmsrk/InsertBatch.java, dan staat deze code op GitHub. Daar kun je specifiek zien welke verzoeken er gegenereerd worden. Het maakt niet uit.
Wij zijn gelanceerd. En het eerste dat we ons realiseerden was dat het simpelweg onmogelijk is om geen batch te gebruiken. Alle batchopties zijn nul, dat wil zeggen dat de uitvoeringstijd vrijwel nul is vergeleken met een eenmalige uitvoering.
Wij voegen gegevens in. Het is een heel eenvoudige tafel. Drie kolommen. En wat zien we hier? We zien dat alle drie deze opties ongeveer vergelijkbaar zijn. En COPY is natuurlijk beter.
Dit is wanneer we stukken invoegen. Toen we zeiden dat één VALUES-waarde, twee VALUES-waarden, drie VALUES-waarden waren, of we gaven er tien aan, gescheiden door een komma. Dit is nu gewoon horizontaal. 10, 1, 2, 4. Het is te zien dat de Batch Insert, die blauw is getekend, hem een stuk beter doet voelen. Dat wil zeggen, als je er één tegelijk invoegt of zelfs als je er vier tegelijk invoegt, wordt het twee keer zo goed, simpelweg omdat we wat meer in WAARDEN hebben gepropt. Minder EXECUTE-bewerkingen.
Het gebruik van COPY op kleine volumes is uiterst weinig belovend. De eerste twee heb ik niet eens getekend. Ze gaan naar de hemel, dat wil zeggen deze groene cijfers voor COPY.
COPY moet worden gebruikt als u minimaal honderd rijen met gegevens heeft. De overhead van het openen van deze verbinding is groot. En eerlijk gezegd heb ik niet in deze richting gegraven. Ik heb Batch geoptimaliseerd, maar niet COPY.
Wat doen we nu? Wij hebben het geprobeerd. We begrijpen dat we structuren moeten gebruiken of een slimme bacth die verschillende betekenissen combineert.
Wat moet u meenemen uit het rapport van vandaag?
- PreparedStatement is ons alles. Dit levert veel op voor de productiviteit. Het produceert een grote flop in de zalf.
- En je moet 6 keer UITLEG ANALYSE doen.
- En we moeten OFFSET 0 en trucs als +0 verdunnen om het resterende percentage van onze problematische zoekopdrachten te corrigeren.
Bron: www.habr.com