Helló.
A nevem Vanya, és Java fejlesztő vagyok. Úgy alakult, hogy sokat dolgozom PostgreSQL-lel – adatbázisok beállításával, struktúrák optimalizálásával, teljesítménnyel foglalkozom, és kicsit hétvégenként DBA-ként is játszom.
Az utóbbi időszakban rendbe raktam néhány adatbázist a mikroszolgáltatásainkban, és írtam egy Java könyvtárat , amely megkönnyíti ezt a munkát, időt takarít meg, és segít elkerülni a fejlesztők által elkövetett tipikus hibákat. Ma erről a könyvtárról fog szólni a beszélgetés.
Megjegyzés
A fő verzió, amellyel dolgozom, a PostgreSQL 10. Az összes SQL lekérdezésemet a 11-es verzióval is teszteltem. A minimálisan támogatott verzió a 9.6.
Háttér
Mindez majdnem egy éve kezdődött egy számomra furcsa helyzettel: az erőforrás-igényes index létrehozása egyenesen hibaüzenettel zárult. Magának az indexnek, ahogy az lenni szokott, érvénytelen állapotban kellett maradnia az adatbázisban. A naplók elemzése hiányzó -et mutatott. És innentől kezdve... Miután jobban beleástam magam, egy rakás problémát találtam az adatbázis konfigurációjában, és ujjat felhúzva, csillogással a szememben, elkezdtem javítgatni őket.
Első probléma – az alapértelmezett konfiguráció
Valószínűleg a metafora, miszerint a Postgres egy kávéfőzőn is futtatható, már mindenkinek elegendő, de... az alapbeállítás valóban több kérdést is felvet. Legalábbis érdemes odafigyelni a maintenance_work_mem, temp_file_limit, statement_timeout és lock_timeout.
esetében, maintenance_work_mem amely alapértelmezetten 64 MB volt, míg temp_file_limit valami körülbelül 2 GB – nyilvánvalóan nem volt elég memóriánk ahhoz, hogy nagy táblán indexet hozzunk létre.
Ezért pg-index-health összegyűjtöttem néhány paramétert, amelyet véleményem szerint érdemes beállítani minden adatbázis esetében.
Második probléma – a duplikált indexek
Az adatbázisaink SSD meghajtókon működnek, és HA-konfigurációt használunk több adatközponttal, mester-kiszolgálóval és n-db számú replikával. A lemezhely nagyon értékes erőforrás számunkra; ugyanolyan fontos, mint a teljesítmény és a CPU fogyasztás. Ezért egyrészt szükségünk van indexekre a gyors olvasáshoz, másrészt nem akarunk felesleges indexeket látni az adatbázisban, mivel ezek helyet foglalnak és lassítják az adatok frissítését.
Miután minden helyreállítottam, és sokat néztem , eldöntöttem, hogy egy „nagy” takarítást végezek. Kiderült, hogy a fejlesztők nem szeretik elolvasni az adatbázis dokumentációját. Nagyon nem szeretik. Ennek következményeként két tipikus hiba lép fel – manuálisan létrehozott index az elsődleges kulcson és hasonló „kézi” index egyedi oszlopokra. Az a helyzet, hogy ezekre nincs szükség – a Postgres mindent megold magától. Az ilyen indexeket nyugodtan törölhetjük, ezért készült el a diagnosztika. .
Harmadik probléma – átfedő indexek
A legtöbb kezdő fejlesztő egyetlen oszlopon hoz létre indexeket. Fokozatosan, ahogy ízlelgetni kezdik ezt a dolgot, az emberek elkezdik optimalizálni a lekérdezéseiket, és összetettebb indexeket adnak hozzá, amelyek több oszlopot tartalmaznak. Így jönnek létre az indexek az oszlopokra. A, A+B, A+B+C stb. Az első kettőt nyugodtan ki lehet dobni, mivel ezek a harmadik előtagjai. Ez is jelentősen megtakarítja a lemez helyet, és ezért van diagnosztika. .
Negyedik probléma – idegen kulcsok indexek nélkül
A Postgres megengedi idegen kulcs korlátozások létrehozását anélkül, hogy támogató indexet kellene megadni. Sok helyzetben ez nem jelent problémát, és még csak nem is nyilvánul meg... Egészen egy idő múlva...
Így volt nálunk is: egyszer csak abban a pillanatban a job, ami ütemezés szerint futott és tisztította a teszt megrendelésektől az adatbázist, elkezdett nekünk egy master hostot felhalmozni. A CPU és IO az egekbe szökött, a lekérdezések akadoztak és időtúllépés miatt megszakadtak, az szolgáltatás ötszázas hibát jelezett. Gyors elemzés. megmutatta, hogy a következő típusú lekérdezések akadtak el:
törölni <table> ahol az id szerepel (…)Ezenkívül a cél táblában természetesen volt index az id-n, és a felvételek törlése a feltétel alapján nagyon kevés volt. Úgy tűnt, hogy mindennek működnie kellene, de sajnos nem működött.
A csodás explain analyze segített, és elmondta, hogy a cél táblában végzett törlés mellett a referenciális integritás ellenőrzése is folyik, és az egyik kapcsolódó táblában ez az ellenőrzés a sorozatos letapogatásba futott a megfelelő index hiánya miatt. Így született meg a diagnosztika. .
Ötödik probléma – null értékek az indexekben
Alapértelmezés szerint a Postgres beleszámítja a null értékeket a btree-indexekbe, de általában nincs szükség rájuk. Ezért szorgalmasan próbálom kidobni ezeket a null-okat (diagnosztika ), részleges indexeket létrehozva a nullable oszlopok esetében, mint például where is not nullEzzel a módszerrel sikerült csökkentenem az egyik indexünk méretét 1877 Mbyte-ról 16 Kbyte-ra. Egyik szolgáltatásunknál pedig az adatbázis mérete összesen 16%-kal (4,3 Gbyte-os abszolút számokban) csökkent a NULL értékek indexekből való eltávolítása révén. Óriási helymegtakarítás viszonylag egyszerű módosítások mellett. 🙂
Hatodik probléma – a primer kulcsok hiánya
A mechanizmus sajátosságai miatt lehetséges olyan helyzet, mint , amikor az adatbázis egyik táblájának mérete gyorsan növekszik a sok elhalt rekord miatt. Naivan azt hittem, hogy minket ez nem fenyeget, és hogy a mi adatbázisunkkal ilyesmi nem történik meg, hiszen mi, ó jaj!!!, normális fejlesztők vagyunk… Milyen buta és naiv voltam…
Egy szép napon egy csodás migráció minden rekordot frissített egy nagy és aktívan használt táblában. 100 Gbyte-tal nőtt a tábla mérete a semmiből. Borzasztóan bosszantó volt, de kalandjaink nem értek véget itt. Miután 15 óra elteltével befejeződött az automatikus takarítás ezen a táblán, világossá vált, hogy a fizikai hely nem tér vissza. Megállítani a szolgáltatást és végrehajtani a VACUUM FULL-t nem tudtuk, ezért úgy döntöttünk, hogy használjuk . És ekkor kiderült, hogy pg_repack nem tudja kezelni a primer kulcs nélküli táblákat vagy egyéb egyedi korlátozásokat, és a mi táblánkon nem volt primer kulcs. Így született meg a diagnosztika .
A könyvtár verziójában 0.1.5 megjelent a lehetőség, hogy adatokat gyűjtsenek a táblák és indexek bloat-járól és időben reagáljanak rá.
Hetedik és nyolcadik probléma – a hiányzó indexek és a nem használt indexek
A következő két diagnosztika – és – a végső formájukban viszonylag nemrégiben jelentek meg. Az a helyzet, hogy ezeket nem lehetett csak úgy hozzáadni.
Ahogy már említettem, több replikás konfigurációt használunk, és a különböző hosztokon eltérő a lekérdezési terhelés. Ennek eredményeként olyan helyzet alakul ki, hogy bizonyos táblák és indexek egyes hosztokon szinte nem használtak, és a statisztikák összegyűjtése minden hoszton a klaszterben szükséges. szintén minden hoszton a klaszterben kell visszaállítani, ezt nem lehet csak a mester hoszton megtenni.
Ez a megközelítés lehetővé tette számunkra, hogy több tíz gigabájtot spóroljunk azzal, hogy eltávolítottuk a sosem használt indexeket, valamint hogy hozzáadtuk a szükséges indexeket a ritkán használt táblákhoz.
Összegzésül
Természetesen szinte minden diagnózist be lehet állítani . Így gyorsan integrálhatjuk az ellenőrzéseket az alkalmazásában, megelőzve az új hibák megjelenését, és fokozatosan javítva a régiakat.
Néhány diagnózist már a funkcionális tesztek során végrehajthatunk közvetlenül a DB migrációk után. És ez talán a könyvtáram egyik legnagyobb lehetősége. Példa a használatra megtekinthető itt: .
Azok az ellenőrzések, amelyek a nem használt vagy hiányzó indexekre, valamint a bloat-ra vonatkoznak, csak valós adatbázison érdemesek végrehajtani. Az összegyűjtött értékeket rögzíthetjük vagy elküldhetjük a monitoring rendszernek.
Nagyon remélem, hogy pg-index-health hasznos és keresett lesz. Ön is hozzájárulhat a könyvtár fejlesztéséhez, ha jelzi a felfedezett problémákat és új diagnosztikákat javasol.
Forrás: habr.com
