Salut.
Mă numesc Vania și sunt dezvoltator Java. Așa se face că lucrez mult cu PostgreSQL – mă ocup de configurarea Bazei de Date, optimizarea structurii, performanței și mă joc puțin de DBA în weekenduri.
În ultimul timp am pus în ordine câteva baze de date în microserviciile noastre și am scris o bibliotecă java , care facilitează această muncă, economisind timp și ajutându-mă să evit unele greșeli comune făcute de dezvoltatori. Despre această bibliotecă va fi vorba astăzi.
Declinarea
Versiunea principală a PostgreSQL cu care lucrez este 10. Toate interogările SQL pe care le folosesc sunt, de asemenea, testate pe versiunea 11. Versiunea minimă suportată este 9.6.
Povestea
Totul a început acum aproape un an cu o situație ciudată pentru mine: crearea concurentă a unui index pe un fond normal s-a încheiat cu o eroare. Indexul, ca de obicei, a rămas în stare invalidă în bază. Analiza logurilor a arătat o lipsă de . Și a început... Săpând mai adânc, am descoperit o mulțime de probleme în configurația Bazei de Date și, cu mânecile suflecate, am început să le repar.
Problema întâi – configurația implicită
Probabil că metafora despre Postgres care poate fi rulat pe o cafea s-a învechit, dar... configurația implicită ridică într-adevăr o serie de întrebări. În cel mai bun caz, merită să acordăm atenție la maintenance_work_mem, temp_file_limit, statement_timeout și lock_timeout.
În cazul nostru maintenance_work_mem era 64 MB în mod implicit, iar temp_file_limit aproximativ 2 GB – pur și simplu nu aveam suficientă memorie pentru a crea un index pe o masă mare.
Așa că în pg-index-health am adunat o serie de , din punctul meu de vedere, esențiali, care merită să fie ajustați pentru fiecare Bază de Date.
Problema a doua – indecși duplicat
Bazele noastre trăiesc pe SSD-uri și folosim HA-configurație cu mai multe centre de date, master-host și n-un număr de replici. Spațiul pe disc este un resursă extrem de valoroasă pentru noi; este la fel de important ca performanța și consumul de CPU. Prin urmare, pe de o parte, avem nevoie de indecși pentru citiri rapide, iar pe de altă parte, nu vrem să vedem indecși suplimentari în Baza de Date, deoarece aceștia ocupă spațiu și încetinesc actualizarea datelor.
Și așa, după ce am restaurat toate și m-am uitat la , am decis să fac o „mare” curățare. S-a dovedit că dezvoltatorii nu iubesc să citească documentația pentru baza de date. Deloc. Din această cauză apar două erori tipice - un index creat manual pe cheia primară și un index similar „manual” pe o coloană unică. Problema este că acestea nu sunt necesare - Postgres se ocupă de tot. Aceste indecși pot fi eliminați fără ezitare, iar pentru aceasta a apărut diagnosticul .
Problema a treia - indecși care se suprapun
Majoritatea dezvoltatorilor începători creează indecși pe o singură coloană. Treptat, odată ce se obișnuiesc cu asta, oamenii încep să își optimizeze cererile și să adauge indecși mai complexi care includ mai multe coloane. Astfel apar indecși pe coloanele A, A+B, A+B+C și așa mai departe. Primele două dintre acești indecși pot fi cu ușurință eliminate, deoarece sunt prefixe ale celui de-al treilea. Acest lucru economisește de asemenea un loc considerabil pe disc și pentru aceasta există diagnosticul .
Problema a patra - chei externe fără indecși
Postgres permite crearea de constrângeri de cheie externă fără indicarea unui index de suport. În multe situații aceasta nu reprezintă o problemă, și chiar poate să nu se manifeste deloc... Până la un anumit moment...
A fost și la noi: pur și simplu, la un moment dat, jobul care se executa conform programului și curăța baza de date de comenzi de testare a început să ne adune un master host. CPU și IO zburau în aer, cererile întârziau și se întrerupeau din cauza timeout-ului, serviciul returna erori 500. O analiză rapidă a arătat că cererile de tip se blocau:
şterge din <table> unde id este în (…)În același timp, indexul pe id în tabelul țintă, evident, exista, iar înregistrările erau șterse pe bază de condiție foarte puțin. Părea că totul ar trebui să funcționeze, dar, din păcate, nu funcționa.
La ajutor a venit minunatul explain analyze și a spus că, pe lângă ștergerea înregistrărilor din tabelul țintă, se face și verificarea integrității referențiale, și în unul dintre tabelele asociate această verificare degenerează într-un sequential scan din cauza lipsei unui index adecvat. Astfel a apărut diagnosticul .
Problema a cincea - valoarea null în indecși
Implicit, Postgres include valorile null în indecșii btree, dar acestea nu sunt, de obicei, necesare acolo. De aceea, mă străduiesc cu sârguință să elimin aceste null-uri (diagnosticul ), creând indecși parțiali pe coloanele nullable de tip where is not null. În acest mod, am reușit să reduc dimensiunea unuia dintre indecșii noștri de la 1877 MB la 16 KB. Într-unul dintre servicii, dimensiunea bazei de date a scăzut cu 16% (cu 4.3 GB în cifre absolute) prin excluderea valorilor null din indici. O economie colosală de spațiu pe disc cu modificări destul de simple. 🙂
Problema a șasea – lipsa cheilor primare
Datorită particularităților mecanismului poate apărea o situație în care , atunci când dimensiunea tabelului dumneavoastră crește rapid din cauza unei cantități mari de înregistrări moarte. Am crezut naiv că nu ne amenință și că baza noastră nu va păți așa ceva, deoarece suntem, wow!!!, dezvoltatori normali… Cât de prost și naiv am fost…
Într-o zi frumoasă, o migrare minunată a luat și a actualizat toate înregistrările dintr-un tabel mare și folosit activ. Am obținut +100 GB la dimensiunea tabelului fără un motiv vizibil. A fost extrem de frustrant, dar neajunsurile noastre nu s-au oprit aici. După ce avacuumul automat a durat 15 ore pe acest tabel, a devenit clar că locul fizic nu se va întoarce. Nu am putut opri serviciul și realiza un VACUUM FULL, așa că am decis să folosim . Și aici s-a descoperit că pg_repack nu poate procesa tabele fără o cheie primară sau cu altă restricție de unicitate, iar tabelul nostru nu avea cheie primară. Așa a apărut diagnosticul .
În versiunea bibliotecii 0.1.5 s-a adăugat abilitatea de a colecta date despre bloat-ul tabelelor și indicilor și de a reacționa la timp.
Problemele șapte și opt – lipsa indicilor și indecși neutilizați
Următoarele două diagnostice – și – au apărut în forma lor finală relativ recent. Problema este că nu puteau fi adăugate pur și simplu.
Așa cum am mai spus, folosim o configurație cu mai multe replici, iar încărcătura de citire pe diferite gazde este fundamental diferită. Drept urmare, apare o situație în care anumite tabele și indici pe anumite gazde practic nu sunt utilizați, și pentru analiză trebuie să colectăm statistici de pe toate gazdele din cluster. de asemenea, trebuie realizată pe fiecare gazdă din cluster, nu se poate face doar pe master.
Această abordare ne-a permis să economisim câteva zeci de gigabytes prin eliminarea indexurilor care nu au fost niciodată utilizate și prin adăugarea indexurilor lipsă pe tabelele rareori folosite.
În concluzie
Desigur, pentru aproape toate diagnosticările se poate configura . Astfel, este posibil să implementați rapid verificările în aplicația dumneavoastră, prevenind apariția unor noi erori și apoi corectând treptat erorile vechi.
O parte dintre diagnosticări pot fi efectuate deja în testele funcționale imediat după aplicarea migrațiilor Bazei de Date. Și aceasta este, fără îndoială, una dintre cele mai puternice capabilități ale bibliotecii mele. Un exemplu de utilizare poate fi văzut în .
Verificările pentru indexuri neutilizate sau absente, precum și pentru bloat, au sens să fie efectuate doar pe o Bază de Date reală. Valorile colectate pot fi înregistrate în sau trimise către un sistem de monitorizare.
Sper sincer că pg-index-health va fi utilă și solicitată. De asemenea, puteți contribui la dezvoltarea bibliotecii, raportând problemele descoperite și propunând noi diagnosticări.
Sursa: habr.com
