Navrhujem, aby ste si prečítali prepis správy Vladimíra Sitnikova zo začiatku roku 2016 „PostgreSQL a JDBC vytláčajú všetku šťavu“
Dobrý deň Volám sa Vladimír Sitnikov. Pre NetCracker pracujem 10 rokov. A hlavne sa venujem produktivite. Všetko, čo súvisí s Java, všetko, čo súvisí s SQL, je to, čo milujem.
A dnes budem hovoriť o tom, s čím sme sa stretli vo firme, keď sme začali používať PostgreSQL ako databázový server. A väčšinou pracujeme s Javou. Ale to, čo vám dnes poviem, nie je len o Jave. Ako ukázala prax, vyskytuje sa to aj v iných jazykoch.
Budeme hovoriť:
- o vzorkovaní údajov.
- O ukladaní údajov.
- A tiež o výkone.
- A o podvodných hrabloch, ktoré sú tam zahrabané.
Začnime jednoduchou otázkou. Z tabuľky vyberieme jeden riadok na základe primárneho kľúča.
Databáza je umiestnená na rovnakom hostiteľovi. A celé toto farmárčenie trvá 20 milisekúnd.
Týchto 20 milisekúnd je veľa. Ak máte 100 takýchto žiadostí, trávite čas každú sekundu prechádzaním týchto žiadostí, t. j. strácame čas.
Neradi to robíme a pozrime sa, čo nám na to základňa ponúka. Databáza nám ponúka dve možnosti vykonávania dotazov.
Prvou možnosťou je jednoduchá žiadosť. čo je na tom dobré? To, že to vezmeme a pošleme, a nič viac.
Databáza má aj pokročilý dotaz, ktorý je zložitejší, ale funkčnejší. Samostatne môžete odoslať požiadavku na analýzu, vykonanie, variabilné viazanie atď.
Super rozšírený dopyt je niečo, čomu sa v aktuálnom prehľade nebudeme venovať. Možno chceme niečo z databázy a existuje nejaký zoznam želaní, ktorý sa v nejakej forme vytvoril, teda toto chceme, ale teraz a v budúcom roku je to nemožné. Takže sme to len nahrali a budeme otriasať hlavnými ľuďmi.
A čo môžeme urobiť, je jednoduchý dopyt a rozšírený dopyt.
Čo je na každom prístupe špecifické?
Jednoduchý dotaz je vhodný na jednorazové vykonanie. Raz hotové a zabudnuté. A problém je, že nepodporuje binárny dátový formát, teda nie je vhodný pre niektoré výkonné systémy.
Rozšírený dotaz – umožňuje ušetriť čas pri analýze. To je to, čo sme urobili a začali používať. Toto nám naozaj pomohlo. Úspory nie sú len pri analýze. Pri prenose dát dochádza k úsporám. Prenos dát v binárnom formáte je oveľa efektívnejší.
Prejdime k praxi. Takto vyzerá typická aplikácia. Môže to byť Java atď.
Vytvorili sme vyhlásenie. Vykonal príkaz. Vytvorené blízko. Kde je tu chyba? Aký je problém? Žiaden problém. Toto sa píše vo všetkých knihách. Takto by sa to malo písať. Ak chcete maximálny výkon, píšte takto.
Ale prax ukázala, že to nefunguje. prečo? Pretože máme metódu „blízko“. A keď to urobíme, z pohľadu databázy sa ukáže, že je to ako keď fajčiar pracuje s databázou. Povedali sme „PARSE EXECUTE DEALLOCATE“.
Prečo celé to extra vytváranie a vykladanie výpisov? Nikto ich nepotrebuje. Ale to, čo sa zvyčajne stáva v PreparedStatements je, že keď ich zatvoríme, zatvoria všetko v databáze. Toto nie je to, čo chceme.
Chceme, ako zdraví ľudia, pracovať s bazou. Raz sme vzali a pripravili naše vyhlásenie, potom sme ho vykonali mnohokrát. V skutočnosti boli aplikácie analyzované mnohokrát – to je raz za celú životnosť aplikácií. A používame rovnaké ID výpisu na rôznych RESToch. Toto je náš cieľ.
Ako to môžeme dosiahnuť?
Je to veľmi jednoduché – nie je potrebné uzatvárať vyhlásenia. Píšeme to takto: „pripraviť“ „vykonať“.
Ak niečo také spustíme, tak je jasné, že niekde niečo pretečie. Ak to nie je jasné, môžete to vyskúšať. Napíšme benchmark, ktorý používa túto jednoduchú metódu. Vytvorte vyhlásenie. Spustíme ho na nejakej verzii ovládača a zistíme, že sa pomerne rýchlo zrúti so stratou všetkej pamäte, ktorú mal.
Je jasné, že takéto chyby sa dajú ľahko opraviť. Nebudem o nich hovoriť. Ale poviem, že nová verzia funguje oveľa rýchlejšie. Metóda je hlúpa, ale aj tak.
Ako správne pracovať? Čo pre to musíme urobiť?
V skutočnosti aplikácie vždy zatvoria príkazy. Vo všetkých knihách hovoria, že to treba zavrieť, inak pamäť vytečie.
A PostgreSQL nevie, ako ukladať dotazy do vyrovnávacej pamäte. Je potrebné, aby každá relácia vytvorila túto vyrovnávaciu pamäť pre seba.
A tiež nechceme strácať čas analýzou.
A ako obvykle máme dve možnosti.
Prvá možnosť je, že to vezmeme a povieme, že všetko zabalíme do PgSQL. Je tam keška. Ukladá všetko do vyrovnávacej pamäte. Dopadne to skvele. Toto sme videli. Máme 100500 XNUMX žiadostí. Nefunguje. Nesúhlasíme s manuálnym premieňaním žiadostí na procedúry. Nie nie.
Máme druhú možnosť – vezmite si to a nakrájajte si to sami. Otvoríme pramene a začneme rezať. Videli sme a videli. Ukázalo sa, že to nie je také ťažké.
Toto sa objavilo v auguste 2015. Teraz existuje modernejšia verzia. A všetko je skvelé. Funguje to tak dobre, že v aplikácii nič nemeníme. A dokonca sme prestali uvažovať smerom k PgSQL, t. j. toto nám celkom stačilo na zníženie všetkých režijných nákladov takmer na nulu.
V súlade s tým sa príkazy pripravené serverom aktivujú pri 5. vykonaní, aby sa predišlo plytvaniu pamäťou v databáze pri každej jednorazovej požiadavke.
Môžete sa opýtať - kde sú čísla? čo dostaneš? A tu nebudem uvádzať čísla, pretože každá požiadavka má svoje vlastné.
Naše dopyty boli také, že sme strávili asi 20 milisekúnd analýzou dopytov OLTP. Na vykonanie bolo 0,5 milisekúnd, na analýzu 20 milisekúnd. Požiadavka – 10 KiB textu, 170 riadkov plánu. Toto je požiadavka OLTP. Vyžaduje 1, 5, 10 riadkov, niekedy aj viac.
Ale vôbec sme nechceli premrhať 20 milisekúnd. Znížili sme to na 0. Všetko je skvelé.
Čo si odtiaľto môžete odniesť? Ak máte Javu, vezmite si modernú verziu ovládača a radujte sa.
Ak hovoríte iným jazykom, pomyslite si - možno to tiež potrebujete? Pretože napríklad z pohľadu finálneho jazyka, ak máte PL 8 alebo máte LibPQ, tak vám nie je zrejmé, že trávite čas nie vykonávaním, parsovaním, a to sa oplatí skontrolovať. Ako? Všetko je zadarmo.
Až na to, že sú tam chyby a nejaké zvláštnosti. A práve o nich budeme hovoriť. Väčšina z toho bude o priemyselnej archeológii, o tom, čo sme našli, na čo sme narazili.
Ak je požiadavka generovaná dynamicky. To sa stáva. Niekto zlepí reťazce dohromady, čo vedie k dotazu SQL.
Prečo je zlý? Je to zlé, pretože zakaždým skončíme s iným reťazcom.
A hashCode tohto odlišného reťazca je potrebné prečítať znova. Toto je skutočne úloha CPU - nájsť dlhý text požiadavky v existujúcom hashove nie je také jednoduché. Preto je záver jednoduchý – negenerovať požiadavky. Uložte ich do jednej premennej. A radujte sa.
Ďalší problém. Typy údajov sú dôležité. Existujú ORM, ktoré hovoria, že nezáleží na tom, aký druh NULL existuje, nech je nejaký. Ak Int, potom povieme setInt. A ak NULL, tak nech je to vždy VARCHAR. A aký je rozdiel v tom, čo je tam NULL? Samotná databáza bude všetkému rozumieť. A tento obrázok nefunguje.
V praxi je to databáze úplne jedno. Ak ste prvýkrát povedali, že toto je číslo, a druhýkrát ste povedali, že ide o VARCHAR, potom nie je možné znova použiť príkazy pripravené serverom. A v tomto prípade musíme naše vyhlásenie znovu vytvoriť.
Ak vykonávate rovnaký dotaz, uistite sa, že typy údajov vo vašom stĺpci nie sú zamieňané. Treba si dávať pozor na NULL. Toto je bežná chyba, ktorú sme mali, keď sme začali používať PreparedStatements
Dobre, zapnuté. Možno zobrali šoféra. A produktivita klesla. Veci sa zhoršili.
Ako sa to stane? Je to chyba alebo funkcia? Bohužiaľ nebolo možné pochopiť, či ide o chybu alebo funkciu. Existuje však veľmi jednoduchý scenár na reprodukovanie tohto problému. Úplne nečakane nás prepadla. A spočíva v odbere vzoriek doslova z jedného stola. Takýchto požiadaviek sme mali, samozrejme, viac. Spravidla zahŕňali dva alebo tri stoly, ale existuje aj takýto scenár prehrávania. Vezmite si akúkoľvek verziu z databázy a zahrajte si ju.
Ide o to, že máme dva stĺpce, z ktorých každý je indexovaný. V jednom stĺpci NULL je milión riadkov. A druhý stĺpec obsahuje iba 20 riadkov. Keď vykonávame bez viazaných premenných, všetko funguje dobre.
Ak začneme vykonávať s viazanými premennými, t. j. spustíme "?" alebo „1 dolár“ za našu požiadavku, čo nakoniec dostaneme?
Prvá realizácia je podľa očakávania. Druhý je o niečo rýchlejší. Niečo bolo uložené do vyrovnávacej pamäte. Tretí, štvrtý, piaty. Potom buch - a niečo také. A najhoršie je, že sa to stane pri šiestej poprave. Kto vedel, že je potrebné urobiť presne šesť popráv, aby sme pochopili, aký je skutočný plán popravy?
Kto je vinný? Čo sa stalo? Databáza obsahuje optimalizáciu. A zdá sa, že je optimalizovaný pre všeobecný prípad. A preto, počnúc v určitom okamihu, prejde na všeobecný plán, ktorý sa, žiaľ, môže ukázať ako iný. Môže sa ukázať, že je to rovnaké, alebo to môže byť iné. A existuje nejaký druh prahovej hodnoty, ktorá vedie k tomuto správaniu.
Čo s tým môžete urobiť? Tu je, samozrejme, ťažšie čokoľvek predpokladať. Existuje jednoduché riešenie, ktoré používame. Toto je +0, OFFSET 0. Určite poznáte takéto riešenia. Len to vezmeme a k žiadosti pridáme „+0“ a všetko je v poriadku. Ukážem ti to neskôr.
A je tu ešte jedna možnosť – pozornejšie si prezerať plány. Vývojár musí nielen napísať žiadosť, ale aj šesťkrát povedať „vysvetliť analýzu“. Ak je 6, nebude to fungovať.
A je tu aj tretia možnosť – napísať list pgsql-hackerom. Napísal som, ale zatiaľ nie je jasné, či ide o chybu alebo funkciu.
Zatiaľ čo uvažujeme, či ide o chybu alebo funkciu, poďme to opraviť. Zoberme si našu požiadavku a pridáme „+0“. Všetko je v poriadku. Dva symboly a nemusíte ani premýšľať o tom, aké to je alebo čo to je. Veľmi jednoduché. Jednoducho sme databáze zakázali používať index v tomto stĺpci. V stĺpci „+0“ nemáme index a to je všetko, databáza index nepoužíva, všetko je v poriadku.
Toto je pravidlo 6 vysvetliť. Teraz v aktuálnych verziách to musíte urobiť 6-krát, ak máte viazané premenné. Ak nemáte viazané premenné, robíme to my. A nakoniec je to práve táto požiadavka, ktorá zlyhá. Nie je to nič zložité.
Zdalo by sa, koľko je možné? Chyba sem, chyba tam. V skutočnosti je chyba všade.
Poďme sa na to pozrieť bližšie. Napríklad máme dve schémy. Schéma A s tabuľkou S a schéma B s tabuľkou S. Dopyt – výber údajov z tabuľky. Čo budeme mať v tomto prípade? Budeme mať chybu. Budeme mať všetko vyššie uvedené. Platí pravidlo - chyba je všade, budeme mať všetko vyššie uvedené.
Teraz otázka znie: "Prečo?" Zdá sa, že existuje dokumentácia, že ak máme schému, potom existuje premenná „search_path“, ktorá nám hovorí, kde máme tabuľku hľadať. Zdalo by sa, že existuje premenná.
Aký je problém? Problém je v tom, že príkazy pripravené serverom nemajú podozrenie, že search_path môže niekto zmeniť. Táto hodnota zostáva pre databázu akoby konštantná. A niektoré časti nemusia získať nový význam.
Samozrejme to závisí od verzie, ktorú testujete. Závisí to od toho, ako vážne sa líšia vaše tabuľky. A verzia 9.1 jednoducho vykoná staré požiadavky. Nové verzie môžu zachytiť chybu a oznámiť vám, že máte chybu.
Ako to liečiť? Existuje jednoduchý recept - nerobte to. Keď je aplikácia spustená, nie je potrebné meniť search_path. Ak zmeníte, je lepšie vytvoriť nové pripojenie.
Môžete diskutovať, teda otvárať, diskutovať, pridávať. Možno sa nám podarí presvedčiť vývojárov databázy, že keď niekto zmení hodnotu, databáza by o tom mala klientovi povedať: „Pozri, tu bola aktualizovaná vaša hodnota. Možno budete musieť resetovať vyhlásenia a znova ich vytvoriť?" Teraz sa databáza chová skryto a nijako nehlási, že by sa výpisy niekde vo vnútri zmenili.
A ešte raz zdôrazním – to je niečo, čo nie je pre Javu typické. To isté uvidíme v PL/pgSQL jeden k jednému. Ale bude sa tam reprodukovať.
Skúsme ešte výber údajov. Vyberáme a vyberáme. Máme tabuľku s miliónom riadkov. Každý riadok je kilobajt. Približne gigabajt dát. A máme operačnú pamäť v stroji Java 128 megabajtov.
Ako sa odporúča vo všetkých knihách, používame spracovanie prúdov. To znamená, že otvoríme sadu výsledkov a postupne odtiaľ načítame údaje. Bude to fungovať? Vypadne z pamäti? Budete trochu čítať? Dôverujme databáze, dôverujme Postgresu. Neveríme tomu. Vypadneme z pamäte? Kto zažil OutOfMemory? Komu sa to potom podarilo opraviť? Niekomu sa to podarilo opraviť.
Ak máte milión riadkov, nemôžete si len vyberať. Vyžaduje sa OFFSET/LIMIT. Kto je za túto možnosť? A kto je za hru s autoCommit?
Tu sa ako obvykle ukáže ako správna najneočakávanejšia možnosť. A ak náhle vypnete autoCommit, pomôže to. prečo je to tak? Veda o tom nevie.
V predvolenom nastavení však všetci klienti, ktorí sa pripájajú k databáze Postgres, načítajú všetky údaje. PgJDBC nie je v tomto ohľade výnimkou, vyberie všetky riadky.
Existuje variácia témy FetchSize, t. j. na úrovni samostatného vyhlásenia môžete povedať, že tu, prosím, vyberte údaje o 10, 50. Toto však nebude fungovať, kým nevypnete funkciu autoCommit. Vypnutý autoCommit - začne fungovať.
Ale prejsť kód a nastaviť setFetchSize všade je nepohodlné. Preto sme urobili nastavenie, ktoré povie predvolenú hodnotu pre celé pripojenie.
To sme si povedali. Parameter bol nakonfigurovaný. A čo sme dostali? Ak vyberieme malé sumy, ak napríklad vyberieme 10 riadkov naraz, potom máme veľmi veľké režijné náklady. Preto by mala byť táto hodnota nastavená na približne sto.
V ideálnom prípade sa, samozrejme, stále musíte naučiť, ako to obmedziť v bajtoch, ale recept je takýto: nastavte defaultRowFetchSize na viac ako sto a buďte spokojní.
Prejdime k vkladaniu údajov. Vkladanie je jednoduchšie, existujú rôzne možnosti. Napríklad INSERT, VALUES. Toto je dobrá možnosť. Môžete povedať „INSERT SELECT“. V praxi je to to isté. Vo výkone nie je rozdiel.
Knihy hovoria, že musíte vykonať príkaz Batch, knihy hovoria, že môžete vykonávať zložitejšie príkazy s niekoľkými zátvorkami. A Postgres má skvelú funkciu - môžete robiť KOPÍROVANIE, t. j. robiť to rýchlejšie.
Ak to zmeriate, môžete opäť urobiť niekoľko zaujímavých objavov. Ako chceme, aby to fungovalo? Nechceme analyzovať a nevykonávať zbytočné príkazy.
V praxi nám to TCP neumožňuje. Ak je klient zaneprázdnený odosielaním požiadavky, potom databáza nečíta požiadavky pri pokusoch o odoslanie odpovedí. Konečným výsledkom je, že klient čaká, kým databáza prečíta požiadavku, a databáza čaká, kým klient prečíta odpoveď.
A preto je klient nútený pravidelne posielať synchronizačný paket. Ďalšie sieťové interakcie, ďalšia strata času.
A čím viac ich pridávame, tým je to horšie. Vodič je dosť pesimistický a pridáva ich pomerne často, cca raz za 200 riadkov, podľa veľkosti riadkov atď.
Stáva sa, že opravíte len jeden riadok a všetko sa 10x zrýchli. To sa stáva. prečo? Ako to už býva, taká konštanta už niekde bola použitá. A hodnota „128“ znamenala nepoužívať dávkovanie.
Je dobré, že to nebolo zahrnuté v oficiálnej verzii. Objavené pred začiatkom vydania. Všetky významy, ktoré uvádzam, sú založené na moderných verziách.
Poďme si to vyskúšať. InsertBatch meriame jednoducho. InsertBatch meriame viackrát, teda to isté, no hodnôt je veľa. Ťažký ťah. Nie každý to dokáže, ale je to taký jednoduchý krok, oveľa jednoduchší ako COPY.
Môžete urobiť KOPÍROVANIE.
A môžete to urobiť na štruktúrach. Deklarujte predvolený typ používateľa, odovzdajte pole a INSERT priamo do tabuľky.
Ak otvoríte odkaz: pgjdbc/ubenchmsrk/InsertBatch.java, potom je tento kód na GitHub. Môžete vidieť konkrétne, aké požiadavky sa tam generujú. To je jedno.
Spustili sme. A prvá vec, ktorú sme si uvedomili, bolo, že nepoužívanie dávky je jednoducho nemožné. Všetky možnosti dávkovania sú nulové, t.j. čas vykonania je prakticky nulový v porovnaní s jednorazovým vykonaním.
Vložíme údaje. Je to veľmi jednoduchý stôl. Tri stĺpce. A čo tu vidíme? Vidíme, že všetky tieto tri možnosti sú zhruba porovnateľné. A COPY je, samozrejme, lepšia.
To je, keď vkladáme kúsky. Keď sme povedali, že jedna hodnota VALUES, dve hodnoty VALUES, tri hodnoty VALUES, alebo sme ich označili 10 oddelenými čiarkou. Toto je teraz len horizontálne. 1, 2, 4, 128. Vidno, že vďaka Modrej vložke sa cíti oveľa lepšie. To znamená, že keď vložíte jeden po druhom alebo dokonca aj štyri naraz, bude to dvakrát lepšie, jednoducho preto, že sme do HODNOT napchali trochu viac. Menej operácií EXECUTE.
Používanie COPY na malých objemoch je mimoriadne neperspektívne. Na prvých dvoch som ani nekreslil. Idú do neba, teda tieto zelené čísla pre COPY.
COPY by ste mali použiť, ak máte aspoň sto riadkov údajov. Réžia otvorenia tohto spojenia je veľká. A aby som bol úprimný, týmto smerom som nekopal. Optimalizoval som Batch, ale nie COPY.
čo budeme robiť ďalej? Vyskúšali sme si to. Chápeme, že musíme použiť buď štruktúry, alebo šikovné bacth, ktorý kombinuje niekoľko významov.
Čo by ste si mali odniesť z dnešnej reportáže?
- PreparedStatement je naše všetko. To dáva veľa pre produktivitu. V masti to spôsobí veľký prepadák.
- A musíte vykonať EXPLAIN ANALYZE 6-krát.
- A musíme rozriediť OFFSET 0 a triky ako +0, aby sme opravili zostávajúce percento našich problematických dopytov.
Zdroj: hab.com