Suosittelen, että luet tekstin Vladimir Sitnikovin vuoden 2016 alun raportista "PostgreSQL ja JDBC puristavat kaiken mehun"
Hyvää iltapäivää Nimeni on Vladimir Sitnikov. Olen työskennellyt NetCrackerilla 10 vuotta. Ja olen lähinnä tuottavuuden parissa. Kaikkea Javaan liittyvää, kaikkea SQL:ään liittyvää rakastan.
Ja tänään puhun siitä, mitä kohtasimme yrityksessä, kun aloimme käyttää PostgreSQL:ää tietokantapalvelimena. Ja työskentelemme enimmäkseen Javalla. Mutta se, mitä aion kertoa teille tänään, ei koske vain Javaa. Kuten käytäntö on osoittanut, tämä tapahtuu myös muilla kielillä.
Me tulemme juttelemaan:
- tietojen näytteenotosta.
- Tietoja tietojen säästämisestä.
- Ja myös suorituskyvystä.
- Ja sinne haudatuista vedenalaisista haravoista.
Aloitetaan yksinkertaisella kysymyksellä. Valitsemme taulukosta yhden rivin ensisijaisen avaimen perusteella.
Tietokanta sijaitsee samalla isännällä. Ja kaikki tämä viljely kestää 20 millisekuntia.
Nämä 20 millisekuntia ovat paljon. Jos sinulla on 100 tällaista pyyntöä, käytät aikaa sekunnissa näiden pyyntöjen selaamiseen, eli hukkaamme aikaa.
Emme halua tehdä tätä ja katsoa, mitä tukikohta tarjoaa meille tähän. Tietokanta tarjoaa meille kaksi vaihtoehtoa kyselyjen suorittamiseen.
Ensimmäinen vaihtoehto on yksinkertainen pyyntö. Mitä hyvää siinä on? Se, että otamme sen ja lähetämme sen, eikä mitään muuta.
Tietokannassa on myös edistynyt kysely, joka on hankalampi, mutta toimivampi. Voit lähettää erikseen jäsennys-, suoritus-, muuttujan sidontapyynnön jne.
Erittäin laajennettu kysely on asia, jota emme käsittele tässä raportissa. Haluamme kenties tietokannasta jotain ja on olemassa toivelista, joka on jossain muodossa muodostunut, eli tätä haluamme, mutta se on mahdotonta nyt ja ensi vuonna. Joten me vain äänitimme sen ja lähdemme ravistelemaan päähenkilöitä.
Ja mitä voimme tehdä, on yksinkertainen kysely ja laajennettu kysely.
Mitä erityistä kussakin lähestymistavassa on?
Yksinkertainen kysely on hyvä kertaluonteiseen suoritukseen. Kerran tehty ja unohdettu. Ja ongelma on, että se ei tue binääritietomuotoa, eli se ei sovellu joihinkin korkean suorituskyvyn järjestelmiin.
Laajennettu kysely – voit säästää jäsennysaikaa. Näin teimme ja aloimme käyttää. Tämä todella, todella auttoi meitä. Säästöjä ei saavuteta vain jäsentämisessä. Tiedonsiirrossa on säästöjä. Tietojen siirto binäärimuodossa on paljon tehokkaampaa.
Jatketaan harjoittelua. Tältä näyttää tyypillinen sovellus. Se voi olla Java tms.
Teimme lausunnon. Suoritti käskyn. Luotu lähellä. Missä tässä on vika? Mikä on ongelma? Ei ongelmaa. Näin sanotaan kaikissa kirjoissa. Näin se pitää kirjoittaa. Jos haluat maksimaalisen suorituskyvyn, kirjoita näin.
Mutta käytäntö on osoittanut, että tämä ei toimi. Miksi? Koska meillä on "läheinen" menetelmä. Ja kun teemme tämän, tietokannan näkökulmasta käy ilmi, että se on kuin tupakoitsija, joka työskentelee tietokannan kanssa. Sanoimme "PARSE EXECUTE DEALLOCATE".
Miksi tämä ylimääräinen lausuntojen luominen ja purkaminen? Kukaan ei tarvitse niitä. Mutta PreparedStatementsissa yleensä tapahtuu, että kun suljemme ne, ne sulkevat kaiken tietokannassa. Tätä emme halua.
Haluamme terveiden ihmisten tavoin työskennellä tukikohdan kanssa. Otimme ja valmistelimme lausuntomme kerran, sitten toteutamme sen monta kertaa. Itse asiassa monta kertaa - tämä on kerran sovellusten koko elinkaaren aikana - ne on jäsennetty. Ja käytämme samaa lauseketta eri REST:issä. Tämä on tavoitteemme.
Miten voimme saavuttaa tämän?
Se on hyvin yksinkertaista - lausuntoja ei tarvitse sulkea. Kirjoitamme sen näin: "valmistele" "suorita".
Jos käynnistämme jotain tällaista, on selvää, että jotain vuotaa jossain yli. Jos se ei ole selvä, voit kokeilla sitä. Kirjoitetaan vertailuarvo, joka käyttää tätä yksinkertaista menetelmää. Luo lausunto. Käynnistämme sen jossain ajurin versiossa ja huomaamme, että se kaatuu melko nopeasti ja menettää kaiken sen muistin.
On selvää, että tällaiset virheet on helppo korjata. En aio puhua niistä. Mutta sanon, että uusi versio toimii paljon nopeammin. Menetelmä on typerä, mutta silti.
Kuinka toimia oikein? Mitä meidän on tehtävä tämän eteen?
Todellisuudessa sovellukset sulkevat aina lausunnot. Kaikissa kirjoissa sanotaan, että sulje se, muuten muisti vuotaa.
Ja PostgreSQL ei osaa tallentaa kyselyitä välimuistiin. On välttämätöntä, että jokainen istunto luo tämän välimuistin itselleen.
Emme myöskään halua tuhlata aikaa jäsentämiseen.
Ja kuten tavallista, meillä on kaksi vaihtoehtoa.
Ensimmäinen vaihtoehto on, että otamme sen ja sanomme, että kääritään kaikki PgSQL:ään. Siellä on kätkö. Se tallentaa kaiken välimuistiin. Siitä tulee hienoa. Näimme tämän. Meillä on 100500 XNUMX pyyntöä. Ei toimi. Emme suostu muuttamaan pyyntöjä toimenpiteiksi manuaalisesti. Ei ei.
Meillä on toinen vaihtoehto - ota se ja leikkaa se itse. Avaamme lähteet ja aloitamme leikkaamisen. Näimme ja näimme. Kävi ilmi, että sen tekeminen ei ole niin vaikeaa.
Tämä ilmestyi elokuussa 2015. Nyt on olemassa modernimpi versio. Ja kaikki on hienoa. Se toimii niin hyvin, että emme muuta sovelluksessa mitään. Ja lopetimme jopa ajattelun PgSQL:n suuntaan, eli tämä riitti, että saimme kaikki yleiskustannukset lähes nollaan.
Vastaavasti palvelimen valmistelemat käskyt aktivoidaan viidennessä suorituskerrassa, jotta vältytään tietokannan muistin tuhlaamiselta jokaisen kertapyynnön yhteydessä.
Saatat kysyä – missä numerot ovat? Mitä saat? Ja tässä en anna numeroita, koska jokaisella pyynnöllä on omansa.
Kyselymme olivat sellaisia, että käytimme noin 20 millisekuntia OLTP-kyselyiden jäsentämiseen. Suoritukseen oli aikaa 0,5 millisekuntia ja jäsentämiseen 20 millisekuntia. Pyyntö – 10 KiB tekstiä, 170 riviä suunnitelmaa. Tämä on OLTP-pyyntö. Se pyytää 1, 5, 10 riviä, joskus enemmän.
Mutta emme halunneet hukata 20 millisekuntia ollenkaan. Vähensimme sen nollaan. Kaikki on hienoa.
Mitä voit viedä täältä pois? Jos sinulla on Java, ota ohjaimen nykyaikainen versio ja iloitse.
Jos puhut eri kieltä, mieti - ehkä tarvitset myös tätä? Koska esimerkiksi lopullisen kielen kannalta, jos sinulla on PL 8 tai sinulla on LibPQ, niin sinulle ei ole selvää, että käytät aikaa muuhun kuin suoritukseen, jäsentämiseen, ja tämä kannattaa tarkistaa. Miten? Kaikki on ilmaista.
Paitsi että siinä on virheitä ja joitain erikoisuuksia. Ja me puhumme niistä juuri nyt. Suurin osa siitä tulee käsittelemään teollista arkeologiaa, siitä mitä löysimme, mitä löysimme.
Jos pyyntö luodaan dynaamisesti. Se tapahtuu. Joku liimaa merkkijonot yhteen, mikä johtaa SQL-kyselyyn.
Miksi hän on huono? Se on huono, koska joka kerta päädymme eri merkkijonoon.
Ja tämän eri merkkijonon hashCode on luettava uudelleen. Tämä on todella CPU-tehtävä - pitkän pyyntötekstin löytäminen edes olemassa olevasta hashista ei ole niin helppoa. Siksi johtopäätös on yksinkertainen - älä luo pyyntöjä. Tallenna ne yhteen muuttujaan. Ja iloitse.
Seuraava ongelma. Tietotyypit ovat tärkeitä. On ORM:ita, jotka sanovat, että ei ole väliä millainen NULL on, olkoon jonkinlainen. Jos Int, sanomme setInt. Ja jos NULL, niin se olkoon aina VARCHAR. Ja mitä eroa sillä loppujen lopuksi on, mikä NULL on? Tietokanta itse ymmärtää kaiken. Ja tämä kuva ei toimi.
Käytännössä tietokanta ei välitä ollenkaan. Jos sanoit ensimmäisen kerran, että tämä on numero, ja toisella kerralla, että se on VARCHAR, on mahdotonta käyttää uudelleen palvelimella valmistettuja lausuntoja. Ja tässä tapauksessa meidän on luotava lausuntomme uudelleen.
Jos suoritat samaa kyselyä, varmista, että sarakkeen tietotyypit eivät ole sekaisin. Sinun on varottava NULL:ia. Tämä on yleinen virhe, kun aloitimme PreparedStatementsin käytön
Okei, päällä. Ehkä he ottivat kuljettajan. Ja tuottavuus laski. Asiat menivät huonosti.
Miten tämä tapahtuu? Onko tämä bugi vai ominaisuus? Valitettavasti ei ollut mahdollista ymmärtää, onko tämä vika vai ominaisuus. Mutta tämän ongelman toistamiseen on olemassa hyvin yksinkertainen skenaario. Hän väijytti meidät täysin odottamatta. Ja se koostuu näytteenotosta kirjaimellisesti yhdestä taulukosta. Meillä oli tietysti enemmän tällaisia pyyntöjä. Yleensä ne sisälsivät kaksi tai kolme pöytää, mutta on olemassa tällainen toistoskenaario. Ota mikä tahansa versio tietokannastasi ja pelaa sitä.
Asia on siinä, että meillä on kaksi saraketta, joista jokainen on indeksoitu. Yhdessä NULL-sarakkeessa on miljoona riviä. Ja toisessa sarakkeessa on vain 20 riviä. Kun suoritamme ilman sidottuja muuttujia, kaikki toimii hyvin.
Jos aloitamme suorittamisen sidotuilla muuttujilla, eli suoritamme "?" tai "$1" pyyntöömme, mitä saamme?
Ensimmäinen suoritus on odotetusti. Toinen on hieman nopeampi. Jotain oli välimuistissa. Kolmas, neljäs, viides. Sitten bang - ja jotain sellaista. Ja pahinta on, että tämä tapahtuu kuudennen teloituksen yhteydessä. Kuka tiesi, että oli tarpeen tehdä täsmälleen kuusi teloitusta ymmärtääkseen, mikä todellinen teloitussuunnitelma oli?
Kuka on syyllinen? Mitä tapahtui? Tietokanta sisältää optimoinnin. Ja se näyttää olevan optimoitu yleiseen tapaukseen. Ja vastaavasti hän siirtyy jossain vaiheessa yleiseen suunnitelmaan, joka valitettavasti voi osoittautua erilaiseksi. Se voi osoittautua samaksi tai se voi olla erilainen. Ja on olemassa jonkinlainen kynnysarvo, joka johtaa tähän käyttäytymiseen.
Mitä voit tehdä asialle? Tässä on tietysti vaikeampi olettaa mitään. Käytössämme on yksinkertainen ratkaisu. Tämä on +0, OFFSET 0. Tiedät varmasti tällaiset ratkaisut. Otamme sen ja lisäämme pyyntöön "+0" ja kaikki on hyvin. Näytän sinulle myöhemmin.
Ja on toinenkin vaihtoehto - katso suunnitelmia tarkemmin. Kehittäjän ei tarvitse vain kirjoittaa pyyntöä, vaan myös sanoa "selitä analysoida" 6 kertaa. Jos se on 5, se ei toimi.
Ja on kolmas vaihtoehto - kirjoita kirje pgsql-hakkereille. Kirjoitin, mutta ei ole vielä selvää, onko tämä virhe vai ominaisuus.
Kun mietimme, onko tämä virhe vai ominaisuus, korjataan se. Otetaan pyyntömme ja lisätään "+0". Kaikki on hyvin. Kaksi symbolia ja sinun ei tarvitse edes ajatella, miten se on tai mitä se on. Erittäin yksinkertainen. Kielsimme vain tietokantaa käyttämästä indeksiä tässä sarakkeessa. Meillä ei ole indeksiä sarakkeessa "+0", ja siinä kaikki, tietokanta ei käytä indeksiä, kaikki on kunnossa.
Tämä on sääntö 6 selittää. Nykyisissä versioissa sinun on tehtävä se 6 kertaa, jos sinulla on sidotut muuttujat. Jos sinulla ei ole sidottuja muuttujia, teemme näin. Ja lopulta juuri tämä pyyntö epäonnistuu. Se ei ole hankala asia.
Vaikuttaa siltä, kuinka paljon on mahdollista? Vika täällä, vika siellä. Itse asiassa vika on kaikkialla.
Katsotaanpa tarkemmin. Meillä on esimerkiksi kaksi mallia. Kaavio A taulukolla S ja kaavio B taulukolla S. Kysely – valitse tiedot taulukosta. Mitä meillä on tässä tapauksessa? Meillä on virhe. Meillä on kaikki edellä mainitut. Sääntö on - bugi on kaikkialla, meillä on kaikki edellä mainitut.
Nyt kysymys kuuluu: "Miksi?" Vaikuttaa siltä, että on olemassa dokumentaatiota siitä, että jos meillä on skeema, on olemassa "search_path"-muuttuja, joka kertoo meille, mistä taulukkoa etsiä. Vaikuttaa siltä, että siinä on muuttuja.
Mikä on ongelma? Ongelmana on, että palvelimen valmistelemat lausunnot eivät epäile, että joku voi muuttaa search_path. Tämä arvo pysyy ikään kuin vakiona tietokannassa. Ja jotkut osat eivät välttämättä saa uusia merkityksiä.
Tämä riippuu tietysti testattavasta versiosta. Riippuu siitä, kuinka vakavasti taulukot eroavat toisistaan. Ja versio 9.1 yksinkertaisesti suorittaa vanhat pyynnöt. Uudet versiot saattavat havaita vian ja kertoa sinulle, että sinulla on virhe.
Miten sitä hoidetaan? On yksinkertainen resepti - älä tee sitä. Hakupolkua ei tarvitse muuttaa sovelluksen ollessa käynnissä. Jos muutat, on parempi luoda uusi yhteys.
Voit keskustella, eli avata, keskustella, lisätä. Ehkä voimme vakuuttaa tietokannan kehittäjät, että kun joku muuttaa arvoa, tietokannan pitäisi kertoa asiakkaalle tästä: "Katso, arvosi on päivitetty tänne. Ehkä sinun on nollattava lausunnot ja luotava ne uudelleen?" Nyt tietokanta käyttäytyy salaa eikä ilmoita millään tavalla, että lausunnot olisivat muuttuneet jossain sisällä.
Ja korostan vielä kerran - tämä on jotain, mikä ei ole tyypillistä Javalle. Näemme saman asian PL/pgSQL:ssä yksitellen. Mutta siellä se toistetaan.
Kokeillaan lisää datavalintaa. Valitsemme ja valitsemme. Meillä on taulukko, jossa on miljoona riviä. Jokainen rivi on kilotavu. Noin gigatavu dataa. Ja meillä on Java-koneessa 128 megatavun työmuisti.
Kuten kaikissa kirjoissa suositellaan, käytämme stream-käsittelyä. Eli avaamme resultSetin ja luemme sieltä pikkuhiljaa dataa. Toimiiko se? Putoaako se muistista? Luetko vähän? Luotetaan tietokantaan, luotetaan Postgresiin. Emme usko sitä. Jäämmekö muistin ulkopuolelle? Kuka koki OutOfMemoryn? Kuka sen jälkeen onnistui korjaamaan? Joku onnistui korjaamaan sen.
Jos sinulla on miljoona riviä, et voi vain valita. OFFSET/LIMIT vaaditaan. Kuka kannattaa tätä vaihtoehtoa? Ja kuka kannattaa autoCommitilla pelaamista?
Täällä, kuten tavallista, odottamattomin vaihtoehto osoittautuu oikeaksi. Ja jos yhtäkkiä sammutat autoCommitin, se auttaa. Miksi niin? Tiede ei tiedä tästä.
Mutta oletusarvoisesti kaikki Postgres-tietokantaan muodostavat asiakkaat hakevat koko tiedot. PgJDBC ei ole tässä suhteessa poikkeus; se valitsee kaikki rivit.
FetchSize-teemassa on muunnelma, eli voit sanoa erillisen lausunnon tasolla, että tässä, ole hyvä ja valitse tiedot 10, 50 mukaan. Tämä ei kuitenkaan toimi ennen kuin kytket automaattisen liitteen pois päältä. AutoCommit pois päältä - se alkaa toimia.
Mutta koodin läpikäyminen ja setFetchSize-asetus kaikkialla on hankalaa. Siksi teimme asetuksen, joka sanoo oletusarvon koko yhteydelle.
Niin me sanoimme. Parametri on määritetty. Ja mitä saimme? Jos valitsemme pieniä määriä, jos esimerkiksi valitsemme 10 riviä kerrallaan, meillä on erittäin suuret yleiskustannukset. Siksi tämä arvo tulisi asettaa noin sataan.
Ihannetapauksessa sinun on tietysti vielä opittava rajoittamaan sitä tavuissa, mutta ohje on tämä: aseta defaultRowFetchSize arvoon yli sata ja ole onnellinen.
Siirrytään tietojen lisäämiseen. Lisääminen on helpompaa, vaihtoehtoja on erilaisia. Esimerkiksi INSERT, ARVOT. Tämä on hyvä vaihtoehto. Voit sanoa "INSERT SELECT". Käytännössä se on sama asia. Suorituskyvyssä ei ole eroa.
Kirjat sanovat, että sinun on suoritettava Batch-lause, kirjoissa sanotaan, että voit suorittaa monimutkaisempia komentoja useilla suluilla. Ja Postgresissa on upea ominaisuus - voit tehdä COPY:n eli tehdä sen nopeammin.
Jos mittaat sen, voit jälleen tehdä mielenkiintoisia löytöjä. Miten haluamme tämän toimivan? Haluamme olla jäsentämättä emmekä suorittamatta tarpeettomia komentoja.
Käytännössä TCP ei salli meidän tehdä tätä. Jos asiakas on kiireinen pyynnön lähettämisessä, tietokanta ei lue pyyntöjä yrittäessään lähettää meille vastauksia. Lopputuloksena on, että asiakas odottaa, että tietokanta lukee pyynnön, ja tietokanta odottaa, että asiakas lukee vastauksen.
Ja siksi asiakkaan on lähetettävä ajoittain synkronointipaketti. Ylimääräistä verkkovuorovaikutusta, ylimääräistä ajanhukkaa.
Ja mitä enemmän lisäämme niitä, sitä huonommaksi se muuttuu. Kuljettaja on melko pessimistinen ja lisää niitä melko usein, noin kerran 200 rivin välein, riippuen rivien koosta jne.
Tapahtuu, että korjaat vain yhden rivin ja kaikki nopeutuu 10 kertaa. Se tapahtuu. Miksi? Kuten tavallista, tällaista vakiota on jo käytetty jossain. Ja arvo "128" tarkoitti, ettei erän käyttöä käytetä.
On hyvä, että tämä ei sisältynyt viralliseen versioon. Löytyi ennen julkaisun alkamista. Kaikki antamani merkitykset perustuvat nykyaikaisiin versioihin.
Kokeillaanpa sitä. Mittaa InsertBatch yksinkertainen. Mittaamme InsertBatchin useita kertoja, eli saman asian, mutta arvoja on monia. Hankala liike. Kaikki eivät voi tehdä tätä, mutta se on niin yksinkertainen toimenpide, paljon helpompi kuin COPY.
Voit tehdä KOPIOINTI.
Ja voit tehdä tämän rakenteilla. Ilmoita käyttäjän oletustyyppi, välitä array ja INSERT suoraan taulukkoon.
Jos avaat linkin: pgjdbc/ubenchmsrk/InsertBatch.java, tämä koodi on GitHubissa. Näet tarkasti, mitä pyyntöjä siellä luodaan. Ei sillä ole väliä.
Käynnistimme. Ja ensimmäinen asia, jonka tajusimme, oli, että erän käyttämättä jättäminen on yksinkertaisesti mahdotonta. Kaikki erävaihtoehdot ovat nolla, eli suoritusaika on käytännössä nolla verrattuna kertaluontoiseen suoritukseen.
Lisäämme tiedot. Se on hyvin yksinkertainen pöytä. Kolme saraketta. Ja mitä me täällä näemme? Näemme, että kaikki kolme vaihtoehtoa ovat suunnilleen vertailukelpoisia. Ja COPY on tietysti parempi.
Tämä on silloin, kun laitamme kappaleita. Kun sanoimme, että yksi ARVOT-arvo, kaksi ARVOT-arvoa, kolme ARVOT-arvoa, tai ilmoitimme niistä 10 pilkulla erotettuna. Tämä on nyt vaakatasossa. 1, 2, 4, 128. Voidaan nähdä, että sinisellä piirretty Eräliite saa hänet tuntemaan olonsa paljon paremmaksi. Eli kun lisäät yhden kerrallaan tai vaikka lisäät neljä kerrallaan, siitä tulee kaksi kertaa parempi, yksinkertaisesti siksi, että ahmimme hieman enemmän ARVOT. Vähemmän EXECUTE-toimintoja.
COPY:n käyttäminen pienissä määrissä on erittäin lupaamatonta. En edes piirtänyt kahta ensimmäistä. He menevät taivaaseen, eli nämä vihreät numerot KOPIOIN.
COPY-komentoa tulee käyttää, kun tietoja on vähintään sata riviä. Tämän yhteyden avaaminen on suuri. Ja ollakseni rehellinen, en kaivanut tähän suuntaan. Optimoin erän, mutta en COPY:ia.
Mitä teemme seuraavaksi? Kokeilimme sitä. Ymmärrämme, että meidän on käytettävä joko rakenteita tai näppärää bactia, joka yhdistää useita merkityksiä.
Mitä sinun pitäisi ottaa pois tämän päivän raportista?
- PreparedStatement on kaikkemme. Tämä antaa paljon tuottavuudelle. Se tuottaa suuren flopin.
- Ja sinun on tehtävä EXPLAIN ANALYSE 6 kertaa.
- Ja meidän on laimennettava OFFSET 0 ja temppuja, kuten +0, korjataksemme jäljellä olevan prosenttiosuuden ongelmallisista kyselyistämme.
Lähde: will.com