PostgreSQL indeksite tervis Java arendaja silmade läbi

Tere.

Minu nimi on Vanya ja ma olen Java-arendaja. Olen töötanud palju PostgreSQL-iga – seadistades andmebaase, optimeerides struktuuri, tõhusust ja mängides natuke DBA-d nädalavahetustel.

Viimase aja jooksul olen korrastanud mitmeid andmebaase meie mikroteenustes ja kirjutanud Java-raamatukogu pg-index-health, mis lihtsustab seda tööd, säästab minu aega ja aitab vältida mõningaid tüüpilisi vigu, mida arendajad teevad. Just sellest raamatukogust räägime täna.

Märkus

Peamine PostgreSQL versioon, millega ma töötan, on 10. Olen kontrollinud ka kõiki kasutatavaid SQL-päringuid 11. versioonis. Minimumnõudlik versioon on 9.6.

Eelalugu

Kõik algas peaaegu aasta tagasi kummalise olukorraga minu jaoks: konkurentsivõimeline indekseerimise loomine tühjal kohal lõppes veaga. Ise indeks, nagu ikka, jäi vale olekusse andmebaasis. Logide analüüs näitas, et puudus temp_file_limit. Ja läks lahti... Sügavamale kaevudes leidsin hulga probleeme andmebaasi konfiguratsioonis ja ülesäratatud kätega, sädemetega silmades, asusin neid parandama.

Esimene probleem – vaikimisi konfiguratsioon

Tõenäoliselt on Postgres'e kohviaparatuuriga käivitusmeetanid juba paljudele kõvasti kõrva jäänud, kuid… vaikeseaded tõepoolest tekitavad mitmeid küsimusi. Esiteks tasub tähelepanu pöörata maintenance_work_mem, temp_file_limit, statement_timeout ja lock_timeout.

Meie puhul maintenance_work_mem oli vaikimisi 64 MB, kuid temp_file_limit umbes 2 GB – meil lihtsalt ei jätkunud mälu suure tabeli indeksi loomiseks.

Seega pg-index-health koondasin mõned olulised, minu arvates, parameetrid, mida tuleks iga DB jaoks seadistada.

Teine probleem – dubleeritud indeksid

Meie andmebaasid asuvad SSD kettadel ja kasutame HA- konfiguratsiooni mitmete andmekeskustega, peahostiga ja n- arvukate koopiatega. Kettaruumi on meie jaoks väga väärtuslik ressurss; see on sama oluline kui jõudlus ja CPU tarbimine. Seetõttu vajame, ühelt poolt, kiire lugemise jaoks indekseid, kuid teisalt ei soovi me DBs näha liigseid indekseid, kuna need kulutavad ruumi ja aeglustavad andmete värskendamist.

Ja nii, taastades kõik kehtetud indeksid ja vaadates Oleg Bartunovi ettekandeid, ma otsustasin korraldada «suure» puhastuse. Selgus, et arendajad ei armasta andmebaasi dokumentatsiooni lugeda. Nad ei armasta seda üldse. Selle tõttu tekivad kaks tüüpilist viga – käsitsi loodud indeks primaarvõtmele ja sarnane «käsitsi» indeks unikaalsele veerule. Asi on selles, et need ei ole vajalikud – Postgres teeb kõik ise. Selliseid indekse saab julgelt eemaldada ja selleks on välja töötatud diagnoos. duplicated_indexes.

Kolmas probleem – kattuvad indeksid

Enamik algajaid arendajaid loob indekseid ühe veeru põhjal. Aja jooksul, peale selle ettevõtteks proovimist, hakkavad inimesed oma päringute optimeerimist laiendama ja lisama keerulisemaid indekseid, mis hõlmavad mitut veergu. Nii tekivad indeksid veergudele A, A+B, A+B+C jne. Esimese kahe indeksi võib julgelt kõrvaldada, kuna need on kolmanda prefiksid. See säästab ka korralikult kettaruumi ja selle jaoks on välja kujundatud diagnoos. intersected_indexes.

Neljandaks probleemiks on välisvõtmed ilma indeksiteta.

Postgres võimaldab luua välisvõtme piiranguid ilma toetava indeksita. Paljudes olukordades ei ole see probleem, ega pruugi isegi välja tulla... Kuni teatud hetkeni...

Nii juhtus ka meiega: järsku hakkas ajastatud job, mis eemaldas testimisjärgud andmebaasist, meil „laduma” peahostit. CPU ja IO tõusid lakke, päringud aeglustusid ja katkestasid ajaülesande tõttu, teenus andis 500 viga. Kiire analüüs pg_stat_activity näitas, et seisma jäid päringud, mis nägid välja nagu:

kustuta <table> kus id on (…)

Samuti oli siht tabelis loomulikult indeks id järgi, ja kirjeid kustutati tingimuse alusel üsna vähe. Tundus, et kõik peaks töötama, aga kahjuks ei töötanud.

Appi tuli imeline explain analyze ja näitas, et lisaks kirje kustutamisele siht tabelis tehakse veel viidete terviklikkuse kontroll ning ühe seotud tabeli osas see kontroll kukub järjestikusse skaneerimisse sobiva indeksi puudumise tõttu. Nii sündis diagnoos foreign_keys_without_index.

Viies probleem – null väärtus indeksites

Vaikimisi sisaldab Postgres btree-indeksite seas null-väärtuseid, kuid need ei ole seal tavaliselt vajalikud. Seetõttu püüan ma null'id eemaldada (diagnostika indexes_with_null_values), luues osalisi indekseid nullable-veergude tüübi põhjal where is not null. Sel viisil olen suutnud vähendada ühe meie indeksi suurust 1877 MB-lt 16 KB-le. Ja ühes teenuses vähenes andmebaasi suurus kokku 16% (4.3 GB absoluutsetes numbrites) null-väärtuste indeksitest väljaarvamise tõttu. Kolossaalne ruumi kokkuhoid üsna lihtsate paranduste kaudu. 🙂

Probleem kuus – primaarsete võtmete puudumine

Postgres'i MVCC mehhanismi tõttu on võimalik olukord, kus bloat, teie tabeli suurus kasvab kiiresti suure hulga surnud kirje tõttu. Ma arvasin naiivselt, et see meid ei puuduta ja et meie andmebaasiga sellist ei juhtu, sest me, oi-oi!!!, oleme ju normaalsed arendajad… Kui rumal ja naiivne ma olin…

Ühel kaunil päeval tegi üks imeline migratsioon kõigis suurtes ja aktiivselt kasutatavates tabelites kõik kirjed uuenduseks. Meie tabeli suurus suurenes järsult +100 GB võrra. See oli äärmiselt frustreeriv, kuid meie katsumused sellega ei lõppenud. Pärast 15 tunni möödumist autovakumeerimisest sel tabelil, sai selgeks, et füüsiline koht ei naase. Me ei saanud teenust peatada ega teha VACUUM FULL-i, seetõttu otsustati kasutada pg_repack. Siis selgus, et pg_repack ei oska töödelda tabeleid, millel pole esmast võtmed või mingit unikaalsuse piirangut, ja meie tabelil ei olnud esmast võtit. Nii sündis diagnostika tables_without_primary_key.

Raamatukogu versioonis 0.1.5 lisati võimalus koguda andmeid tabelite ja indeksite paisumise kohta ning sellele õigel ajal reageerida.

Probleemid seitse ja kaheksa – indeksite puudus ja kasutamata indeksid

Kaks järgmist diagnostikat — tables_with_missing_indexes ja unused_indexes – ilmusid oma lõplikus vormis suhteliselt hiljuti. Asi on selles, et neid ei saanud lihtsalt juurde lisada.

Nagu ma juba mainisin, kasutame mitme replikaga konfiguratsiooni, kus lugemiskoormus erinevates hostides on põhimõtteliselt erinev. Tulemuseks on olukord, kus mõned tabelid ja indeksid mõnes hostis praktiliselt ei kasutata ning analüüsi jaoks tuleb koguda statistikat kõikidest klastris olevatest hostidest. Statistika lähtestamine on samuti vajalik igas klastris asuvas hostis, ei saa seda teha ainult peahostis.

See lähenemine on võimaldanud meil säästa tosin gigabaidi võrra, eemaldades indeksid, mida kunagi ei kasutatud, ja lisades vajalikke indekseid harva kasutatavatele tabelitele.

Kokkuvõtteks

Muidugi saab peaaegu kõiki diagnostikaid seadistada väljaarvamisnimekiri. Nii saab kiiresti rakendada kontrollid teie rakenduses, et vältida uute vigade ilmnemist, ja seejärel järk-järgult parandada vanu.

Mõned diagnostikad võivad toimuda juba funktsionaalsete testide käigus vahetult pärast andmebaasi rände rakendamist. Ja see on tõenäoliselt üks võimsamaid võimalusi minu raamatukogus. Näiteks kasutamise kohta saab vaadata demo.

Kasutamata või puuduvate indeksite ning bloat'i kontrollid on mõttekad teha ainult reaalses andmebaasis. Kogutud väärtused võivad olla salvestatud ClickHouse või saadetud monitooringusüsteemi.

Ma loodan tõeliselt, et pg-index-health on kasulik ja nõutud. Saate samuti toetada teegira arendamist, teavitades avastatud probleemidest ja pakkudes uusi diagnostikate.

Allikas: habr.com