Tere
Minu nimi on Vanya ja ma olen Java arendaja. Juhtub nii, et töötan palju PostgreSQL-iga – seadistan andmebaasi, optimeerin struktuuri, jõudlust ja mängin nädalavahetustel veidi DBA-d.
Viimasel ajal olen korrastanud mitmeid meie mikroteenuste andmebaase ja kirjutanud Java raamatukogu , mis muudab selle töö lihtsamaks, säästab minu aega ja aitab vältida mõningaid arendajate levinud vigu. Just sellest raamatukogust me täna räägime.
Kaebused
PostgreSQL-i põhiversioon, millega ma töötan, on 10. Kõiki minu kasutatavaid SQL-päringuid testitakse ka versioonis 11. Minimaalne toetatud versioon on 9.6.
eelajalugu
Kõik sai alguse peaaegu aasta tagasi minu jaoks kummalise olukorraga: indeksi konkureeriv loomine ootamatult lõppes veaga. Indeks ise, nagu ikka, jäi andmebaasi kehtetuks. Palkide analüüs näitas puudujääki . Ja läksime... Sügavamale kaevates avastasin andmebaasi konfiguratsioonis terve hunniku probleeme ja käisin käärides, sära silmis, hakkasin neid parandama.
Probleem üks – vaikekonfiguratsioon
Ilmselt on kõik üsna väsinud metafoorist Postgresi kohta, mida saab kohvimasinaga käivitada, kuid... vaikekonfiguratsioon tekitab tõesti mitmeid küsimusi. Vähemalt tasub sellele tähelepanu pöörata hooldustöö_mälu, temp_file_limit, avaldus_ajalõpp и lock_timeout.
Meie puhul hooldustöö_mälu oli vaikimisi 64 MB ja temp_file_limit midagi 2 GB ringis – meil lihtsalt ei jätkunud piisavalt mälu, et suurele lauale indeksit luua.
Seetõttu sisse pg-indeks-tervis Kogusin sarja , minu arvates parameetrid, mis tuleks iga andmebaasi jaoks seadistada.
Probleem kaks – topeltindeksid
Meie andmebaasid asuvad SSD-draividel ja me kasutame HA-konfiguratsioon mitme andmekeskuse, peamise hosti ja n- koopiate arv. Kettaruum on meie jaoks väga väärtuslik ressurss; see pole vähem oluline kui jõudlus ja protsessori tarbimine. Seetõttu vajame ühest küljest indekseid kiireks lugemiseks ja teisest küljest ei taha me andmebaasis näha tarbetuid indekseid, kuna need söövad ruumi ja aeglustavad andmete värskendamist.
Ja nüüd, olles kõik taastanud ja olles piisavalt näinud , otsustasin korraldada "suurepärase" puhastuse. Selgus, et arendajatele ei meeldi andmebaaside dokumentatsiooni lugeda. Neile see väga ei meeldi. Seetõttu ilmnevad kaks tüüpilist viga - primaarvõtme käsitsi loodud indeks ja kordumatu veeru sarnane käsitsi loodud indeks. Fakt on see, et neid pole vaja – Postgres teeb kõik ise. Selliseid indekseid saab ohutult kustutada ja selleks on ilmunud diagnostika .
Kolmas ülesanne – ristuvad indeksid
Enamik algajaid arendajaid loob indeksid ühes veerus. Järk-järgult, olles seda äri põhjalikult kogenud, hakkavad inimesed oma päringuid optimeerima ja lisama keerukamaid indekseid, mis sisaldavad mitut veergu. Nii kuvatakse veergude indeksid A, A + B, A+B+C. ja nii edasi. Esimesed kaks neist indeksist võib julgelt välja visata, kuna need on kolmanda eesliited. See säästab ka kõvasti kettaruumi ja selleks on olemas diagnostika .
Probleem neli – võõrvõtmed ilma indeksiteta
Postgres võimaldab luua võõrvõtmepiiranguid ilma tugiindeksit määramata. Paljudes olukordades pole see probleem ja ei pruugi isegi avalduda... Esialgu...
Meiega oli samamoodi: lihtsalt mingil hetkel hakkas peamajutaja meile "lisama" graafiku järgi töötavat ja testtellimuste andmebaasi tühjendavat tööd. CPU ja IO läksid raisku, päringud aeglustusid ja aegusid, teenus oli viissada. Kiire analüüs näitas, et sellised päringud nagu:
delete from <table> where id in (…)Sel juhul oli sihttabelis muidugi indeks id järgi ja vastavalt tingimusele kustutati väga vähe kirjeid. Tundus, et kõik peaks toimima, aga paraku ei läinud.
Imeline tuli appi selgitage analüüsi ja ütles, et lisaks kirjete kustutamisele sihttabelis on olemas ka viiteterviklikkuse kontroll ja ühes seotud tabelis see kontroll ebaõnnestub järjestikune skaneerimine sobiva indeksi puudumise tõttu. Nii sündis diagnostika .
Ülesanne viis – nullväärtus indeksites
Vaikimisi sisaldab Postgres btree indeksitesse nullväärtusi, kuid tavaliselt pole neid seal vaja. Seetõttu proovin usinalt need nullid välja visata (diagnostika ), luues osalised indeksid nullitatavatele veergudele tüübi järgi where <A> is not null. Nii suutsin ühe meie indeksi suurust vähendada 1877 MB-lt 16 KB-le. Ja ühes teenuses vähenes andmebaasi suurus nullväärtuste väljajätmise tõttu indeksitest kokku 16% (absoluutarvudes 4.3 GB võrra). Tohutu kettaruumi kokkuhoid väga lihtsate muudatustega. 🙂
Kuues probleem – primaarvõtmete puudumine
Mehhanismi olemuse tõttu selline olukord on võimalik kui teie tabeli suurus kasvab kiiresti suure hulga surnud kirjete tõttu. Uskusin naiivselt, et see meid ei ähvarda ja meie baasiga seda ei juhtu, sest me, vau!!!, oleme normaalsed arendajad... Kui rumal ja naiivne ma olin...
Ühel päeval võttis üks imeline migratsioon ja uuendas kõik kirjed suures ja aktiivselt kasutatavas tabelis. Tabeli suurusele saime täiesti ootamatult +100 GB. Sellest oli pagana kahju, aga sellega meie äpardused ei lõppenud. Pärast seda, kui autovaakum sellel laual 15 tundi hiljem lõppes, sai selgeks, et füüsiline asukoht ei naase. Me ei saanud teenust peatada ja VACUUM FULL'iks muuta, seega otsustasime kasutada . Ja siis selgus, et pg_repack ei tea, kuidas töödelda tabeleid ilma primaarvõtme või muu unikaalsuspiiranguta ning meie tabelil polnud primaarvõtit. Nii sündis diagnostika .
Raamatukogu versioonis 0.1.5 Lisatud on võimalus koguda andmeid tabelite ja indeksite paisumisest ning neile õigeaegselt reageerida.
Probleemid seitse ja kaheksa – ebapiisavad indeksid ja kasutamata indeksid
Järgmised kaks diagnostikat on: и – ilmusid oma lõplikul kujul suhteliselt hiljuti. Asi on selles, et neid ei saanud lihtsalt võtta ja lisada.
Nagu ma juba kirjutasin, kasutame mitme koopiaga konfiguratsiooni ja erinevate hostide lugemiskoormus on põhimõtteliselt erinev. Selle tulemusena selgub, et mõnda tabelit ja indeksit mõnel hostil praktiliselt ei kasutata ning analüüsimiseks peate koguma statistikat kõigi klastri hostide kohta. See on vajalik ka igas klastri hostis; seda ei saa teha ainult ülemseadmega.
See lähenemine võimaldas meil säästa mitukümmend gigabaiti, eemaldades indeksid, mida kunagi ei kasutatud, ning lisades harva kasutatavatele tabelitele puuduvad indeksid.
Kokkuvõtteks
Loomulikult saate peaaegu kõigi diagnostikate jaoks konfigureerida . Nii saate oma rakenduses kiiresti kontrollida, vältides uute vigade ilmumist, ja seejärel järk-järgult parandada vanu.
Mõnda diagnostikat saab teha funktsionaalsetest testidest kohe pärast andmebaasi migratsiooni käivitamist. Ja see on võib-olla minu raamatukogu üks võimsamaid funktsioone. Kasutamise näite leiate aadressilt .
Kasutamata või puuduvate indeksite, aga ka ülepaisumise kontrollimine on mõttekas ainult reaalses andmebaasis. Kogutud väärtused saab salvestada või saadetakse seiresüsteemi.
Ma väga loodan seda pg-indeks-tervis on kasulik ja nõutud. Samuti saate raamatukogu arengusse kaasa aidata, teatades leitud probleemidest ja soovitades uusi diagnostikameetodeid.
Allikas: www.habr.com