Pakun tutvuda 2016. aasta alguses Vladimir Sitnikovi aruande "PostgreSQL ja JDBC - võtame kõik välja" tõlkega
Tere päevast! Minu nimi on Vladislav Sitnikov. Olen töötanud 10 aastat ettevõttes NetCracker. Peamiselt tegeleme tootlikkusega. Kõik, mis on seotud Java ja SQL-iga, on minu kirg.
Ja täna räägin sellest, millega me ettevõttes silmitsi seisime, kui alustasime PostgreSQL kasutamist andmebaasiserverina. Peamiselt töötame Java-ga, kuid see, millest ma täna räägin, ei piirdu ainult Java-ga. Praktika on näidanud, et see esineb ka teistes keeltes.
Räägime:
- andmete valimisest.
- Andmete salvestamisest.
- Ja ka tootlikkusest.
- Ja varjatest probleemidest, mis seal võivad olla.
Alustame lihtsast küsimusest. Valime tabelist ühe rea primaarvõtme alusel.
Andmebaas asub samas hostis. Ja kõik see võtab aega 20 millisekundit.
Need 20 millisekundit on tõesti palju. Kui teil on 100 sellist päringut, siis kulutate sekundite kaupa aega nende töötlemiseks, st raiskate aega.
Me ei armasta seda teha ja vaatame, mida andmebaas meile pakub. Andmebaas pakub meile kahte võimalust päringute täitmiseks.
Esimene variant on lihtne päring. Miks see on hea? Sest me võtame selle ja saadame, ja midagi rohkemat ei ole vaja.
Andmebaasil on ka laiendatud päring, mis on keerulisem, kuid funktsionaalsem. Saame saata eraldi päringu parsingu, täitmise ja muutujate sidumise jaoks jne.
Super laiendatud päring – seda me ei kata selgituses. Meil võib olla midagi, mida me soovime andmebaasist, ja on olemas soovide nimekiri, mis on mingil kujul koostatud, st see, mida me tahame, kuid mis ei ole praegu ja lähima aasta jooksul võimalik. Seega lihtsalt kirjutame üles ja käime peamisi inimesi kõigutamas.
Aga see, mida me teha saame, on lihtne päring ja laiendatud päring.
Milline on iga lähenemise eripära?
Lihtsat päringut on hea kasutada ühekordseks täitmiseks. Üks kord täidetud ja unustatud. Probleem on selles, et see ei toeta binaarset andmeformaati, st mõne suure jõudlusega süsteemi jaoks see ei sobi.
Laiendatud päring - võimaldab säästa aega sql-parsingu protsessis. Just seda me tegime ja hakkasime kasutama. See on meile äärmiselt, äärmiselt kasulik olnud. Seal on mitte ainult sääst sql-parsimisel. On ka andmete edastamise sääst. Andmete edastamine binaarformaadis on palju tõhusam.
Liigume praktika juurde. Nii näeb välja tüüpiline rakendus. See võib olla Java jne.
Me lõime statement'i. Täitsime käsu. Lõime close. Kus on viga? Mis on probleem? Pole probleeme. Nii kirjutavad kõik raamatud. Nii tuleb kirjutada. Kui soovite maksimaalset jõudlust, kirjutage nii.
Aga praktika on näidanud, et see ei tööta. Miks? Sest meil on meetod „close“. Ja kui me nii teeme, on see andmebaasi vaatenurgast nagu suitsetaja töö andmebaasiga. Me ütlesime „PARSE EXECUTE DEALLOCATE“.
Miks need liigsed statement'id ja andmete väljavõtmine? Need ei ole kellelegi vajalikud. Kuid tavaliselt juhtub PreparedStatement'iga just nii, et kui me need sulgeme, suletakse need andmebaasis kõik. See ei ole see, mida me tahame.
Me tahame, et tervetena saaksime andmebaasiga töötada. Üks kord tegime ja koostasime oma statement'i, mille pärast täidame seda mitu korda. Tegelikult on palju kordi – see on üks kord, kui rakenduse jooksul statement'it parsiti. Ja erinevates REST-ides kasutame sama statement id-d. See on meie eesmärk.
Kuidas me seda saavutame?
Väga lihtsalt – statements ei tohi sulgeda. Kirjutame nii: «prepare» «execute».
Kui me sellise käivitame, on selge, et meie sees midagi ületäitub. Kui ei ole selge, siis saame mõõta. Teeme bänkimise, milles on see lihtne meetod. Loome statement'i. Käivitame mingisugusel draiveri versioonil ja saame, et see kukub üsna kiiresti kokku kogu mälu kaotusega, mis meil seal on.
Selge on, et selliseid vigu on lihtne parandada. Ma ei hakka neist rääkima. Kuid ma ütlen, et uues versioonis töötab see palju kiiremini. Meetod on rumal, kuid siiski.
Kuidas õigesti töötada? Mida me peame selleks tegema?
Tegelikult suletakse rakendustes alati statements. Kõikides raamatutes kirjutatakse, et need tuleb sulgeda, vastasel juhul lekkib mälu.
Ja PostgreSQL ei oska päringute vahemälu talletada. Iga sessioon peab ise selle vahemälu looma.
Me ei soovi ka aega raisata parsimisele.
Ja nagu tavaliselt on meil kaks varianti.
Esimene variant – me ütleme, et laseme kõik PgSQL-i kokku panna. Seal on vahemälu. See kõik salvestab. Tuleb suurepärane lahendus. Oleme seda kontrollinud. Meil on 100500 päringut. Ei toimi. Me ei nõustu – me ei muuda päringuid protseduurideks. Ei-ei.
Meil on teine variant – võtta ja ise välja töötada. Avame lähtekoodi, hakkame skriptima. Skriptime-skriptime. Selgub, et see pole nii keeruline.
See ilmus augustis 2015. Praegu on olemas kaasaegsem versioon. Ja kõik on suurepärane. See töötab nii hästi, et me ei muuda rakenduses midagi. Oleme isegi lõpetanud mõtlema PgSQL suunas, st sellest on piisavalt, et vähendada kõik halduskulud praktiliselt nulli.
Seega aktiveeritakse Server-prepared statements viiendal täitmisel, et mitte raisata mälu andmebaasis iga üheainsa korduva päringu peale.
Võib küsida – kus on numbrid? Mida te saate? Siin ma numbreid ei anna, sest igal päringul on oma numbrid.
Meil olid sellised päringud, et me kulutasime OLTP-päringutel umbes 20 millisekundit parsimiseks. Seal oli 0,5 millisekundit täitmiseks ja 20 millisekundit parsimiseks. Päring – 10 KiB teksti, 170 rida plaani. See on OLTP päring. See küsib 1, 5, 10 rida, mõnikord rohkem.
Kuid me ei soovinud kulutada 20 millisekundit. Me viimistlesime selle nulli. Kõik on suurepärane.
Mida saate siit õppida? Kui teil on Java, siis võtate kaasa moodsa versiooni draiverist ja naudite.
Kui teil on mõni muu keel, siis mõelge – võib-olla peaksite seda ka kaaluma? Sest lõppkeele, näiteks PL 8 või LibPQ puhul, ei pruugi olla ilmne, et kulutate aega mitte täitmisele, vaid parsimisele, ja seda tasub kontrollida. Kuidas? Kõik on tasuta.
Välja arvatud juhul, kui esinevad vead või mõningad eripärad. Ja just neist me praegu räägime. Suur osa on tööstuslikust arheoloogiast, sellest, mida me avastasime, millele me sattusime.
Kui päring genereeritakse dünaamiliselt. Nii ka juhtub. Keegi liidab read kokku ja see teeb SQL-päringu.
Miks see halb on? See on halb selle poolest, et iga kord saame me lõpuks erineva stringi.
Ja selle erineva stringi hashCode tuleb uuesti arvutada. See on tõeliselt CPU ülesanne – leida pikka päringuteksti isegi olemasolevas hash’is ei ole lihtne. Seega järeldus on selge – ärge genereerige päringuid. Hoidke need ühes muutuja. Ja nautige.
Järgmine probleem. Andmetüübid on olulised. On ORM-e, mis ütlevad, et pole oluline, milline NULL, las olla mingi. Kui on Int, siis me ütleme setInt. Ja kui on NULL, siis las VARCHAR on alati. Ja mis vahet seal on, mis NULL seal lõpuks on? Andmebaas mõistab ise kõike. Ja selline pilt ei toimi.
Praktikas ei ole andmebaasil sugugi ükskõik. Kui te esmakordselt ütlete, et see on number, ja teiseks ütlete, et see on VARCHAR, siis ei saa Server-prepared statements’i uuesti kasutada. Ja sellisel juhul tuleb meie statement uuesti luua.
Kui teete sama päringut, siis hoidke silma peal, et andmetüübid teie veergudes ei seguneks. Tuleb jälgida NULL-e. See on sage viga, millega oleme kokku puutunud pärast PreparedStatements’i kasutusele võtmist.
Hästi, lülitasime sisse. Võtsime võib-olla draiveri. Ja jõudlus langes. Kõik läks halvaks.
Kuidas nii juhtub? Kas see on viga või omadus? Kahjuks ei õnnestunud mõista – kas see on viga või omadus. Kuid on täiesti lihtne stsenaarium, kuidas seda probleemi uuesti tekitada. See tabas meid täiesti ootamatult. Ja see seisneb tegelikult teabe tõmbamises ühest ainust tabelist. Meil on muidugi olnud rohkem selliseid päringuid. Need sisaldasid tavaliselt kahte-kolme tabelit, kuid on selline stsenaarium, mida saab reproduktsiooniks kasutada. Võtke oma andmebaasis mis tahes versioon ja proovige uuesti.
Oluline on see, et meil on kaks veergu, millest igaüks on indekseeritud. Ühes veerus on NULL väärtusega miljon rida. Ja teises veerus on vaid 20 rida. Kui me teeme päringu ilma seotud muutujateta, töötab kõik hästi.
Kui me hakkame päringut tegema seotud muutujatega, st kui kasutame märki «?» või «$1» oma päringus, siis mida me lõpuks saame?
Esimene täitmine – nagu tavaliselt. Teine – veidi kiiremini. Midagi on vahemä_BUFFERd_atud. Kolmas-neljas-viies. Siis aga plaks! – ja nii see läheb. Ja kõige halvem on see, et see juhtub kuuendal täitmisel. Kes oleks teadnud, et tuleb teha just kuus täitmist, et aru saada, milline on tegelikult täitmisplaan?
Kes vastutab? Mis juhtus? Andmebaas sisaldab optimeerimist. Ja see on üldiselt optimeeritud tavalise juhtumi jaoks. Seega, alates teatud hetkest, läheb see üle üldisele plaanile, mis kahjuks võib olla erinev. See võib olla sama, aga ka teine. Seal on mingi künnise väärtus, mis viib sellise käitumiseni.
Mida sellega teha? Siin on muidugi keerulisem midagi eeldada. On olemas lihtne lahendus, mida me kasutame. See on +0, OFFSET 0. Kindlasti tunnete selliseid lahendusi. Lihtsalt võtame ja lisame päringusse „+0” ja kõik on hästi. Näitan hiljem.
Ja on veel üks variant – vaadata hoolikamalt plaane. Arendaja peab mitte ainult päringu kirjutama, vaid ka 6 korda ütlema „explain analyze”. Kui 5, siis ei sobi.
Ja on veel kolmas variant – kirjutada pgsql-hackers'i kirja. Ma kirjutasin, aga hetkel pole selge, kas see on bug või funktsioon.
Kui me mõtleme, kas see on viga või funktsioon, siis parandame ära. Võtame meie päringu ja lisame "+0". Kõik on korras. Kaks sümbolit ja isegi ei pea mõtlema, kuidas seal ja mis seal on. Väga lihtne. Me lihtsalt keelasime andmebaasi indeksi kasutamise selle veeru puhul. Meil pole indeksi veerus "+0" ja kõik, andmebaas ei kasuta indekseid, kõik on korras.
See on reegel 6 explain'i kohta. Praegustes versioonides tuleb seda teha 6 korda, kui teil on seotud muutujad. Kui seotud muutujaid ei ole, siis teeme nii. Ja meil on lõpuks just see päring, mis kukub. Pole keeruline.
Tundub, et kui palju võib olla? Siin on viga, seal on viga. Tegelikult on viga igal pool.
Vaadakem veel. Näiteks on meil kaks skeemi. Skeem A, kus on tabel Y, ja skeem B, kus on tabel Y. Päring on – valida andmed tabelist. Mis siis juhtub? Meil tekib viga. Meil on kõik eelnevalt loetletud. Reegel on selline – viga igal pool, meil on kõik eelnevalt loetletud.
Nüüd on küsimus: "Miks?". Tundub, et on dokumentatsioon, et kui meil on skeem, siis on muutuja "search_path", mis ütleb, kus tuleb tabelit otsida. Tundub, et muutuja on olemas.
Mis probleem on? Probleem on selles, et serveri ettevalmistatud lausete puhul ei arvata, et search_path võib keegi muuta. See väärtus jääb andmebaasile nagu konstantseks. Mõned osad ei pruugi uusi väärtusi üles korjata.
Muidugi sõltub see versioonist, millel te testite. See sõltub ka sellest, kui palju teie tabelid erinevad. Versioon 9.1 lihtsalt täidab vanu päringuid. Uuemad versioonid võivad näha petu ja öelda, et teil on viga.
Kuidas seda parandada? Lihtne retsept – ärge tehke nii. Ärge muutke search_path'i rakenduse töö käigus. Kui te muudate, on parem luua uus ühendus.
Saame arutada, st avada, arutada, täiendada. Võib-olla suudame veenda andmebaasi arendajaid, et juhul, kui keegi muudab väärtust, peaks andmebaas klienti teavitama: "Vaadake, teil on siin väärtus uuendatud. Kas peaksite statements'i värskendama, uuesti looma?" Praegu käitub andmebaas peidus ja ei teavitada kuidagi, et kuskil sees on statements muutunud.
Ja rõhutan veel kord - see ei ole Java jaoks tüüpiline. Me näeme seda sama PL/pgSQL-is üks ühele. Kuid seal see taaselustub.
Proovime veel andmeid valida. Valime, valime. Meil on tabel, kus on miljon rida. Iga rida on üks kilobait. Umbes gigabait andmeid. Ja meil on Java masinas 128 megabaidi töömälu.
Kasutame nagu kõikides raamatutes soovitatakse, voogedastust. St avame resultSet-i ja loeme sealt andmeid järk-järgult. Kas see töötab? Kas mälu puhub üles? Kas loeb natuke? Usume, et Postgres töötab. Ei usu. Kas saame OutOfMemory? Kes on saanud OutOfMemory? Ja kes suutis pärast seda parandada? Kas keegi suutis parandada.
Kui sul on miljon rida, siis ei saa lihtsalt niimoodi valida. Peab kindlasti olema OFFSET/LIMIT. Kes on sellise variandi poolt? Ja kes on variandi poolt, et tuleb autoCommit'iga mängida?
Siin on nagu tavaliselt kõige ootamatum variant õige. Ja kui sa juhuslikult autoCommiti välja lülitad, siis see aitab. Miks nii? Teadusele sellest ei ole teada.
Aga vaikimisi valivad kõik Postgresi andmebaasiga ühenduvate klientide andmed täielikult. PgJDBC selles osas ei ole erand, valib kõik read.
On the subject of FetchSize, there is a variation, meaning you can specify at the level of a single statement to retrieve data in chunks of 10, 50, etc. However, this doesn't work until you turn off autoCommit. Once autoCommit is turned off, it starts to function.
However, going through the code and setting setFetchSize everywhere is inconvenient. Therefore, we created a setting that defines the default value for the entire connection.
So, we configured this parameter. And what do we have? If we choose a small number, for example, retrieving 10 rows at a time, we incur significant overhead. Thus, this value should be set around a hundred.
Ideally, we should also learn to limit this in bytes, but the rule of thumb is: set defaultRowFetchSize above a hundred and enjoy the benefits.
Now, let's move on to data insertion. Insertion is simpler, and there are different options. For instance, INSERT, VALUES. This is a good option. You can also say "INSERT SELECT". In practice, there is no difference; it performs the same.
Raamatud ütlevad, et Batch-laused tuleb täita, raamatud ütlevad, et on võimalik täita keerulisemaid käske mitme sulgudega. Ja Postgresel on suurepärane funktsioon – saab kasutada COPY-d, st teha seda kiiremini.
Kui mõõta, siis võib mõningaid huvitavaid avastusi taas teha. Kuidas me soovime, et see toimiks? Soovime, et ei peaks analüüsima ja mitte ühtegi liigset käsku täitma.
Praktikas ei lase TCP meil nii teha. Kui klient on hõivatud päringu saatmisega, siis andmebaas üritab meile vastuseid saata, kuid päringut ei loeta. Tulemuseks on see, et klient ootab andmebaasi, kuni see päringu loeb, ja andmebaas ootab klienti, kuni see vastuse loeb.
Seega on klient sunnitud perioodiliselt saatma sünkroniseerimispakette. Liigsed võrguühendused, liigsed ajakaotused.
Ja mida rohkem me neid lisame, seda halvemaks asi muutub. Juhtme abil on see üsna pessimistlik ja lisab neid sageli, ligikaudu iga 200 rea järel, sõltuvalt ridade suurusest jne.
Kordagi väike muudatus võib kümme korda kiiremaks muuta. Miks see nii on? Nagu tavaliselt, kuskil on juba kasutatud sama konstandi. Ja väärtus '128' tähendas, et batchingut ei tohi kasutada.
Hea, et see ametlikku versiooni ei saanud. Avastasime enne, kui hakkasime väljalaset tegema. Kõik väärtused, mida mainin, põhinevad uusimatel versioonidel.
Mõõdame nüüd. Mõõdame InsertBatch'i lihtsana. Mõõdame InsertBatch'i mitmekordse, st sama asja, aga palju väärtusi. Nipid, mida mitte kõik ei oska, kuid see on nii lihtne käik, palju lihtsam kui COPY.
Saame teha COPY.
Ja seda saab teha struktuuride peal. Deklareeri kasutaja vaikimisi tüüp, edasta massiiv ja INSERT otse tabelisse.
Kui avate lingi: pgjdbc/ubenchmsrk/InsertBatch.java, siis see kood on GitHubis. Saate vaadata, millised päringud seal genereeritakse. Ei ole oluline.
Oleme käivitanud. Ja esimene, mida mõistsime, on see, et batchi mitte kasutamine on lihtsalt võimatu. Kõik batching'u variandid on null, st täitmise aeg on praktiliselt null võrreldes ühe korraliku täitmisega.
Kandmeed salvestatakse. Seal on üsna lihtne tabel. Kolm veergu. Ja mida me siin näeme? Me näeme, et kõik need kolm varianti on enam-vähem võrreldavad. Ja COPY on loomulikult parem.
See on siis, kui me sisestame tükkide kaupa. Kui me rääkisime, et ühte väärtust VALUES, kahte väärtust VALUES, kolme väärtust VALUES või me panime seal 10 komaga kokku. See on just praegu horisontaalselt. 1, 2, 4, 128. On näha, et Batch Insert, mis on sinisega joonistatud, muudab selle lihtsamaks. See tähendab, et kui sisestate ühe kaupa või isegi neli, siis muutub see kaks korda paremaks, kuna me VALUES sisse panime veidi rohkem. Vähem EXECUTE operatsioone.
COPY kasutamine väikeste andmemahtude korral on äärmiselt ebaefektiivne. Ma ei joonistanud isegi esimest kahte. Need lähevad taevasse, st need rohelised numbrid COPY jaoks.
COPY tuleb kasutada, kui teil on andmemaht vähemalt üle saja rea. Ühenduse avamise kulud on suured. Ja ausalt öeldes, ma ei ole seda teemat sügavamalt uurinud. Batch’i ma optimeerisin, COPY-d mitte.
Mida me edasi teeme? Mõõdame. Saame aru, et tuleb kasutada kas struktuure või nutikat batch’i, mis ühendab mitu väärtust.
Mida peaks tänasest ettekandest välja tooma?
- PreparedStatement – see on meie kõik. See toob palju kasu meie jõudlusele. See annab suure kausi tõrva.
- Ja EXPLAIN ANALYZE tuleb teha 6 korda.
- Ja tuleb kasutada OFFSET 0 ja trikke nagu +0, et reguleerida ülejäänud protsenti meie probleemsetest päringutest.
Allikas: habr.com
