Kumusta
Ang pangalan ko ay Vanya at ako ay isang developer ng Java. Nagkataon na madalas akong nagtatrabaho sa PostgreSQL - ang pagse-set up ng database, pag-optimize ng istraktura, pagganap, at paglalaro ng kaunting DBA sa katapusan ng linggo.
Kamakailan ay inayos ko ang ilang mga database sa aming mga microservice at nagsulat ng isang java library , na nagpapadali sa gawaing ito, nakakatipid sa akin ng oras at tinutulungan akong maiwasan ang ilang karaniwang pagkakamali na ginagawa ng mga developer. Ito ang library na pag-uusapan natin ngayon.
Pagtanggi sa pananagutan
Ang pangunahing bersyon ng PostgreSQL na pinagtatrabahuhan ko ay 10. Lahat ng SQL query na ginagamit ko ay nasubok din sa bersyon 11. Ang minimum na sinusuportahang bersyon ay 9.6.
prehistory
Nagsimula ang lahat halos isang taon na ang nakakaraan sa isang sitwasyon na kakaiba sa akin: ang mapagkumpitensyang paglikha ng isang index na wala sa asul ay natapos sa isang error. Ang index mismo, gaya ng dati, ay nanatili sa database sa isang di-wastong estado. Ang pagtatasa ng log ay nagpakita ng kakulangan . At umalis na kami... Sa paghuhukay ng mas malalim, natuklasan ko ang isang buong bungkos ng mga problema sa configuration ng database at, pag-roll up ng aking mga manggas, nagsimulang ayusin ang mga ito nang may kinang sa aking mga mata.
Unang problema - default na configuration
Marahil ang lahat ay medyo pagod sa metapora tungkol sa Postgres, na maaaring patakbuhin sa isang coffee maker, ngunit... ang default na pagsasaayos ay talagang nagtataas ng isang bilang ng mga katanungan. Sa pinakamababa, ito ay nagkakahalaga ng pagbibigay pansin maintenance_work_mem, temp_file_limit, statement_timeout ΠΈ lock_timeout.
Sa kaso natin maintenance_work_mem ay ang default na 64 MB, at temp_file_limit isang bagay sa paligid ng 2 GB - wala kaming sapat na memorya upang lumikha ng isang index sa isang malaking talahanayan.
Samakatuwid, sa pg-index-kalusugan Nakakolekta ako ng isang serye , sa aking opinyon, ang mga parameter na dapat i-configure para sa bawat database.
Dalawang problema - mga duplicate na index
Nakatira ang aming mga database sa mga SSD drive, at ginagamit namin HA-configuration na may maraming data center, master host at n-bilang ng mga replika. Ang espasyo sa disk ay isang napakahalagang mapagkukunan para sa amin; ito ay hindi mas mahalaga kaysa sa pagganap at pagkonsumo ng CPU. Samakatuwid, sa isang banda, kailangan namin ng mga index para sa mabilis na pagbabasa, at sa kabilang banda, hindi namin nais na makita ang mga hindi kinakailangang index sa database, dahil kumakain sila ng espasyo at nagpapabagal sa pag-update ng data.
At ngayon, na naibalik ang lahat at pagkakaroon ng sapat na nakita , nagpasya akong mag-organisa ng isang "mahusay" na paglilinis. Ito ay lumabas na ang mga developer ay hindi gustong magbasa ng dokumentasyon ng database. Hindi nila ito masyadong gusto. Dahil dito, dalawang tipikal na error ang lumitaw - isang manu-manong ginawang index sa isang pangunahing key at isang katulad na "manual" na index sa isang natatanging column. Ang katotohanan ay hindi sila kailangan - Gagawin ng mga Postgres ang lahat mismo. Ang mga naturang index ay maaaring ligtas na matanggal, at ang mga diagnostic ay lumitaw para sa layuning ito .
Ikatlong problema - intersecting na mga indeks
Karamihan sa mga baguhang developer ay gumagawa ng mga index sa isang column. Unti-unti, nang lubusang naranasan ang negosyong ito, nagsisimulang i-optimize ng mga tao ang kanilang mga query at magdagdag ng mas kumplikadong mga index na may kasamang ilang column. Ganito lumilitaw ang mga index sa mga column A, A + B, A + B + C at iba pa. Ang unang dalawa sa mga indeks na ito ay maaaring ligtas na itapon, dahil ang mga ito ay prefix ng pangatlo. Nakakatipid din ito ng maraming espasyo sa disk at may mga diagnostic para dito .
Problema apat - mga dayuhang key na walang mga index
Binibigyang-daan ka ng mga postgres na lumikha ng mga dayuhang key constraints nang hindi tinukoy ang isang backing index. Sa maraming sitwasyon ito ay hindi isang problema, at maaaring hindi man lang magpakita mismo... Pansamantala...
Ito ay pareho sa amin: ito lamang na sa ilang mga punto sa oras ng isang trabaho, tumatakbo ayon sa isang iskedyul at pag-clear sa database ng mga order ng pagsubok, ay nagsimulang "idagdag" sa amin ng master host. Ang CPU at IO ay nasayang, ang mga kahilingan ay bumagal at nag-time out, ang serbisyo ay limang daan. Mabilis na pagsusuri nagpakita na ang mga query tulad ng:
delete from <table> where id in (β¦)Sa kasong ito, siyempre, mayroong isang index sa pamamagitan ng id sa target na talahanayan, at napakakaunting mga talaan ay tinanggal ayon sa kundisyon. Mukhang dapat gumana ang lahat, ngunit, sayang, hindi.
Ang kahanga-hangang isa ay dumating upang iligtas ipaliwanag ang pagsusuri at sinabi na bilang karagdagan sa pagtanggal ng mga tala sa target na talahanayan, mayroon ding referential integrity check, at sa isa sa mga nauugnay na talahanayan ay nabigo ang pagsusuring ito sunud-sunod na pag-scan dahil sa kakulangan ng angkop na index. Kaya ipinanganak ang mga diagnostic .
Limang problema - null value sa mga index
Bilang default, ang mga Postgres ay nagsasama ng mga null na halaga sa mga btree index, ngunit kadalasan ay hindi sila kailangan doon. Samakatuwid, masigasig kong sinusubukang itapon ang mga null na ito (diagnostics ), na lumilikha ng mga bahagyang index sa mga nullable na column ayon sa uri where <A> is not null. Sa ganitong paraan nagawa kong bawasan ang laki ng isa sa aming mga index mula 1877 MB hanggang 16 KB. At sa isa sa mga serbisyo, ang laki ng database ay nabawasan sa kabuuan ng 16% (sa pamamagitan ng 4.3 GB sa ganap na mga numero) dahil sa pagbubukod ng mga null na halaga mula sa mga index. Napakalaking pagtitipid sa espasyo sa disk na may napakasimpleng pagbabago. π
Ika-anim na problema - kakulangan ng pangunahing mga susi
Dahil sa likas na katangian ng mekanismo ang sitwasyong tulad nito ay posible kapag ang laki ng iyong talahanayan ay mabilis na lumalaki dahil sa isang malaking bilang ng mga patay na talaan. Ako ay walang muwang na naniniwala na ito ay hindi magbabanta sa amin, at na hindi ito mangyayari sa aming base, dahil kami, wow!!!, ay mga normal na developer... Gaano ako katanga at walang muwang...
Isang araw, isang kahanga-hangang paglipat ang kumuha at nag-update ng lahat ng mga tala sa isang malaki at aktibong ginagamit na talahanayan. Nakakuha kami ng +100 GB sa laki ng talahanayan out of the blue. Ito ay isang mapahamak na kahihiyan, ngunit ang aming mga misadventures ay hindi natapos doon. Matapos matapos ang autovacuum sa talahanayang ito makalipas ang 15 oras, naging malinaw na hindi na babalik ang pisikal na lokasyon. Hindi namin mapigil ang serbisyo at gawing PUNO ang VACUUM, kaya nagpasya kaming gamitin . At saka ito pala pg_repack ay hindi alam kung paano iproseso ang mga talahanayan nang walang pangunahing key o iba pang limitasyon sa pagiging natatangi, at ang aming talahanayan ay walang pangunahing key. Kaya ipinanganak ang mga diagnostic .
Sa bersyon ng aklatan 0.1.5 Ang kakayahang mangolekta ng data mula sa bloat ng mga talahanayan at index at tumugon dito sa isang napapanahong paraan ay naidagdag.
Mga problema pito at walo - hindi sapat na mga index at hindi nagamit na mga index
Ang sumusunod na dalawang diagnostic ay: ΠΈ β lumitaw sa kanilang huling anyo kamakailan. Ang punto ay hindi lamang sila maaaring kunin at idagdag.
Tulad ng naisulat ko na, gumagamit kami ng configuration na may ilang mga replika, at ang pag-load ng pagbabasa sa iba't ibang mga host ay sa panimula ay naiiba. Bilang resulta, ang sitwasyon ay lumalabas na ang ilang mga talahanayan at index sa ilang mga host ay halos hindi ginagamit, at para sa pagsusuri kailangan mong mangolekta ng mga istatistika mula sa lahat ng mga host sa cluster. Ito ay kinakailangan din sa bawat host sa cluster; hindi mo ito magagawa sa master lamang.
Ang diskarte na ito ay nagpapahintulot sa amin na makatipid ng ilang sampu-sampung gigabytes sa pamamagitan ng pag-alis ng mga index na hindi kailanman ginamit, pati na rin ang pagdaragdag ng mga nawawalang index sa mga bihirang ginagamit na talahanayan.
Bilang isang konklusyon
Siyempre, para sa halos lahat ng mga diagnostic maaari mong i-configure . Sa ganitong paraan, mabilis mong maipapatupad ang mga pagsusuri sa iyong application, na pumipigil sa mga bagong error na lumitaw, at pagkatapos ay unti-unting ayusin ang mga luma.
Maaaring isagawa ang ilang diagnostic sa mga functional na pagsusuri kaagad pagkatapos ilunsad ang mga paglilipat ng database. At ito marahil ang isa sa pinakamakapangyarihang feature ng aking library. Ang isang halimbawa ng paggamit ay matatagpuan sa .
Makatuwirang magsagawa ng mga pagsusuri para sa hindi nagamit o nawawalang mga index, pati na rin para sa bloat, sa isang tunay na database lamang. Ang mga nakolektang halaga ay maaaring maitala sa o ipinadala sa sistema ng pagsubaybay.
Sana talaga pg-index-kalusugan magiging kapaki-pakinabang at in demand. Maaari ka ring mag-ambag sa pag-unlad ng aklatan sa pamamagitan ng pag-uulat ng mga problemang makikita mo at pagmumungkahi ng mga bagong diagnostic.
Pinagmulan: www.habr.com