Hello.
A nevem Ványa, Java fejlesztő vagyok. Megesik, hogy sokat dolgozom a PostgreSQL-lel – felállítom az adatbázist, optimalizálom a szerkezetet, a teljesítményt, és hétvégén egy kis DBA-t játszom.
A közelmúltban több adatbázist is rendbe raktam a mikroszolgáltatásainkban, és írtam egy java könyvtárat , ami megkönnyíti ezt a munkát, időt takarít meg, és segít elkerülni a fejlesztők által elkövetett gyakori hibákat. Erről a könyvtárról fogunk ma beszélni.
A felelősség megtagadása
A PostgreSQL fő verziója, amellyel dolgozom, a 10. Az összes általam használt SQL-lekérdezés a 11-es verzión is tesztelve van. A minimálisan támogatott verzió a 9.6.
őstörténet
Az egész csaknem egy éve egy számomra furcsa helyzettel kezdődött: a semmiből kiinduló kompetitív indexalkotás hibával végződött. Maga az index szokás szerint érvénytelen állapotban maradt az adatbázisban. A naplóelemzés hiányt mutatott ki . És indulunk... Mélyebbre ásva egy csomó problémát fedeztem fel az adatbázis-konfigurációban, és felgyűrve az ingujjamat, szikrázó szemmel elkezdtem javítani őket.
Egy probléma - alapértelmezett konfiguráció
Valószínűleg már mindenki eléggé elege van a Postgres-ről szóló metaforából, ami egy kávéfőzőn is futtatható, de... az alapértelmezett konfiguráció valóban számos kérdést vet fel. Minimum érdemes odafigyelni karbantartási_munkamem, temp_file_limit, nyilatkozat_időtúllépés и lock_timeout.
A mi esetünkben karbantartási_munkamem az alapértelmezett 64 MB volt, és temp_file_limit valami 2 GB körüli - egyszerűen nem volt elég memóriánk ahhoz, hogy indexet hozzunk létre egy nagy táblán.
Ezért, ben pg-index-health Összegyűjtöttem egy sorozatot , véleményem szerint az egyes adatbázisokhoz beállítandó paraméterek.
Második probléma – ismétlődő indexek
Adatbázisaink SSD meghajtókon élnek, és mi használjuk HA-konfiguráció több adatközponttal, fő gazdagéppel és n- a replikák száma. A lemezterület nagyon értékes erőforrás számunkra; nem kevésbé fontos, mint a teljesítmény és a CPU fogyasztás. Ezért egyrészt indexekre van szükségünk a gyors olvasáshoz, másrészt nem akarunk felesleges indexeket látni az adatbázisban, hiszen ezek felemésztik a helyet és lassítják az adatfrissítést.
És most, miután mindent helyreállított és eleget látott , úgy döntöttem, hogy szervezek egy „nagyszerű” tisztogatást. Kiderült, hogy a fejlesztők nem szeretnek adatbázis-dokumentációt olvasni. Nem nagyon szeretik. Emiatt két tipikus hiba lép fel: egy manuálisan létrehozott index az elsődleges kulcson és egy hasonló „kézi” index egy egyedi oszlopon. A helyzet az, hogy nincs rájuk szükség – a Postgres mindent maga fog megtenni. Az ilyen indexek nyugodtan törölhetők, erre a célra diagnosztika jelent meg .
Harmadik probléma – egymást metsző indexek
A legtöbb kezdő fejlesztő egyetlen oszlopban hoz létre indexeket. Fokozatosan, miután alaposan megtapasztalták ezt az üzletet, az emberek elkezdik optimalizálni lekérdezéseiket, és összetettebb, több oszlopot tartalmazó indexeket adnak hozzá. Így jelennek meg az oszlopok indexei A, A + B, A+B+C stb. Ezen indexek közül az első kettőt nyugodtan ki lehet dobni, mivel ezek a harmadik előtagjai. Ezzel is sok lemezterületet takaríthatunk meg, és erre vannak diagnosztikai eszközök .
Negyedik probléma - idegen kulcsok indexek nélkül
A Postgres lehetővé teszi az idegen kulcs megszorítások létrehozását háttérindex megadása nélkül. Sok helyzetben ez nem probléma, sőt meg sem nyilvánul... Egyelőre...
Nálunk is így volt: csak valamikor egy ütemterv szerint futó és a tesztrendelések adatbázisát kiürítő munkát kezdett „hozzáadni” hozzánk a főgazda. A CPU és az IO tönkrement, a kérések lelassultak és lejártak, a szolgáltatás ötszáz volt. Gyors elemzés megmutatta, hogy a következő lekérdezések:
delete from <table> where id in (…)Ebben az esetben természetesen a céltáblában volt egy id szerinti index, és nagyon kevés rekordot töröltek a feltételnek megfelelően. Úgy tűnt, mindennek működnie kell, de sajnos nem.
A csodálatos jött a megmentésre magyarázza az elemzést és azt mondta, hogy a céltáblában lévő rekordok törlése mellett van egy hivatkozási integritás-ellenőrzés is, és az egyik kapcsolódó táblán ez az ellenőrzés sikertelen. szekvenciális szkennelés megfelelő index hiánya miatt. Így született meg a diagnosztika .
Ötödik feladat – null érték az indexekben
Alapértelmezés szerint a Postgres null értékeket tartalmaz a btree indexekben, de általában nincs szükség rájuk. Ezért szorgalmasan igyekszem kidobni ezeket a nullákat (diagnosztika ), részleges indexek létrehozása a nullálható oszlopokon típusonként where <A> is not null. Ily módon sikerült az egyik indexünk méretét 1877 MB-ról 16 KB-ra csökkenteni. Az egyik szolgáltatásban pedig összesen 16%-kal (abszolút számban 4.3 GB-tal) csökkent az adatbázis mérete a null értékek kizárása miatt az indexekből. Óriási megtakarítás a lemezterületen nagyon egyszerű módosításokkal. 🙂
Hatodik probléma – az elsődleges kulcsok hiánya
A mechanizmus természetéből adódóan egy ilyen helyzet lehetséges amikor a táblázat mérete gyorsan növekszik a sok halott rekord miatt. Naivan azt hittem, hogy ez nem fenyeget minket, és ez nem fog megtörténni a bázisunkkal, mert mi, hú!!!, normális fejlesztők vagyunk... Milyen hülye és naiv voltam...
Egy nap egy csodálatos migráció elvette és frissítette az összes rekordot egy nagy és aktívan használt táblázatban. +100 GB-ot kaptunk az asztal méretéhez. Rohadt kár volt, de a szerencsétlenségeink ezzel nem értek véget. Miután ezen az asztalon az autovákuum 15 órával később véget ért, világossá vált, hogy a fizikai hely nem tér vissza. Nem tudtuk leállítani a szolgáltatást és a VÁKUMOT TELJESsé tenni, ezért a használata mellett döntöttünk . És akkor kiderült pg_repack nem tudja, hogyan kell feldolgozni a táblákat elsődleges kulcs vagy más egyediségi megkötés nélkül, és a táblánknak nem volt elsődleges kulcsa. Így született meg a diagnosztika .
A könyvtári változatban 0.1.5 Bekerült az a képesség, hogy adatokat gyűjtsön a táblákból és indexekből, és időben válaszoljon rájuk.
Hetedik és nyolcadik probléma – elégtelen indexek és nem használt indexek
A következő két diagnosztika: и – viszonylag nemrég jelentek meg végleges formájukban. A lényeg az, hogy nem lehetett csak úgy venni és hozzáadni.
Ahogy már írtam, több replikával rendelkező konfigurációt használunk, és a különböző gazdagépek olvasási terhelése alapvetően eltérő. Ennek eredményeként a helyzet úgy alakul, hogy néhány táblát és indexet néhány gazdagépen gyakorlatilag nem használnak, és az elemzéshez statisztikákat kell gyűjteni a fürt összes gazdagépéről. Ez a fürt minden gazdagépén is szükséges; ezt nem teheti meg csak a mesteren.
Ez a megközelítés lehetővé tette számunkra, hogy több tíz gigabájtot takarítsunk meg azáltal, hogy eltávolítjuk a soha nem használt indexeket, valamint hozzáadjuk a hiányzó indexeket a ritkán használt táblákhoz.
Következtetésként
Természetesen szinte minden diagnosztikához konfigurálható . Így gyorsan végrehajthat ellenőrzéseket az alkalmazásban, megakadályozva az új hibák megjelenését, majd fokozatosan kijavíthatja a régieket.
Egyes diagnosztika funkcionális tesztek során azonnal elvégezhető az adatbázis-áttelepítések elindítása után. És talán ez a könyvtáram egyik legerősebb funkciója. A felhasználási példát itt találja .
Célszerű csak valódi adatbázison ellenőrizni a nem használt vagy hiányzó indexeket, valamint a felfúvódást. Az összegyűjtött értékek rögzíthetők vagy elküldjük a megfigyelő rendszernek.
Nagyon remélem pg-index-health hasznos és keresett lesz. A talált problémák bejelentésével és új diagnosztikai javaslatokkal is hozzájárulhat a könyvtár fejlesztéséhez.
Forrás: will.com