Mhoroi
Ini ndinonzi Vanya uye ndiri mugadziri weJava. Izvo zvinoitika kuti ini ndinoshanda zvakanyanya nePostgreSQL - kumisikidza dhatabhesi, kukwenenzvera chimiro, kuita, uye kutamba diki DBA pakupera kwevhiki.
Munguva pfupi yapfuura ndakarongedza akati wandei dhatabhesi mumamicroservices edu uye ndakanyora raibhurari yejava , izvo zvinoita kuti basa iri rive nyore, rinondichengetedza nguva uye rinondibatsira kuti ndidzivise zvimwe zvikanganiso zvinowanzoitika nevagadziri. Ndiyo raibhurari iyi yatichataura nezvayo nhasi.
Disclaimer
Iyo huru vhezheni yePostgreSQL yandinoshanda nayo ndeye gumi. Yese mibvunzo yeSQL yandinoshandisa inoedzwawo pane vhezheni 10. Iyo shoma inotsigirwa shanduro ndeye 11.
prehistory
Izvo zvese zvakatanga rinenge gore rapfuura nemamiriro ezvinhu aishamisa kwandiri: kusikwa kwemakwikwi eindex kunze kwebhuruu kwakapera nekukanganisa. Indekisi pachayo, semazuva ese, yakaramba iri mudura remamiriro ekusashanda. Kuongorora matanda kwakaratidza kushomeka . Uye isu tinoenda ... Kuchera zvakadzika, ndakawana boka rose rezvinetso mukugadzirisa dhatabhesi uye, ndichipeta maoko angu, ndakatanga kuzvigadzirisa nekupenya mumaziso angu.
Dambudziko rimwe - default configuration
Zvichida munhu wese akanaka aneta nedimikira pamusoro pePostgres, iyo inogona kumhanyirwa pamugadziri wekofi, asi ... iyo yakasarudzika gadziriso inomutsa mibvunzo yakawanda. Pazvishoma, zvakakodzera kubhadhara kuchengetedza_basa_mem, temp_file_limit, statement_timeout ΠΈ lock_timeout.
Munyaya yedu kuchengetedza_basa_mem yaive yakasarudzika 64 MB, uye temp_file_limit chimwe chinhu chakatenderedza 2 GB - isu takanga tisina ndangariro yakakwana kugadzira index patafura hombe.
Naizvozvo, mu pg-index-hutano Ndakaunganidza zvakatevedzana , mumaonero angu, maparameter anofanirwa kugadziridzwa kune yega yega database.
Dambudziko rechipiri - duplicate indexes
Yedu dhatabhesi inogara pane SSD madhiraivha, uye isu tinoshandisa HA-kugadzirisa neakawanda data nzvimbo, master host uye n-nhamba yezvinyorwa. Disk nzvimbo chinhu chakakosha zvikuru kwatiri; haina kukosha pane kuita uye kushandiswa kweCPU. Naizvozvo, kune rumwe rutivi, tinoda indexes yekuverenga nekukurumidza, uye kune rumwe rutivi, isu hatidi kuona zvisina kufanira indexes mudhatabhesi, sezvo ivo vachidya nzvimbo uye vanononoka kuvandudza data.
Uye ikozvino, mushure mekudzorera zvinhu zvose uye vaona zvakakwana , ndakasarudza kuronga "kuru" kuchenesa. Zvakazoitika kuti vanogadzira havafarire kuverenga zvinyorwa zve database. Havazvifariri zvakanyanya. Nekuda kweizvi, zvikanganiso zviviri zvakajairika zvinomuka - indekisi yakagadzirwa nemaoko pane kiyi yekutanga uye yakafanana "manual" indekisi pane imwe yakasarudzika. Chokwadi ndechekuti ivo havadiwe - Postgres ichaita zvese pachayo. Ma indexes akadaro anogona kubviswa zvakachengeteka, uye kuongororwa kwakaonekwa nekuda kweichi chinangwa .
Dambudziko rechitatu - intersecting indices
Vazhinji vanogadzira vanogadzira vanogadzira indexes pane imwechete column. Zvishoma nezvishoma, vachinyatsoona bhizinesi iri, vanhu vanotanga kukwidziridza mibvunzo yavo uye nekuwedzera mamwe ma index akaoma anosanganisira makoramu akati wandei. Aya ndiwo maitiro anoita indexes pamakoramu A, A + B, A + B + C. zvichingoenda zvakadaro. Iwo maviri ekutanga eaya indices anogona kukandwa kunze zvakachengeteka, sezvo ari prefixes yechitatu. Izvi zvakare zvinochengetedza yakawanda dhisiki nzvimbo uye kune diagnostics yeizvi .
Dambudziko rechina - makiyi ekunze asina indexes
Postgres inobvumidza iwe kugadzira ekunze kiyi zvipingamupinyi pasina kutsanangura indekisi yekutsigira. Muzviitiko zvakawanda iyi haisi dambudziko, uye inogona kusatombozviratidza ... Kwenguva iripo ...
Zvakanga zvakafanana nesu: ndezvekuti pane imwe nguva basa, rinomhanya maererano nehurongwa uye kuchenesa dhatabhesi yemirairo yekuedza, yakatanga "kuwedzerwa" kwatiri nemukuru weimba. CPU neIO zvakaenda kumarara, zvikumbiro zvakadzikira uye zvakapedzwa nguva, sevhisi yaive mazana mashanu. Kurumidza kuongorora yakaratidza kuti mibvunzo yakadai:
delete from <table> where id in (β¦)Muchiitiko ichi, hongu, pakanga pane index ne id mutafura yechinangwa, uye zvinyorwa zvishomanana zvakabviswa maererano nemamiriro acho. Zvaiita sokuti zvinhu zvose zvinofanira kushanda, asi, nhamo, hazvina kushanda.
Anoshamisa akauya kuzonunura tsanangura ongorora uye akataura kuti pamusoro pekudzima marekodhi mutafura inotarirwa, pane zvakare referensi yekuvimbika, uye pane imwe yematafura ane hukama cheki iyi inotadza. sequential scan nekuda kwekushaikwa kweindekisi yakakodzera. Nokudaro kuongororwa kwakaitwa .
Dambudziko shanu - null kukosha mune indexes
Nekumisikidza, Postgres inosanganisira null kukosha mune btree indexes, asi kazhinji haidiwe ipapo. Naizvozvo, ndinoshingairira kuyedza kukanda kunze idzi nulls (diagnostics ), kugadzira ma indexes pamakoramu asingabatike nemhando where <A> is not null. Neiyi nzira ndakakwanisa kuderedza saizi yeimwe yema indexes edu kubva pa1877 MB kusvika pa16 KB. Uye mune imwe yemasevhisi, saizi yedatabase yakadzikira zvachose ne16% (ne 4.3 GB muhuwandu hwakakwana) nekuda kwekubviswa kwezvisina maturo kubva kuma indexes. Kuchengetedza kwakakura munzvimbo yedhisiki ine zvigadziriso zvakapusa. π
Dambudziko rechitanhatu - kushaya makiyi ekutanga
Nekuda kwechimiro chemaitiro mamiriro akaita seaya anogoneka apo hukuru hwetafura yako huri kukura nokukurumidza nekuda kwenhamba yakawanda yezvinyorwa zvakafa. Ndakatenda nekusaziva kuti izvi hazvizotityisidzira, uye kuti izvi hazvizoitike kunheyo yedu, nekuti isu, wow!!!, tiri vagadziri vemazuva ese ...
Rimwe zuva, kumwe kutama kwakanaka kwakatora nekuvandudza marekodhi mutafura hombe uye yakashandiswa nesimba. Isu tine +100 GB kuhukuru hwetafura kunze kwebhuruu. Kwaive kunyara chaiko, asi nhamo dzedu hadzina kuperera ipapo. Mushure mekunge autovacuum iri patafura iyi yapera maawa gumi nemashanu gare gare, zvakava pachena kuti nzvimbo yacho yaisazodzoka. Hatina kukwanisa kumisa sevhisi uye kugadzira VACUUM FULL, saka takafunga kushandisa . Uye zvakazoitika kuti pg_repack haizive kugadzirisa matafura pasina kiyi yekutanga kana chimwe chinomanikidza, uye tafura yedu yanga isina kiyi yekutanga. Nokudaro kuongororwa kwakaitwa .
Muraibhurari shanduro 0.1.5 Iko kugona kuunganidza data kubva bloat yematafura uye indexes uye kupindura kwairi panguva yakakodzera kwakawedzerwa.
Matambudziko manomwe nemasere - asina kukwana indexes uye asina kushandiswa indexes
Aya maviri anotevera diagnostics ndeaya: ΠΈ - vakaonekwa muchimiro chavo chekupedzisira munguva pfupi yapfuura. Chiripo ndechekuti vaisagona kungotorwa nekuwedzerwa.
Sezvandatonyora, tinoshandisa gadziriso ine akati wandei replicas, uye mutoro wekuverenga pane akasiyana mauto akasiyana zvakanyanya. Nekuda kweizvozvo, mamiriro acho anoitika kuti mamwe matafura uye indexes pane mamwe mauto haatombo shandiswa, uye kuti uongorore iwe unofanirwa kuunganidza manhamba kubva kune ese mauto ari musumbu. Izvi zvinodikanwawo pane yega yega dhizaini; haugone kuita izvi chete pane tenzi.
Iyi nzira yakatibvumira kuchengetedza makumi akati wandei emagigabytes nekubvisa indexes dzisina kumbobvira dzashandiswa, pamwe nekuwedzera zvisipo kumatafura asingawanzo shandiswa.
Semhedziso
Ehe, kune dzinenge dzese diagnostics iwe unogona kugadzirisa . Nenzira iyi, unogona kukurumidza kuita cheki mukushandisa kwako, kudzivirira zvikanganiso zvitsva kubva pakuonekwa, uye wozogadzirisa zvishoma nezvishoma zvekare.
Mamwe ma diagnostics anogona kuitwa mukushanda bvunzo nekukurumidza mushure mekutangisa dhatabhesi kutama. Uye ichi pamwe ndicho chimwe chezvinhu zvine simba zveraibhurari yangu. Muenzaniso wekushandiswa unogona kuwanikwa mukati .
Zvine musoro kuita cheki yezvisina kushandiswa kana zvisipo indexes, pamwe ne bloat, chete pane chaiyo dhatabhesi. Iwo akaunganidzwa maitiro anogona kurekodhwa mukati kana kutumirwa kumonitoring system.
Ndinovimba kuti pg-index-hutano zvichave zvinobatsira uye mukuda. Iwe unogona zvakare kubatsira mukuvandudza raibhurari nekutaura matambudziko aunowana uye nekupa mazano matsva ekuongorora.
Source: www.habr.com