Hei.
Nimeni on Vanya ja olen Java-kehittäjä. Sattuu niin, että työskentelen paljon PostgreSQL:n kanssa - perustan tietokannan, optimoin rakennetta, suorituskykyä ja pelaan vähän DBA:ta viikonloppuisin.
Olen viime aikoina siivonnut useita tietokantoja mikropalveluissamme ja kirjoittanut Java-kirjaston , mikä helpottaa tätä työtä, säästää aikaa ja auttaa minua välttämään joitain kehittäjien tekemiä yleisiä virheitä. Tästä kirjastosta puhumme tänään.
Vastuun kieltäminen
PostgreSQL:n pääversio, jonka kanssa työskentelen, on 10. Kaikki käyttämäni SQL-kyselyt on myös testattu versiossa 11. Pienin tuettu versio on 9.6.
esihistoria
Kaikki alkoi melkein vuosi sitten minulle oudosta tilanteesta: indeksin kilpailukykyinen luominen tyhjästä päättyi virheeseen. Indeksi itse, kuten tavallista, pysyi tietokannassa virheellisessä tilassa. Lokianalyysi osoitti puutetta . Ja nyt mennään... Kaivaessani syvemmälle, löysin koko joukon ongelmia tietokantakokoonpanossa ja käärien hihat, aloin korjata niitä kipinä silmissäni.
Ongelma yksi - oletusasetus
Todennäköisesti kaikki ovat melko väsyneitä Postgres-metaforaan, jota voidaan käyttää kahvinkeittimellä, mutta... oletuskokoonpano herättää todella monia kysymyksiä. Ainakin siihen kannattaa kiinnittää huomiota ylläpitotyömuisti, temp_file_limit, lausunto_aikakatkaisu и lock_timeout.
Meidän tapauksessamme ylläpitotyömuisti oletusarvo oli 64 Mt, ja temp_file_limit jotain noin 2 Gt - meillä ei yksinkertaisesti ollut tarpeeksi muistia indeksin luomiseen suurelle pöydälle.
Siksi sisään pg-index-terveys Keräsin sarjan , mielestäni parametrit, jotka tulisi määrittää kullekin tietokannalle.
Ongelma kaksi - päällekkäiset indeksit
Tietokantamme elävät SSD-asemilla ja käytämme HA-konfigurointi useilla datakeskuksilla, isäntäkoneella ja n-kopioiden määrä. Levytila on meille erittäin arvokas resurssi; se on yhtä tärkeä kuin suorituskyky ja suorittimen kulutus. Siksi toisaalta tarvitsemme indeksejä nopeaan lukemiseen, toisaalta emme halua nähdä tietokannassa tarpeettomia indeksejä, koska ne syövät tilaa ja hidastavat tietojen päivitystä.
Ja nyt, kun kaikki on palautettu ja nähnyt tarpeeksi , päätin järjestää "suuren" puhdistuksen. Kävi ilmi, että kehittäjät eivät halua lukea tietokannan dokumentaatiota. He eivät pidä siitä kovinkaan paljon. Tästä johtuen syntyy kaksi tyypillistä virhettä - manuaalisesti luotu indeksi ensisijaisella avaimella ja samanlainen "manuaalinen" indeksi yksilöivässä sarakkeessa. Tosiasia on, että niitä ei tarvita - Postgres tekee kaiken itse. Tällaiset indeksit voidaan poistaa turvallisesti, ja diagnostiikka on ilmestynyt tätä tarkoitusta varten .
Kolmas tehtävä - leikkaavat indeksit
Useimmat aloittelevat kehittäjät luovat indeksit yhteen sarakkeeseen. Vähitellen, kun ihmiset ovat kokeneet tämän liiketoiminnan perusteellisesti, ihmiset alkavat optimoida kyselyitään ja lisätä monimutkaisempia indeksejä, jotka sisältävät useita sarakkeita. Näin sarakkeiden hakemistot näkyvät A, A + B, A+B+C. ja niin edelleen. Näistä indekseistä kaksi ensimmäistä voidaan turvallisesti heittää pois, koska ne ovat kolmannen etuliitteitä. Tämä säästää myös paljon levytilaa ja tätä varten on olemassa diagnostiikka .
Ongelma neljä - vierasavaimet ilman indeksejä
Postgresin avulla voit luoda vieraiden avainten rajoituksia ilman taustaindeksiä. Monissa tilanteissa tämä ei ole ongelma, eikä välttämättä edes ilmene... Toistaiseksi...
Meillä oli sama: jossain vaiheessa pääisäntä alkoi "lisätä" meille työtehtävää, joka kulkee aikataulun mukaan ja tyhjensi tietokannan testitilauksista. CPU ja IO menivät hukkaan, pyynnöt hidastuivat ja aikakatkaistiin, palvelu oli viisisataa. Nopea analyysi osoitti, että kyselyt kuten:
delete from <table> where id in (…)Tässä tapauksessa kohdetaulukossa oli tietysti id-indeksi, ja hyvin harvat tietueet poistettiin ehdon mukaan. Näytti siltä, että kaiken pitäisi toimia, mutta valitettavasti se ei toiminut.
Ihana tuli apuun selittää analysointia ja sanoi, että kohdetaulukon tietueiden poistamisen lisäksi siellä on myös viittauksen eheystarkistus, ja yhdessä asiaan liittyvistä taulukoista tämä tarkistus epäonnistuu peräkkäinen skannaus sopivan indeksin puuttumisen vuoksi. Näin syntyi diagnostiikka .
Tehtävä viisi – nolla-arvo indekseissä
Oletuksena Postgres sisältää nolla-arvot btree-indekseihin, mutta niitä ei yleensä tarvita siellä. Siksi yritän ahkerasti heittää pois nämä nollat (diagnostiikka ), luomalla osittaisia indeksejä tyhjennettäville sarakkeille tyypin mukaan where <A> is not null. Tällä tavalla pystyin pienentämään yhden hakemistomme koon 1877 megatavusta 16 kilotavuun. Ja yhdessä palvelussa tietokannan koko pieneni yhteensä 16% (absoluuttisesti mitattuna 4.3 Gt), koska nolla-arvot jätettiin pois indekseistä. Valtavat levytilan säästöt hyvin yksinkertaisilla muutoksilla. 🙂
Ongelma kuusi – ensisijaisten avainten puute
Johtuen mekanismin luonteesta tällainen tilanne on mahdollinen kun pöytäsi koko kasvaa nopeasti kuolleiden tietueiden suuren määrän vuoksi. Uskoin naiivisti, että tämä ei uhkaisi meitä, eikä näin tapahtuisi meidän tukikohtaamme, koska me, vau!!!, olemme normaaleja kehittäjiä... Kuinka tyhmä ja naiivi olinkaan...
Eräänä päivänä yksi upea siirto vei ja päivitti kaikki tietueet suuressa ja aktiivisesti käytetyssä taulukossa. Saimme +100 Gt pöytäkokoon tyhjästä. Se oli helvetin sääli, mutta epäonnistumisemme eivät päättyneet siihen. Tämän pöydän automaattisen tyhjiön päättymisen jälkeen 15 tuntia myöhemmin kävi selväksi, että fyysinen sijainti ei palaa. Emme voineet lopettaa palvelua ja tehdä tyhjiöstä TÄYNNÄ, joten päätimme käyttää . Ja sitten kävi niin pg_repack ei osaa käsitellä taulukoita ilman ensisijaista avainta tai muuta ainutlaatuisuusrajoitusta, eikä taulukossamme ollut ensisijaista avainta. Näin syntyi diagnostiikka .
Kirjastoversiossa 0.1.5 Lisätty mahdollisuus kerätä tietoja taulukoiden ja indeksien paisumisesta ja vastata niihin oikea-aikaisesti.
Ongelmat seitsemän ja kahdeksan - riittämättömät indeksit ja käyttämättömät indeksit
Seuraavat kaksi diagnostiikkaa ovat: и – ilmestyivät lopullisessa muodossaan suhteellisen äskettäin. Asia on siinä, että niitä ei voitu vain ottaa ja lisätä.
Kuten jo kirjoitin, käytämme kokoonpanoa, jossa on useita replikoita, ja lukukuorma eri isännillä on olennaisesti erilainen. Tämän seurauksena tilanne osoittautuu, että joitain taulukoita ja indeksejä joissakin isännissä ei käytännössä käytetä, ja analysointia varten sinun on kerättävä tilastot kaikista klusterin isännistä. Tämä on tarpeen myös jokaisessa klusterin isännässä; et voi tehdä tätä vain isäntäkoneella.
Tämä lähestymistapa antoi meille mahdollisuuden säästää useita kymmeniä gigatavuja poistamalla indeksit, joita ei koskaan käytetty, sekä lisäämällä puuttuvia indeksejä harvoin käytettyihin taulukoihin.
Lopuksi
Tietenkin melkein kaikkiin diagnostiikkaan voit määrittää . Näin voit nopeasti toteuttaa tarkistuksia sovelluksessasi ja estää uusien virheiden syntymisen ja korjata sitten vähitellen vanhoja.
Jotkut diagnosoinnit voidaan suorittaa toiminnallisissa testeissä heti tietokannan siirtojen käyttöönoton jälkeen. Ja tämä on ehkä yksi kirjastoni tehokkaimmista ominaisuuksista. Käyttöesimerkki löytyy osoitteesta .
Käyttämättömien tai puuttuvien indeksien sekä bloatin varalta on järkevää tarkistaa vain todellista tietokantaa. Kerätyt arvot voidaan tallentaa tai lähetetään valvontajärjestelmään.
Toivon todella sitä pg-index-terveys ovat hyödyllisiä ja kysyttyjä. Voit myös osallistua kirjaston kehittämiseen ilmoittamalla löytämistäsi ongelmista ja ehdottamalla uusia diagnostiikkaa.
Lähde: will.com