Kumusta
Ang akong ngalan mao si Vanya ug ako usa ka developer sa Java. Nahitabo nga daghan ako nga nagtrabaho sa PostgreSQL - pag-set up sa database, pag-optimize sa istruktura, pasundayag, ug pagdula og gamay nga DBA sa katapusan sa semana.
Bag-ohay lang akong gihapsay ang daghang mga database sa among mga microservice ug nagsulat og java library , nga naghimo niini nga trabaho nga mas sayon, makadaginot kanako ug panahon ug makatabang kanako sa paglikay sa pipila ka kasagarang mga sayop nga gihimo sa mga developers. Kini nga librarya ang atong hisgutan karon.
Disclaimer
Ang nag-unang bersyon sa PostgreSQL nga akong gitrabahoan mao ang 10. Ang tanan nga mga pangutana sa SQL nga akong gigamit gisulayan usab sa bersyon 11. Ang minimum nga gisuportahan nga bersyon mao ang 9.6.
sa naunang kasaysayan
Nagsugod ang tanan hapit usa ka tuig ang milabay sa usa ka sitwasyon nga katingad-an alang kanako: ang kompetisyon nga paghimo sa usa ka indeks nga walaβy asul natapos sa usa ka sayup. Ang indeks mismo, sama sa naandan, nagpabilin sa database sa usa ka dili balido nga kahimtang. Ang pag-analisa sa log nagpakita sa kakulang . Ug sa among paglakaw... Sa pagkalot sa mas lawom, akong nadiskobrehan ang usa ka bug-os nga hugpong sa mga problema sa database configuration ug, gilukot ang akong mga manggas, nagsugod sa pag-ayo niini uban sa usa ka kidlap sa akong mga mata.
Problema uno - default configuration
Tingali ang tanan medyo gikapoy sa metapora bahin sa Postgres, nga mahimoβg ipadagan sa usa ka magbubuhat sa kape, apan ... ang default nga pagsumpo sa tinuud nagpatunghag daghang mga pangutana. Sa labing gamay, angay nga hatagan pagtagad maintenance_work_mem, temp_file_limit, statement_timeout ΠΈ lock_timeout.
Sa among kaso maintenance_work_mem mao ang default nga 64 MB, ug temp_file_limit usa ka butang sa palibot sa 2 GB - wala kami igo nga memorya aron makahimo usa ka indeks sa usa ka dako nga lamesa.
Busa, sa pg-index-panglawas Nagkolekta ako usa ka serye , sa akong opinyon, ang mga parameter nga kinahanglan i-configure alang sa matag database.
Duha ka problema - doble nga mga indeks
Ang among mga database nagpuyo sa SSD drive, ug among gigamit HA-konfigurasyon nga adunay daghang mga sentro sa datos, master host ug n- gidaghanon sa mga replika. Ang espasyo sa disk usa ka bililhon kaayo nga kapanguhaan alang kanamo; kini mao ang dili kaayo importante kay sa performance ug CPU konsumo. Busa, sa usa ka bahin, nagkinahanglan kita og mga indeks alang sa paspas nga pagbasa, ug sa laing bahin, dili nato gusto nga makita ang dili kinahanglan nga mga indeks sa database, tungod kay sila mokaon sa luna ug mohinay sa pag-update sa datos.
Ug karon, nga gipahiuli ang tanan ug igo na nga nakakita , nakahukom ko nga mag-organisar ug βdakongβ paghinlo. Nahibal-an nga ang mga nag-develop dili ganahan nga mobasa sa dokumentasyon sa database. Dili kaayo sila ganahan niini. Tungod niini, duha ka kasagaran nga mga sayup ang mitungha - usa ka manual nga gihimo nga indeks sa usa ka panguna nga yawe ug usa ka parehas nga "manwal" nga indeks sa usa ka talagsaon nga kolum. Ang tinuod mao nga wala sila kinahanglana - Ang mga postgres mismo ang magbuhat sa tanan. Ang ingon nga mga indeks mahimong luwas nga mapapas, ug ang mga diagnostic nagpakita alang niini nga katuyoan .
Tulo nga problema - nag-intersecting nga mga indeks
Kadaghanan sa mga bag-ong developer naghimo og mga indeks sa usa ka kolum. Sa hinay-hinay, nga hingpit nga nasinati kini nga negosyo, ang mga tawo nagsugod sa pag-optimize sa ilang mga pangutana ug pagdugang labi ka komplikado nga mga indeks nga adunay daghang mga kolum. Ingon niini ang pagpakita sa mga indeks sa mga kolum A, Usa ka + B, A + B + C ug uban pa. Ang una nga duha niini nga mga indeks mahimong luwas nga ilabay, tungod kay kini mga prefix sa ikatulo. Nagtipig usab kini og daghang espasyo sa disk ug adunay mga diagnostic alang niini .
Problema sa upat - langyaw nga mga yawe nga walay mga indeks
Gitugotan ka sa mga postgres nga maghimo mga langyaw nga yawe nga mga pagpugong nga wala magpiho sa usa ka indeks sa pagsuporta. Sa daghang mga sitwasyon kini dili usa ka problema, ug mahimong dili gani magpakita sa iyang kaugalingon... Sa pagkakaron...
Kini mao ang sama nga uban kanato: kini lang nga sa usa ka punto sa panahon sa usa ka trabaho, nga nagdagan sumala sa usa ka eskedyul ug paghawan sa database sa pagsulay order, nagsugod nga "idugang" kanato sa master host. Ang CPU ug IO nausik, ang mga hangyo mihinay ug na-time out, ang serbisyo lima ka gatos. Dali nga pagtuki nagpakita nga ang mga pangutana sama sa:
delete from <table> where id in (β¦)Sa kini nga kaso, siyempre, adunay usa ka indeks pinaagi sa id sa target nga lamesa, ug gamay ra nga mga rekord ang natangtang sumala sa kondisyon. Ingon og ang tanan kinahanglan nga molihok, apan, alaut, kini wala.
Ang kahibulongan miabut aron sa pagluwas ipasabot analisaha ug giingon nga dugang sa pagtangtang sa mga rekord sa target nga lamesa, adunay usab usa ka referential integrity check, ug sa usa sa mga may kalabutan nga mga lamesa kini nga tseke napakyas. sequential scan tungod sa kakulang sa usa ka angay nga indeks. Busa ang mga diagnostic natawo .
Lima nga problema - null nga kantidad sa mga indeks
Sa kasagaran, ang Postgres naglakip sa null values ββββsa mga btree index, apan kasagaran dili kini gikinahanglan didto. Busa, makugihon kong naningkamot nga isalikway kining mga null (diagnostics ), paghimo og partial index sa nullable columns sa type where <A> is not null. Niining paagiha akong napakunhod ang gidak-on sa usa sa among mga indeks gikan sa 1877 MB ngadto sa 16 KB. Ug sa usa sa mga serbisyo, ang gidak-on sa database mikunhod sa kinatibuk-an sa 16% (sa 4.3 GB sa hingpit nga mga numero) tungod sa dili paglakip sa mga null nga kantidad gikan sa mga indeks. Dako nga pagtipig sa disk space nga adunay yano nga mga pagbag-o. π
Ikaunom nga problema - kakulang sa panguna nga mga yawe
Tungod sa kinaiya sa mekanismo ang sitwasyon nga sama niini posible kung ang gidak-on sa imong lamesa kusog nga mitubo tungod sa daghang mga patay nga rekord. Nagtuo ko nga dili kini maghulga kanamo, ug nga dili kini mahitabo sa among base, tungod kay, wow!!!, kami mga normal nga developer ... Unsa ko ka buang ug kabuang ...
Usa ka adlaw, usa ka talagsaon nga paglalin ang mikuha ug nag-update sa tanang mga rekord sa usa ka dako ug aktibong gigamit nga lamesa. Nakuha namon ang + 100 GB sa gidak-on sa lamesa nga walaβy asul. Makauulaw kaayo, apan ang among mga kasaypanan wala matapos didto. Human matapos ang autovacuum niini nga lamesa 15 ka oras ang milabay, nahimong klaro nga ang pisikal nga lokasyon dili na mobalik. Dili namo mapahunong ang serbisyo ug himoong VACUUM FULL, mao nga nakahukom mi nga gamiton . Ug unya kini nahimo nga pg_repack wala kahibalo unsaon pagproseso sa mga lamesa nga walay primary key o uban pang uniqueness nga pagpugong, ug ang among table walay primary key. Busa ang mga diagnostic natawo .
Sa bersyon sa librarya 0.1.5 Ang abilidad sa pagkolekta sa datos gikan sa bloat sa mga lamesa ug mga indeks ug pagtubag niini sa tukma sa panahon nga paagi gidugang.
Ang mga problema pito ug walo - dili igo nga mga indeks ug wala magamit nga mga indeks
Ang mosunod nga duha ka mga diagnostic mao ang: ΠΈ β nagpakita sa ilang katapusang porma medyo bag-o lang. Ang punto mao nga dili lang sila madala ug madugang.
Sama sa nasulat na nako, naggamit kami usa ka pagsumpo nga adunay daghang mga replika, ug ang pagkarga sa pagbasa sa lainlaing mga host lahi sa sukaranan. Ingon usa ka sangputanan, ang kahimtang nahimo nga ang pipila nga mga lamesa ug mga indeks sa pipila nga mga host halos wala gigamit, ug alang sa pag-analisar kinahanglan nimo nga mangolekta mga estadistika gikan sa tanan nga mga host sa cluster. Kinahanglan usab kini sa matag host sa cluster; dili nimo mahimo kini sa agalon lamang.
Kini nga pamaagi nagtugot kanamo sa pagluwas sa pipila ka napulo ka gigabytes pinaagi sa pagtangtang sa mga indeks nga wala gayud gigamit, ingon man usab sa pagdugang sa nawala nga mga indeks sa panagsa ra nga gigamit nga mga lamesa.
Isip usa ka konklusyon
Siyempre, alang sa halos tanan nga mga diagnostic nga imong ma-configure . Niining paagiha, dali nimong ipatuman ang mga tseke sa imong aplikasyon, mapugngan ang mga bag-ong sayup nga makita, ug dayon anam-anam nga ayohon ang mga daan.
Ang ubang mga diagnostic mahimong ipahigayon sa functional nga mga pagsulay diha-diha dayon human sa paglansad sa database migrations. Ug kini tingali usa sa labing kusgan nga bahin sa akong librarya. Ang usa ka pananglitan sa paggamit makita sa .
Makataronganon ang paghimo sa mga tseke alang sa wala magamit o nawala nga mga indeks, ingon man usab alang sa bloat, sa tinuod nga database lamang. Ang nakolekta nga mga kantidad mahimong irekord sa o gipadala ngadto sa monitoring system.
Naglaum gyud ko nga pg-index-panglawas mahimong mapuslanon ug gikinahanglan. Mahimo ka usab nga makatampo sa pag-uswag sa librarya pinaagi sa pagreport sa mga problema nga imong nakit-an ug pagsugyot og bag-ong mga diagnostic.
Source: www.habr.com