Siūlau perskaityti 2016 m. pradžios Vladimiro Sitnikovo pranešimo „PostgreSQL ir JDBC išspaudžia visas sultis“ stenogramą.
Laba diena Mano vardas Vladimiras Sitnikovas. NetCracker dirbu 10 metų. Ir aš daugiausiai užsiimu produktyvumu. Viskas, kas susiję su Java, viskas, kas susiję su SQL, yra tai, ką aš myliu.
Ir šiandien kalbėsiu apie tai, su kuo susidūrėme įmonėje, kai pradėjome naudoti PostgreSQL kaip duomenų bazės serverį. Ir daugiausia dirbame su Java. Tačiau tai, ką aš jums šiandien pasakysiu, yra ne tik apie „Java“. Kaip parodė praktika, tai pasitaiko ir kitomis kalbomis.
Mes kalbėsime:
- apie duomenų atranką.
- Apie duomenų išsaugojimą.
- Ir taip pat apie pasirodymą.
- Ir apie ten užkastus povandeninius grėblius.
Pradėkime nuo paprasto klausimo. Mes pasirenkame vieną eilutę iš lentelės pagal pirminį raktą.
Duomenų bazė yra tame pačiame pagrindiniame kompiuteryje. Ir visas šis ūkininkavimas trunka 20 milisekundžių.
Šios 20 milisekundžių yra daug. Jei turite 100 tokių užklausų, tuomet praleidžiate laiką per sekundę, slinkdami per šias užklausas, t. y. švaistome laiką.
Mes nemėgstame to daryti ir žiūrėti, ką bazė mums siūlo už tai. Duomenų bazėje siūlomos dvi užklausų vykdymo galimybės.
Pirmasis variantas yra paprastas prašymas. Kas čia gero? Tai, kad imame ir siunčiame, ir nieko daugiau.
Duomenų bazėje taip pat yra išplėstinė užklausa, kuri yra sudėtingesnė, bet funkcionalesnė. Galite atskirai siųsti užklausą dėl analizavimo, vykdymo, kintamojo susiejimo ir kt.
Šioje ataskaitoje neapžvelgsime itin išplėstinės užklausos. Mes, ko gero, norime kažko iš duomenų bazės ir yra tam tikra forma sudarytas pageidavimų sąrašas, t. y. to mes norime, bet tai neįmanoma dabar ir kitais metais. Taigi mes tiesiog įrašėme jį ir eisime kratydami pagrindinius žmones.
Ir ką mes galime padaryti, tai paprasta ir išplėstinė užklausa.
Kuo ypatingas kiekvienas požiūris?
Paprasta užklausa tinka vienkartiniam vykdymui. Kartą padaryta ir pamiršta. Ir problema yra ta, kad jis nepalaiko dvejetainių duomenų formato, ty jis netinka kai kurioms didelio našumo sistemoms.
Išplėstinė užklausa – leidžia sutaupyti laiko analizuojant. Tai mes padarėme ir pradėjome naudoti. Tai mums tikrai labai padėjo. Sutaupoma ne tik analizuojant. Sutaupoma perkeliant duomenis. Duomenų perkėlimas dvejetainiu formatu yra daug efektyvesnis.
Pereikime prie praktikos. Taip atrodo įprasta programa. Tai gali būti Java ir kt.
Sukūrėme pareiškimą. Įvykdė komandą. Sukurta arti. Kur čia klaida? Kokia problema? Jokiu problemu. Taip rašoma visose knygose. Taip ir reikia parašyti. Jei norite maksimalaus našumo, rašykite taip.
Tačiau praktika parodė, kad tai neveikia. Kodėl? Nes turime „uždarymo“ metodą. Ir kai tai darome, duomenų bazės požiūriu paaiškėja, kad tai tarsi rūkalius, dirbantis su duomenų baze. Mes pasakėme „PARSE EXECUTE DEALLOCATE“.
Kam visas šis papildomas teiginių kūrimas ir iškrovimas? Niekam jų nereikia. Tačiau „PreparedStatements“ paprastai nutinka taip, kad kai juos uždarome, jie uždaro viską, kas yra duomenų bazėje. Tai nėra tai, ko mes norime.
Norime, kaip ir sveiki žmonės, dirbti su baze. Mes ėmėme ir parengėme savo pareiškimą vieną kartą, tada daug kartų jį vykdome. Tiesą sakant, daug kartų – tai kartą per visą programų naudojimo laiką – jos buvo analizuojamos. Ir mes naudojame tą patį pareiškimo ID skirtinguose REST. Tai mūsų tikslas.
Kaip mes galime tai pasiekti?
Tai labai paprasta – nereikia uždaryti pareiškimų. Rašome taip: „paruošti“, „vykdyti“.
Jei paleidžiame kažką panašaus, tada aišku, kad kažkas kažkur persipildys. Jei neaišku, galite pabandyti. Parašykime etaloną, kuriame naudojamas šis paprastas metodas. Sukurkite pareiškimą. Paleidžiame ją tam tikroje tvarkyklės versijoje ir pastebime, kad ji gana greitai sugenda ir praranda visą turėtą atmintį.
Akivaizdu, kad tokias klaidas nesunku ištaisyti. Aš apie juos nekalbėsiu. Bet pasakysiu, kad naujoji versija veikia daug greičiau. Metodas kvailas, bet vis tiek.
Kaip teisingai dirbti? Ką turime padaryti dėl to?
Iš tikrųjų programos visada uždaro teiginius. Visose knygose sakoma uždaryti, kitaip atmintis nutekės.
Ir PostgreSQL nežino, kaip talpykloje įrašyti užklausas. Būtina, kad kiekviena sesija pati sukurtų šią talpyklą.
Taip pat nenorime gaišti laiko analizavimui.
Ir, kaip įprasta, turime dvi galimybes.
Pirmas variantas yra tai, kad imame jį ir sakome, kad viską apvyniokime PgSQL. Ten yra talpykla. Jis talpina viską. Tai pasirodys puikiai. Mes tai matėme. Turime 100500 užklausų. Neveikia. Mes nesutinkame paversti užklausų procedūromis rankiniu būdu. Ne ne.
Turime antrą variantą – imkite ir patys supjaustykite. Atidarome šaltinius ir pradedame pjaustyti. Matėme ir matėme. Paaiškėjo, kad tai padaryti nėra taip sunku.
Tai pasirodė 2015 m. rugpjūčio mėn. Dabar yra modernesnė versija. Ir viskas puiku. Tai veikia taip gerai, kad programoje nieko nekeičiame. Ir net nustojome galvoti PgSQL kryptimi, t. y. to mums visiškai pakako, kad sumažintume visas pridėtines išlaidas beveik iki nulio.
Atitinkamai, serverio paruošti teiginiai suaktyvinami 5-ąjį vykdymą, kad būtų išvengta atminties švaistymo duomenų bazėje kiekvienai vienkartinei užklausai.
Galite paklausti – kur skaičiai? Ką tu gauni? Ir čia aš nepateiksiu skaičių, nes kiekvienas prašymas turi savo.
Mūsų užklausos buvo tokios, kad OLTP užklausoms analizuoti praleidome apie 20 milisekundžių. Vykdyti buvo skirta 0,5 milisekundžių, analizuoti – 20 milisekundžių. Užklausa – 10 KiB teksto, 170 plano eilučių. Tai OLTP užklausa. Ji reikalauja 1, 5, 10 eilučių, kartais daugiau.
Bet mes visai nenorėjome švaistyti 20 milisekundžių. Sumažinome iki 0. Viskas puiku.
Ką tu gali iš čia pasiimti? Jei turite „Java“, imate šiuolaikinę tvarkyklės versiją ir džiaukitės.
Jei kalbate kita kalba, pagalvokite – gal ir jums to reikia? Nes galutinės kalbos požiūriu, pavyzdžiui, jei PL 8 arba turite LibPQ, tada jums nėra akivaizdu, kad jūs skiriate laiką ne vykdymui, analizei, ir tai verta patikrinti. Kaip? Viskas nemokama.
Išskyrus tai, kad yra klaidų ir tam tikrų ypatumų. Ir mes apie juos kalbėsime dabar. Daugiausia bus apie pramoninę archeologiją, apie tai, ką radome, su kuo susidūrėme.
Jei užklausa generuojama dinamiškai. Taip atsitinka. Kažkas sulipdo eilutes, todėl gaunama SQL užklausa.
Kodėl jis blogas? Tai blogai, nes kiekvieną kartą gauname skirtingą eilutę.
Ir šios skirtingos eilutės maišos kodą reikia perskaityti dar kartą. Tai tikrai procesoriaus užduotis – rasti ilgą užklausos tekstą net esamoje maišoje nėra taip paprasta. Todėl išvada paprasta – negeneruokite užklausų. Išsaugokite juos viename kintamajame. Ir džiaukis.
Kita problema. Duomenų tipai yra svarbūs. Yra ORM, kurie sako, kad nesvarbu, koks yra NULL, tegul būna koks nors. Jei Int, tada sakome setInt. O jei NULL, tai tegul visada būna VARCHAR. Ir koks skirtumas, kas ten yra NULL? Pati duomenų bazė viską supras. Ir ši nuotrauka neveikia.
Praktiškai duomenų bazei tai visiškai nerūpi. Jei pirmą kartą pasakėte, kad tai yra skaičius, o antrą kartą pasakėte, kad tai yra VARCHAR, tada serverio paruoštų teiginių pakartotinai naudoti neįmanoma. Ir šiuo atveju turime iš naujo sukurti savo pareiškimą.
Jei vykdote tą pačią užklausą, įsitikinkite, kad duomenų tipai jūsų stulpelyje nėra supainioti. Turite saugotis NULL. Tai dažna klaida, kurią padarėme pradėjus naudoti PreparedStatements
Gerai, įjungta. Galbūt jie paėmė vairuotoją. Ir produktyvumas sumažėjo. Reikalai pasidarė blogai.
Kaip tai atsitinka? Ar tai klaida ar funkcija? Deja, nepavyko suprasti, ar tai klaida, ar funkcija. Tačiau yra labai paprastas šios problemos atkūrimo scenarijus. Ji visiškai netikėtai mus užklupo. Ir tai susideda iš mėginių ėmimo tiesiogine prasme iš vienos lentelės. Tokių prašymų, žinoma, turėjome ir daugiau. Paprastai jie apėmė dvi ar tris lenteles, tačiau yra toks atkūrimo scenarijus. Paimkite bet kurią versiją iš savo duomenų bazės ir paleiskite ją.
Esmė ta, kad turime du stulpelius, kurių kiekvienas yra indeksuotas. Viename NULL stulpelyje yra milijonas eilučių. O antrame stulpelyje yra tik 20 eilučių. Kai vykdome be susietų kintamųjų, viskas veikia gerai.
Jei pradedame vykdyti su susietais kintamaisiais, t.y. vykdome "?" arba „1 USD“ už mūsų užklausą, ką mes gauname?
Pirmoji egzekucija tokia, kaip ir tikėtasi. Antrasis yra šiek tiek greitesnis. Kažkas buvo talpykloje. Trečia, ketvirta, penkta. Tada sprogimas – ir kažkas panašaus. Ir blogiausia, kad tai nutinka šeštąją egzekuciją. Kas žinojo, kad norint suprasti, koks yra tikrasis egzekucijos planas, reikia atlikti tiksliai šešias egzekucijas?
Kas kaltas? Kas nutiko? Duomenų bazėje yra optimizavimas. Ir atrodo, kad jis optimizuotas bendrajam atvejui. Ir atitinkamai nuo tam tikru momentu ji pereina prie bendro plano, kuris, deja, gali pasirodyti kitoks. Gali pasirodyti, kad tai yra tas pats, arba gali būti kitaip. Ir yra tam tikra slenkstinė vertė, kuri lemia tokį elgesį.
Ką galite padaryti dėl to? Čia, žinoma, sunkiau ką nors manyti. Yra paprastas sprendimas, kurį naudojame. Tai yra +0, OFFSET 0. Tikrai žinote tokius sprendimus. Tiesiog imame ir pridedame prie užklausos „+0“ ir viskas gerai. Aš tau parodysiu vėliau.
Ir yra dar vienas variantas – atidžiau pažiūrėkite į planus. Kūrėjas turi ne tik parašyti prašymą, bet ir 6 kartus pasakyti „paaiškinti analizuoti“. Jei 5, tai neveiks.
Ir yra trečias variantas – parašyk laišką pgsql-hakeriams. Parašiau, tačiau dar neaišku, ar tai klaida, ar funkcija.
Kol galvojame, ar tai klaida, ar funkcija, ištaisykime ją. Paimkime užklausą ir pridėkime „+0“. Viskas gerai. Du simboliai ir jums net nereikia galvoti, kaip tai yra ar kas tai yra. Labai paprasta. Mes tiesiog uždraudėme duomenų bazei naudoti indeksą šiame stulpelyje. Mes neturime indekso stulpelyje „+0“ ir viskas, duomenų bazė nenaudoja indekso, viskas gerai.
Tai yra 6 paaiškinimo taisyklė. Dabartinėse versijose turite tai padaryti 6 kartus, jei turite susietų kintamųjų. Jei neturite susietų kintamųjų, mes tai darome. Ir galiausiai būtent šis prašymas žlunga. Tai nėra sudėtingas dalykas.
Atrodytų, kiek įmanoma? Klaida čia, klaida ten. Tiesą sakant, klaida yra visur.
Pažiūrėkime atidžiau. Pavyzdžiui, turime dvi schemas. Schema A su lentele S ir diagrama B su lentele S. Užklausa – pasirinkite duomenis iš lentelės. Ką mes turėsime šiuo atveju? Turėsime klaidą. Turėsime visa tai, kas išdėstyta aukščiau. Taisyklė yra tokia – klaida yra visur, mes turėsime visa tai, kas išvardinta.
Dabar kyla klausimas: „Kodėl? Atrodo, kad yra dokumentacijos, kad jei turime schemą, tada yra kintamasis "search_path", kuris nurodo, kur ieškoti lentelės. Atrodytų, kad yra kintamasis.
Kokia problema? Problema ta, kad serverio parengti teiginiai neįtaria, kad paieškos_pagalba kas nors gali būti pakeista. Ši reikšmė duomenų bazėje išlieka tarsi pastovi. Ir kai kurios dalys gali neįgauti naujų reikšmių.
Žinoma, tai priklauso nuo versijos, kurią bandote. Priklauso nuo to, kiek jūsų lentelės skiriasi. Ir 9.1 versija tiesiog vykdys senas užklausas. Naujos versijos gali užfiksuoti klaidą ir pranešti, kad turite klaidą.
Kaip tai gydyti? Yra paprastas receptas – nedaryk. Nereikia keisti search_path, kol programa veikia. Jei pakeisite, geriau sukurti naują ryšį.
Galima diskutuoti, t.y. atvirauti, diskutuoti, papildyti. Galbūt galime įtikinti duomenų bazės kūrėjus, kad kai kas nors pakeičia vertę, duomenų bazė turėtų klientui apie tai pasakyti: „Žiūrėkite, čia jūsų vertė atnaujinta. Gal reikia iš naujo nustatyti teiginius ir juos sukurti iš naujo? Dabar duomenų bazė elgiasi slaptai ir niekaip nepraneša, kad kažkur viduje pasikeitę teiginiai.
Ir dar kartą pabrėšiu – tai Javai nebūdinga. Tą patį matysime PL/pgSQL vienas prieš vieną. Bet ten jis bus atkurtas.
Pabandykime pasirinkti daugiau duomenų. Renkamės ir renkamės. Mes turime lentelę su milijonu eilučių. Kiekviena eilutė yra kilobaitas. Maždaug gigabaitas duomenų. Ir mes turime 128 megabaitų darbinę atmintį „Java“ mašinoje.
Mes, kaip rekomenduojama visose knygose, naudojame srauto apdorojimą. Tai yra, atidarome resultSet ir po truputį skaitome iš ten esančius duomenis. Ar pavyks? Ar iškris iš atminties? Ar paskaitysi truputį? Pasitikėkime duomenų baze, pasitikėkime Postgres. Mes netikime. Ar iškrisime iš atminties? Kas patyrė OutOfMemory? Kam po to pavyko tai sutvarkyti? Kažkam pavyko tai sutvarkyti.
Jei turite milijoną eilučių, negalite tiesiog atsirinkti. Reikalingas OFFSET/LIMIT. Kas už šį variantą? O kas už žaidimą su autoCommit?
Čia, kaip įprasta, pats netikėčiausias variantas pasirodo teisingas. Ir jei staiga išjungsite autoCommit, tai padės. Kodėl taip? Mokslas apie tai nežino.
Tačiau pagal numatytuosius nustatymus visi klientai, prisijungiantys prie Postgres duomenų bazės, gauna visus duomenis. PgJDBC šiuo atžvilgiu nėra išimtis; jis pasirenka visas eilutes.
Yra FetchSize temos variantų, t. y. atskiro teiginio lygiu galite pasakyti, kad čia prašome pasirinkti duomenis pagal 10, 50. Tačiau tai neveiks, kol neišjungsite automatinio įpareigojimo. AutoCommit išjungtas – pradeda veikti.
Tačiau perskaityti kodą ir visur nustatyti setFetchSize yra nepatogu. Todėl padarėme nustatymą, kuriame bus nurodyta numatytoji viso ryšio reikšmė.
Taip ir pasakėme. Parametras sukonfigūruotas. Ir ką mes gavome? Jei pasirenkame mažas sumas, jei, pavyzdžiui, vienu metu pasirenkame 10 eilučių, tada turime labai dideles pridėtines išlaidas. Todėl ši vertė turėtų būti maždaug šimtas.
Idealiu atveju, žinoma, vis tiek turite išmokti jį apriboti baitais, tačiau receptas yra toks: nustatykite defaultRowFetchSize į daugiau nei šimtą ir būkite laimingi.
Pereikime prie duomenų įvedimo. Įdėti lengviau, yra įvairių variantų. Pavyzdžiui, INSERT, VALUES. Tai geras pasirinkimas. Galite pasakyti „INSERT SELECT“. Praktikoje tai tas pats. Veikimo skirtumo nėra.
Knygose rašoma, kad reikia vykdyti partijos sakinį, o knygose rašoma, kad sudėtingesnes komandas galima vykdyti su keliais skliaustais. O „Postgres“ turi nuostabią savybę – galite KOPYTI, t.y. tai padaryti greičiau.
Jei išmatuosite, vėl galėsite padaryti įdomių atradimų. Kaip norime, kad tai veiktų? Norime neanalizuoti ir nevykdyti nereikalingų komandų.
Praktiškai TCP neleidžia mums to padaryti. Jei klientas yra užsiėmęs siųsdamas užklausą, duomenų bazė neskaito užklausų, bandydama atsiųsti mums atsakymus. Galutinis rezultatas yra tas, kad klientas laukia, kol duomenų bazė nuskaitys užklausą, o duomenų bazė laukia, kol klientas perskaitys atsakymą.
Ir todėl klientas yra priverstas periodiškai siųsti sinchronizavimo paketą. Papildoma tinklo sąveika, papildomas laiko švaistymas.
Ir kuo daugiau jų pridedame, tuo blogėja. Vairuotojas gana pesimistiškas ir jas prideda gana dažnai, maždaug kartą per 200 eilučių, priklausomai nuo eilučių dydžio ir pan.
Būna, kad pataisysite tik vieną eilutę ir viskas paspartės 10 kartų. Taip atsitinka. Kodėl? Kaip įprasta, tokia konstanta jau buvo kažkur panaudota. Ir reikšmė „128“ reiškia nenaudoti paketų.
Gerai, kad tai nebuvo įtraukta į oficialią versiją. Atrasta prieš prasidedant išleidimui. Visos mano pateiktos reikšmės yra pagrįstos šiuolaikinėmis versijomis.
Išbandykime. Mes matuojame InsertBatch simple. „InsertBatch“ matuojame kelis kartus, t. y. tą patį, tačiau yra daug reikšmių. Sudėtingas judesys. Ne visi gali tai padaryti, bet tai toks paprastas veiksmas, daug lengvesnis nei KOPIJA.
Galite padaryti KOPIJUOTĄ.
Ir jūs galite tai padaryti ant konstrukcijų. Paskelbkite vartotojo numatytąjį tipą, perduokite masyvą ir INSERT tiesiai į lentelę.
Jei atidarysite nuorodą: pgjdbc/ubenchmsrk/InsertBatch.java, tada šis kodas yra GitHub. Konkrečiai galite pamatyti, kokios užklausos ten generuojamos. Nesvarbu.
Mes paleidome. Ir pirmas dalykas, kurį supratome, buvo tai, kad nenaudoti partijos yra tiesiog neįmanoma. Visos paketų parinktys yra lygios nuliui, t. y. vykdymo laikas yra praktiškai lygus nuliui, palyginti su vienkartiniu vykdymu.
Įdedame duomenis. Tai labai paprasta lentelė. Trys stulpeliai. Ir ką mes čia matome? Matome, kad visos trys šios parinktys yra maždaug palyginamos. Ir COPY, žinoma, yra geriau.
Tai yra tada, kai įdedame gabalus. Kai pasakėme, kad viena VERČIŲ reikšmė, dvi VALUES reikšmės, trys VALUES reikšmės arba nurodėme 10 iš jų, atskirtus kableliu. Dabar tai tik horizontali. 1, 2, 4, 128. Matyti, kad partijos įdėklas, nupieštas mėlyna spalva, leidžia jam jaustis daug geriau. Tai yra, kai įterpiate po vieną ar net keturis vienu metu, jis tampa dvigubai geresnis, nes į VERTYBES įtraukėme šiek tiek daugiau. Mažiau EXECUTE operacijų.
Naudoti COPY mažuose kiekiuose yra labai neperspektyvu. Ant pirmųjų dviejų net neprisitraukiau. Jie eina į dangų, tai yra, šie žali numeriai, skirti KOPIJAI.
COPY turėtų būti naudojamas, kai turite bent šimtą duomenų eilučių. Šios jungties atidarymo išlaidos yra didelės. Ir, tiesą pasakius, aš nesigilinau šia kryptimi. Aš optimizavau paketą, bet ne COPY.
Ką darysime toliau? Mes jį išbandėme. Suprantame, kad reikia naudoti arba struktūras, arba protingą batą, kuris sujungia kelias reikšmes.
Ką turėtumėte paimti iš šios dienos pranešimo?
- Paruoštas pareiškimas yra mūsų viskas. Tai daug duoda produktyvumui. Tai sukelia didelį šnipštas.
- Ir jums reikia PAAIŠKINTI ANALIZĘ 6 kartus.
- Ir turime atskiesti OFFSET 0 ir tokius triukus kaip +0, kad ištaisytume likusį probleminių užklausų procentą.
Šaltinis: www.habr.com