PostgreSQL ja JDBC pigistame välja iga tilka. Vladimir Sitnikov

Soovitan tutvuda Vladimir Sitnikovi 2016. aasta alguses peetud ettekande "PostgreSQL ja JDBC pigistame välja iga tilka" kokkuvõttega

Tere päeva! Minu nimi on Vladimir Sitnikov. Töösklen ettevõttes NetCracker juba 10 aastat. Ja peamiselt tegelen tootlikkuse küsimustega. Kõik, mis on seotud Java ja SQL-iga, on see, mida ma armastan.

Ja täna räägin teile sellest, millega me ettevõttes silmitsi seisime, kui hakkasime PostgreSQL kasutama andmebaasiserverina. Peamiselt töötame Java'ga. Kuid see, millest ma täna räägin, ei seondu vaid Java'ga. Kogemus on näidanud, et sama juhtub ka teistes keeltes.

Räägime järgmistest asjadest:

• andmete valimisest.
• Andmete salvestamisest.
• Ning tootlikkusest.
• Ja ka peidetud kaevandustest, mis seal on.

Alustame lihtsast küsimusest. Valime tabelist ühe rea primaarvõtme alusel.

Andmebaas asub samal hostil. Ja kogu see tegevus kestab 20 millisekundit.

Need 20 millisekundit on väga palju. Kui teil on 100 sellist päringut, siis kulutate aega sekundites, et neid päringuid töödeldada, st raiskate aega.

Me ei soovi seda teha ja vaatame, mida andmebaas meile selleks pakub. Andmebaas pakub meile kahte valikut päringute täitmiseks.

Esimene variant on lihtne päring. Miks see on hea? Sellega on see, et me võtame selle ja saadame, ja ei midagi enamat.

https://github.com/pgjdbc/pgjdbc/pull/478

Andmebaasil on veel ka täiustatud päring, mis on keerulisem, kuid funktsionaalsem. Võib eraldi saata päringu parsimiseks, täitmiseks, muutujate ühendamiseks jne.

Super täiustatud päring – seda me ei käsitle praeguses ettekandes. Võib-olla tahame andmebaasilt midagi ja meil on soovide nimekiri, mis on mingil kujul koostatud, st see, mida me soovime, kuid ei ole praegu ega ka lähima aasta jooksul võimalik. Seetõttu kirjutasime selle lihtsalt üles ja käime peamisi inimesi pommitamas.

Aga see, mida me saame teha, on lihtne päring ja täiustatud päring.

Mis on iga lähenemise eripära?

Lihtsat päringut on hea kasutada ühekordseks täitmiseks. Täidetud ja unustatud. Probleem on selles, et see ei toeta binaarset andmevormingut, st mõned kõrge jõudlusega süsteemid ei sobi selleks.

Pikendatud päring - aitab säästa aega parsimisel. See on see, mida me tegime ja hakkasime kasutama. See on meid äärmiselt aidanud. Siin on mitte ainult sääst parsimisel. On olemas ka andmete edastamise sääst. Andmete edastamine binaarses formaadis on oluliselt efektiivsem.

Liigume praktikasse. Näeb välja nagu tüüpiline rakendus. See võib olla Java jne.

Me lõime statement'i. Käivitasime käsu. Lõime close. Kus on viga? Mis on probleem? Probleeme ei ole. Nii on kirjutatud kõigis raamatutes. Nii tuleb kirjutada. Kui tahate maksimaalset jõudlust, kirjutage nii.

Aga praktika näitas, et see ei tööta. Miks? Sest meil on meetod "close". Ja kui me nii teeme, siis andmebaasi vaatenurgast on see nagu suitsetaja töö andmebaasiga. Me ütlesime "PARSE EXECUTE DEALLOCATE".

Miks need liigsed statementide loomised ja eksportimine? Need ei ole kellelegi vajalikud. Aga tavaliselt on PreparedStatement'i puhul nii, et kui me need kinni paneme, siis nad sulgevad kõik andmebaasis. See ei ole see, mida me soovime.

Me tahame, nagu terved inimesed, andmebaasiga töötada. Otsustame ühe korra valmistada meie statement'i ja siis käivitame selle mitu korda. Tegelikult on palju kordi – see on üks kord kogu rakenduse elu jooksul parsimiseks. Ja kasutame erinevates REST'ides ühte ja sama statement'i id-d. See on meie eesmärk.

Kuidas me sellele eesmärgile jõuame?

Väga lihtne - statements ei pea sulgema. Kirjutame nii: "prepare" "execute".

Kui me midagi sellist käivitame, on selge, et kusagil mingi asi ületäidab. Kui ei ole selge, siis saame mõõta. Võtame ja kirjutame benchmark'i, kus on lihtsalt selline meetod. Loome statement'i. Käivitame mingis versioonis draiverist ja saame, et see kukub üsna kiiresti kokku mälukaotusega, mis meil tekib.

Selge, et sellised vead on kergesti parandatavad. Ma ei hakka neist rääkima. Kuid ma ütlen, et uues versioonis töötab see palju kiiremini. Meetod on mõttetu, kuid siiski.

Kuidas õigesti töötada? Mida me selleks peame tegema?

Tegelikult sulgevad rakendused alati statements'id. Kõikides raamatutes kirjutatakse, et peate need sulgema, vastasel juhul lekib mälu.

Ja PostgreSQL ei oska päringuid vahemälus hoida. Iga seanss peab oma jaoks selle vahemälu looma.

Ja aega parsimisele me samuti ei taha kulutada.

Ja nagu tavaliselt, on meil kaks varianti.

Esimene variant on, et võtame ja ütleme, et laseme kõik PgSQL-i mähkida. Seal on vahemälu. See kõik salvestatakse. Tulemus on imeline. Oleme sellist asja vaadanud. Meil on 100500 päringut. See ei toimi. Me ei ole nõus – käsitsi muundama päringud protseduurideks. Ei-ei.

Meil on teine variant – võtta ja ise teha. Avame lähtekoodid, alustame tööd. Töötame ja töötame. Selgub, et seda on mitte nii keeruline teha.

https://github.com/pgjdbc/pgjdbc/pull/319

See ilmus augustis 2015. Praegu on juba kaasaegsem versioon. Ja kõik on suurepärane. See töötab nii hästi, et me ei muuda rakenduses midagi. Ja me oleme isegi lõpetanud mõtlemise PgSQL-i suunas, st see oli piisav, et kõik kõrvalkulud vähendada praktiliselt nullini.

Seega Server-prepared statements aktiveeritakse 5. täitmisel, et mitte raisata mälu andmebaasis iga ühekordse päringu jaoks.

Võib küsida – kus arvud? Mida te saate? Ja siin ma arvuteid ei anna, sest iga päringu jaoks on need omad.

Meie päringud olid sellised, et OLTP-päringutes kulutasime umbes 20 millisekundit parsimiseks. Seal oli 0,5 millisekundit täitmiseks, 20 millisekundit parsimiseks. Päring – 10 KiB teksti, 170 reavaadet. See on OLTP päring. See küsib 1, 5, 10 rida, mõnikord rohkem.

Aga me ei tahtnud üldse kulutada 20 millisekundit. Me viisime selle nulli. Kõik on suurepärane.

Mida te siit kaasa võtate? Kui teil on Java, siis võtate kaasaegse draiveri versiooni ja olete rõõmsad.

Kui teil on mõni muu keel, siis mõelge – võib-olla vajate ka seda? Sest lõppkeele vaatepunktist, näiteks kui PL 8 või teil on LibPQ, siis ei ole teile ilmselge, et te raiskate aega mitte täitmiseks, vaid parsimiseks ja see tasub üle vaadata. Kuidas? Kõik tasuta.

V.a see, et on vigu, mõned spetsiifikat. Ja neist me just praegu räägime. Suur osa tuleb tööstuslikust arheoloogiast, sellest, mida oleme leidnud, millele oleme sattunud.

Kui päring genereeritakse dünaamiliselt. Sedasi juhtub. Keegi liidab stringid, tulemuseks saab SQL-päring.

Miks see halb on? See on halb, sest iga kord saame lõpuks erineva stringi.

Ja selle erineva reaga peab hashCode uuesti arvutama. See on tõepoolest CPU ülesanne – leida pikka päringuteksti isegi olemasolevas hash' üheselt ei ole nii lihtne. Seetõttu on lihtne järeldus – ärge genereerige päringuid. Hoidke need mingis ühes muutujas. Ja rõõmustage.

Järgmine probleem. Andmetüübid on olulised. On olemas ORM-id, mis ütlevad, et pole vahet, milline NULL, olgu see mingi. Kui on Int, siis ütleme setInt. Ja kui on NULL, siis olgu see alati VARCHAR. Ja mis vahet seal lõpuks on, milline NULL? Andmebaasil on endal kõik arusaadav. Ja selline pilt ei toimi.

Praktikas ei ole andmebaasilt sugugi kõik ühtemoodi. Kui olete esmakordselt öelnud, et see on teil number, ja teisel korral öelnud, et see on VARCHAR, siis ei saa Server-prepared statements'i uuesti kasutada. Sellisel juhul tuleb meie statement jälle uuesti luua.

Kui täidate sama päringut, siis jälgige, et andmetüübid veergudes ei seguneks. Peab jälgima NULL-i. See on sage viga, mis meil esines pärast PreparedStatements'i kasutuselevõttu.

Hästi, lülitasime sisse. Võib-olla kasutasime draiverit. Ja jõudlus langes. Kõik muutus halvaks.

Kuidas see on võimalik? Kas see on bug või funktsioon? Kahjuks ei õnnestunud mõista – kas see on bug või funktsioon. Aga on täiesti lihtne stsenaarium, kuidas seda probleemi paljundada. See ootamatult püüdis meid kinni. Ja seisneb valimises, mis on praktiliselt ainult ühest tabelist. Meil oli muidki selliseid päringuid. Need sisaldasid reeglina kahte-kolme tabelit, kuid siin on selline paljundamissenaarium. Võtke oma andmebaasist mis tahes versioon ja paljundage.

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

Mõte on selles, et meil on kaks veergu, millest kummaski on indeks. Ühes veerus on NULL-i väärtus miljon rida. Teises veerus on vaid 20 rida. Kui täidame ilma sidumata muutujateta, siis kõik töötab hästi.

Kui hakkame täitma sidustatud muutujatega, st täidame märgi "?" või "$1" meie päringule, siis mida me lõpuks saame?

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

Esimene täitmine – nagu alati. Teine – natuke kiiremini. Midagi vahemällu salvestus. Kolmas-neljanda viies. Siis plahvatab – ja just nii. Ja kõige halvem on see, et see juhtub kuuendal täitmisel. Kes teadis, et peab tegema just kuus täitmist, et aru saada, milline seal reaalselt täitmisplaan on?

Kes on süüdi? Mis juhtus? Andmebaas sisaldab optsioonide optimeerimist. Ja see on nagu optimeeritud üldiseks olukorraks. Seetõttu, alates mingist hetkest, liigub ta üldisele plaanile, mis, kahjuks, võib osutuda teistsuguseks. Ta võib osutuda samaks või teistsuguseks. Seal on mingi künnisväärtus, mis toob kaasa sellise käitumise.

Mida sellega teha? Siin on tõenäoliselt keerulisem midagi oletada. On lihtne lahendus, mida me kasutame. See on +0, OFFSET 0. Kindlasti teate selliseid lahendusi. Lihtsalt võtame ja lisame päringusse „+0” ja kõik on hästi. Näitan hiljem.

Ja on veel variant – vaadata hoolikamalt plaane. Arendaja peab mitte ainult päringut kirjutama, vaid ka 6 korda ütlema „explain analyze”. Kui 5, siis ei sobi.

Ja on veel kolmas variant – kirjutada pgsql-hackers-le kiri. Ma kirjutasin, tõsi küll, hetkel ei ole selge – kas see on viga või omadus.

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

Kuni me mõtleme – kas see on viga või omadus, lahendame probleemid. Võtame meie päringu ja lisame „+0”. Kõik on hästi. Kaks märki ja isegi ei pea mõtlema, kuidas ja mis seal on. Väga lihtne. Me lihtsalt käskisime andmebaasil mitte kasutada indeksi selle veeru jaoks. Meil ei ole indeksi veert „+0” ja kõik, andmebaas ei kasuta indeksit, kõik on hästi.

Siin on reegel 6 explain'ist. Hetkel käesolevates versioonides tuleb teha 6 korda, kui teil on seotud muutujad. Kui teil ei ole seotud muutujaid, siis teeme nii. Ja meil lõpuks täpselt see päring kukub. Asi pole keeruline.

Tundub, kui kaua saab? Siin on viga, seal on viga. Tegelikult on viga igal pool.

Vaatame veel. Näiteks, meil on kaks skeemi. Skeem A tabeliga Ы ja skeem B sama tabeliga Ы. Päring – valida andmed tabelist. Mis meil sellega on? Meil on viga. Meil on kõik eelnevalt loetletud. Reegel on selline – viga igal pool, meil on kõik eelnevalt loetletud.

Nüüd küsimus: „Miks?”. Tundub, et on dokumentatsioon, et kui meil on skeem, siis on muutuja „search_path”, mis ütleb, kus peab otsima tabelit. Tundub, et muutuja on olemas.

Mis on probleem? Probleem on selles, et serveri valmistatud avaldused ei kahtle, et search_pathi võib keegi muuta. See väärtus jääb andmebaasi jaoks nagu konstantseks. Ja mõned osad ei pruugi haarata uusi väärtusi.

Loomulikult sõltub see versioonist, millega te testite. See sõltub ka sellest, kui ulatuslikud teie tabelid on. Versioon 9.1 käivitab lihtsalt vanad päringud. Uuemad versioonid võivad aga avastada petu ja öelda, et teil on viga.

Seadke search_path + serveri ettevalmistatud laused =
vahemälu plaan ei tohi muuta tulemuste tüüpi

Kuidas seda parandada? Lihtne retsept – ärge tehke nii. Ärge muutke search_path'i rakenduse töö käigus. Kui muudate, siis on parem luua uus ühendus.

Saame arutada, st avada, arutada ja kirjutada juurde. Võib-olla suudame veenda andmebaasi arendajaid, et juhul, kui keegi muudab väärtust, peaks andmebaas sellest kliendile teatama: „Vaadake, teie väärtus on siin uuendatud. Kas peaksite statements'id lähtestama, uuesti looma?”. Praegu käitub andmebaas salaja ja ei teata mingil moel, et kuskil sees on statements'id muutunud.

Ja ma rõhutan taas – see pole Java puhul tüüpiline. Näeme seda sama ka PL/pgSQL'is sõna-sõnalt. Seal see ka kordub.

Proovime veel andmeid valida. Valime, valime. Meil on tabel, kus on miljon rida. Iga rida üks kilobait. Umbes gigabait andmeid. Ja meil on Java masina töötav mälu 128 megabaiti.

Kasutame nagu kõikides raamatutes soovitatakse voogedastust. St avame resultSet ja loeme sealt andmeid tasapisi. Kas see töötab? Kas see ei kuku mälu tõttu? Kas see loeb natuke? Usume andmebaasi, usume Postgressesse. Ei usu. Kukume OutOfMemory? Kes on kukkunud OutOfMemory? Ja kes suutis pärast seda parandada? Keegi suutis parandada.

Kui teil on miljon rida, siis ei saa lihtsalt nii valida. Peab kindlasti olema OFFSET/LIMIT. Kes on sellise variandi poolt? Ja kes on variandi poolt, et tuleb autoCommit'iga mängida?

Siin, nagu tavaliselt, osutub kõige ootamatum variant õigeks. Ja kui te äkitselt lülitate autoCommit'i välja, siis see aitab. Miks nii? Teadusele see ei ole teada.

Aga vaikimisi valivad kõik kliendid, kes ühenduvad Postgres andmebaasiga, andmeid tervikuna. PgJDBC selle osas pole erand, valib kõik read.

On variatsioon FetchSize'il, st saate eraldi statements'i tasandil öelda, et siit palun valige andmeid 10, 50. Kuid see ei tööta, kuni te ei lülita autoCommit'i välja. Lülitasite autoCommit'i välja – hakkab tööle.

Koodi läbimine ja igal pool setFetchSize seadmine on ebamugav. Seetõttu tegime sellise seadistuse, mis määrab vaikimisi väärtuse kogu ühenduse jaoks.

Nii et me seda ütlesime. Seadisime parameetri. Ja mis me nüüd saime? Kui valime väikese, näiteks 10 rida, siis on meie kulud üsna suured. Seetõttu peaksime selle väärtuse seadma umbes sajale.

Ideaalis võiksime muidugi õppida piirama ka baitides, aga retsept on selline: seab defaultRowFetchSize üle saja ja oleme rahul.

Liigume andmete sisestamise juurde. Sisestamine on lihtsam, seal on erinevaid variante. Näiteks, INSERT, VALUES. See on hea variant. Võime rääkida "INSERT SELECT". Praktikas on need üks ja sama. Erinevust jõudluses ei ole.

Raamatud ütlevad, et tuleb täita Batch statement, raamatud ütlevad, et võib täita keerulisemaid käske mitme sulgudega. Ja Postgres'is on suurepärane funktsioon - võib teha COPY, st teha seda kiiremini.

Kui mõõta, võib teha uuesti mitmeid huvitavaid avastusi. Kuidas me tahame, et see töötab? Tahame mitte parsida ja mitte täiendavaid käske täita.

Praktikas ei lase TCP meil nii teha. Kui klient on hõivatud päringu saatmisega, siis andmebaas proovib meile vastuseid saata, aga päringuid ei loeta. Lõpuks ootab klient andmebaasi, kuni see päringu loeb, samal ajal kui andmebaas ootab klienti, kuni see vastuse loeb.

Seetõttu peab klient perioodiliselt saatma sünkroniseerimise paketi. Üksikud võrgu suhtlused, aeglane ajakadu.

Ja mida rohkem me neid lisame, seda halvemaks läheb. Draiver on üsna pessimistlik ja lisab neid üsna sageli, umbes iga 200 rea järel, sõltuvalt ridade suurusest jms.

https://github.com/pgjdbc/pgjdbc/pull/380

Juhtub, et parandad ühte rida ja kõik kiireneb 10 korda. Nii juhtub. Miks? Nagu tavaliselt, mingi konstandi oli seal juba kasutatud. Ja väärtus „128” tähendas - mitte kasutada batchingut.

Java microbenchmark harness

Hea, et see ei sattunud ametlikku versioonisse. Leidsime selle enne, kui hakkasime välja andma väljaannet. Kõik väärtused, mida ma nimetan, põhinevad kaasaegsetel versioonidel.

Mõõgame. Me mõõdame lihtsat InsertBatch'i. Me mõõdame mitmekordset InsertBatch'i, st sama asi, kuid palju values. Kaval käik. Mitte kõik ei oska nii teha, aga see on lihtne käik, palju lihtsam kui COPY.

Saab teha COPY.

Ja saab seda teha struktuurides. Deklareeri kasutaja vaikimisi tüüp, edastades massiivi ja INSERT otse tabelisse.

Kui avate lingi: pgjdbc/ubenchmsrk/InsertBatch.java, siis see kood on GitHubis. Saate vaadata täpselt, milliseks päringud seal genereeritakse. See ei ole olulised.

Me käivitasime selle. Ja esimene asi, mille me mõistsime, oli see, et batchi mittekasutamine on lihtsalt välistatud. Kõik batchimise variandid on nullid, st täitmise aeg on praktiliselt null võrreldes ühekordse täitmisega.

Me sisestame andmed. Seal on väga lihtne tabel. Kolm veergu. Ja mida me siin näeme? Me näeme, et kõik need kolm varianti on umbes võrreldavad. Ja COPY on loomulikult parem.

See, kui me tükikaupa sisestame. Kui me rääkisime, et üks väärtus VALUES, kaks väärtust VALUES, kolm väärtust VALUES või kui me neid 10 komaga nimetasime. See peaks olema nüüd horisontaalsel tasandil. 1, 2, 4, 128. On näha, et Batch Insert, mis on siniseks joonistatud, muudab asja kergemaks. St kui sisestate ükshaaval või isegi nelja kaupa, siis see aitab kahel korral paremini, lihtsalt seetõttu, et me panime VALUESisse veidi rohkem. Vähem EXECUTE operatsioone.

Kopeerimise kasutamine väikeste mahtude korral on äärmiselt vähelootustandev. Ma ei joonistanud isegi kahe esimesega. Need lähevad taevasse, st need rohelised numbrid jaoks COPY.

COPY-d tuleks kasutada, kui teie andmemaht on vähemalt üle saja rea. Ühenduse avamise kulud on suured. Ja, ausalt öeldes, ma ei ole selle poolega süvenenud. Batch'i optimeerisin, COPY-d mitte.

Mida me edasi teeme? Me mõõdame. Saame aru, et peame kasutama kas struktuure või nutikat batching'u, mis liidab mitu väärtust.

Mida tuleb tänasest ettekandest välja tuua?

• PreparedStatement – see on meie kõik. See toob väga palju kaasa jõudlusele. See toob suure alusikka.
• Ja tuleb teha EXPLAIN ANALYZE 6 korda.
• Ja tuleb lahjendada OFFSET 0 ning trikkidega nagu +0, et korvata veel jäänud protsent meie probleemsetest päringutest.

Allikas: habr.com