Azt javaslom, olvassa el Vlagyimir Szitnyikov 2016 eleji jelentésének átiratát: „A PostgreSQL és a JDBC kinyomja a levet”
Jó napot A nevem Vladimir Sitnikov. 10 éve dolgozom a NetCrackernél. És leginkább a termelékenységgel foglalkozom. Minden, ami a Java-val kapcsolatos, minden, ami az SQL-hez kapcsolódik, az, amit szeretek.
Ma pedig arról fogok beszélni, hogy mivel találkoztunk a cégnél, amikor elkezdtük használni a PostgreSQL-t adatbázis-kiszolgálóként. És többnyire Java-val dolgozunk. De amit ma elmondok, az nem csak a Java-ról szól. Amint a gyakorlat megmutatta, ez más nyelvekben is előfordul.
Majd beszélünk:
- az adatmintavételről.
- Az adatmentésről.
- És a teljesítményről is.
- És az ott elásott víz alatti gereblyékről.
Kezdjük egy egyszerű kérdéssel. Az elsődleges kulcs alapján kiválasztunk egy sort a táblázatból.
Az adatbázis ugyanazon a gazdagépen található. És ez az egész gazdálkodás 20 milliszekundumot vesz igénybe.
Ez a 20 ezredmásodperc sok. Ha 100 ilyen kérése van, akkor másodpercenként időt tölt ezeken a kéréseken, vagyis időt vesztegetünk.
Nem szeretjük ezt csinálni, és azt nézzük, mit kínál nekünk ehhez a bázis. Az adatbázis két lehetőséget kínál a lekérdezések végrehajtására.
Az első lehetőség egy egyszerű kérés. Mi a jó benne? Az, hogy elvesszük és elküldjük, és semmi több.
Az adatbázisnak van egy speciális lekérdezése is, amely bonyolultabb, de funkcionálisabb. Külön is küldhet kérelmet elemzésre, végrehajtásra, változó kötésre stb.
A szuper kibővített lekérdezés olyan dolog, amelyre a jelenlegi jelentésben nem térünk ki. Talán szeretnénk valamit az adatbázisból, és van egy kívánságlista, ami valamilyen formában kialakult, vagyis ezt szeretnénk, de ez most és a következő évben lehetetlen. Tehát csak felvettük, és körbejárjuk a főembereket.
És amit tehetünk, az az egyszerű lekérdezés és a kiterjesztett lekérdezés.
Mi a különleges az egyes megközelítésekben?
Egy egyszerű lekérdezés egyszeri végrehajtásra jó. Egyszer kész és elfelejtett. A probléma pedig az, hogy nem támogatja a bináris adatformátumot, vagyis egyes nagy teljesítményű rendszerekhez nem alkalmas.
Kiterjesztett lekérdezés – időt takaríthat meg az elemzéssel. Ezt csináltuk és elkezdtük használni. Ez nagyon-nagyon segített nekünk. Nem csak az elemzéssel takarítható meg. Az adatátvitelnél megtakarítás érhető el. Az adatok bináris formátumban történő átvitele sokkal hatékonyabb.
Térjünk át a gyakorlásra. Így néz ki egy tipikus alkalmazás. Lehet Java, stb.
Nyilatkozatot készítettünk. Végrehajtotta a parancsot. Létrehozva közel. Hol itt a hiba? Mi a probléma? Nincs mit. Minden könyvben ez áll. Ezt így kell írni. Ha maximális teljesítményre vágysz, írj így.
De a gyakorlat azt mutatja, hogy ez nem működik. Miért? Mert van egy "közeli" módszerünk. És amikor ezt megtesszük, az adatbázis szempontjából kiderül, hogy olyan, mint egy dohányos, aki adatbázissal dolgozik. Azt mondtuk: "PARSE EXECUTE DEALLOCATE".
Miért ez a sok plusz állításalkotás és kirakás? Senkinek nincs szüksége rájuk. A PreparedStatementsben azonban általában az történik, hogy amikor bezárjuk őket, mindent bezárnak az adatbázisban. Nem ezt akarjuk.
Az egészséges emberekhez hasonlóan mi is a bázissal akarunk dolgozni. Egyszer vettük és készítettük el a nyilatkozatunkat, majd sokszor végrehajtjuk. Valójában sokszor – ez egyszer van az alkalmazások teljes élettartama során – elemzésre kerültek. És ugyanazt az azonosítót használjuk a különböző REST-eken. Ez a célunk.
Hogyan érhetjük el ezt?
Nagyon egyszerű – nem kell lezárni a nyilatkozatokat. Ezt így írjuk: „prepare” „execute”.
Ha valami ilyesmit elindítunk, akkor egyértelmű, hogy valahol valami túlcsordul. Ha nem egyértelmű, akkor kipróbálhatja. Írjunk egy benchmarkot, amely ezt az egyszerű módszert használja. Hozzon létre egy nyilatkozatot. Elindítjuk az illesztőprogram valamelyik verzióján, és úgy találjuk, hogy elég gyorsan összeomlik, és elveszíti az összes memóriáját.
Nyilvánvaló, hogy az ilyen hibák könnyen javíthatók. Nem beszélek róluk. De azt mondom, hogy az új verzió sokkal gyorsabban működik. A módszer hülyeség, de akkor is.
Hogyan kell helyesen dolgozni? Mit kell ehhez tennünk?
A valóságban az alkalmazások mindig bezárják az utasításokat. Minden könyvben azt mondják, hogy zárd be, különben kiszivárog az emlék.
A PostgreSQL pedig nem tudja, hogyan kell a lekérdezéseket gyorsítótárba helyezni. Szükséges, hogy minden munkamenet létrehozza magának ezt a gyorsítótárat.
És nem akarunk időt vesztegetni elemzésre sem.
És szokás szerint két lehetőségünk van.
Az első lehetőség az, hogy vesszük, és azt mondjuk, hogy csomagoljunk mindent PgSQL-be. Van ott egy gyorsítótár. Mindent gyorsítótáraz. Remek lesz. Ezt láttuk. 100500 kérésünk van. Nem működik. Nem járulunk hozzá ahhoz, hogy a kéréseket manuálisan eljárásokká alakítsuk. Nem nem.
Van egy második lehetőségünk is: vegyük és vágjuk meg magunk. Megnyitjuk a forrásokat és elkezdjük vágni. Láttunk és láttunk. Kiderült, hogy ezt nem is olyan nehéz megcsinálni.
Ez 2015 augusztusában jelent meg. Most van egy modernebb verzió. És minden nagyszerű. Olyan jól működik, hogy semmit nem változtatunk az alkalmazáson. És még abba is hagytuk a PgSQL irányába való gondolkodást, vagyis ez bőven elég volt ahhoz, hogy az összes rezsiköltséget szinte nullára csökkentsük.
Ennek megfelelően a szerver által előkészített utasítások az 5. végrehajtáskor aktiválódnak, hogy elkerüljük az adatbázis memóriaveszteségét minden egyes egyszeri kérésnél.
Kérdezheti – hol vannak a számok? mit kapsz? És itt nem adok számokat, mert minden kérésnek megvan a sajátja.
Lekérdezéseink olyanok voltak, hogy körülbelül 20 ezredmásodpercet töltöttünk az OLTP-lekérdezések elemzésével. 0,5 ezredmásodperc volt a végrehajtásra, 20 ezredmásodperc az elemzésre. Kérelem – 10 KiB szöveg, 170 soros terv. Ez egy OLTP-kérés. 1, 5, 10 sort kér, néha többet is.
De egyáltalán nem akartunk 20 milliszekundumot elpazarolni. 0-ra csökkentettük. Minden nagyszerű.
Mit lehet innen elvinni? Ha Java-val rendelkezik, akkor vegye elő az illesztőprogram modern verzióját, és örüljön.
Ha más nyelven beszél, akkor gondolja át – talán erre is szüksége van? Mert a végső nyelv szempontjából ha pl PL 8 vagy LibPQ van, akkor nem egyértelmű számodra, hogy nem a végrehajtásra, elemzésre fordítod az időt, és ezt érdemes ellenőrizni. Hogyan? Minden ingyenes.
Kivéve, hogy vannak hibák és néhány sajátosság. És most róluk fogunk beszélni. A legtöbb az ipari régészetről fog szólni, arról, hogy mit találtunk, mire bukkantunk.
Ha a kérés dinamikusan generálódik. Megtörténik. Valaki összeragasztja a karakterláncokat, ami SQL-lekérdezést eredményez.
Miért rossz? Rossz, mert minden alkalommal más-más húrhoz jutunk.
És ennek a különböző karakterláncnak a hashCode-ját újra el kell olvasni. Ez valóban CPU-feladat – hosszú kérésszöveg megtalálása még egy meglévő hashben sem olyan egyszerű. Ezért a következtetés egyszerű - ne generáljon kéréseket. Tárolja őket egy változóban. És örülj.
Következő probléma. Az adattípusok fontosak. Vannak ORM-ek, amelyek azt mondják, hogy nem mindegy, milyen NULL van, legyen valamiféle. Ha Int, akkor azt mondjuk, setInt. És ha NULL, akkor legyen mindig VARCHAR. És mi a különbség a végén, hogy mi a NULL? Maga az adatbázis mindent megért. És ez a kép nem működik.
A gyakorlatban az adatbázist egyáltalán nem érdekli. Ha először azt mondta, hogy ez egy szám, másodszor pedig azt, hogy ez egy VARCHAR, akkor lehetetlen újra felhasználni a szerver által készített utasításokat. És ebben az esetben újra kell alkotnunk a kijelentésünket.
Ha ugyanazt a lekérdezést hajtja végre, győződjön meg arról, hogy az oszlopban lévő adattípusok nincsenek összetévesztve. Vigyázni kell a NULL-ra. Ez egy gyakori hiba, amelyet a PreparedStatements használatának megkezdése után tapasztaltunk
Oké, bekapcsolva. Talán elvitték a sofőrt. És a termelékenység csökkent. A dolgok rosszra fordultak.
Hogyan történik ez? Ez hiba vagy funkció? Sajnos nem lehetett megérteni, hogy ez hiba vagy funkció. De van egy nagyon egyszerű forgatókönyv a probléma reprodukálására. Teljesen váratlanul lesből támadt ránk. És szó szerint egy táblázatból való mintavételből áll. Természetesen több ilyen kérésünk is volt. Általában két vagy három asztalt tartalmaztak, de van ilyen lejátszási forgatókönyv. Vegye ki bármelyik verziót az adatbázisából, és játssza le.
A lényeg az, hogy két oszlopunk van, amelyek mindegyike indexelt. Egy NULL oszlopban millió sor van. A második oszlop pedig csak 20 sort tartalmaz. Ha kötött változók nélkül hajtjuk végre, minden jól működik.
Ha kötött változókkal kezdjük a végrehajtást, azaz végrehajtjuk a "?" vagy „$1” a kérésünkre, mit kapunk végül?
Az első végrehajtás a vártnak megfelelő. A második egy kicsit gyorsabb. Valami gyorsítótárba került. Harmadik, negyedik, ötödik. Aztán bumm – és valami ilyesmi. És a legrosszabb az, hogy ez a hatodik kivégzéskor történik. Ki tudta, hogy pontosan hat kivégzést kell végrehajtani ahhoz, hogy megértsük, mi a tényleges végrehajtási terv?
Ki a bűnös? Mi történt? Az adatbázis optimalizálást tartalmaz. És úgy tűnik, hogy általános esetre van optimalizálva. És ennek megfelelően egy bizonyos ponttól kezdve általános tervre vált, amely sajnos másképp alakulhat. Kiderülhet, hogy ugyanaz, vagy más. És van valamiféle küszöbérték, ami ehhez a viselkedéshez vezet.
Mit tehetsz ellene? Itt persze nehezebb bármit is feltételezni. Van egy egyszerű megoldás, amit használunk. Ez +0, OFFSET 0. Biztosan ismersz ilyen megoldásokat. Csak vesszük, és hozzáadjuk a „+0”-t a kéréshez, és minden rendben van. majd később megmutatom.
És van egy másik lehetőség - nézze meg alaposabban a terveket. A fejlesztőnek nem csak kérést kell írnia, hanem hatszor ki kell mondania, hogy „magyarázza analízist”. Ha 6, akkor nem fog működni.
És van egy harmadik lehetőség - írjon levelet a pgsql-hackereknek. Azt írtam, azonban még nem világos, hogy ez hiba vagy funkció.
Amíg azon gondolkodunk, hogy ez hiba vagy funkció, javítsuk ki. Vegyük a kérésünket, és adjunk hozzá "+0"-t. Minden rendben. Két szimbólum, és nem is kell gondolkodni azon, hogy ez vagy mi az. Nagyon egyszerű. Egyszerűen megtiltottuk, hogy az adatbázis indexet használjon ezen az oszlopon. Nincs indexünk a „+0” oszlopban és ennyi, az adatbázis nem használja az indexet, minden rendben.
Ez a szabály a 6 magyarázat. Most a jelenlegi verziókban 6-szor kell megtennie, ha vannak kötött változói. Ha nincsenek kötött változói, mi ezt tesszük. És végül éppen ez a kérés az, ami kudarcot vall. Ez nem egy trükkös dolog.
Úgy tűnik, mennyi lehetséges? Egy hiba itt, egy hiba ott. Valójában a hiba mindenhol ott van.
Nézzük meg közelebbről. Például két sémánk van. A séma S táblázattal és B diagram S táblázattal. Lekérdezés – adatok kiválasztása egy táblázatból. Mi lesz ebben az esetben? Hibánk lesz. A fentiek mindegyike rendelkezésünkre áll. A szabály az, hogy egy hiba mindenhol megtalálható, a fentiek mindegyike meglesz.
Most a kérdés: "Miért?" Úgy tűnik, hogy van olyan dokumentáció, amely szerint ha van sémánk, akkor van egy "search_path" változó, amely megmondja, hol keressük a táblát. Úgy tűnik, hogy van egy változó.
Mi a probléma? A probléma az, hogy a szerver által előkészített utasítások nem sejtik, hogy valaki megváltoztathatja a keresési_útvonalat. Ez az érték mintegy állandó marad az adatbázisban. És előfordulhat, hogy egyes részek nem vesznek fel új jelentést.
Természetesen ez a tesztelt verziótól függ. Attól függ, mennyire különböznek egymástól az asztalok. A 9.1-es verzió pedig egyszerűen végrehajtja a régi kéréseket. Az új verziók elkaphatják a hibát, és jelezhetik, hogy van egy hiba.
Hogyan kell kezelni? Van egy egyszerű recept – ne csináld. Az alkalmazás futása közben nincs szükség a search_path módosítására. Ha változtat, jobb, ha új kapcsolatot hoz létre.
Lehet megbeszélni, azaz nyitni, megbeszélni, hozzátenni. Talán meg tudjuk győzni az adatbázis-fejlesztőket, hogy ha valaki megváltoztat egy értéket, akkor az adatbázisnak ezt közölnie kell a klienssel: „Nézd, itt frissült az értéked. Lehet, hogy vissza kell állítania az állításokat, és újra létre kell hoznia őket?” Most az adatbázis titokban viselkedik, és semmilyen módon nem jelenti azt, hogy az állítások valahol belül megváltoztak.
És még egyszer hangsúlyozom - ez nem jellemző a Java-ra. Ugyanezt fogjuk látni PL/pgSQL-ben egytől egyig. De ott reprodukálják.
Próbáljunk meg még néhány adatkiválasztást. Mi választunk és választunk. Van egy millió soros táblázatunk. Minden sor egy kilobájt. Körülbelül egy gigabájt adat. És van egy 128 megabájtos munkamemóriánk a Java gépben.
Mi, mint minden könyvben ajánlott, adatfolyam-feldolgozást használunk. Vagyis megnyitjuk az resultSet-et, és onnan apránként kiolvassuk az adatokat. Működni fog? Kiesik az emlékezetből? Olvasol egy kicsit? Bízzunk az adatbázisban, bízzunk a Postgresben. Nem hisszük el. Kiesünk a memóriából? Ki tapasztalta az OutOfMemory-t? Kinek sikerült ezek után megjavítania? Valakinek sikerült megjavítania.
Ha millió sora van, nem válogathat csak úgy. OFFSET/LIMIT szükséges. Ki támogatja ezt a lehetőséget? És ki támogatja az autoCommit-tal való játékot?
Itt, mint általában, a legváratlanabb lehetőség bizonyul helyesnek. És ha hirtelen kikapcsolja az autoCommitot, az segíteni fog. Miert van az? A tudomány erről nem tud.
De alapértelmezés szerint a Postgres adatbázishoz csatlakozó összes kliens lekéri a teljes adatot. Ez alól a PgJDBC sem kivétel, minden sort kijelöl.
A FetchSize témában van egy variáció, vagyis külön nyilatkozat szintjén mondhatjuk, hogy itt kérjük, 10, 50 értékkel válasszuk ki az adatokat. De ez addig nem működik, amíg ki nem kapcsoljuk az autoCommit funkciót. Az autoCommit kikapcsolva - elkezd működni.
De kényelmetlen a kód áthaladása és a setFetchSize beállítása mindenhol. Ezért olyan beállítást végeztünk, amely a teljes kapcsolat alapértelmezett értékét mondja.
Ezt mondtuk. A paraméter konfigurálva lett. És mit kaptunk? Ha kis összegeket választunk ki, ha például egyszerre 10 sort választunk ki, akkor nagyon nagy rezsiköltségeink vannak. Ezért ezt az értéket körülbelül százra kell állítani.
Ideális esetben persze még meg kell tanulnod, hogyan korlátozhatod bájtokban, de a recept a következő: állítsd a defaultRowFetchSize-t száznál többre, és légy boldog.
Térjünk át az adatok beszúrására. A beillesztés egyszerűbb, különböző lehetőségek vannak. Például INSERT, VALUES. Ez egy jó lehetőség. Kimondhatja, hogy „INSERT SELECT”. A gyakorlatban ez ugyanaz. Teljesítményben nincs különbség.
A könyvek azt mondják, hogy Batch utasítást kell végrehajtani, a könyvek szerint bonyolultabb parancsokat is végrehajthat több zárójellel. És a Postgres rendelkezik egy csodálatos funkcióval – másolhat, azaz gyorsabban.
Ha megméred, ismét érdekes felfedezéseket tehetsz. Hogyan akarjuk, hogy ez működjön? Nem akarjuk elemezni, és nem akarjuk végrehajtani a szükségtelen parancsokat.
A gyakorlatban a TCP ezt nem teszi lehetővé. Ha a kliens egy kérés küldésével van elfoglalva, akkor az adatbázis nem olvassa be a kéréseket, amikor megpróbál választ küldeni nekünk. A végeredmény az, hogy a kliens arra vár, hogy az adatbázis elolvassa a kérést, az adatbázis pedig arra, hogy az ügyfél elolvassa a választ.
Ezért a kliens kénytelen időnként szinkronizáló csomagot küldeni. Extra hálózati interakciók, extra időpazarlás.
És minél többet adjuk hozzá őket, annál rosszabb lesz. A driver meglehetősen pesszimista, és elég gyakran, kb. 200 soronként adja hozzá őket, a sorok méretétől függően stb.
Előfordul, hogy csak egy sort javítasz, és minden 10-szer felgyorsul. Megtörténik. Miért? Szokás szerint egy ilyen állandót már használtak valahol. A „128” érték pedig azt jelentette, hogy ne használjunk kötegelést.
Még jó, hogy ez nem került be a hivatalos verzióba. A megjelenés megkezdése előtt fedezték fel. Minden jelentés, amit megadok, a modern változatokon alapul.
Próbáljuk ki. Az InsertBatch egyszerűt mérjük. Az InsertBatch-et többször mérjük, vagyis ugyanazt, de sok érték van. Trükkös lépés. Nem mindenki tudja ezt megtenni, de ez egy olyan egyszerű lépés, sokkal könnyebb, mint a COPY.
Megteheti a MÁSOLÁST.
És ezt megteheti a szerkezeteken. Deklarálja a felhasználó alapértelmezett típusát, adja át a tömböt és az INSERT-t közvetlenül a táblába.
Ha megnyitja a linket: pgjdbc/ubenchmsrk/InsertBatch.java, akkor ez a kód a GitHubon található. Konkrétan láthatja, hogy milyen kérések jönnek létre ott. Nem számít.
Elindítottuk. És az első dolog, amire rájöttünk, az volt, hogy a kötegelt nem használata egyszerűen lehetetlen. Minden kötegelési opció nulla, azaz a végrehajtási idő gyakorlatilag nulla az egyszeri végrehajtáshoz képest.
Adatokat szúrunk be. Ez egy nagyon egyszerű asztal. Három oszlop. És mit látunk itt? Úgy látjuk, hogy mindhárom lehetőség nagyjából összehasonlítható. És a COPY természetesen jobb.
Ilyenkor darabokat szúrunk be. Amikor azt mondtuk, hogy egy ÉRTÉK, két ÉRTÉK, három ÉRTÉK érték, vagy ezek közül 10-et vesszővel elválasztva jeleztünk. Ez most csak vízszintes. 1, 2, 4, 128. Látható, hogy a kékkel rajzolt Batch Insert sokkal jobban érzi magát. Vagyis ha egyszerre egyet, vagy akár négyet is beszúr, kétszer olyan jó lesz, egyszerűen azért, mert egy kicsit többet zsúfoltunk az ÉRTÉKEKbe. Kevesebb EXECUTE művelet.
A COPY használata kis köteteken rendkívül kilátástalan. Az első kettőből nem is húztam. Mennek a mennyországba, vagyis ezek a zöld számok MÁSOLÁSRA.
A COPY parancsot akkor kell használni, ha legalább száz sornyi adattal rendelkezik. A kapcsolat megnyitásának költsége nagy. És, hogy őszinte legyek, nem ástam ebbe az irányba. A Batch-et optimalizáltam, de a COPY-t nem.
Mi lesz a következő, amit csinálunk? Felpróbáltuk. Megértjük, hogy vagy struktúrákat kell használnunk, vagy egy okos bactot, amely több jelentést egyesít.
Mit érdemes levonni a mai jelentésből?
- A PreparedStatement mindenünk. Ez sokat ad a termelékenységnek. Nagy bukást produkál a kenőcsben.
- És meg kell tennie 6-szor az EXPLAIN ANALYZE-t.
- És fel kell hígítanunk az OFFSET 0-t és a +0-hoz hasonló trükköket, hogy kijavítsuk problémás lekérdezéseink fennmaradó százalékát.
Forrás: will.com