Hoi
Myn namme is Vanya en ik bin in Java-ûntwikkelder. It bart sa dat ik in protte mei PostgreSQL wurkje - de database ynstelle, de struktuer, prestaasjes optimalisearje en in bytsje DBA yn 'e wykeinen spielje.
Koartlyn haw ik ferskate databases yn ús mikrotsjinsten opslein en in java-bibleteek skreaun , dy't dit wurk makliker makket, besparret my tiid en helpt my wat gewoane flaters te foarkommen makke troch ûntwikkelders. It is dizze biblioteek dêr't wy hjoed oer sille prate.
Disclaimer
De haadferzje fan PostgreSQL wêrmei ik wurkje is 10. Alle SQL-fragen dy't ik brûk wurde ek testen op ferzje 11. De minimale stipe ferzje is 9.6.
prehistoarje
It begon allegear hast in jier lyn mei in situaasje dy't my frjemd wie: it kompetitive oanmeitsjen fan in yndeks út 'e blau einige mei in flater. De yndeks sels, lykas gewoanlik, bleau yn 'e databank yn in ûnjildige steat. Log analyse liet in tekoart sjen . En wy geane ... Djipper graven, ûntduts ik in hiele boskje problemen yn 'e databasekonfiguraasje en, myn mouwen oprôle, begon se te reparearjen mei in glâns yn myn eagen.
Probleem ien - standert konfiguraasje
Wierskynlik is elkenien aardich wurch fan 'e metafoar oer Postgres, dy't kin wurde útfierd op in kofjesetapparaat, mar ... de standertkonfiguraasje ropt echt in oantal fragen op. Op syn minst is it wurdich omtinken te jaan ûnderhâld_wurk_mem, temp_file_limit, statement_timeout и lock_timeout.
Yn ús gefal ûnderhâld_wurk_mem wie de standert 64 MB, en temp_file_limit eat om 2 GB - wy hawwe gewoan net genôch ûnthâld foar in meitsje in yndeks op in grutte tafel.
Dêrom, yn pg-index-sûnens Ik sammele in rige , yn myn miening, de parameters dy't moatte wurde konfigurearre foar eltse databank.
Probleem twa - dûbele yndeksen
Us databases libje op SSD-skiven, en wy brûke HA-konfiguraasje mei meardere data sintra, master host en n- oantal replika's. Skiifromte is in tige weardefolle boarne foar ús; it is net minder wichtich as prestaasjes en CPU-konsumpsje. Dêrom hawwe wy oan 'e iene kant yndeksen nedich foar fluch lêzen, en oan' e oare kant wolle wy gjin ûnnedige yndeksen yn 'e databank sjen, om't se romte ite en it bywurkjen fan gegevens fertrage.
En no, alles restaurearre en genôch sjoen hawwe , Ik besleat om in "grutte" purge te organisearjen. It die bliken dat ûntwikkelders net graach databasedokumintaasje lêze. Se fine it net hiel leuk. Hjirtroch ûntsteane twa typyske flaters - in mei de hân oanmakke yndeks op in primêre kaai en in ferlykbere "hânlieding" yndeks op in unike kolom. It feit is dat se net nedich binne - Postgres sil alles sels dwaan. Sokke yndeksen kinne feilich wiske wurde, en foar dit doel binne diagnostyk ferskynd .
Probleem trije - krusende yndeksen
De measte begjinnende ûntwikkelders meitsje yndeksen op ien kolom. Stadichoan, nei't se dit bedriuw yngeand ûnderfûn hawwe, begjinne minsken har fragen te optimalisearjen en kompleksere yndeksen ta te foegjen dy't ferskate kolommen omfetsje. Dit is hoe't yndeksen op kolommen ferskine A, A + B, A + B + C ensafuorthinne. De earste twa fan dizze yndeksen kinne feilich smiten wurde, om't se foarheaksels binne fan 'e tredde. Dit besparret ek in soad skiifromte en dêr binne diagnostyk foar .
Probleem fjouwer - frjemde kaaien sûnder yndeksen
Postgres lit jo bûtenlânske kaaibeperkingen oanmeitsje sûnder in backing-yndeks op te jaan. Yn in protte situaasjes is dit gjin probleem, en kin it sels net manifestearje... Foar it momint...
It wie itselde mei ús: it is gewoan dat op in stuit in baan, dy't rint neffens in skema en it wiskjen fan de databank fan testopdrachten, begûn te "tafoege" oan ús troch de master host. CPU en IO gie te fergriemen, fersiken fertrage en waarden time-out, de tsjinst wie fiifhûndert. Fluch analyze liet sjen dat fragen lykas:
delete from <table> where id in (…)Yn dit gefal wie d'r fansels in yndeks troch id yn 'e doeltabel, en hiel pear records waarden wiske neffens de betingst. It like derop dat alles wurkje soe, mar helaas, it die net.
De prachtige kaam te rêden ferklearje analysearje en sei dat njonken it wiskjen fan records yn 'e doeltabel, d'r ek in referinsjele yntegriteitskontrôle is, en op ien fan' e relatearre tabellen mislearret dizze kontrôle sekwinsjele scan troch it ûntbrekken fan in gaadlike yndeks. Sa waard diagnostyk berne .
Probleem fiif - nul wearde yn yndeksen
Standert omfettet Postgres nulwearden yn btree-yndeksen, mar se binne dêr normaal net nedich. Dêrom besykje ik iverich dizze nulls (diagnostyk ), it meitsjen fan dielde yndeksen op nulbere kolommen per type where <A> is not null. Op dizze manier koe ik de grutte fan ien fan ús yndeksen ferminderje fan 1877 MB nei 16 KB. En yn ien fan 'e tsjinsten fermindere de databankgrutte yn totaal mei 16% (troch 4.3 GB yn absolute getallen) troch it útsluten fan nulwearden fan' e yndeksen. Enorme besparring yn skiifromte mei heul ienfâldige oanpassingen. 🙂
Probleem seis - gebrek oan primêre kaaien
Troch de aard fan it meganisme in situaasje as dizze is mooglik doe't de grutte fan jo tafel groeit hurd troch in grut oantal deade records. Ik leaude naïvich dat dit ús net bedrige soe, en dat dit net mei ús basis barre soe, om't wy, wow!!!, normale ûntwikkelders binne ... Hoe dom en nayf wie ik ...
Ien dei, ien prachtige migraasje naam en bywurke alle records yn in grutte en aktyf brûkte tabel. Wy krigen +100 GB oan 'e tafelgrutte út' e blau. It wie in ferrekte skande, mar dêr binne ús ûngelokken net ophâlden. Nei it autovacuum op dizze tafel einige 15 oeren letter, waard dúdlik dat de fysike lokaasje soe net werom. Wy koenen net stopje de tsjinst en meitsje VACUUM FULL, dus wy besletten om te brûken . En doe die bliken dat pg_repack wit net hoe't jo tabellen ferwurkje sûnder in primêre kaai of oare unykheidsbeheining, en ús tabel hie gjin primêre kaai. Sa waard diagnostyk berne .
Yn de bibleteek ferzje 0.1.5 De mooglikheid om gegevens te sammeljen út bloat fan tabellen en yndeksen en dêr op 'e tiid op te reagearjen is tafoege.
Problemen sân en acht - net genôch yndeksen en net brûkte yndeksen
De folgjende twa diagnostyk binne: и - ferskynde yn har definitive foarm relatyf koartlyn. It punt is dat se net gewoan wurde nommen en tafoege.
Lykas ik al skreau, brûke wy in konfiguraasje mei ferskate replika's, en de lêsbelesting op ferskate hosts is prinsipieel oars. As gefolch, de situaasje docht bliken dat guon tabellen en yndeksen op guon hosts wurde praktysk net brûkt, en foar analyze moatte sammelje statistiken fan alle hosts yn it kluster. Dit is ek nedich op elke host yn it kluster; jo kinne dit net allinich dwaan op 'e master.
Dizze oanpak stelde ús ta om ferskate tsientallen gigabytes te bewarjen troch yndeksen te ferwiderjen dy't noait waarden brûkt, en ek ûntbrekkende yndeksen tafoegje oan selden brûkte tabellen.
As konklúzje
Fansels kinne jo foar hast alle diagnostyk konfigurearje . Op dizze manier kinne jo kontrôles fluch ymplementearje yn jo applikaasje, foarkomme dat nije flaters ferskine, en dan âlde stadichoan reparearje.
Guon diagnostyk kin wurde útfierd yn funksjonele tests direkt nei it útroljen fan databasemigraasjes. En dit is miskien ien fan 'e machtichste funksjes fan myn bibleteek. In foarbyld fan gebrûk is te finen yn .
It makket sin om kontrôles út te fieren foar net brûkte of ûntbrekkende yndeksen, lykas ek foar bloat, allinich op in echte databank. De sammele wearden kinne wurde opnommen yn of stjoerd nei it tafersjochsysteem.
Ik hoopje dat echt pg-index-sûnens sil nuttich en yn fraach wêze. Jo kinne ek bydrage oan de ûntwikkeling fan de bibleteek troch problemen te melden dy't jo fine en nije diagnostyk foarstelle.
Boarne: www.habr.com