Përshëndetje.
Emri im është Vanya dhe unë jam një zhvillues Java. Ndodh që unë punoj shumë me PostgreSQL - duke vendosur bazën e të dhënave, duke optimizuar strukturën, performancën dhe duke luajtur pak DBA në fundjavë.
Kohët e fundit kam rregulluar disa baza të dhënash në mikroshërbimet tona dhe kam shkruar një bibliotekë java , gjë që e bën këtë punë më të lehtë, më kursen kohë dhe më ndihmon të shmang disa gabime të zakonshme të bëra nga zhvilluesit. Është kjo bibliotekë për të cilën do të flasim sot.
Mohim përgjegjësie
Versioni kryesor i PostgreSQL me të cilin punoj është 10. Të gjitha pyetjet SQL që përdor janë testuar gjithashtu në versionin 11. Versioni minimal i mbështetur është 9.6.
parahistorinë
Gjithçka nisi gati një vit më parë me një situatë që ishte e çuditshme për mua: krijimi konkurrues i një indeksi jashtë mase përfundoi me një gabim. Vetë indeksi, si zakonisht, mbeti në bazën e të dhënave në një gjendje të pavlefshme. Analiza e regjistrave tregoi një mungesë . Dhe shkojmë... Duke gërmuar më thellë, zbulova një sërë problemesh në konfigurimin e bazës së të dhënave dhe, duke përveshur mëngët, fillova t'i rregulloj ato me një shkëlqim në sytë e mi.
Problemi i parë - konfigurimi i paracaktuar
Ndoshta të gjithë janë goxha të lodhur nga metafora për Postgres, e cila mund të përdoret në një aparat kafeje, por... konfigurimi i paracaktuar me të vërtetë ngre një sërë pyetjesh. Së paku, ia vlen t'i kushtohet vëmendje mirëmbajtja_puna_mem, temp_file_limit, deklarata_koha и lock_timeout.
Në rastin tonë mirëmbajtja_puna_mem ishte parazgjedhja 64 MB, dhe temp_file_limit diçka rreth 2 GB - thjesht nuk kishim memorie të mjaftueshme për të krijuar një indeks në një tabelë të madhe.
Prandaj, në fq-indeks-shëndet Kam mbledhur një seri , për mendimin tim, parametrat që duhet të konfigurohen për çdo bazë të dhënash.
Problemi i dytë - indekse të dyfishta
Bazat tona të të dhënave jetojnë në disqet SSD dhe ne i përdorim HA-konfigurim me qendra të shumta të dhënash, host master dhe n-numri i kopjeve. Hapësira në disk është një burim shumë i vlefshëm për ne; nuk është më pak i rëndësishëm se performanca dhe konsumi i CPU-së. Prandaj, nga njëra anë, ne kemi nevojë për indekse për lexim të shpejtë, dhe nga ana tjetër, ne nuk duam të shohim indekse të panevojshme në bazën e të dhënave, pasi ato hanë hapësirë dhe ngadalësojnë përditësimin e të dhënave.
Dhe tani, pasi të keni rivendosur gjithçka dhe duke parë mjaftueshëm , vendosa të organizoj një spastrim “të madh”. Doli se zhvilluesve nuk u pëlqen të lexojnë dokumentacionin e bazës së të dhënave. Nuk e pëlqejnë shumë. Për shkak të kësaj, lindin dy gabime tipike - një indeks i krijuar manualisht në një çelës primar dhe një indeks i ngjashëm "manual" në një kolonë unike. Fakti është se ato nuk janë të nevojshme - Postgres do të bëjë gjithçka vetë. Indekse të tilla mund të fshihen në mënyrë të sigurt dhe janë shfaqur diagnostifikime për këtë qëllim .
Problemi i tretë - indekset e kryqëzimit
Shumica e zhvilluesve fillestarë krijojnë indekse në një kolonë të vetme. Gradualisht, pasi e kanë përjetuar plotësisht këtë biznes, njerëzit fillojnë të optimizojnë pyetjet e tyre dhe të shtojnë indekse më komplekse që përfshijnë disa kolona. Kështu shfaqen indekset në kolona A, A + B, A+B+C e kështu me radhë. Dy të parët e këtyre indekseve mund të hidhen me siguri, pasi ato janë parashtesa të të tretit. Kjo gjithashtu kursen shumë hapësirë në disk dhe ka diagnostifikime për këtë .
Problemi katër - çelësa të huaj pa indekse
Postgres ju lejon të krijoni kufizime kyçe të huaja pa specifikuar një indeks mbështetës. Në shumë situata ky nuk është problem dhe mund të mos shfaqet as vetë... Për momentin...
Ishte e njëjta gjë me ne: thjesht në një moment në kohë një punë, duke funksionuar sipas një plani dhe duke pastruar bazën e të dhënave të urdhrave të testimit, filloi të na "shtohej" nga hosti kryesor. CPU dhe IO shkuan dëm, kërkesat u ngadalësuan dhe u ndërprenë, shërbimi ishte pesëqind. Analizë e shpejtë tregoi se pyetje si:
delete from <table> where id in (…)Në këtë rast, natyrisht, kishte një indeks sipas id në tabelën e synuar dhe shumë pak regjistrime u fshinë sipas kushtit. Dukej sikur gjithçka duhej të funksiononte, por, mjerisht, nuk ndodhi.
E mrekullueshme erdhi në shpëtim shpjegoj analizoj dhe tha se përveç fshirjes së të dhënave në tabelën e synuar, ekziston edhe një kontroll i integritetit referues, dhe në një nga tabelat përkatëse ky kontroll dështon skanim sekuencial për shkak të mungesës së një indeksi të përshtatshëm. Kështu lindi diagnoza .
Problemi i pestë – vlera zero në indekse
Si parazgjedhje, Postgres përfshin vlera null në indekset btree, por ato zakonisht nuk nevojiten atje. Prandaj, unë me zell përpiqem t'i hedh këto nule (diagnostika ), duke krijuar indekse të pjesshme në kolonat e pavlefshme sipas llojit where <A> is not null. Në këtë mënyrë unë munda të zvogëloja madhësinë e një prej indekseve tona nga 1877 MB në 16 KB. Dhe në një nga shërbimet, madhësia e bazës së të dhënave u ul në total me 16% (me 4.3 GB në numra absolut) për shkak të përjashtimit të vlerave nule nga indekset. Kursime të mëdha në hapësirën e diskut me modifikime shumë të thjeshta. 🙂
Problemi i gjashtë - mungesa e çelësave kryesorë
Për shkak të natyrës së mekanizmit një situatë e tillë është e mundur kur madhësia e tabelës suaj po rritet me shpejtësi për shkak të një numri të madh regjistrimesh të vdekura. Besova me naivitet se kjo nuk do të na kërcënonte, dhe se kjo nuk do të ndodhte me bazën tonë, sepse ne, uau!!!, jemi zhvillues normal... Sa budalla dhe naiv isha...
Një ditë, një migrim i mrekullueshëm mori dhe përditësoi të gjitha të dhënat në një tabelë të madhe dhe të përdorur në mënyrë aktive. Ne morëm +100 GB në madhësinë e tabelës nga bluja. Ishte një turp i madh, por fatkeqësitë tona nuk mbaruan me kaq. Pasi autovakuumi në këtë tryezë përfundoi 15 orë më vonë, u bë e qartë se vendndodhja fizike nuk do të kthehej. Nuk mundëm ta ndalonim shërbimin dhe ta bënim VACUUM FULL, ndaj vendosëm ta përdorim . Dhe pastaj doli që pg_repack nuk di të përpunojë tabela pa një çelës primar ose kufizime të tjera unike, dhe tabela jonë nuk kishte një çelës primar. Kështu lindi diagnoza .
Në versionin e bibliotekës 0.1.5 Është shtuar aftësia për të mbledhur të dhëna nga fryrja e tabelave dhe indekseve dhe për t'iu përgjigjur atyre në kohën e duhur.
Problemet shtatë dhe tetë - indekse të pamjaftueshme dhe indekse të papërdorura
Dy diagnostikimet e mëposhtme janë: и – u shfaqën në formën e tyre përfundimtare relativisht kohët e fundit. Çështja është se ato nuk mund të merren dhe të shtohen.
Siç kam shkruar tashmë, ne përdorim një konfigurim me disa kopje, dhe ngarkesa e leximit në host të ndryshëm është thelbësisht e ndryshme. Si rezultat, situata rezulton se disa tabela dhe indekse në disa host praktikisht nuk përdoren, dhe për analizë ju duhet të mbledhni statistika nga të gjithë hostet në grup. Kjo është gjithashtu e nevojshme për çdo host në grup; nuk mund ta bëni këtë vetëm në master.
Kjo qasje na lejoi të kursenim disa dhjetëra gigabajt duke hequr indekset që nuk ishin përdorur kurrë, si dhe duke shtuar indekset që mungojnë në tabelat e përdorura rrallë.
Si përfundim
Sigurisht, për pothuajse të gjitha diagnostikimet mund të konfiguroni . Në këtë mënyrë, ju mund të zbatoni shpejt kontrollet në aplikacionin tuaj, duke parandaluar shfaqjen e gabimeve të reja dhe më pas të rregulloni gradualisht të vjetrat.
Disa diagnostifikime mund të kryhen në teste funksionale menjëherë pas hapjes së migrimeve të bazës së të dhënave. Dhe kjo është ndoshta një nga veçoritë më të fuqishme të bibliotekës sime. Një shembull përdorimi mund të gjendet në .
Ka kuptim të kryhen kontrolle për indekset e papërdorura ose të munguara, si dhe për fryrje, vetëm në një bazë të dhënash reale. Vlerat e mbledhura mund të regjistrohen në ose dërgohen në sistemin e monitorimit.
Unë vërtet shpresoj se fq-indeks-shëndet do të jetë e dobishme dhe në kërkesë. Ju gjithashtu mund të kontribuoni në zhvillimin e bibliotekës duke raportuar problemet që gjeni dhe duke sugjeruar diagnostifikime të reja.
Burimi: www.habr.com