Ég mæli með að þú lesir afritið af skýrslu Vladimir Sitnikov snemma árs 2016 „PostgreSQL og JDBC kreista út allan safa“
Góðan daginn Ég heiti Vladimir Sitnikov. Ég hef unnið hjá NetCracker í 10 ár. Og ég er aðallega í framleiðni. Allt sem tengist Java, allt sem tengist SQL er það sem ég elska.
Og í dag mun ég tala um það sem við lentum í í fyrirtækinu þegar við byrjuðum að nota PostgreSQL sem gagnagrunnsþjón. Og við vinnum aðallega með Java. En það sem ég ætla að segja þér í dag er ekki bara um Java. Eins og æfingin hefur sýnt, gerist þetta líka á öðrum tungumálum.
Við munum tala saman:
- um gagnaúrtak.
- Um vistun gagna.
- Og líka um frammistöðu.
- Og um neðansjávarhrífurnar sem þar eru grafnar.
Við skulum byrja á einfaldri spurningu. Við veljum eina línu úr töflunni út frá aðallyklinum.
Gagnagrunnurinn er staðsettur á sama vélinni. Og allur þessi búskapur tekur 20 millisekúndur.
Þessar 20 millisekúndur eru mikið. Ef þú ert með 100 slíkar beiðnir, þá eyðirðu tíma á sekúndu í að fletta í gegnum þessar beiðnir, þ.e.a.s. við erum að sóa tíma.
Okkur líkar ekki að gera þetta og skoðum hvað grunnurinn býður okkur fyrir þetta. Gagnagrunnurinn býður okkur upp á tvo möguleika til að framkvæma fyrirspurnir.
Fyrsti kosturinn er einföld beiðni. Hvað er gott við það? Sú staðreynd að við tökum það og sendum það, og ekkert meira.
Gagnagrunnurinn hefur einnig háþróaða fyrirspurn, sem er erfiðari, en virkari. Þú getur sérstaklega sent beiðni um þáttun, framkvæmd, breytubindingu osfrv.
Ofur útbreidd fyrirspurn er eitthvað sem við munum ekki fjalla um í núverandi skýrslu. Við viljum kannski eitthvað úr gagnagrunninum og það er óskalisti sem hefur myndast í einhverri mynd, þ.e.a.s. þetta er það sem við viljum, en það er ómögulegt núna og á næsta ári. Svo við tókum það bara upp og við munum fara um og hrista aðalfólkið.
Og það sem við getum gert er einföld fyrirspurn og víðtæk fyrirspurn.
Hvað er sérstakt við hverja nálgun?
Einföld fyrirspurn er góð fyrir framkvæmd í eitt skipti. Einu sinni búið og gleymt. Og vandamálið er að það styður ekki tvöfalda gagnasniðið, þ.e. það hentar ekki fyrir sum afkastamikil kerfi.
Ítarleg fyrirspurn – gerir þér kleift að spara tíma við þáttun. Þetta er það sem við gerðum og byrjuðum að nota. Þetta hjálpaði okkur virkilega. Það er ekki aðeins sparnaður við þáttun. Það er sparnaður við gagnaflutning. Flutningur gagna á tvíundarsniði er mun skilvirkari.
Við skulum halda áfram að æfa. Svona lítur dæmigerð forrit út. Það gæti verið Java osfrv.
Við bjuggum til yfirlýsingu. Framkvæmdi skipunina. Búið til nálægt. Hvar eru mistökin hér? Hvað er vandamálið? Ekkert mál. Þetta er það sem stendur í öllum bókunum. Svona á þetta að vera skrifað. Ef þú vilt hámarksafköst, skrifaðu svona.
En æfingin hefur sýnt að þetta gengur ekki. Hvers vegna? Vegna þess að við höfum "nálæga" aðferð. Og þegar við gerum þetta, frá gagnagrunnssjónarmiði kemur í ljós að þetta er eins og reykingamaður sem vinnur með gagnagrunn. Við sögðum „RÁÐAÐU FRAMKVÆMA ÚTVERKJA“.
Hvers vegna allt þetta auka sköpun og affermingu á yfirlýsingum? Enginn þarfnast þeirra. En það sem gerist venjulega í PreparedStatements er að þegar við lokum þeim loka þeir öllu í gagnagrunninum. Þetta er ekki það sem við viljum.
Við viljum, eins og heilbrigt fólk, vinna með grunninn. Við tókum og undirbjuggum yfirlýsinguna okkar einu sinni, síðan framkvæmum við hana mörgum sinnum. Reyndar, oft - þetta er einu sinni á öllu lífi umsókna - hafa þær verið flokkaðar. Og við notum sama staðhæfingarauðkenni á mismunandi REST. Þetta er markmið okkar.
Hvernig getum við náð þessu?
Það er mjög einfalt - engin þörf á að loka yfirlýsingar. Við skrifum það svona: „undirbúa“ „framkvæma“.
Ef við ræsum eitthvað svona, þá er ljóst að eitthvað mun flæða yfir einhvers staðar. Ef það er ekki ljóst geturðu prófað það. Skrifum viðmið sem notar þessa einföldu aðferð. Búðu til yfirlýsingu. Við ræsum það á einhverri útgáfu af bílstjóranum og komumst að því að það hrynur nokkuð hratt með tapi á öllu minni sem það hafði.
Það er ljóst að slíkar villur eru auðveldlega leiðréttar. Ég ætla ekki að tala um þá. En ég mun segja að nýja útgáfan virkar miklu hraðar. Aðferðin er heimskuleg, en samt.
Hvernig á að vinna rétt? Hvað þurfum við að gera fyrir þetta?
Í raun loka umsóknum alltaf yfirlýsingum. Í öllum bókum er sagt að loka því, annars mun minnið leka.
Og PostgreSQL veit ekki hvernig á að vista fyrirspurnir. Það er nauðsynlegt að hver lota búi til þetta skyndiminni fyrir sig.
Og við viljum heldur ekki eyða tíma í þáttun.
Og eins og venjulega höfum við tvo valkosti.
Fyrsti kosturinn er að við tökum það og segjum að við skulum pakka öllu inn í PgSQL. Þar er skyndiminni. Það geymir allt. Það mun reynast frábært. Við sáum þetta. Við höfum 100500 beiðnir. Virkar ekki. Við samþykkjum ekki að breyta beiðnum í verklag með höndunum. Nei nei.
Við höfum annan valkost - taktu það og klipptu það sjálf. Við opnum heimildirnar og byrjum að klippa. Við sáum og sáum. Það kom í ljós að það er ekki svo erfitt að gera.
Þetta birtist í ágúst 2015. Nú er komin nútímalegri útgáfa. Og allt er frábært. Það virkar svo vel að við breytum engu í umsókninni. Og við hættum meira að segja að hugsa í áttina að PgSQL, þ.e.a.s. þetta var alveg nóg fyrir okkur til að minnka allan kostnaðarkostnað niður í næstum núll.
Í samræmi við það eru tilbúnar yfirlýsingar miðlara virkjaðar við 5. framkvæmd til að forðast að sóa minni í gagnagrunninum við hverja einskiptisbeiðni.
Þú gætir spurt - hvar eru tölurnar? Hvað ertu að fá? Og hér mun ég ekki gefa upp tölur, því hver beiðni hefur sína eigin.
Fyrirspurnir okkar voru þannig að við eyddum um 20 millisekúndum í þáttun á OLTP fyrirspurnum. Það voru 0,5 millisekúndur fyrir framkvæmd, 20 millisekúndur fyrir þáttun. Beiðni - 10 KiB af texta, 170 línur af áætlun. Þetta er OLTP beiðni. Það biður um 1, 5, 10 línur, stundum fleiri.
En við vildum alls ekki eyða 20 millisekúndum. Við lækkuðum það niður í 0. Allt frábært.
Hvað geturðu tekið með þér héðan? Ef þú ert með Java, þá tekurðu nútímaútgáfuna af bílstjóranum og fagnar.
Ef þú talar annað tungumál, hugsaðu þá - kannski þarftu þetta líka? Vegna þess að frá sjónarhóli lokamálsins, til dæmis, ef PL 8 eða þú ert með LibPQ, þá er það ekki augljóst fyrir þig að þú eyðir tíma ekki í framkvæmd, í þáttun, og þetta er þess virði að athuga. Hvernig? Allt er ókeypis.
Nema að það eru villur og einhver sérkenni. Og við munum tala um þau núna. Mest mun það fjalla um iðnaðarfornleifafræði, um það sem við fundum, hvað við komumst að.
Ef beiðnin er mynduð á kraftmikinn hátt. Það gerist. Einhver límir strengina saman, sem leiðir til SQL fyrirspurn.
Af hverju er hann vondur? Það er slæmt því í hvert skipti endum við með annan streng.
Og hashCode þessa mismunandi strengs þarf að lesa aftur. Þetta er í raun CPU verkefni - það er ekki svo auðvelt að finna langan beiðnitexta í jafnvel núverandi kjötkássa. Þess vegna er niðurstaðan einföld - ekki búa til beiðnir. Geymdu þær í einni breytu. Og gleðjast.
Næsta vandamál. Gagnategundir eru mikilvægar. Það eru ORM sem segja að það skipti ekki máli hvers konar NULL það er, láttu það vera einhvers konar. Ef Int, þá segjum við setInt. Og ef NULL, þá láttu það alltaf vera VARCHAR. Og hvaða munur skiptir það á endanum hvaða NULL er þarna? Gagnagrunnurinn sjálfur mun skilja allt. Og þessi mynd virkar ekki.
Í reynd er gagnagrunninum alveg sama. Ef þú sagðir í fyrsta skiptið að þetta væri númer og í seinna skiptið sem þú sagðir að þetta væri VARCHAR, þá er ómögulegt að endurnýta yfirlýsingar sem eru undirbúnar fyrir netþjón. Og í þessu tilfelli verðum við að endurskapa yfirlýsingu okkar.
Ef þú ert að framkvæma sömu fyrirspurn, vertu viss um að gagnategundirnar í dálknum þínum séu ekki ruglaðar. Þú þarft að passa þig á NULL. Þetta er algeng villa sem við fengum eftir að við byrjuðum að nota PreparedStatements
Allt í lagi, kveikt á. Kannski tóku þeir bílstjórann. Og framleiðni minnkaði. Það fór illa.
Hvernig gerist þetta? Er þetta galli eða eiginleiki? Því miður var ekki hægt að skilja hvort þetta er galli eða eiginleiki. En það er mjög einföld atburðarás til að endurskapa þetta vandamál. Hún lagði algjörlega óvænt fyrirsát á okkur. Og það samanstendur af sýnatöku bókstaflega úr einni töflu. Við fengum að sjálfsögðu fleiri slíkar beiðnir. Að jafnaði innihéldu þau tvö eða þrjú borð, en það er slík spilunaratburðarás. Taktu hvaða útgáfu sem er úr gagnagrunninum þínum og spilaðu hana.
Málið er að við höfum tvo dálka sem hver um sig er verðtryggður. Það eru milljón raðir í einum NULL dálki. Og annar dálkurinn inniheldur aðeins 20 línur. Þegar við keyrum án bundinna breyta virkar allt vel.
Ef við byrjum að keyra með bundnum breytum, þ.e.a.s. við keyrum "?" eða „$1“ fyrir beiðni okkar, hvað fáum við á endanum?
Fyrsta framkvæmdin er eins og búist var við. Sá seinni er aðeins hraðari. Eitthvað var í skyndiminni. Þriðja, fjórða, fimmta. Svo bang - og eitthvað svoleiðis. Og það versta er að þetta gerist við sjöttu aftökuna. Hver vissi að það væri nauðsynlegt að framkvæma nákvæmlega sex aftökur til að skilja hver hin raunverulega aftökuáætlun væri?
Hver er sekur? Hvað gerðist? Gagnagrunnurinn inniheldur hagræðingu. Og það virðist vera fínstillt fyrir almenna málið. Og í samræmi við það, byrjar hún á einhverjum tímapunkti, skiptir hún yfir í almenna áætlun, sem því miður getur reynst öðruvísi. Það getur reynst það sama, eða það getur verið öðruvísi. Og það er einhvers konar þröskuldsgildi sem leiðir til þessarar hegðunar.
Hvað getur þú gert í því? Hér er auðvitað erfiðara að gera ráð fyrir einhverju. Það er einföld lausn sem við notum. Þetta er +0, OFFSET 0. Þú veist örugglega svona lausnir. Við tökum það bara og bætum „+0“ við beiðnina og allt er í lagi. Ég skal sýna þér það seinna.
Og það er annar valkostur - skoðaðu áætlanirnar betur. Framkvæmdaraðilinn verður ekki aðeins að skrifa beiðni heldur einnig segja „útskýra greiningu“ 6 sinnum. Ef það er 5, þá virkar það ekki.
Og það er þriðji valkosturinn - skrifaðu bréf til pgsql-hakkara. Ég skrifaði, hins vegar er ekki enn ljóst hvort þetta er galli eða eiginleiki.
Á meðan við erum að hugsa hvort þetta sé galla eða eiginleiki, skulum við laga það. Við skulum taka beiðni okkar og bæta við "+0". Allt er í lagi. Tvö tákn og þú þarft ekki einu sinni að hugsa um hvernig það er eða hvað það er. Mjög einfalt. Við einfaldlega bönnuðum gagnagrunninum að nota vísitölu á þennan dálk. Við erum ekki með vísitölu á „+0“ dálknum og það er það, gagnagrunnurinn notar ekki vísitöluna, allt er í lagi.
Þetta er reglan um 6 útskýrðu. Nú í núverandi útgáfum þarftu að gera það 6 sinnum ef þú ert með bundnar breytur. Ef þú ert ekki með bundnar breytur, þá er þetta það sem við gerum. Og á endanum er það einmitt þessi beiðni sem mistekst. Það er ekki erfiður hlutur.
Það virðist, hversu mikið er mögulegt? Galla hér, galla þar. Reyndar er gallinn alls staðar.
Við skulum skoða nánar. Til dæmis höfum við tvö skema. Skema A með töflu S og skýringarmynd B með töflu S. Fyrirspurn – veldu gögn úr töflu. Hvað munum við hafa í þessu tilfelli? Við munum hafa villu. Við munum hafa allt ofangreint. Reglan er - galla er alls staðar, við munum hafa allt ofangreint.
Nú er spurningin: "Af hverju?" Það virðist vera til gögn um að ef við erum með skema, þá er "search_path" breyta sem segir okkur hvar við eigum að leita að töflunni. Það virðist vera breytilegt.
Hvað er vandamálið? Vandamálið er að setningar sem eru undirbúnar á netþjóni grunar ekki að einhver geti breytt search_path. Þetta gildi helst sem sagt stöðugt fyrir gagnagrunninn. Og sumir hlutar geta ekki tekið upp nýja merkingu.
Þetta fer auðvitað eftir útgáfunni sem þú ert að prófa á. Fer eftir því hversu alvarlega töflurnar þínar eru mismunandi. Og útgáfa 9.1 mun einfaldlega framkvæma gömlu beiðnirnar. Nýjar útgáfur gætu lent í villunni og sagt þér að þú sért með villu.
Hvernig á að meðhöndla það? Það er einföld uppskrift - ekki gera það. Það er engin þörf á að breyta search_path á meðan forritið er í gangi. Ef þú breytir er betra að búa til nýja tengingu.
Þú getur rætt, þ.e.a.s. opnað, rætt, bætt við. Kannski getum við sannfært gagnagrunnshönnuði um að þegar einhver breytir gildi ætti gagnagrunnurinn að segja viðskiptavininum frá þessu: „Sjáðu, gildið þitt hefur verið uppfært hér. Kannski þarftu að endurstilla staðhæfingarnar og endurskapa þær?“ Nú hagar gagnagrunnurinn sér leynilega og segir ekki á nokkurn hátt að staðhæfingarnar hafi breyst einhvers staðar inni.
Og ég mun leggja áherslu á aftur - þetta er eitthvað sem er ekki dæmigert fyrir Java. Við munum sjá það sama í PL/pgSQL einn á móti einum. En það verður afritað þar.
Við skulum reyna meira gagnaval. Við veljum og veljum. Við erum með töflu með milljón línum. Hver lína er kílóbæti. Um það bil gígabæt af gögnum. Og við erum með vinnsluminni í Java vélinni sem er 128 megabæti.
Við, eins og mælt er með í öllum bókum, notum straumvinnslu. Það er, við opnum resultSet og lesum gögnin þaðan smátt og smátt. Mun það virka? Mun það falla úr minni? Ætlarðu að lesa smá? Treystum á gagnagrunninn, við skulum treysta á Postgres. Við trúum því ekki. Munum við falla úr minni? Hver upplifði OutOfMemory? Hver náði að laga það eftir það? Einhver tókst að laga það.
Ef þú ert með milljón raðir geturðu ekki bara valið og valið. OFFSET/LIMIT er krafist. Hver er fyrir þennan valkost? Og hver er hlynntur því að spila með autoCommit?
Hér, eins og venjulega, reynist óvæntasti kosturinn réttur. Og ef þú slekkur skyndilega á autoCommit mun það hjálpa. Afhverju er það? Vísindin vita ekki um þetta.
En sjálfgefið, allir viðskiptavinir sem tengjast Postgres gagnagrunni sækja öll gögnin. PgJDBC er engin undantekning í þessu sambandi; það velur allar línur.
Það er til afbrigði af FetchSize þemað, þ.e. þú getur sagt á stigi sérstakra staðhæfingar að hér, vinsamlegast veldu gögn með 10, 50. En þetta virkar ekki fyrr en þú slekkur á autoCommit. Slökkt á autoCommit - það byrjar að virka.
En að fara í gegnum kóðann og stilla setFetchSize alls staðar er óþægilegt. Þess vegna gerðum við stillingu sem mun segja sjálfgefið gildi fyrir alla tenginguna.
Það er það sem við sögðum. Færibreytan hefur verið stillt. Og hvað fengum við? Ef við veljum litlar upphæðir, ef við veljum til dæmis 10 línur í einu, þá erum við með mjög háan kostnaðarkostnað. Þess vegna ætti þetta gildi að vera stillt á um hundrað.
Helst þarftu auðvitað enn að læra hvernig á að takmarka það í bætum, en uppskriftin er þessi: stilltu defaultRowFetchSize á meira en hundrað og vertu ánægður.
Við skulum halda áfram að setja inn gögn. Innsetning er auðveldari, það eru mismunandi valkostir. Til dæmis, INSERT, VALUES. Þetta er góður kostur. Þú getur sagt „INSERT SELECT“. Í reynd er það sami hluturinn. Það er enginn munur á frammistöðu.
Bækur segja að þú þurfir að framkvæma Batch yfirlýsingu, bækur segja að þú getur framkvæmt flóknari skipanir með nokkrum svigum. Og Postgres hefur frábæran eiginleika - þú getur gert COPY, þ.e. gert það hraðar.
Ef þú mælir það geturðu aftur gert nokkrar áhugaverðar uppgötvanir. Hvernig viljum við að þetta virki? Við viljum ekki flokka og ekki framkvæma óþarfa skipanir.
Í reynd leyfir TCP okkur ekki að gera þetta. Ef viðskiptavinurinn er upptekinn við að senda beiðni, þá les gagnagrunnurinn ekki beiðnirnar í tilraunum til að senda okkur svör. Lokaniðurstaðan er sú að viðskiptavinurinn bíður eftir að gagnagrunnurinn lesi beiðnina og gagnagrunnurinn bíður eftir að viðskiptavinurinn lesi svarið.
Og þess vegna neyðist viðskiptavinurinn til að senda samstillingarpakka reglulega. Auka netsamskipti, auka tímasóun.
Og því meira sem við bætum þeim við, því verra verður það. Bílstjórinn er frekar svartsýnn og bætir þeim nokkuð oft við, svona einu sinni á 200 línum, allt eftir stærð línanna o.s.frv.
Það gerist að þú leiðréttir bara eina línu og allt flýtir 10 sinnum. Það gerist. Hvers vegna? Eins og venjulega hefur svona fasti þegar verið notaður einhvers staðar. Og gildið „128“ þýddi að nota ekki lotur.
Það er gott að þetta var ekki innifalið í opinberu útgáfunni. Uppgötvuð áður en útgáfan hófst. Allar merkingar sem ég gef eru byggðar á nútímaútgáfum.
Við skulum reyna það. Við mælum InsertBatch einfalt. Við mælum InsertBatch mörgum sinnum, þ.e.a.s. það sama, en það eru mörg gildi. Vandræðaleg hreyfing. Það geta ekki allir gert þetta, en þetta er svo einfalt skref, miklu auðveldara en COPY.
Þú getur gert COPY.
Og þú getur gert þetta á mannvirkjum. Lýstu yfir sjálfgefna tegund notanda, sendu fylki og INSERT beint í töfluna.
Ef þú opnar hlekkinn: pgjdbc/ubenchmsrk/InsertBatch.java, þá er þessi kóði á GitHub. Þú getur séð sérstaklega hvaða beiðnir eru búnar til þar. Það skiptir ekki máli.
Við settum af stað. Og það fyrsta sem við áttuðum okkur á var að það er einfaldlega ómögulegt að nota lotu. Allir lotuvalkostir eru núll, þ.e. framkvæmdartíminn er nánast núll miðað við einstaka framkvæmd.
Við setjum inn gögn. Þetta er mjög einfalt borð. Þrír dálkar. Og hvað sjáum við hér? Við sjáum að allir þessir þrír valkostir eru nokkurn veginn sambærilegir. Og COPY er auðvitað betra.
Þetta er þegar við setjum inn stykki. Þegar við sögðum að eitt VALUES gildi, tvö VALUES gildi, þrjú VALUES gildi, eða við bentum á 10 þeirra aðskilin með kommu. Þetta er nú bara lárétt. 1, 2, 4, 128. Það má sjá að Batch Insert, sem er teiknað í bláu, lætur honum líða miklu betur. Það er að segja, þegar þú setur inn einn í einu eða jafnvel þegar þú setur inn fjóra í einu, þá verður það tvöfalt betra, einfaldlega vegna þess að við troðum aðeins meira inn í VERÐI. Færri EXECUTE aðgerðir.
Að nota COPY á litlu magni er afar óvænt. Ég teiknaði ekki einu sinni fyrstu tvo. Þeir fara til himna, það er að segja þessar grænu tölur fyrir COPY.
COPY ætti að nota þegar þú ert með að minnsta kosti hundrað raðir af gögnum. Kostnaður við að opna þessa tengingu er mikill. Og satt að segja kafaði ég ekki í þessa átt. Ég fínstillti Batch, en ekki COPY.
Hvað gerum við næst? Við prófuðum það. Við skiljum að við þurfum að nota annaðhvort mannvirki eða snjallt bacth sem sameinar nokkrar merkingar.
Hvað ættir þú að taka frá skýrslu dagsins?
- PreparedStatement er allt okkar. Þetta gefur mikið fyrir framleiðni. Það framleiðir stórt flopp í smyrslinu.
- Og þú þarft að gera EXPLAIN ANALYZE 6 sinnum.
- Og við þurfum að þynna út OFFSET 0 og brellur eins og +0 til að leiðrétta hlutfallið sem eftir er af erfiðum fyrirspurnum okkar.
Heimild: www.habr.com