Živjo.
Moje ime je Vanya in sem Java razvijalec. Na žalost sem veliko dela z PostgreSQL – ukvarjam se z nastavitvijo Baze podatkov, optimizacijo strukture, zmogljivosti in malo igram DBA ob koncih tedna.
V zadnjem času sem uredil nekaj baz podatkov v naših mikroservisih in napisal java knjižnico , ki olajša to delo, prihrani moj čas in pomaga preprečiti nekatere običajne napake, ki jih razvijalci pogosto delajo. Danes bo govora prav o tej knjižnici.
Odricanje od odgovornosti
Osnovna različica PostgreSQL, s katero delam, je 10. Vse SQL poizvedbe, ki jih uporabljam, so bile prav tako preverjene na 11. različici. Najmanj podprta različica je 9.6.
Pozadina
Vse se je začelo skoraj pred letom dni s čudno situacijo zame: konkurenčna ustvaritev indeksa na praznem mestu se je končala z napako. Sam indeks, kot ponavadi, je ostal v neveljavnem stanju v bazi. Analiza dnevnikov je pokazala pomanjkanje . In potem je šlo vse naprej... Ko sem se potopil globlje, sem odkril celo vrsto težav v konfiguraciji Baze podatkov, in z zavihanimi rokavi ter sijajem v očeh sem se lotil njihovega popravila.
Prva težava – privzeta konfiguracija
Verjetno je že postala metafora o Postgresu, ki ga lahko zaženeš na kavnih avtomatih, vsem dokaj nadležna, vendar… privzeta konfiguracija dejansko vzbuja vrsto vprašanj. Minimalno bi morali obrniti pozornost na maintenance_work_mem, temp_file_limit, statement_timeout in lock_timeout.
V našem primeru maintenance_work_mem je bil privzeto 64 MB, a temp_file_limit okoli 2 GB – preprosto nam je zmanjkalo pomnilnika za ustvarjanje indeksa na veliki tabeli.
Zato sem v pg-index-health zbral vrsto , po mojem mnenju, parametrov, ki jih je vredno nastaviti za vsako Bazo podatkov.
Druga težava – podvojeni indeksi
Naše baze delujejo na SSD diskih, in uporabljamo HA-konfiguracijo z več podatkovnimi centri, glavno gostiteljsko napravo in n-številom replik. Prostor na disku je za nas zelo dragocen vir; ni manj pomemben od zmogljivosti in porabe CPU. Zato, po eni strani, potrebujemo indekse za hitro branje, po drugi strani pa ne želimo videti odvečnih indeksov v Bazi podatkov, saj ti zavzemajo prostor in upočasnjujejo posodobitve podatkov.
In tako, ko sem obnovil vse in se natančno ogledal , odločil sem se za 'veliko' čiščenje. Izkazalo se je, da razvijalci ne marajo brati dokumentacije za bazo podatkov. Zelo ne marajo. Zaradi tega nastaneta dve tipični napaki – ročno ustvarjena indeksa na primarni ključ in podoben 'ročni' indeks na unikatni stolpec. Gre za to, da niso potrebni – Postgres vse naredi sam. Te indekse lahko brez skrbi odstranite in za to je na voljo diagnostika. .
Tretja težava – prekrivni indeksi
Večina začetnih razvijalcev ustvarja indekse na en stolpec. Sčasoma, ko začnejo to zadevo okusiti, ljudje začnejo optimizirati svoje poizvedbe in dodajati bolj kompleksne indekse, ki vključujejo več stolpcev. Tako se pojavijo indeksi na stolpcih A, A+B, A+B+C itd. Prva dva od teh indeksov lahko brez skrbi izbrišete, saj sta prefiksi tretjega. To prav tako precej prihrani prostor na disku in za to je na voljo diagnostika. .
Četrta težava – zunanje ključe brez indeksov
Postgres omogoča ustvarjanje omejitev zunanjega ključa brez navedbe podpornega indeksa. V mnogih situacijah to ni težava in se celo lahko ne pokaže… Do določene mere…
Tako je bilo tudi pri nas: preprosto je v nekem trenutku job, ki se izvaja po razporedu in čisti bazo od testnih naročil, začel 'zlagati' naš glavni strežnik. CPU in IO sta letela v višave, poizvedbe so se upočasnile in prekinile zaradi časovne omejitve, storitev je izvajala napako 500. Hiter pregled je pokazal, da so se ustavljale poizvedbe tipa:
izbriši iz <table> kjer je id v (…)Ob tem je bil indeks po id v ciljni tabeli, seveda, prisoten in zapisov je bilo odstranjevanih po pogojih čisto malo. Zdi se, da bi vse moralo delovati, a, žal, ni delovalo.
Na pomoč je prišel čudežni explain analyze in povedal, da poleg odstranjevanja zapisov v ciljnosti tabeli poteka še preverjanje referenčne celovitosti, in na eni izmed povezanih tabel ta preverjanje zruši v sekvenčno skeniranje zaradi pomanjkanja ustreznega indeksa. Tako je nastala diagnostika .
Peta težava – null vrednosti v indeksih
Po privzetku Postgres vključuje null vrednosti v btree-indekse, toda tam ponavadi niso potrebni. Zato si prizadevam te null vrednosti izločiti (diagnostika ), ustvarjam delne indekse na nullable-stolpcih tipa where is not null. Na ta način sem uspel zmanjšati velikost ene naših indeksov s 1877 MB na 16 kB. V eni od storitev se je skupna velikost Baze podatkov zmanjšala za 16% (za 4,3 GB v absolutnih številkah) zaradi izključitve null vrednosti iz indeksov. Kolosalna ekonomija diskovnega prostora pri precej enostavnih prilagoditvah. 🙂
Težava šest – odsotnost primarnih ključev
Zaradi posebnosti mehanizma je možna takšna situacija, kot , ko se velikost vaše tabele hitro povečuje zaradi velikega števila mrtvih zapisov. Naivno sem verjel, da nas to ne ogroža, in da se to z našo bazo ne bo zgodilo, saj smo, ojoj!!!, normalni razvijalci... Kako neumen in naiven sem bil...
Nekega lepega dne se je ena čudovita migracija odločila in posodila vse zapise v veliki in aktivno uporabljani tabeli. Dobili smo +100 GB k velikosti tabele kar tako. Bilo je grozno žalostno, a naša zložitja se tu niso končala. Po 15-ih urah je avto vakuum na tej tabeli končal, jasno je postalo, da fizično mesto ne bo vrnjeno. Nismo mogli ustaviti storitve in narediti VACUUM FULL, zato smo se odločili uporabiti . In tukaj se je izkazalo, da pg_repack ne zna obdelovati tabel brez primarnega ključa ali drugega omejitev edinstvenosti, našo tabelo pa primarni ključ ni imela. Tako je nastala diagnostika .
V različici knjižnice 0.1.5 je bila dodana možnost zbiranja podatkov o bloatu tabel in indeksov in pravočasno reagirati nanj.
Težave sedem in osem – pomanjkanje indeksov in neuporabni indeksi
Naslednji dve diagnostiki – in – sta se v svoji končni obliki pojavili relativno nedavno. Gre za to, da jih ni bilo mogoče enostavno dodati.
Kot sem že pisal, uporabljamo konfiguracijo z več replikami, in bralna obremenitev na različnih gostiteljih je načeloma zelo različna. Rezultat je situacija, da se nekatere tabele in indeksi na nekaterih gostiteljih praktično ne uporabljajo, in za analizo je potrebno zbirati statistiko z vseh gostiteljev v grozdu. tudi treba ponastaviti na vsakem gostitelju v grozdu, tega ni mogoče storiti samo na masterju.
Taka metoda nam je omogućila da uštedimo nekoliko desetina gigabajta uklanjanjem indeksa koji nikada nisu korišćeni, kao i dodavanjem nedostajućih indeksa na retko korišćene tabele.
В качестве заключения
Naravno, praktično za sve dijagnostike može se podesiti . Na taj način se mogu brzo implementirati provere u vašoj aplikaciji, sprečavajući pojavu novih grešaka, a zatim postepeno ispravljajući stare.
Deo dijagnostika može se obaviti već u funkcionalnim testovima odmah nakon uvođenja migracija Baze podataka. I to je, verovatno, jedna od najmoćnijih mogućnosti moje biblioteke. Primer korišćenja možete pogledati u .
Provere za neiskorišćene ili nedostajuće indekse, kao i za bloat, smisleno je izvoditi samo na stvarnoj Bazi podataka. Prikupljene vrednosti mogu biti zapisane u ili poslati u sistem za monitoring.
Veoma se nadam da pg-index-health će biti korisna i tražena. Takođe možete doprineti razvoju biblioteke prijavljivanjem otkrivenih problema i predlaganjem novih dijagnostika.
Izvor: habr.com
