Zdravo
Moje ime je Vanya in sem razvijalec Java. Tako se zgodi, da veliko delam s PostgreSQL – postavljam bazo podatkov, optimiziram strukturo, zmogljivost in se malo igram DBA ob vikendih.
Pred kratkim sem pospravil več baz podatkov v naših mikrostoritvah in napisal java knjižnico , kar olajša to delo, mi prihrani čas in se mi pomaga izogniti nekaterim pogostim napakam razvijalcev. O tej knjižnici bomo danes govorili.
Zavrnitev odgovornosti
Glavna različica PostgreSQL, s katero delam, je 10. Vse poizvedbe SQL, ki jih uporabljam, so preizkušene tudi na različici 11. Najmanjša podprta različica je 9.6.
prazgodovina
Vse skupaj se je začelo pred skoraj enim letom z zame nenavadno situacijo: konkurenčna izdelava indeksa se je kar naenkrat končala z napako. Sam indeks je kot običajno ostal v bazi podatkov v neveljavnem stanju. Analiza dnevnika je pokazala pomanjkanje . In gremo... Ko sem se poglobil, sem odkril cel kup težav v konfiguraciji baze podatkov in jih, zavihal rokave, z iskrico v očeh začel odpravljati.
Prva težava - privzeta konfiguracija
Verjetno so že vsi že pošteno naveličani metafore o Postgresu, ki ga je mogoče pognati na kavnem aparatu, ampak ... privzeta konfiguracija res sproža vrsto vprašanj. Vsaj na to je vredno biti pozoren vzdrževanje_delo_mem, omejitev_temp_file, stavek_timeout и lock_timeout.
V našem primeru vzdrževanje_delo_mem je bilo privzeto 64 MB in omejitev_temp_file nekaj okoli 2 GB - preprosto nismo imeli dovolj pomnilnika, da bi ustvarili indeks na veliki tabeli.
Zato v pg-index-zdravje Zbral sem serijo , po mojem mnenju, parametri, ki jih je treba konfigurirati za vsako bazo podatkov.
Drugi problem - podvojeni indeksi
Naše baze podatkov živijo na pogonih SSD in jih uporabljamo HA-konfiguracija z več podatkovnimi centri, glavnim gostiteljem in n-število replik. Prostor na disku je za nas zelo dragocen vir; ni nič manj pomembna kot zmogljivost in poraba procesorja. Zato po eni strani potrebujemo indekse za hitro branje, po drugi strani pa ne želimo videti nepotrebnih indeksov v bazi, saj odžirajo prostor in upočasnjujejo ažuriranje podatkov.
In zdaj, ko sem vse obnovil in videl dovolj , sem se odločil organizirati "veliko" čistko. Izkazalo se je, da razvijalci ne marajo brati dokumentacije baze podatkov. Ni jim preveč všeč. Zaradi tega se pojavita dve tipični napaki - ročno ustvarjen indeks na primarnem ključu in podoben "ročni" indeks na edinstvenem stolpcu. Dejstvo je, da niso potrebni - Postgres bo vse naredil sam. Takšne indekse je mogoče varno izbrisati in v ta namen se je pojavila diagnostika .
Tretji problem - križajoči se indeksi
Večina razvijalcev začetnikov ustvari indekse v enem samem stolpcu. Postopoma, ko so temeljito izkusili ta posel, ljudje začnejo optimizirati svoje poizvedbe in dodajati bolj zapletene indekse, ki vključujejo več stolpcev. Tako se prikažejo indeksi v stolpcih A, A + B, A + B + C in tako naprej. Prva dva od teh indeksov lahko varno zavržete, saj sta predponi tretjega. To tudi prihrani veliko prostora na disku in za to obstaja diagnostika .
Težava štiri - tuji ključi brez indeksov
Postgres vam omogoča, da ustvarite omejitve tujega ključa, ne da bi določili podporni indeks. V mnogih situacijah to ni problem in se morda niti ne pokaže ... Zaenkrat ...
Enako je bilo pri nas: le da nam je glavni gostitelj v nekem trenutku začel »dodajati« opravilo, ki teče po urniku in čisti bazo testnih naročil. CPU in IO sta šla v nič, zahteve so se upočasnile in potekle, storitev je bila petsto. Hitra analiza je pokazala, da poizvedbe, kot so:
delete from <table> where id in (…)V tem primeru je seveda obstajal indeks po id v ciljni tabeli in zelo malo zapisov je bilo izbrisanih v skladu s pogojem. Zdelo se je, da bi moralo vse delovati, a žal ni.
Čudoviti je priskočil na pomoč pojasniti analizirati in povedal, da poleg brisanja zapisov v ciljni tabeli obstaja tudi preverjanje referenčne integritete, na eni od povezanih tabel pa to preverjanje ne uspe zaporedno skeniranje zaradi pomanjkanja ustreznega indeksa. Tako se je rodila diagnostika .
Peta težava – ničelna vrednost v indeksih
Postgres privzeto vključuje ničelne vrednosti v indekse btree, vendar tam običajno niso potrebne. Zato se pridno trudim vreči ven te ničle (diagnostika ), ustvarjanje delnih indeksov na ničelnih stolpcih po vrsti where <A> is not null. Na ta način sem lahko zmanjšal velikost enega od naših indeksov s 1877 MB na 16 KB. In v eni od storitev se je velikost baze podatkov skupaj zmanjšala za 16% (za 4.3 GB v absolutnem številu) zaradi izključitve ničelnih vrednosti iz indeksov. Ogromen prihranek prostora na disku z zelo preprostimi spremembami. 🙂
Problem šest – pomanjkanje primarnih ključev
Zaradi narave mehanizma takšna situacija je možna ko velikost vaše tabele hitro narašča zaradi velikega števila mrtvih zapisov. Naivno sem verjel, da nas to ne bo ogrožalo, in da se to ne bo zgodilo naši bazi, saj smo, vau!!!, normalni razvijalci... Kako sem bil neumen in naiven...
Nekega dne je ena čudovita selitev vzela in posodobila vse zapise v veliki in aktivno uporabljeni tabeli. Kar naenkrat smo dobili +100 GB k velikosti mize. Res škoda, a naše nesreče se tu niso končale. Ko se je avtovakuum na tej mizi končal 15 ur kasneje, je postalo jasno, da se fizična lokacija ne bo vrnila. Storitve nismo mogli ustaviti in narediti VACUUM FULL, zato smo se odločili za uporabo . In potem se je izkazalo, da pg_repack ne zna obdelati tabel brez primarnega ključa ali druge omejitve edinstvenosti, naša tabela pa ni imela primarnega ključa. Tako se je rodila diagnostika .
V knjižnični različici 0.1.5 Dodana je možnost zbiranja podatkov iz napolnjenosti tabel in indeksov ter pravočasnega odzivanja nanje.
Težavi sedem in osem - nezadostna kazala in neuporabljena kazala
Naslednji dve diagnostiki sta: и – so se v končni obliki pojavili relativno nedavno. Bistvo je, da jih ni bilo mogoče kar vzeti in dodati.
Kot sem že napisal, uporabljamo konfiguracijo z več replikami in obremenitev branja na različnih gostiteljih je bistveno drugačna. Posledično se izkaže, da se nekatere tabele in indeksi na nekaterih gostiteljih praktično ne uporabljajo, za analizo pa morate zbrati statistiko vseh gostiteljev v gruči. To je potrebno tudi na vsakem gostitelju v gruči; tega ne morete storiti samo na glavnem.
Ta pristop nam je omogočil, da smo prihranili nekaj deset gigabajtov z odstranitvijo indeksov, ki niso bili nikoli uporabljeni, kot tudi dodajanjem manjkajočih indeksov redko uporabljenim tabelam.
Kot zaključek
Seveda lahko konfigurirate skoraj vse diagnostike . Na ta način lahko hitro implementirate preverjanja v vaši aplikaciji, preprečite pojav novih napak in nato postopoma odpravite stare.
Nekatere diagnostike je mogoče izvesti v funkcionalnih testih takoj po uvedbi selitev baze podatkov. In to je morda ena najmočnejših funkcij moje knjižnice. Primer uporabe lahko najdete v .
Preverjanje neuporabljenih ali manjkajočih indeksov ter napihnjenosti je smiselno izvajati samo na pravi bazi podatkov. Zbrane vrednosti je mogoče zabeležiti v ali poslana v nadzorni sistem.
Res upam, da pg-index-zdravje bo uporabno in v povpraševanju. K razvoju knjižnice lahko prispevate tudi tako, da poročate o težavah, ki jih najdete, in predlagate novo diagnostiko.
Vir: www.habr.com