Ahoj.
Jmenuji se Vanya a jsem Java vývojář. Tak se stalo, že hodně pracuji s PostgreSQL – zabývám se nastavením databází, optimalizací struktury, výkonem a o víkendech si trochu hraji na DBA.
V poslední době jsem uspořádal několik databází v našich mikroservisách a napsal java knihovnu , která tuto práci usnadňuje, šetří můj čas a pomáhá se vyhnout některým typickým chybám, které dělají vývojáři. O této knihovně bude dnes řeč.
Poznámka
Hlavní verze PostgreSQL, se kterou pracuji, je 10. Všechny SQL dotazy, které používám, byly také ověřeny na 11. verzi. Minimálně podporovaná verze je 9.6.
Pozadí
Všechno to začalo před téměř rokem podivnou situací pro mě: konkurentní vytvoření indexu na prázdném místě skončilo s chybou. Samotný index, jak už to bývá, zůstal v databázi v neplatném stavu. Analýza logů ukázala nedostatek . A začalo to… Po podrobnějším zkoumání jsem objevil celou řadu problémů v konfiguraci databáze a, založiv si rukávy, s leskem v očích jsem se pustil do jejich oprav.
První problém – výchozí konfigurace
Možná už je metafora o Postgresu, který můžete spustit na kávovaru, dost unavující, ale… výchozí konfigurace skutečně vyvolává řadu otázek. Minimálně bychom měli věnovat pozornost maintenance_work_mem, temp_file_limit, statement_timeout a lock_timeout.
V našem případě maintenance_work_mem byla výchozí hodnota 64 MB, a temp_file_limit něco kolem 2 GB – prostě nám chyběla paměť pro vytvoření indexu na velké tabulce.
Proto jsem pg-index-health shromáždil řadu , podle mého názoru, parametrů, které je třeba nastavit pro každou databázi.
Druhý problém – duplicitní indexy
Naše databáze běží na SSD discích a používáme HA-konfiguraci s několika datovými centry, master hostem a n-ním množstvím replik. Místo na disku je pro nás velmi cenný zdroj; je to stejně důležité jako výkon a spotřeba CPU. Proto na jedné straně potřebujeme indexy pro rychlé čtení, a na druhé straně nechceme vidět v databázi nadbytečné indexy, které zabírají místo a zpomalují aktualizaci dat.
A tak, poté, co jsem obnovil všechny a podíval jsem se na , rozhodl jsem se provést "velký" úklid. Ukázalo se, že vývojáři neradi čtou dokumentaci k databázím. Opravu neradi. To vede ke vzniku dvou typických chyb – ručně vytvořený index na primární klíč a podobný "ruční" index na unikátní sloupec. Spočívá v tom, že nejsou potřeba – Postgres vše udělá sám. Takové indexy lze bezpečně odstranit a pro to byla vytvořena diagnostika .
Třetí problém – překrývající se indexy
Většina začínajících vývojářů vytváří indexy na jeden sloupec. Postupně, jak si na to zvyknou, začínají optimalizovat své dotazy a přidávat složitější indexy, které zahrnují více sloupců. Tak vznikají indexy na sloupce A, A+B, A+B+C a tak dále. První dva z těchto indexů lze bezpečně vyhodit, protože jsou prefixy třetího. To také slušně šetří místo na disku a pro to existuje diagnostika .
Čtvrtý problém – cizí klíče bez indexů
Postgres umožňuje vytvářet omezení cizího klíče bez uvedení podpůrného indexu. V mnoha situacích to není problém a nemusí se to nijak projevovat… Až do určité doby…
To bylo i u nás: v určitém okamžiku job, který se vykonával podle plánu a odstraňoval testovací objednávky, začal "skládat" nám master host. CPU a IO šly do pekla, dotazy se zpomalovaly a přerušovaly z důvodu časového limitu, služba vyhazovala pětistovky. Rychlá analýza ukázala, že se zasekávaly dotazy typu:
smazat z <table> kde id v (…)Přitom index podle id v cílové tabulce samozřejmě existoval a záznamy se odstraňovaly podle podmínky úplně málo. Zdálo se, že by to mělo fungovat, ale bohužel nefungovalo.
Na pomoc přišlo kouzelné explain analyze a řeklo, že kromě odstraňování záznamů v cílové tabulce probíhá také kontrola referenční integrity, a na jedné z propojených tabulek tato kontrola spadla do sekvencního skenování kvůli absenci vhodného indexu. Tak vznikla diagnostika .
Pátý problém – null hodnoty v indexech
Výchozím nastavením Postgres zahrnuje null hodnoty do btree indexů, ale obvykle tam nejsou potřeba. Proto se snažím tyto null hodnoty vyhazovat (diagnostika ), vytvářím částečné indexy na nullable sloupce ve tvaru where 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 jednom ze služeb se celková velikost databáze snížila o 16 % (o 4,3 GB v absolutních číslech) díky vyloučení hodnot null z indexů. Obrovská úspora místa na disku při celkem jednoduchých úpravách. 🙂
Šestý problém – absence primárních klíčů
Vzhledem k charakteristice mechanismu může nastat taková situace, kdy velikost vaší tabulky rychle roste kvůli velkému množství mrtvých záznamů. Naivně jsem si myslel, že nám se to nestane, a že s naší databází se to nikdy nestane, vždyť jsme, hrome!!!, normální vývojáři… Jak naivní jsem byl… Jednoho krásného dne jedna úžasná migrace vzala a aktualizovala všechny záznamy ve velké a aktivně využívané tabulce. Na velikosti tabulky jsme získali +100 GB doslova zahodile. Bylo to neskutečně frustrující, ale naše útrapy tímto neskončily. Poté, co po 15 hodinách skončila automatická vakuace na této tabulce, bylo jasné, že fyzické místo se nevrátí. Nemohli jsme zastavit služby a provést VACUUM FULL, proto se rozhodlo použít
. A tady se ukázalo, že neumí zpracovávat tabulky bez primárního klíče nebo jiného unikátního omezení, a na naší tabulce žádný primární klíč nebyl. Tak vznikla diagnostika pg_repack tables_without_primary_key .
byla přidána možnost sbírat data o zneužívání tabulek a indexů a včas na ně reagovat. 0.1.5 Problémy sedm a osm – nedostatek indexů a nevyužívané indexy
Následující dvě diagnostiky –
tables_with_missing_indexes a Jak jsem již psal, používáme konfiguraci s více replikami a zátěž čtení na různých hostitelích je principiálně odlišná. V důsledku toho vzniká situace, kdy některé tabulky a indexy na některých hostitelích prakticky nejsou využívány, a pro analýzu je třeba sbírat statistiky ze všech hostitelů ve clusteru.
Resetovat statistiky to také potřebné na každém hostu v clusteru, nelze to udělat pouze na masteru.
Tento přístup nám umožnil ušetřit několik desítek gigabajtů odstraněním indexů, které nikdy nebyly použity, a také přidat chybějící indexy na málo používané tabulky.
Na závěr
Samozřejmě, prakticky pro všechny diagnostiky lze nastavit . Takto můžete rychle implementovat kontroly ve vaší aplikaci, předejít vzniku nových chyb a následně postupně opravovat staré.
Některé diagnostiky mohou být prováděny již ve funkčních testech ihned po nasazení migrací databáze. A to je pravděpodobně jedna z nejmocnějších funkcí mé knihovny. Příklad použití si můžete prohlédnout v .
Kontroly nevyužívaných nebo chybějících indexů, stejně jako na bloat, mají smysl provádět pouze na reálné databázi. Shromážděné hodnoty mohou být zaznamenány do nebo odeslány do monitorovacího systému.
Opravdu doufám, že pg-index-health bude užitečná a žádaná. Můžete také přispět k rozvoji knihovny tím, že nám oznámíte nalezené problémy a navrhnete nové diagnostiky.
Zdroj: habr.com
