Ahoj.
Volám sa Ivan a som Java vývojár. Našťastie, veľa pracujem s PostgreSQL - zaoberám sa nastavením databáz, optimalizáciou štruktúry a výkonnosti a občas si zahrám na DBA cez víkendy.
V poslednom čase som upratal niekoľko databáz v našich mikroservisoch a napísal java knižnicu , ktorá uľahčuje túto prácu, šetrí môj čas a pomáha vyhnúť sa niektorým bežným chybám, ktorých sa dopúšťajú vývojári. O tejto knižnici bude dnes reč.
Upozornenie
Hlavná verzia PostgreSQL, s ktorou pracujem, je 10. Všetky SQL dopyty, ktoré používam, sú taktiež overené na 11. verzii. Minimálna podporovaná verzia je 9.6.
Pozadie
Všetko sa to začalo pred takmer rokom so zvláštnou situáciou: konkurentné vytváranie indexu na prázdnom mieste skončilo s chybou. Index, ako obvykle, zostal v neplatnom stave v databáze. Analýza logov ukázala nedostatok . A začalo sa... Keď som sa trochu do toho ponoril, objavil som množstvo problémov v konfigurácii databázy a, s vyhrnutými rukávmi a iskrou v očiach, som sa pustil do ich opravovania.
Prvý problém - predvolená konfigurácia
Možno už všetkým lezie na nervy metafora o Postgres, ktorý sa dá spustiť na kávovare, ale... predvolené nastavenia skutočne vyvolávajú množstvo otázok. Minimálne by ste mali venovať pozornosť maintenance_work_mem, temp_file_limit, statement_timeout a lock_timeout.
V našom prípade maintenance_work_mem bol predvolený na 64 MB, zatiaľ čo temp_file_limit približne 2 GB - proste sme nemali dostatok pamäte na vytvorenie indexu na veľkej tabuľke.
Preto som v pg-index-health zhromždil radu , podľa môjho názoru, parametrov, ktoré je potrebné nastaviť pre každú databázu.
Druhý problém - duplicované indexy
Naše databázy bežia na SSD diskoch a používame HA-konfiguráciu s viacerými dátovými centrami, hlavnou hostiteľskou a n-kým počtom replik. Miesto na disku je pre nás veľmi cenný zdroj; je rovnako dôležité ako výkon a spotreba CPU. Preto, na jednej strane, potrebujeme indexy pre rýchle čítanie, ale na druhej strane, nechceme vidieť v databáze zbytočné indexy, pretože zaberajú miesto a spomaľujú aktualizáciu dát.
A tak, po obnovení všetkých a po pozorovaní , rozhodol som sa vykonať „veľké“ čistenie. Ukázalo sa, že vývojári nemajú radi čítanie dokumentácie k databázam. Veľmi nemajú. Kvôli tomu vznikajú dve typové chyby – ručne vytvorený index na primárny kľúč a podobný „ručný“ index na unikátny stĺpec. Ide o to, že nie sú potrebné – Postgres to spraví sám. Takéto indexy je možné bez obáv odstrániť, a na to je k dispozícii diagnostika. .
Tretí problém – prekryvajúce sa indexy
Väčšina začínajúcich vývojárov vytvára indexy na jeden stĺpec. Postupom času, keď si na to zvyknú, začnú optimalizovať svoje dotazy a pridávať zložitejšie indexy, ktoré obsahujú viac stĺpcov. Tak vznikajú indexy na stĺpce A, A+B, A+B+C a podobne. Prvé dva z týchto indexov môžeme bez obáv vyhodiť, pretože sú prefixami tretieho. To tiež značně šetrí miesto na disku a na to je k dispozícii diagnostika. .
Štvrtý problém – cudzích kľúčov bez indexov
Postgres umožňuje vytvárať obmedzenia cudzích kľúčov bez určenia podporujúceho indexu. V mnohých situáciách to nie je problém a dokonca sa to môže nijako neprejavovať… Do istej doby…
Tak to bolo aj u nás: v istom okamihu sa job, ktorý sa vykonáva podľa rozvrhu a čistí databázu od testovacích príkazov, začal „zhromažďovať“ náš master host. CPU a IO išli do výšin, dotazy spomaľovali a prerušovali sa kvôli timeoutu, služba vracala päťstovky. Rýchla analýza ukázala, že viseli dotazy typu:
vymazať z <table> kde id v (…)Pri tom index podľa id v cieľovej tabuľke, samozrejme, bol, a záznamy sa mazali na základe podmienky iba vo veľmi malom množstve. Zdalo sa, že všetko by malo fungovať, ale, žiaľ, nefungovalo.
Na pomoc prišla úžasná explain analyze a povedala, že okrem mazania záznamov v cieľovej tabuľke prebieha aj kontrola referenčnej integrity, a na jednej z prepojených tabuliek sa táto kontrola dostáva do sequential scan kvôli absencii vhodného indexu. Tak sa zrodila diagnostika .
Piaty problém – null hodnota v indexoch
Podľa predvolených nastavení Postgres zahŕňa null hodnoty do btree-indexov, ale tam zvyčajne nie sú potrebné. Preto sa snažím tieto null hodnoty odstraňovať (diagnostika ), vytváraním čiastočných indexov na nullable-stĺpce typu where 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 jednom z našich služieb sa celková veľkosť DB znížila o 16% (o 4,3 GB v absolútnych číslach) vylúčením null hodnôt z indexov. Obrovská úspora miesta na disku pri celkom jednoduchých úpravách. 🙂
Problém šiesty – absencia primárnych kľúčov
Z dôvodu špecifík mechanizmu môže nastať taká situácia, ako , keď sa veľkosť vašej tabuľky rýchlo zvyšuje v dôsledku veľkého množstva mŕtvych záznamov. Naivne som si myslel, že sa nás to netýka a že s našou databázou sa to nestane, pretože sme, no áno!!!, normálni developeri… Aký som bol hlúpy a naivný…
Jedného krásneho dňa veľmi podarená migrácia vzala a aktualizovala všetky záznamy vo veľkej a aktívne používané tabuľke. Získali sme +100 GB na veľkosti tabuľky z čistého miesta. Bolo to prehnane frustrujúce, ale naše strasti na tom neskončili. Po 15 hodinách sa automatická údržba tejto tabuľky skončila a bolo jasné, že fyzické miesto sa nevráti. Nemohli sme zastaviť službu a vykonať VACUUM FULL, preto bolo prijaté rozhodnutie použiť . A tu sa ukázalo, že pg_repack nevie spracovávať tabuľky bez primárneho kľúča alebo iného obmedzenia jedinečnosti, a na našej tabuľke primárny kľúč neexistoval. Tak vznikla diagnostika .
Vo verzii knižnice 0.1.5 bola pridaná možnosť zhromažďovať údaje o bloat’ e tabuliek a indexov a včas naň reagovať.
Problémy sedem a osem – nedostatok indexov a nevyužité indexy
Dve nasledujúce diagnostiky – a – v svojom konečnom znení sa objavili relatívne nedávno. Ide o to, že ich nebolo možné len tak jednoducho pridať.
Ako som už spomenul, používame konfiguráciu s viacerými replikami a čítací nápor na rôznych hostiteľoch je zásadne odlišný. Nakoniec vzniká situácia, že niektoré tabuľky a indexy na niektorých hostiteľoch sa takmer nepoužívajú, a na analýzu je potrebné zhromažďovať štatistiky zo všetkých hostiteľov v clustri. tiež treba vykonať na každom hostiteľovi v clustri, nemožno to urobiť len na majstrovi.
Tento prístup nám umožnil ušetriť niekoľko desiatok gigabajtov odstraňovaním indexov, ktoré sa nikdy nepoužívali, ako aj pridaním chýbajúcich indexov do zriedka používaných tabuliek.
Na záver
Samozrejme, pre väčšinu diagnostík je možné nastaviť . Takto je možné rýchlo zaviesť kontroly vo vašej aplikácii, zabrániť vzniku nových chýb a potom postupne opravovať staré.
Niektoré diagnostiky môžu byť vykonané už vo funkčných testoch hneď po aplikovaní migrácií databázy. A toto je pravdepodobne jedna z najvýkonnejších funkcií mojej knižnice. Príklad použitia si môžete pozrieť v .
Kontroly na nepoužívané alebo chýbajúce indexy, ako aj na bloat, má zmysel vykonávať iba na reálnej databáze. Zozbierané hodnoty môžu byť zapísané do alebo zaslané do monitorovacieho systému.
Veľmi dúfam, že pg-index-health bude užitočná a vyhľadávaná. Môžete tiež prispieť k rozvoju knižnice oznámením o objavených problémoch a navrhnutím nových diagnostík.
Zdroj: habr.com
