Ahoj
Jmenuji se Vanya a jsem Java vývojář. Stává se, že hodně pracuji s PostgreSQL – nastavuji databázi, optimalizuji strukturu, výkon a o víkendech hraji trochu DBA.
Nedávno jsem dal do pořádku několik databází v našich mikroslužbách a napsal java knihovnu , což tuto práci usnadňuje, šetří mi čas a pomáhá mi vyhnout se některým běžným chybám, kterých se vývojáři dopouštějí. Právě o této knihovně si dnes povíme.
Odmítnutí odpovědnosti
Hlavní verze PostgreSQL, se kterou pracuji, je 10. Všechny SQL dotazy, které používám, jsou také testovány na verzi 11. Minimální podporovaná verze je 9.6.
pravěk
Vše začalo téměř před rokem pro mě zvláštní situací: konkurenční tvorba indexu z ničeho nic skončila chybou. Samotný index jako obvykle zůstal v databázi v neplatném stavu. Analýza protokolů ukázala nedostatek . A jdeme na to... Když jsem se ponořil hlouběji, objevil jsem spoustu problémů v konfiguraci databáze a vyhrnul si rukávy a začal je s jiskrou v očích opravovat.
Problém jedna - výchozí konfigurace
Asi každého už dost unavuje metafora o Postgresu, který lze provozovat na kávovaru, ale... výchozí konfigurace skutečně vyvolává řadu otázek. Minimálně stojí za to věnovat pozornost údržba_práce_pam, limit_temp_file_limit, výpis_časový limit и lock_timeout.
V našem případě údržba_práce_pam bylo výchozích 64 MB a limit_temp_file_limit něco kolem 2 GB - prostě jsme neměli dostatek paměti na vytvoření indexu na velké tabulce.
Proto v pg-index-health Shromáždil jsem sérii , podle mého názoru, parametry, které by měly být nakonfigurovány pro každou databázi.
Problém dva – duplicitní indexy
Naše databáze žijí na discích SSD a my je používáme HA-konfigurace s více datovými centry, hlavním hostitelem a n- počet replik. Prostor na disku je pro nás velmi cenným zdrojem; není o nic méně důležitý než výkon a spotřeba CPU. Na jednu stranu tedy potřebujeme indexy pro rychlé čtení a na druhou stranu nechceme v databázi vidět zbytečné indexy, protože zabírají místo a zpomalují aktualizaci dat.
A teď, když jsem vše obnovil a viděl dost , rozhodl jsem se uspořádat „velkou“ čistku. Ukázalo se, že vývojáři neradi čtou dokumentaci databáze. Moc se jim to nelíbí. Z tohoto důvodu vznikají dvě typické chyby – ručně vytvořený index na primárním klíči a podobný „ruční“ index na jedinečném sloupci. Faktem je, že nejsou potřeba – Postgres udělá vše sám. Takové indexy lze bezpečně smazat a pro tento účel se objevila diagnostika .
Problém třetí - protínající se indexy
Většina začínajících vývojářů vytváří indexy na jednom sloupci. Postupně, po důkladném prozkoumání tohoto podnikání, lidé začnou optimalizovat své dotazy a přidávat složitější indexy, které obsahují několik sloupců. Takto se zobrazují indexy ve sloupcích A, A + B, A+B+C a tak dále. První dva z těchto indexů lze bezpečně vyhodit, protože jsou předponami třetího. To také šetří hodně místa na disku a existuje na to diagnostika .
Problém čtvrtý - cizí klíče bez indexů
Postgres vám umožňuje vytvářet omezení cizích klíčů bez zadání podpůrného indexu. V mnoha situacích to není problém a nemusí se to ani projevit... Prozatím...
U nás to bylo stejné: jen nám v určitém okamžiku začal hlavní hostitel „přidávat“ zakázku, která běží podle plánu a čistí databázi testovacích zakázek. CPU a IO šly do odpadu, požadavky se zpomalovaly a vypršely, služba byla za pět stovek. Rychlá analýza ukázalo, že dotazy jako:
delete from <table> where id in (…)V tomto případě byl samozřejmě v cílové tabulce index podle id a podle podmínky bylo odstraněno jen velmi málo záznamů. Zdálo se, že by vše mělo fungovat, ale bohužel ne.
Ten úžasný přišel na pomoc vysvětlit analýzu a řekl, že kromě mazání záznamů v cílové tabulce existuje také kontrola referenční integrity a na jedné ze souvisejících tabulek tato kontrola selže sekvenční skenování kvůli nedostatku vhodného indexu. Tak se zrodila diagnostika .
Úloha XNUMX – nulová hodnota v indexech
Ve výchozím nastavení Postgres obsahuje hodnoty null v indexech btree, ale obvykle tam nejsou potřeba. Proto se pilně snažím tyto nuly vyhodit (diagnostika ), vytváření částečných indexů ve sloupcích s možnou hodnotou Null podle typu where <A> is not null. Tímto způsobem se mi podařilo zmenšit velikost jednoho z našich indexů z 1877 MB na 16 KB. A v jedné ze služeb se velikost databáze snížila celkem o 16 % (o 4.3 GB v absolutních číslech) kvůli vyloučení nulových hodnot z indexů. Obrovská úspora místa na disku s velmi jednoduchými úpravami. 🙂
Problém šest – nedostatek primárních klíčů
Vzhledem k povaze mechanismu taková situace je možná když velikost vašeho stolu rychle roste kvůli velkému počtu mrtvých záznamů. Naivně jsem věřil, že nám tohle nehrozí, a že se to naší základně nestane, protože my, wow!!!, jsme normální vývojáři... Jak jsem byl hloupý a naivní...
Jednoho dne jedna úžasná migrace vzala a aktualizovala všechny záznamy ve velké a aktivně používané tabulce. Z ničeho nic jsme dostali +100 GB k velikosti tabulky. Byla to zatracená škoda, ale tím naše neštěstí neskončila. Poté, co autovakuum na tomto stole skončilo o 15 hodin později, bylo jasné, že fyzické umístění se nevrátí. Nemohli jsme zastavit službu a udělat VACUUM FULL, tak jsme se rozhodli použít . A pak se to ukázalo pg_repack neví, jak zpracovat tabulky bez primárního klíče nebo jiného omezení jedinečnosti, a naše tabulka neměla primární klíč. Tak se zrodila diagnostika .
Ve verzi knihovny 0.1.5 Byla přidána možnost sbírat data z nadbytečných tabulek a indexů a reagovat na ně včas.
Problém sedm a osm - nedostatečné indexy a nepoužívané indexy
Následující dvě diagnostiky jsou: и – se ve své konečné podobě objevily relativně nedávno. Jde o to, že je nelze jen tak vzít a přidat.
Jak jsem již psal, používáme konfiguraci s několika replikami a zátěž čtení na různých hostitelích je zásadně odlišná. V důsledku toho se situace ukazuje, že některé tabulky a indexy na některých hostitelích se prakticky nepoužívají a pro analýzu je třeba shromáždit statistiky od všech hostitelů v clusteru. To je také nutné na každém hostiteli v clusteru, nemůžete to udělat pouze na hlavním serveru.
Tento přístup nám umožnil ušetřit několik desítek gigabajtů odstraněním indexů, které nebyly nikdy použity, a také přidáním chybějících indexů do zřídka používaných tabulek.
Jako závěr
Samozřejmě, pro téměř všechny diagnostiky můžete konfigurovat . Tímto způsobem můžete ve své aplikaci rychle implementovat kontroly, které zabrání výskytu nových chyb, a poté postupně opravit ty staré.
Některou diagnostiku lze provést ve funkčních testech ihned po zavedení migrace databáze. A to je možná jedna z nejvýkonnějších funkcí mé knihovny. Příklad použití najdete v .
Má smysl provádět kontroly na nepoužívané nebo chybějící indexy, stejně jako na nadýmání, pouze na skutečné databázi. Shromážděné hodnoty lze zaznamenat do nebo odeslány do monitorovacího systému.
Opravdu v to doufám pg-index-health budou užitečné a žádané. Můžete také přispět k rozvoji knihovny tím, že budete hlásit problémy, které najdete, a navrhovat novou diagnostiku.
Zdroj: www.habr.com