Soovitan teil lugeda Vladimir Sitnikovi 2016. aasta alguse raporti "PostgreSQL ja JDBC pigistavad kogu mahla välja" ärakirja.
Tere päevast Minu nimi on Vladimir Sitnikov. Olen NetCrackeris töötanud 10 aastat. Ja ma tegelen peamiselt tootlikkusega. Kõik, mis on seotud Javaga, kõik, mis on seotud SQL-iga, on see, mida ma armastan.
Ja täna räägin sellest, millega me ettevõttes kokku puutusime, kui alustasime PostgreSQL-i andmebaasiserverina. Ja me töötame enamasti Javaga. Kuid see, mida ma teile täna räägin, ei puuduta ainult Java. Nagu praktika on näidanud, esineb seda ka teistes keeltes.
Me räägime:
- andmete valimi võtmise kohta.
- Andmete salvestamise kohta.
- Ja ka jõudluse kohta.
- Ja veealustest rehadest, mis sinna maetud on.
Alustame lihtsa küsimusega. Valime tabelist ühe rea primaarvõtme alusel.
Andmebaas asub samas hostis. Ja kogu see põllutöö võtab aega 20 millisekundit.
Need 20 millisekundit on palju. Kui teil on 100 sellist päringut, siis kulutate aega sekundis nende päringute sirvimisele, st me raiskame aega.
Meile ei meeldi seda teha ja vaadata, mida baas meile selle jaoks pakub. Andmebaas pakub meile päringute täitmiseks kahte võimalust.
Esimene võimalus on lihtne taotlus. Mis selles head on? See, et me võtame ja saadame, ja ei midagi enamat.
Andmebaasis on ka täiustatud päring, mis on keerulisem, kuid funktsionaalsem. Eraldi saate saata päringu parsimiseks, täitmiseks, muutujate sidumiseks jne.
Eriti laiendatud päring on midagi, mida me käesolevas aruandes ei käsitle. Võib-olla tahame midagi andmebaasist ja soovinimekiri on mingil kujul moodustatud, st see on see, mida me tahame, kuid see on võimatu praegu ja järgmisel aastal. Nii et me lihtsalt salvestasime selle ja raputame peamisi inimesi.
Ja mida me teha saame, on lihtne päring ja laiendatud päring.
Mis on iga lähenemisviisi puhul erilist?
Lihtne päring sobib ühekordseks täitmiseks. Kord tehtud ja unustatud. Ja probleem on selles, et see ei toeta binaarandmete vormingut, st see ei sobi mõne suure jõudlusega süsteemi jaoks.
Laiendatud päring – võimaldab säästa aega parsimisel. Seda me tegime ja hakkasime kasutama. See aitas meid tõesti. Kokkuhoid ei ole ainult sõelumisel. Andmeedastuse pealt on kokkuhoid. Andmete edastamine binaarvormingus on palju tõhusam.
Liigume edasi praktika juurde. Selline näeb välja tüüpiline rakendus. See võib olla Java vms.
Koostasime avalduse. Täitis käsu. Loodud lähedal. Kus siin viga on? Milles on probleem? Pole probleemi. Nii on kirjas kõigis raamatutes. Nii tulebki kirjutada. Kui soovite maksimaalset jõudlust, kirjutage nii.
Kuid praktika on näidanud, et see ei tööta. Miks? Sest meil on "lähedane" meetod. Ja kui me seda teeme, siis andmebaasi vaatenurgast selgub, et see on nagu suitsetaja, kes töötab andmebaasiga. Me ütlesime "PARSE EXECUTE DEALLOCATE".
Milleks kogu see lisaavalduste loomine ja mahalaadimine? Keegi ei vaja neid. Kuid PreparedStatementsis juhtub tavaliselt see, et kui me need sulgeme, sulgevad nad kõik andmebaasis olevad andmed. See pole see, mida me tahame.
Me tahame, nagu terved inimesed, töötada baasiga. Võtsime ja koostasime oma avalduse üks kord, siis täidame seda mitu korda. Tegelikult on neid mitu korda sõelutud – see on üks kord kogu rakenduste eluea jooksul. Ja me kasutame sama avalduse ID-d erinevatel REST-idel. See on meie eesmärk.
Kuidas seda saavutada?
See on väga lihtne – avaldusi pole vaja sulgeda. Kirjutame selle järgmiselt: “valmistama” “täitma”.
Kui me midagi sellist käivitame, on selge, et kuskil voolab midagi üle. Kui see pole selge, võite seda proovida. Kirjutame võrdlusaluse, mis kasutab seda lihtsat meetodit. Loo avaldus. Käivitame selle mõnes draiveri versioonis ja avastame, et see jookseb üsna kiiresti kokku ja kaotab kogu sellel olnud mälu.
On selge, et sellised vead on kergesti parandatavad. Ma ei hakka neist rääkima. Aga ma ütlen, et uus versioon töötab palju kiiremini. Meetod on rumal, aga siiski.
Kuidas õigesti töötada? Mida me selleks tegema peame?
Tegelikkuses sulgevad rakendused alati avaldused. Kõikides raamatutes öeldakse, et pange see kinni, muidu mälu lekib.
Ja PostgreSQL ei tea, kuidas päringuid vahemällu salvestada. On vaja, et iga seanss looks selle vahemälu enda jaoks.
Ja me ei taha ka sõelumisele aega raisata.
Ja nagu tavaliselt, on meil kaks võimalust.
Esimene võimalus on see, et võtame selle ja ütleme, et mähkime kõik PgSQL-i. Seal on vahemälu. See salvestab kõik vahemällu. See osutub suurepäraseks. Me nägime seda. Meil on 100500 XNUMX taotlust. Ei tööta. Me ei nõustu taotlusi käsitsi protseduurideks muutma. Ei ei.
Meil on teine võimalus – võta ja lõika ise. Avame allikad ja hakkame lõikama. Nägime ja nägime. Selgus, et seda polegi nii raske teha.
See ilmus augustis 2015. Nüüd on moodsam versioon. Ja kõik on suurepärane. See töötab nii hästi, et me ei muuda rakenduses midagi. Ja me isegi lõpetasime mõtlemise PgSQL-i suunas, st sellest piisas täiesti, et kõik üldkulud peaaegu nullini viia.
Vastavalt sellele aktiveeritakse serveri koostatud avaldused 5. täitmisel, et vältida andmebaasi mälu raiskamist iga ühekordse päringu korral.
Võite küsida – kus on numbrid? Mida sa saad? Ja siin ma numbreid ei anna, sest igal taotlusel on oma.
Meie päringud olid sellised, et kulutasime OLTP-päringute sõelumisele umbes 20 millisekundit. Täitmiseks oli aega 0,5 millisekundit, sõelumiseks 20 millisekundit. Taotlus – 10 KiB teksti, 170 rida kava. See on OLTP päring. See nõuab 1, 5, 10 rida, mõnikord rohkem.
Kuid me ei tahtnud üldse 20 millisekundit raisata. Vähendasime selle 0-ni. Kõik on suurepärane.
Mida saab siit kaasa võtta? Kui teil on Java, siis võtate draiveri kaasaegse versiooni ja rõõmustage.
Kui räägite teist keelt, siis mõelge – äkki vajate ka seda? Sest lõppkeele seisukohalt, kui näiteks PL 8 või teil on LibPQ, siis pole teile selge, et te ei kuluta aega mitte täitmisele, sõelumisele ja seda tasub kontrollida. Kuidas? Kõik on tasuta.
Välja arvatud see, et seal on vigu ja mõningaid iseärasusi. Ja me räägime neist kohe. Suurem osa sellest puudutab tööstusarheoloogiat, seda, mida me leidsime, millega kokku puutusime.
Kui päring genereeritakse dünaamiliselt. Juhtub. Keegi liimib stringid kokku, mille tulemuseks on SQL-päring.
Miks ta on halb? See on halb, sest iga kord jõuame erineva nööriga.
Ja selle erineva stringi hashCode tuleb uuesti lugeda. See on tõesti protsessori ülesanne – pika päringu teksti leidmine isegi olemasolevast räsist pole nii lihtne. Seetõttu on järeldus lihtne - ärge genereerige päringuid. Salvestage need ühes muutujas. Ja rõõmusta.
Järgmine probleem. Andmetüübid on olulised. On ORM-e, mis ütlevad, et pole vahet, milline NULL on olemas, olgu mingisugune. Kui Int, siis ütleme setInt. Ja kui NULL, siis olgu see alati VARCHAR. Ja mis vahet sellel lõpuks on, mis NULL seal on? Andmebaas ise saab kõigest aru. Ja see pilt ei tööta.
На практике базе данных совершенно не все равно. Kui ütlesite esimest korda, et see on number, ja teisel korral ütlesite, et see on VARCHAR, siis pole serveri koostatud avaldusi võimalik uuesti kasutada. Ja sel juhul peame oma avalduse uuesti looma.
Kui täidate sama päringut, veenduge, et teie veerus olevad andmetüübid ei oleks segaduses. Peate jälgima NULL-i. See on tavaline viga, mis tekkis pärast PreparedStatementsi kasutamise alustamist
Olgu, sisse lülitatud. Võib-olla võtsid nad juhi kaasa. Ja tootlikkus langes. Asi läks halvaks.
Kuidas see juhtub? Kas see on viga või funktsioon? Kahjuks ei olnud võimalik aru saada, kas see on viga või funktsioon. Kuid selle probleemi taasesitamiseks on väga lihtne stsenaarium. Ta varitses meid täiesti ootamatult. Ja see koosneb proovide võtmisest sõna otseses mõttes ühest tabelist. Selliseid taotlusi oli meil muidugi rohkem. Reeglina sisaldasid nad kahte või kolme tabelit, kuid on olemas selline taasesituse stsenaarium. Võtke oma andmebaasist mis tahes versioon ja mängige seda.
Asi on selles, et meil on kaks veergu, millest igaüks on indekseeritud. Ühes NULL veerus on miljon rida. Ja teine veerg sisaldab ainult 20 rida. Kui käivitame ilma seotud muutujateta, toimib kõik hästi.
Kui alustame täitmist seotud muutujatega, st käivitame "?" või "$1" meie taotluse puhul, mida me lõpuks saame?
Esimene hukkamine on ootuspärane. Teine on veidi kiirem. Midagi oli vahemällu salvestatud. Kolmas, neljas, viies. Siis paugu – ja midagi sellist. Ja kõige hullem on see, et see juhtub kuuendal hukkamisel. Kes teadis, et on vaja teha täpselt kuus hukkamist, et mõista, milline on tegelik hukkamisplaan?
Kes on süüdi? Mis juhtus? Andmebaas sisaldab optimeerimist. Ja tundub, et see on üldise juhtumi jaoks optimeeritud. Ja vastavalt sellele lülitub ta mingil hetkel üle üldisele plaanile, mis kahjuks võib osutuda teistsuguseks. See võib osutuda samaks või võib olla erinev. Ja on mingisugune läviväärtus, mis sellise käitumiseni viib.
Mida saate sellega teha? Siin on muidugi raskem midagi eeldada. Meil on lihtne lahendus, mida kasutame. See on +0, OFFSET 0. Kindlasti teate selliseid lahendusi. Me lihtsalt võtame selle ja lisame päringule “+0” ja kõik on korras. Ma näitan teile hiljem.
Ja on veel üks võimalus – vaadake plaane hoolikamalt. Arendaja ei pea mitte ainult päringu kirjutama, vaid ka 6 korda ütlema "selgitage analüüsi". Kui see on 5, siis see ei tööta.
Ja on ka kolmas võimalus - kirjutage pgsql-häkkeritele kiri. Kirjutasin, aga pole veel selge, kas see on viga või funktsioon.
Kui me mõtleme, kas see on viga või funktsioon, parandame selle. Võtame oma soovi ja lisame "+0". Kõik on korras. Kaks sümbolit ja te ei pea isegi mõtlema, kuidas see on või mis see on. Väga lihtne. Me lihtsalt keelasime andmebaasil selles veerus indeksi kasutamise. Meil pole veerus "+0" indeksit ja see on kõik, andmebaas ei kasuta indeksit, kõik on korras.
See on 6. selgitamise reegel. Praegustes versioonides peate seda tegema 6 korda, kui teil on seotud muutujad. Kui teil pole seotud muutujaid, teeme seda. Ja lõpuks just see taotlus ebaõnnestub. See pole keeruline asi.
Näib, kui palju on võimalik? Viga siin, viga seal. Tegelikult on viga kõikjal.
Vaatame lähemalt. Näiteks on meil kaks skeemi. Skeem A tabeliga S ja diagramm B tabeliga S. Päring – valige tabelist andmed. Mis meil sel juhul on? Meil tekib viga. Meil on kõik ülaltoodud. Reegel on - viga on kõikjal, meil on kõik ülalnimetatud.
Nüüd on küsimus: "Miks?" Näib, et on olemas dokumentatsioon, et kui meil on skeem, siis on olemas muutuja "search_path", mis ütleb meile, kust tabelit otsida. Näib, et seal on muutuja.
Milles on probleem? Probleem on selles, et serveri poolt ettevalmistatud avaldused ei kahtlusta, et keegi võib otsinguteed muuta. See väärtus jääb andmebaasi jaoks justkui konstantseks. Ja mõned osad ei pruugi omandada uusi tähendusi.
Muidugi oleneb see versioonist, mida testite. Oleneb sellest, kui tõsiselt teie tabelid erinevad. Ja versioon 9.1 täidab lihtsalt vanad taotlused. Uued versioonid võivad vea tabada ja öelda, et teil on viga.
Kuidas seda ravida? Seal on lihtne retsept – ära tee seda. Rakenduse töötamise ajal ei ole vaja otsinguteed muuta. Kui muudate, on parem luua uus ühendus.
Saab arutada, s.t avada, arutada, lisada. Võib-olla suudame andmebaasi arendajaid veenda, et kui keegi väärtust muudab, peaks andmebaas sellest kliendile teatama: “Vaata, siin on teie väärtust uuendatud. Võib-olla peate avaldused lähtestama ja uuesti looma? Nüüd käitub andmebaas salaja ega teata kuidagi, et väited on kuskil sees muutunud.
Ja rõhutan veel kord - see on midagi, mis Java jaoks pole tüüpiline. Näeme sama asja PL/pgSQL-is üks ühele. Aga seda seal paljundatakse.
Proovime veel andmevalikut teha. Valime ja valime. Meil on miljoni reaga tabel. Iga rida on kilobait. Ligikaudu gigabait andmemahtu. Ja meil on Java masinas 128 megabaidine töömälu.
Nagu kõigis raamatutes soovitatakse, kasutame vootöötlust. See tähendab, et avame resultSeti ja loeme sealt vähehaaval andmeid. Kas see toimib? Kas see kukub mälust? Kas sa loed natuke? Usaldagem andmebaasi, usaldagem Postgresi. Me ei usu seda. Kas me langeme mälust välja? Kes koges OutOfMemoryt? Kes suutis selle pärast seda parandada? Keegi suutis selle parandada.
Kui teil on miljon rida, ei saa te lihtsalt noppida ja valida. OFFSET/LIMIT on nõutav. Kes on selle variandi poolt? Ja kes pooldab autoCommitiga mängimist?
Siin, nagu tavaliselt, osutub õigeks kõige ootamatum variant. Ja kui lülitate autoCommit äkki välja, aitab see. Miks nii? Teadus sellest ei tea.
Kuid vaikimisi toovad kõik Postgresi andmebaasiga ühenduse loovad kliendid kogu andmed. PgJDBC pole selles osas erand; see valib kõik read.
FetchSize teemal on variatsioon, st võite eraldi avalduse tasemel öelda, et siin valige andmed 10, 50 kaupa. Kuid see ei toimi enne, kui lülitate automaatse kinnitamise välja. AutoCommit välja lülitatud – hakkab tööle.
Kuid koodi läbimine ja setFetchSize'i seadistamine kõikjal on ebamugav. Seetõttu tegime sätte, mis ütleb kogu ühenduse vaikeväärtuse.
Seda me ütlesime. Parameeter on konfigureeritud. Ja mida me saime? Kui valime väikesed summad, kui valime näiteks 10 rida korraga, siis on meil väga suured üldkulud. Seetõttu tuleks selle väärtuseks määrata umbes sada.
Ideaalis peate muidugi veel õppima, kuidas seda baitides piirata, kuid retsept on järgmine: määrake defaultRowFetchSize väärtuseks rohkem kui sada ja olge õnnelikud.
Liigume edasi andmete sisestamise juurde. Sisestamine on lihtsam, valikuid on erinevaid. Näiteks INSERT, VÄÄRTUSED. See on hea valik. Võite öelda "INSERT SELECT". Praktikas on see sama asi. Toimivuses pole vahet.
Raamatud ütlevad, et peate täitma Batch-lause, raamatud ütlevad, et saate täita keerukamaid käske mitme sulguga. Ja Postgresil on imeline funktsioon – saate teha KOPeerimist, s.t teha seda kiiremini.
Kui seda mõõta, saate jälle teha huvitavaid avastusi. Kuidas me tahame, et see toimiks? Soovime mitte parsida ja mitte täita tarbetuid käske.
Praktikas ei võimalda TCP meil seda teha. Kui klient on päringu saatmisega hõivatud, siis andmebaas ei loe päringuid, kui nad üritavad meile vastuseid saata. Lõpptulemus on see, et klient ootab, kuni andmebaas päringu loeb, ja andmebaas ootab, kuni klient loeb vastuse.
Ja seetõttu on klient sunnitud perioodiliselt saatma sünkroonimispaketti. Täiendav võrgu suhtlus, täiendav ajaraiskamine.
Ja mida rohkem me neid lisame, seda hullemaks see läheb. Juht on üsna pessimistlik ja lisab neid päris tihti, umbes kord 200 rea tagant, olenevalt ridade suurusest jne.
Juhtub, et parandate vaid ühe rea ja kõik kiireneb 10 korda. Juhtub. Miks? Nagu ikka, on sellist konstanti kuskil juba kasutatud. Ja väärtus "128" tähendas, et ei kasutata partiisid.
Hea, et seda ametlikku versiooni ei lisatud. Avastati enne vabastamise algust. Kõik minu antud tähendused põhinevad tänapäevastel versioonidel.
Proovime selga. Me mõõdame InsertBatch lihtsat. Me mõõdame InsertBatch mitu korda, st sama asja, kuid väärtusi on palju. Keeruline käik. Mitte igaüks ei saa seda teha, kuid see on nii lihtne samm, palju lihtsam kui KOPIJA.
Saate teha KOPEERIMISE.
Ja seda saate teha struktuuride puhul. Deklareerige kasutaja vaiketüüp, edastage massiiv ja INSERT otse tabelisse.
Kui avate lingi: pgjdbc/ubenchmsrk/InsertBatch.java, siis on see kood GitHubis. Saate konkreetselt näha, milliseid päringuid seal genereeritakse. Vahet pole.
Käivitasime. Ja esimene asi, mida mõistsime, oli see, et partii mittekasutamine on lihtsalt võimatu. Kõik partiimise valikud on nullid, st täitmisaeg on praktiliselt null võrreldes ühekordse täitmisega.
Sisestame andmed. See on väga lihtne tabel. Kolm veergu. Ja mida me siin näeme? Näeme, et kõik need kolm võimalust on ligikaudu võrreldavad. Ja COPY on muidugi parem.
See on siis, kui sisestame tükid. Kui ütlesime, et üks VÄÄRTUSED, kaks VÄÄRTUSED, kolm väärtust VÄÄRTUSED, või märkisime neist 10 komaga eraldatuna. See on praegu lihtsalt horisontaalne. 1, 2, 4, 128. On näha, et sinisega joonistatud Batch Insert teeb tema enesetunde palju paremaks. See tähendab, et kui sisestate ühe korraga või isegi kui sisestate neli korraga, muutub see kaks korda paremaks, lihtsalt sellepärast, et panime VÄÄRTUStesse veidi rohkem. Vähem EXECUTE toiminguid.
COPY kasutamine väikestes kogustes on äärmiselt vähetõotav. Kahele esimesele ma isegi ei joonistanud. Nad lähevad taevasse, st need rohelised numbrid KOOPIA jaoks.
Koopiat tuleks kasutada siis, kui andmeid on vähemalt sada rida. Selle ühenduse avamise üldkulud on suured. Ja ausalt öeldes ma ei kaevanud selles suunas. Optimeerisin Batch, kuid mitte COPY.
Mida me edasi teeme? Proovisime selga. Mõistame, et peame kasutama kas struktuure või nutikat batti, mis ühendab mitu tähendust.
Mida peaksite tänasest raportist välja võtma?
- PreparedStatement on meie jaoks kõik. See annab tootlikkusele palju juurde. See tekitab suure kukkumise.
- Ja peate 6 korda tegema EXPLAIN ANALÜÜSI.
- Ja me peame lahjendama OFFSET 0 ja selliseid nippe nagu +0, et parandada ülejäänud probleemsete päringute protsent.
Allikas: www.habr.com