PostgreSQL indeksite tervis Java-arendaja silmade läbi

Tere.

Mina nimi on Vanya ja olen Java arendaja. Juhtus nii, et töötan palju PostgreSQL-iga – tegeleme andmebaasi seadistamise, struktuuri optimeerimise, jõudluse ning natuke mängin DBA nädalavahetustel.

Viimasel ajal 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 täna ka jutt.

Märkus

Peamine PostgreSQL versioon, millega ma töötan, on 10. Kõik minu kasutatavad SQL päringud on samuti kontrollitud 11. versioonis. Minimaalne toetatud versioon on 9.6.

Eellugu

Kõik algas peaaegu aasta tagasi kummalise olukorraga: konkurentsi käigus indeksi loomine ebaõnnestus. Ise indeks, nagu ikka, jäi andmebaasi kehtetuks. Logide analüüs näitas, et puudub temp_file_limit. Ja siis läks lahti... Kaevudes sügavamale, avastasin terve hunniku probleeme andmebaasi konfiguratsioonis ja tõukasin käised üles, pannes särama silmad, hakkasin neid parandama.

Esimene probleem – vaikesätte

Tõenäoliselt on metafoor Postgresist, mida saab käivitada kohvimasinal, kõigil juba kõrini, aga... vaikeconfiguraatsioon tõepoolest tekitab küsimusi. Vähemalt tasub tähelepanu pöörata maintenance_work_mem, temp_file_limit, statement_timeout ja lock_timeout.

Meie puhul maintenance_work_mem oli vaikesäteteks 64 MB, kuid temp_file_limit ligikaudu 2 GB - meil oli lihtsalt puudu mälu indeksi loomise jaoks suurel tabelil.

Seetõttu olen pg-index-health koondanud mitmed võtme, minu arvates, parameetrid, mida tasub iga andmebaasi jaoks seadistada.

Teine probleem - dubleeritud indeksid

Meie andmebaasid asuvad SSD ketastel ja kasutame HA-konfiguratsiooni mitme andmekeskusega, meistriserveri ja n-arvu koopiatega. Ruumi kettal on meie jaoks äärmiselt väärtuslik ressurss; see on sama oluline kui jõudlus ja CPU tarbimine. Seetõttu vajame ühtepidi indekseid kiireks lugemiseks, aga teisest küljest ei soovi me näha andmebaasis liigseid indekseid, kuna need söövad ruumi ja aeglustavad andmete uuendamist.

Ja nii, taastades kõik kehtetuks jäänud indeksid ja olles vaadanud Oleg Bartunovi ettekandeid, otsustasin teha „suure“ puhastuse. Selgus, et arendajad ei armasta andmebaasi dokumentatsiooni lugeda. Ükski neist ei armasta. Selle tõttu tekivad kaks tüüpilist viga – käsitsi loodud indeks põhivõtmele ja sarnane „käsi” indeks unikaalsele veerule. Asjaolu on see, et neid pole vaja – Postgres teeb kõik ise. Sellised indeksid võib julgelt kustutada ja nende jaoks on välja antud diagnostika. dubleeritud_indeksid.

Kolmas probleem – kattuvad indeksid

Enamik algajaid arendajaid loob indekseid ühe veeru jaoks. Aja jooksul, kui nad hakkavad seda protsessi korralikult hindama, hakkavad nad optimeerima oma päringuid ja lisama keerulisemaid indekseid, mis hõlmavad mitmeid veerge. Nii tekivad indeksid veergudele A, A+B, A+B+C jne. Esimesed kaks neist indeksitest võib julgelt välja visata, kuna need on kolmanda prefiksid. See säästab ka diskiruumi ja selleks on olemas diagnostika. katkendlikud_indeksid.

Neljas probleem – välisvõtmed ilma indeksiteta

Postgres võimaldab luua välisvõtme piiranguid näitamata toetavat indeksi. Paljudes olukordades ei ole see probleemiks, ja isegi ei pruugi end kuidagi ilmutada… Kuni hetkeni…

Nii oli ka meiega: lihtsalt mingil hetkel ajastatud töö, mis kustutatakse testtellimustest, hakkas „koguma“ meile meistrihostit. CPU ja IO läksid lakke, päringud pidurdusid ja aegusid, teenus andis 500 viga. Kiire analüüs pg_stat_activity näitas, et takerdusid päringud tüüpi:

kustuta <table> kus id on (…)

Samas, indeks ID järgi sihtlaudas oli loomulikult olemas ja kirjeid kustutati tingimuse järgi üsna vähe. Tundus, et kõik peaks töötama, kuid kahjuks ei töötanud.

Abi tuli imelise explain analyze ja ütles, et lisaks kirje eemaldamisele sihtlaudades toimub ka linkide terviklikkuse kontroll ja ühel seotud laual jõuab see kontroll järjestikusse skaneerimisse sobiva indeksi puudumise tõttu. Nii sündis diagnostika. valmis_võtmed_ilma_indeksita.

Viies probleem – null väärtus indeksites

Vaikimisi sisaldab Postgres null-väärtusi btree-indeksites, kuid need ei ole seal reeglina vajalikud. Seetõttu üritan ma need nullid hoolikalt eemaldada (diagnostika indeksid_null_väärtustega), luues osutilised indeksid nullitavate veergude jaoks, nagu kus ei ole nullSel viisil suutsin ma vähendada ühe meie indeksi suurust 1877 MB-st 16 KB-ks. Ja ühes teenuses vähenes andmebaasi kogu suurus 16% (valus mõõtudes 4,3 GB) nullväärtuste eemaldamise tõttu indeksitest. Kolossaalne ruumi kokkuhoid üsna lihtsate täiustuste kaudu. 🙂

Probleem kuues – esmase võtme puudumine

Seoses mehhanismi eripäradega MVCC Postgres'is on võimalik olukord, kus bloatteie tabeli suurus kasvab kiiresti suurte hulkade surnud kirjeid tõttu. Ma arvasin naiivselt, et me ei pea selle pärast muretsema, ja et meie andmebaas ei satu sellesse situatsiooni, kuna me, vähemasti, oleme normaalsed arendajad… Kui rumal ja naiivne ma olin…

Üks ilus päev uue migratsiooni käigus värskendati kõiki kirjeid suures ja aktiivselt kasutuses olevas tabelis. Saime +100 GB tabeli suurenduseks niisama. See oli kohutavalt kurb, kuid meie seiklused sellega ei lõppenud. Pärast 15 tunni jooksul lõppenud automaatsest tühjendamisest selgus, et füüsiline ruum ei naase. Me ei saanud teenust peatada ja teha VACUUM FULL, seetõttu otsustati kasutada pg_repack. Ja siis selgus, et pg_repack ei oska töötada tabelitega, kus puudub esmane võti või muu unikaalsuse piirang, ning meie tabelil ei olnud esmase võti. Niimoodi sündis diagnostika tables_without_primary_key.

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

Probleemid seitse ja kaheksa – indeksite puudumine ja kasutamata indeksid

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

Nagu ma juba mainisin, kasutame mitme koopia konfigureerimist ja lugemise koormus erinevatel hostidel on sisuliselt erinev. Lõppkokkuvõttes tekib olukord, kus teatud tabelid ja indeksid mõnel hostil praktiliselt ei kasutata, ja analüüsimiseks on vajalik koguda statistikat kõigilt klastris olevatelt hostidelt. Statistika tühjendamine on samuti vajalik igas hostis klastri sees, ei saa seda teha ainult meistril.

Selle lähenemisega suutsime säästa mitu tosinat gigabaiti, kustutades indekseid, mida kunagi ei kasutatud, ning lisades puuduolevad indekseid harva kasutatavatele tabelitele.

Kokkuvõtteks

Muidugi on enamikku diagnostikast võimalik seadistada välja arvatud nimekiri. Nii saab kiiresti rakendada kontrolle teie rakenduses, vältides uusi vigu ja seejärel järk-järgult vana parandades.

Mõne diagnostika võib teostada juba funktsionaalsetes testides kohe pärast andmebaasi migratsioonide rakendamist. Ja see on tõenäoliselt üks minu teegi kõige võimsamaid funktsioone. Kasutamise näidet saab vaadata demo.

Kontrollid mittekasutatud või puuduvate indeksete ning bloat'i osas on mõttekas teostada ainult reaalsetes andmebaasides. Kogutud väärtused võivad olla salvestatud ClickHouse või saadetud jälgimisse süsteemi.

Loodan väga, et pg-index-health osutub kasulikuks ja nõutuks. Samuti saate aidata teegi arengut, teavitades avastatud probleemidest ja pakkudes uusi diagnostikaid.

Allikas: habr.com