Zdravím.
Volám sa Vanya a som vývojár Java. Stáva sa, že veľa pracujem s PostgreSQL – nastavujem databázu, optimalizujem štruktúru, výkon a cez víkendy hrám trochu DBA.
Nedávno som dal do poriadku niekoľko databáz v našich mikroslužbách a napísal java knižnicu , čo uľahčuje túto prácu, šetrí mi čas a pomáha mi vyhnúť sa niektorým bežným chybám vývojárov. Práve o tejto knižnici budeme dnes hovoriť.
Vylúčenie zodpovednosti
Hlavná verzia PostgreSQL, s ktorou pracujem, je 10. Všetky SQL dotazy, ktoré používam, sú tiež testované vo verzii 11. Minimálna podporovaná verzia je 9.6.
pravek
Všetko to začalo takmer pred rokom pre mňa zvláštnou situáciou: súťažné vytváranie indexu z ničoho nič skončilo chybou. Samotný index ako obvykle zostal v databáze v neplatnom stave. Analýza protokolov ukázala nedostatok . A ide sa... Keď som sa pohrabal hlbšie, objavil som množstvo problémov v konfigurácii databázy a vyhrnul som si rukávy a s iskrou v očiach som ich začal opravovať.
Problém XNUMX - predvolená konfigurácia
Asi každého už dosť unavuje metafora o Postgrese, ktorý sa dá spustiť na kávovare, ale... predvolená konfigurácia skutočne vyvoláva množstvo otázok. Minimálne stojí za to venovať pozornosť údržba_práca_mem, limit_temp_file_limit, statement_timeout и lock_timeout.
V našom prípade údržba_práca_mem bolo predvolených 64 MB a limit_temp_file_limit niečo okolo 2 GB – jednoducho sme nemali dostatok pamäte na vytvorenie indexu na veľkej tabuľke.
Preto v pg-index-health Zozbieral som sériu , podľa môjho názoru, parametre, ktoré by mali byť nakonfigurované pre každú databázu.
Problém dva - duplicitné indexy
Naše databázy žijú na jednotkách SSD a používame ich HA-konfigurácia s viacerými dátovými centrami, hlavným hostiteľom a n- počet replík. Priestor na disku je pre nás veľmi cenným zdrojom; nie je o nič menej dôležitá ako výkon a spotreba CPU. Preto na jednej strane potrebujeme indexy na rýchle čítanie a na druhej strane nechceme vidieť zbytočné indexy v databáze, keďže zaberajú miesto a spomaľujú aktualizáciu dát.
A teraz, keď som všetko obnovil a vidieť dosť , rozhodol som sa zorganizovať “veľkú” čistku. Ukázalo sa, že vývojári neradi čítajú databázovú dokumentáciu. Veľmi sa im to nepáči. Z tohto dôvodu vznikajú dve typické chyby – manuálne vytvorený index na primárnom kľúči a podobný „manuálny“ index na jedinečnom stĺpci. Faktom je, že nie sú potrebné - Postgres urobí všetko sám. Takéto indexy je možné bezpečne vymazať a na tento účel sa objavila diagnostika .
Problém tri - pretínajúce sa indexy
Väčšina začínajúcich vývojárov vytvára indexy v jednom stĺpci. Postupne, po dôkladnom ochutnaní tohto podnikania, ľudia začínajú optimalizovať svoje dopyty a pridávať zložitejšie indexy, ktoré obsahujú niekoľko stĺpcov. Takto sa zobrazujú indexy v stĺpcoch A, A + B, A + B + C a tak ďalej. Prvé dva z týchto indexov možno bezpečne vyhodiť, pretože sú predponami tretieho. To tiež šetrí veľa miesta na disku a existuje na to diagnostika .
Problém štyri - cudzie kľúče bez indexov
Postgres vám umožňuje vytvárať obmedzenia cudzieho kľúča bez zadania podporného indexu. V mnohých situáciách to nie je problém a nemusí sa to ani prejaviť... Zatiaľ...
S nami to bolo rovnaké: v určitom okamihu nám hlavný hostiteľ začal „pridávať“ úlohu, ktorá prebieha podľa plánu a čistí databázu testovacích objednávok. CPU a IO išli do odpadu, požiadavky sa spomalili a vypršali, služba bola päťsto. Rýchla analýza ukázalo, že dopyty ako:
delete from <table> where id in (…)V tomto prípade sa samozrejme v cieľovej tabuľke nachádzal index podľa id a len veľmi málo záznamov bolo vymazaných podľa podmienky. Zdalo sa, že všetko by malo fungovať, ale, bohužiaľ, nebolo.
Ten úžasný prišiel na pomoc vysvetliť analýzu a povedal, že okrem vymazania záznamov v cieľovej tabuľke existuje aj kontrola referenčnej integrity a na jednej zo súvisiacich tabuliek táto kontrola zlyhá sekvenčné skenovanie z dôvodu nedostatku vhodného indexu. Tak sa zrodila diagnostika .
Úloha piata – nulová hodnota v indexoch
V predvolenom nastavení Postgres obsahuje nulové hodnoty v indexoch btree, ale zvyčajne tam nie sú potrebné. Preto sa usilovne snažím vyhodiť tieto nuly (diagnostika ), vytváranie čiastočných indexov v stĺpcoch s možnosťou null podľa typu where <A> is not null. Týmto spôsobom sa mi podarilo zmenšiť veľkosť jedného z našich indexov z 1877 MB na 16 KB. A v jednej zo služieb sa veľkosť databázy celkovo znížila o 16% (o 4.3 GB v absolútnych číslach) v dôsledku vylúčenia nulových hodnôt z indexov. Obrovská úspora miesta na disku s veľmi jednoduchými úpravami. 🙂
Problém šiesty – nedostatok primárnych kľúčov
Vzhľadom na povahu mechanizmu takáto situácia je možná keď veľkosť vášho stola rýchlo rastie kvôli veľkému počtu mŕtvych záznamov. Naivne som veril, že toto nám nehrozí a našej základni sa to nestane, veď my, wow!!!, sme normálni vývojári... Aký som bol hlúpy a naivný...
Jedného dňa jedna úžasná migrácia vzala a aktualizovala všetky záznamy vo veľkej a aktívne používanej tabuľke. Z ničoho nič sme dostali +100 GB k veľkosti tabuľky. Bola to prekliata hanba, ale tým sa naše nešťastia neskončili. Keď sa autovákuum na tomto stole skončilo o 15 hodín neskôr, bolo jasné, že fyzické umiestnenie sa nevráti. Nemohli sme zastaviť službu a urobiť VACUUM FULL, tak sme sa rozhodli použiť . A potom sa to ukázalo pg_repack nevie spracovať tabuľky bez primárneho kľúča alebo iného obmedzenia jedinečnosti a naša tabuľka nemala primárny kľúč. Tak sa zrodila diagnostika .
Vo verzii knižnice 0.1.5 Bola pridaná možnosť zbierať údaje z nadbytočných tabuliek a indexov a včas na ne reagovať.
Problémy sedem a osem - nedostatočné indexy a nepoužívané indexy
Nasledujúce dve diagnostiky sú: и – sa vo finálnej podobe objavili pomerne nedávno. Ide o to, že ich nebolo možné len tak vziať a pridať.
Ako som už písal, používame konfiguráciu s niekoľkými replikami a záťaž čítania na rôznych hostiteľoch je zásadne odlišná. V dôsledku toho sa situácia ukazuje, že niektoré tabuľky a indexy na niektorých hostiteľoch sa prakticky nepoužívajú a na analýzu musíte zbierať štatistiky od všetkých hostiteľov v klastri. Toto je tiež potrebné na každom hostiteľovi v klastri; nemôžete to urobiť iba na hlavnom.
Tento prístup nám umožnil ušetriť niekoľko desiatok gigabajtov odstránením indexov, ktoré sa nikdy nepoužívali, ako aj pridaním chýbajúcich indexov do zriedkavo používaných tabuliek.
Ako záver
Samozrejme, pre takmer všetky diagnostiky môžete konfigurovať . Týmto spôsobom môžete vo svojej aplikácii rýchlo implementovať kontroly, ktoré zabránia vzniku nových chýb, a potom postupne opraviť staré.
Niektoré diagnostiky je možné vykonať vo funkčných testoch ihneď po spustení migrácií databáz. A to je možno jedna z najsilnejších funkcií mojej knižnice. Príklad použitia nájdete v .
Má zmysel vykonávať kontroly nepoužívaných alebo chýbajúcich indexov, ako aj nafúknutia, iba na skutočnej databáze. Zozbierané hodnoty je možné zaznamenať do alebo odoslaná do monitorovacieho systému.
V to naozaj dúfam pg-index-health budú užitočné a žiadané. K rozvoju knižnice môžete prispieť aj nahlasovaním problémov, ktoré nájdete, a navrhovaním novej diagnostiky.
Zdroj: hab.com