Ek stel voor jy lees die transkripsie van Vladimir Sitnikov se vroeë 2016-verslag "PostgreSQL en JDBC druk al die sap uit"
Goeie middag My naam is Vladimir Sitnikov. Ek werk al 10 jaar vir NetCracker. En ek is meestal in produktiwiteit. Alles wat met Java verband hou, alles wat met SQL verband hou, is waarvan ek hou.
En vandag sal ek praat oor wat ons in die maatskappy teëgekom het toe ons PostgreSQL as 'n databasisbediener begin gebruik het. En ons werk meestal met Java. Maar wat ek jou vandag gaan vertel, gaan nie net oor Java nie. Soos die praktyk getoon het, kom dit ook in ander tale voor.
Ons sal praat:
- oor datasteekproefneming.
- Oor die stoor van data.
- En ook oor prestasie.
- En oor die onderwaterharke wat daar begrawe is.
Kom ons begin met 'n eenvoudige vraag. Ons kies een ry uit die tabel gebaseer op die primêre sleutel.
Die databasis is op dieselfde gasheer geleë. En al hierdie boerdery neem 20 millisekondes.
Hierdie 20 millisekondes is baie. As jy 100 sulke versoeke het, spandeer jy tyd per sekonde om deur hierdie versoeke te blaai, dit wil sê ons mors tyd.
Ons doen dit nie graag nie en kyk wat die basis ons hiervoor bied. Die databasis bied ons twee opsies om navrae uit te voer.
Die eerste opsie is 'n eenvoudige versoek. Wat is goed daaraan? Die feit dat ons dit vat en stuur, en niks meer nie.
Die databasis het ook 'n gevorderde navraag, wat moeiliker is, maar meer funksioneel. Jy kan afsonderlik 'n versoek stuur vir ontleding, uitvoering, veranderlike binding, ens.
Super uitgebreide navraag is iets wat ons nie in die huidige verslag sal dek nie. Ons wil dalk iets van die databasis hê en daar is 'n wenslys wat in een of ander vorm gevorm is, dit wil sê dit is wat ons wil hê, maar dit is onmoontlik nou en in die volgende jaar. So ons het dit net opgeneem en ons gaan rond en skud die hoofmense.
En wat ons kan doen is eenvoudige navraag en uitgebreide navraag.
Wat is spesiaal aan elke benadering?
'n Eenvoudige navraag is goed vir eenmalige uitvoering. Sodra klaar en vergeet. En die probleem is dat dit nie die binêre dataformaat ondersteun nie, dit wil sê dit is nie geskik vir sommige hoëprestasiestelsels nie.
Uitgebreide navraag – laat jou toe om tyd te bespaar met ontleding. Dit is wat ons gedoen het en begin gebruik het. Dit het ons regtig baie gehelp. Daar is nie net besparings op ontleding nie. Daar is besparings op data-oordrag. Die oordrag van data in binêre formaat is baie meer doeltreffend.
Kom ons gaan oor na oefening. Dit is hoe 'n tipiese toepassing lyk. Dit kan Java, ens.
Ons het 'n verklaring geskep. Het die opdrag uitgevoer. Geskep naby. Waar is die fout hier? Wat is die probleem? Geen probleem. Dit is wat dit in al die boeke sê. Dit is hoe dit geskryf moet word. As jy maksimum prestasie wil hê, skryf so.
Maar die praktyk het getoon dat dit nie werk nie. Hoekom? Omdat ons 'n "close" metode het. En wanneer ons dit doen, blyk dit uit die databasis-oogpunt dat dit is soos 'n roker wat met 'n databasis werk. Ons het gesê "PARSE EXECUTE DEALLOCATE".
Waarom al hierdie ekstra skepping en aflaai van stellings? Niemand het hulle nodig nie. Maar wat gewoonlik in PreparedStatements gebeur, is dat wanneer ons dit toemaak, hulle alles op die databasis toemaak. Dit is nie wat ons wil hê nie.
Ons wil, soos gesonde mense, met die basis werk. Ons het ons verklaring een keer geneem en voorberei, dan voer ons dit baie keer uit. Trouens, baie keer - dit is een keer in die hele lewe van toepassings - is hulle ontleed. En ons gebruik dieselfde stelling-ID op verskillende RESTs. Dit is ons doelwit.
Hoe kan ons dit bereik?
Dit is baie eenvoudig - dit is nie nodig om verklarings te sluit nie. Ons skryf dit so: “berei voor” “uitvoer”.
As ons so iets loods, dan is dit duidelik dat iets iewers sal oorloop. As dit nie duidelik is nie, kan jy dit probeer. Kom ons skryf 'n maatstaf wat hierdie eenvoudige metode gebruik. Skep 'n stelling. Ons begin dit op een of ander weergawe van die bestuurder en vind dat dit redelik vinnig ineenstort met die verlies van al die geheue wat dit gehad het.
Dit is duidelik dat sulke foute maklik reggestel word. Ek sal nie oor hulle praat nie. Maar ek sal sê dat die nuwe weergawe baie vinniger werk. Die metode is dom, maar tog.
Hoe om korrek te werk? Wat moet ons hiervoor doen?
In werklikheid sluit aansoeke altyd verklarings. In alle boeke sê hulle om dit toe te maak, anders gaan die geheue lek.
En PostgreSQL weet nie hoe om navrae te kas nie. Dit is nodig dat elke sessie hierdie kas vir homself skep.
En ons wil ook nie tyd mors op ontleding nie.
En soos gewoonlik het ons twee opsies.
Die eerste opsie is dat ons dit neem en sê dat kom ons draai alles in PgSQL. Daar is 'n kas daar. Dit kas alles. Dit sal puik uitdraai. Ons het dit gesien. Ons het 100500 XNUMX versoeke. Werk nie. Ons stem nie in om versoeke handmatig in prosedures te omskep nie. Nee nee.
Ons het 'n tweede opsie - neem dit en sny dit self. Ons maak die bronne oop en begin sny. Ons het gesien en gesien. Dit het geblyk dat dit nie so moeilik is om te doen nie.
Dit het in Augustus 2015 verskyn. Nou is daar 'n meer moderne weergawe. En alles is wonderlik. Dit werk so goed dat ons niks in die toepassing verander nie. En ons het selfs opgehou om in die rigting van PgSQL te dink, dit wil sê dit was genoeg vir ons om alle oorhoofse koste tot byna nul te verminder.
Gevolglik word Bediener-voorbereide stellings geaktiveer op die 5de uitvoering om te verhoed dat geheue in die databasis vermors word op elke eenmalige versoek.
Jy mag dalk vra – waar is die getalle? Wat kry jy? En hier sal ek nie nommers gee nie, want elke versoek het sy eie.
Ons navrae was so dat ons ongeveer 20 millisekondes spandeer het om op OLTP-navrae te ontleed. Daar was 0,5 millisekondes vir uitvoering, 20 millisekondes vir ontleding. Versoek – 10 KiB teks, 170 reëls plan. Dit is 'n OLTP-versoek. Dit vra 1, 5, 10 reëls, soms meer.
Maar ons wou glad nie 20 millisekondes mors nie. Ons het dit tot 0 verminder. Alles is wonderlik.
Wat kan jy van hier af wegneem? As jy Java het, neem jy die moderne weergawe van die bestuurder en jubel.
As jy 'n ander taal praat, dink dan - dalk het jy dit ook nodig? Want uit die oogpunt van die finale taal, byvoorbeeld, as PL 8 of jy LibPQ het, is dit nie vir jou duidelik dat jy tyd spandeer nie aan uitvoering nie, aan ontleding, en dit is die moeite werd om na te gaan. Hoe? Alles is gratis.
Behalwe dat daar foute en 'n paar eienaardighede is. En ons sal nou oor hulle praat. Die meeste daarvan sal handel oor industriële argeologie, oor wat ons gevind het, wat ons teëgekom het.
As die versoek dinamies gegenereer word. Dit gebeur. Iemand plak die snare aanmekaar, wat 'n SQL-navraag tot gevolg het.
Hoekom is hy sleg? Dit is erg, want elke keer eindig ons met 'n ander tou.
En die hashCode van hierdie verskillende string moet weer gelees word. Dit is regtig 'n SVE-taak - om 'n lang versoekteks in selfs 'n bestaande hash te vind, is nie so maklik nie. Daarom is die gevolgtrekking eenvoudig - moenie versoeke genereer nie. Stoor hulle in een veranderlike. En wees bly.
Volgende probleem. Datatipes is belangrik. Daar is ORM's wat sê dat dit nie saak maak watter soort NULL daar is nie, laat daar 'n soort wees. As Int, dan sê ons setInt. En as NULL, laat dit dan altyd VARCHAR wees. En watter verskil maak dit op die ou end watter NULL daar is? Die databasis self sal alles verstaan. En hierdie prentjie werk nie.
In die praktyk gee die databasis glad nie om nie. As jy die eerste keer gesê het dat dit 'n nommer is, en die tweede keer dat jy gesê het dat dit 'n VARCHAR is, dan is dit onmoontlik om Bediener-voorbereide stellings te hergebruik. En in hierdie geval moet ons ons stelling herskep.
As jy dieselfde navraag uitvoer, maak seker dat die datatipes in jou kolom nie verwar word nie. Jy moet oppas vir NULL. Dit is 'n algemene fout wat ons gehad het nadat ons PreparedStatements begin gebruik het
Goed, aangeskakel. Miskien het hulle die bestuurder gevat. En produktiwiteit het gedaal. Dinge het sleg geraak.
Hoe gebeur dit? Is dit 'n fout of 'n kenmerk? Ongelukkig was dit nie moontlik om te verstaan of dit 'n fout of 'n kenmerk is nie. Maar daar is 'n baie eenvoudige scenario om hierdie probleem weer te gee. Sy het ons heeltemal onverwags oorval. En dit bestaan uit steekproefneming letterlik uit een tabel. Ons het natuurlik meer sulke versoeke gehad. As 'n reël het hulle twee of drie tafels ingesluit, maar daar is so 'n speelscenario. Neem enige weergawe van jou databasis en speel dit.
Die punt is dat ons twee kolomme het, wat elkeen geïndekseer is. Daar is 'n miljoen rye in een NULL-kolom. En die tweede kolom bevat slegs 20 reëls. Wanneer ons sonder gebonde veranderlikes uitvoer, werk alles goed.
As ons begin uitvoer met gebonde veranderlikes, dit wil sê ons voer die "?" of "$1" vir ons versoek, wat kry ons uiteindelik?
Die eerste uitvoering is soos verwag. Die tweede een is 'n bietjie vinniger. Iets is gekas. Derde, vierde, vyfde. Dan bang - en so iets. En die ergste is dat dit met die sesde teregstelling gebeur. Wie het geweet dat dit nodig was om presies ses teregstellings te doen om te verstaan wat die werklike teregstellingsplan was?
Wie is skuldig? Wat het gebeur? Die databasis bevat optimalisering. En dit blyk geoptimaliseer te wees vir die generiese geval. En dienooreenkomstig, begin op 'n stadium, skakel sy oor na 'n generiese plan, wat ongelukkig anders kan blyk te wees. Dit kan dieselfde blyk te wees, of dit kan anders wees. En daar is 'n soort drempelwaarde wat tot hierdie gedrag lei.
Wat kan jy daaromtrent doen? Hier is dit natuurlik moeiliker om iets te veronderstel. Daar is 'n eenvoudige oplossing wat ons gebruik. Dit is +0, OFFSET 0. Jy ken sekerlik sulke oplossings. Ons neem dit net en voeg "+0" by die versoek en alles is reg. Ek sal jou later wys.
En daar is nog 'n opsie - kyk noukeuriger na die planne. Die ontwikkelaar moet nie net 'n versoek skryf nie, maar ook 6 keer sê "verduidelik ontleed". As dit 5 is, sal dit nie werk nie.
En daar is 'n derde opsie - skryf 'n brief aan pgsql-hackers. Ek het geskryf, dit is egter nog nie duidelik of dit 'n fout of 'n kenmerk is nie.
Terwyl ons dink of dit 'n fout of 'n kenmerk is, laat ons dit regmaak. Kom ons neem ons versoek en voeg "+0" by. Alles is reg. Twee simbole en jy hoef nie eers te dink oor hoe dit is of wat dit is nie. Baie eenvoudig. Ons het eenvoudig die databasis verbied om 'n indeks op hierdie kolom te gebruik. Ons het nie 'n indeks op die "+0" kolom nie en dit is dit, die databasis gebruik nie die indeks nie, alles is in orde.
Dit is die reël van 6 verduidelik. Nou in huidige weergawes moet jy dit 6 keer doen as jy gebonde veranderlikes het. As jy nie gebonde veranderlikes het nie, is dit wat ons doen. En op die ou end is dit juis hierdie versoek wat misluk. Dit is nie 'n moeilike ding nie.
Dit wil voorkom, hoeveel is moontlik? 'n Gogga hier, 'n gogga daar. Eintlik is die gogga oral.
Kom ons kyk van naderby. Ons het byvoorbeeld twee skemas. Skema A met tabel S en diagram B met tabel S. Navraag – kies data uit 'n tabel. Wat sal ons in hierdie geval hê? Ons sal 'n fout hê. Ons sal al die bogenoemde hê. Die reël is - 'n gogga is oral, ons sal al die bogenoemde hê.
Nou is die vraag: "Hoekom?" Dit wil voorkom asof daar dokumentasie is dat as ons 'n skema het, daar 'n "search_path" veranderlike is wat ons vertel waar om na die tabel te soek. Dit wil voorkom asof daar 'n veranderlike is.
Wat is die probleem? Die probleem is dat bediener-voorbereide stellings nie vermoed dat search_path deur iemand verander kan word nie. Hierdie waarde bly as 't ware konstant vir die databasis. En sommige dele kry dalk nie nuwe betekenisse nie.
Dit hang natuurlik af van die weergawe waarop u toets. Hang af van hoe ernstig jou tabelle verskil. En weergawe 9.1 sal eenvoudig die ou navrae uitvoer. Nuwe weergawes kan dalk die fout opvang en jou vertel dat jy 'n fout het.
Hoe om dit te behandel? Daar is 'n eenvoudige resep - moenie dit doen nie. Dit is nie nodig om search_path te verander terwyl die toepassing loop nie. As jy verander, is dit beter om 'n nuwe verbinding te skep.
Jy kan bespreek, dit wil sê oopmaak, bespreek, byvoeg. Miskien kan ons die databasisontwikkelaars oortuig dat wanneer iemand ’n waarde verander, die databasis die kliënt hiervan moet vertel: “Kyk, jou waarde is hier opgedateer. Miskien moet jy die stellings terugstel en dit herskep?” Nou tree die databasis in die geheim op en rapporteer geensins dat die stellings iewers binne verander het nie.
En ek sal weer beklemtoon - dit is iets wat nie tipies vir Java is nie. Ons sal dieselfde ding in PL/pgSQL een tot een sien. Maar dit sal daar weergegee word.
Kom ons probeer 'n bietjie meer dataseleksie. Ons kies en kies. Ons het 'n tabel met 'n miljoen rye. Elke reël is 'n kilogreep. Ongeveer 'n gigagreep data. En ons het 'n werkende geheue in die Java-masjien van 128 megagrepe.
Ons, soos aanbeveel in alle boeke, gebruik stroomverwerking. Dit wil sê, ons maak resultaatSet oop en lees die data bietjie vir bietjie daarvandaan. Sal dit werk? Sal dit uit die geheue val? Sal jy 'n bietjie lees? Kom ons vertrou op die databasis, kom ons vertrou op Postgres. Ons glo dit nie. Sal ons OutOFMemory val? Wie het OutOfMemory ervaar? Wie het dit daarna reggekry? Iemand het dit reggekry.
As jy 'n miljoen rye het, kan jy nie net kies en keur nie. OFFSET/LIMIT word vereis. Wie is vir hierdie opsie? En wie is ten gunste daarvan om met autoCommit te speel?
Hier, soos gewoonlik, blyk die mees onverwagte opsie korrek te wees. En as jy skielik autoCommit afskakel, sal dit help. Hoekom is dit? Die wetenskap weet nie hiervan nie.
Maar by verstek haal alle kliënte wat aan 'n Postgres-databasis koppel die hele data. PgJDBC is geen uitsondering in hierdie verband nie; dit kies alle rye.
Daar is 'n variasie op die FetchSize-tema, dit wil sê jy kan op die vlak van 'n aparte stelling sê dat hier asseblief data met 10, 50 kies. Maar dit werk nie totdat jy autoCommit afskakel nie. AutoCommit afgeskakel - dit begin werk.
Maar om oral deur die kode te gaan en setFetchSize in te stel, is ongerieflik. Daarom het ons 'n instelling gemaak wat die verstekwaarde vir die hele verbinding sal sê.
Dit is wat ons gesê het. Die parameter is opgestel. En wat het ons gekry? As ons klein hoeveelhede kies, as ons byvoorbeeld 10 rye op 'n slag kies, dan het ons baie groot oorhoofse koste. Daarom moet hierdie waarde op ongeveer honderd gestel word.
Ideaal gesproke moet jy natuurlik nog leer hoe om dit in grepe te beperk, maar die resep is dit: stel defaultRowFetchSize op meer as honderd en wees gelukkig.
Kom ons gaan voort met die invoeging van data. Invoeging is makliker, daar is verskillende opsies. Byvoorbeeld, INSERT, VALUES. Dit is 'n goeie opsie. Jy kan sê "INSERT SELECT". In die praktyk is dit dieselfde ding. Daar is geen verskil in prestasie nie.
Boeke sê dat jy 'n Batch-stelling moet uitvoer, boeke sê dat jy meer komplekse opdragte met verskeie hakies kan uitvoer. En Postgres het 'n wonderlike kenmerk - jy kan COPY doen, dit wil sê doen dit vinniger.
As jy dit meet, kan jy weer 'n paar interessante ontdekkings maak. Hoe wil ons hê dit moet werk? Ons wil nie ontleed en nie onnodige opdragte uitvoer nie.
In die praktyk laat TCP ons nie toe om dit te doen nie. As die kliënt besig is om 'n versoek te stuur, lees die databasis nie die versoeke in pogings om vir ons antwoorde te stuur nie. Die eindresultaat is dat die kliënt wag vir die databasis om die versoek te lees, en die databasis wag vir die kliënt om die antwoord te lees.
En daarom word die kliënt gedwing om periodiek 'n sinchronisasiepakkie te stuur. Ekstra netwerkinteraksies, ekstra tydmors.
En hoe meer ons hulle byvoeg, hoe erger word dit. Die drywer is redelik pessimisties en voeg hulle gereeld by, omtrent een keer elke 200 reëls, afhangende van die grootte van die lyne, ens.
Dit gebeur dat jy net een reël regstel en alles sal 10 keer versnel. Dit gebeur. Hoekom? Soos gewoonlik is 'n konstante soos hierdie al iewers gebruik. En die waarde "128" het bedoel om nie groepering te gebruik nie.
Dit is goed dat dit nie in die amptelike weergawe ingesluit is nie. Ontdek voordat die vrystelling begin het. Al die betekenisse wat ek gee is gebaseer op moderne weergawes.
Kom ons probeer dit. Ons meet InsertBatch eenvoudig. Ons meet InsertBatch verskeie kere, dit wil sê dieselfde ding, maar daar is baie waardes. Moeilike skuif. Nie almal kan dit doen nie, maar dit is so 'n eenvoudige skuif, baie makliker as COPY.
Jy kan COPY doen.
En jy kan dit op strukture doen. Verklaar Gebruiker verstek tipe, gee skikking en INSERT direk na die tabel.
As jy die skakel oopmaak: pgjdbc/ubenchmsrk/InsertBatch.java, dan is hierdie kode op GitHub. Jy kan spesifiek sien watter versoeke daar gegenereer word. Dit maak nie saak nie.
Ons het geloods. En die eerste ding wat ons besef het, was dat dit eenvoudig onmoontlik is om 'n bondel te gebruik. Alle bondelopsies is nul, dit wil sê die uitvoeringstyd is feitlik nul in vergelyking met 'n eenmalige uitvoering.
Ons voeg data in. Dit is 'n baie eenvoudige tafel. Drie kolomme. En wat sien ons hier? Ons sien dat al drie hierdie opsies min of meer vergelykbaar is. En COPY is natuurlik beter.
Dit is wanneer ons stukke insit. Toe ons gesê het dat een VALUES-waarde, twee VALUES-waardes, drie VALUES-waardes, of ons 10 van hulle aangedui het, geskei deur 'n komma. Dit is nou net horisontaal. 1, 2, 4, 128. Daar kan gesien word dat die Batch Insert, wat in blou geteken is, hom baie beter laat voel. Dit wil sê, wanneer jy een op 'n slag insit of selfs wanneer jy vier op 'n slag insit, word dit twee keer so goed, bloot omdat ons 'n bietjie meer in WAARDES geprop het. Minder VOER bewerkings uit.
Die gebruik van COPY op klein volumes is uiters onbelowend. Ek het nie eers op die eerste twee geteken nie. Hulle gaan hemel toe, dit wil sê hierdie groen nommers vir KOPIE.
COPY moet gebruik word wanneer jy ten minste honderd rye data het. Die bokoste om hierdie verbinding oop te maak is groot. En, om eerlik te wees, ek het nie in hierdie rigting gegrawe nie. Ek het Batch geoptimaliseer, maar nie COPY nie.
Wat doen ons volgende? Ons het dit probeer. Ons verstaan dat ons óf strukture óf 'n slim batch moet gebruik wat verskeie betekenisse kombineer.
Wat moet jy wegneem uit vandag se verslag?
- PreparedStatement is ons alles. Dit gee baie vir produktiwiteit. Dit produseer 'n groot flop in die salf.
- En jy moet 6 keer VERDUIDELIK ANALISEER doen.
- En ons moet OFFSET 0 verdun, en truuks soos +0 om die oorblywende persentasie van ons problematiese navrae reg te stel.
Bron: will.com