Ndinoda kugoverana newe chiitiko changu chekutanga chakabudirira chekudzoreredza dhatabhesi rePostgres kuti riite zvizere. Ndakazivana nePostgres DBMS hafu yegore rapfuura; izvi zvisati zvaitika ndakanga ndisina ruzivo muhutungamiri hwe database zvachose.
Ini ndinoshanda semi-DevOps injiniya mune yakakura IT kambani. Kambani yedu inogadzira software yeakakwira-mutoro masevhisi, uye ini ndine basa rekuita, kugadzirisa uye kutumira. Ini ndakapihwa basa rakajairwa: kugadzirisa application pane imwe server. Chikumbiro chakanyorwa muDjango, panguva yekuvandudza kutama kunoitwa (shanduko mune dhatabhesi chimiro), uye tisati taita izvi tinotora yakazara dhatabhesi yekurasa kuburikidza neyakajairwa pg_dump chirongwa, kana zvikaitika.
Chikanganiso chisingatarisirwi chakaitika pakutora kurasira (Postgres vhezheni 9.5):
pg_dump: Oumping the contents of table “ws_log_smevlog” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
pg_dump: The command was: COPY public.ws_log_smevlog [...]
pg_dunp: [parallel archtver] a worker process dled unexpectedly
mhosho "peji isingashande mubhuroko" inotaura nezvematambudziko pachiyero chefaira system, iyo yakaipa kwazvo. Pamaforamu akasiyana-siyana zvakakurudzirwa kuita FULL VACUUM nesarudzo zero_damaged_pages kugadzirisa dambudziko iri. Zvakanaka, ngatiedze ...
Kugadzirira kupora
KUDZIDZA! Iva nechokwadi chekutora Postgres backup isati yaedza kudzoreredza database yako. Kana iwe uine chaiwo muchina, misa iyo dhatabhesi uye tora snapshot. Kana zvisingaite kutora mufananidzo, misa dhatabhesi uye kopi zviri mukati meiyo Postgres dhairekitori (kusanganisira wal mafaera) kunzvimbo yakachengeteka. Chinhu chikuru mubhizinesi redu hachisi kuita kuti zvinhu zviwedzere. Verenga .
Sezvo dhatabhesi raiwanzondishandira, ndakazvimisira kune yenguva dzose yekurasira dhatabhesi, asi ndakasiya tafura ine data rakakuvadzwa (sarudzo -T, --exclude-tafura=TABLE mu pg_dump).
Sevha yaive yemuviri, zvaisaita kutora snapshot. Backup yabviswa, ngatienderere mberi.
Faira system cheki
Tisati taedza kudzoreredza dhatabhesi, isu tinofanirwa kuve nechokwadi chekuti zvese zvakarongeka neiyo faira system pachayo. Uye kana paine zvikanganiso, varuramise, nekuti kana zvisina kudaro iwe unogona kungoita kuti zvinhu zviwedzere.
Mune yangu, iyo faira system ine database yakaiswa mukati "/srv" uye type yacho yaive ext4.
Kumisa database: systemctl kumira [email inodzivirirwa] uye tarisa kuti iyo faira system haisi kushandiswa nemunhu uye inogona kuderedzwa uchishandisa rairo lsof:
lsof +D /srv
Ini zvakare ndaifanira kumisa redis dhatabhesi, sezvo yaishandisa zvakare "/srv". Ndakazodzika / srv (kukwira).
Iyo faira system yakatariswa uchishandisa utility eeeva ne switch -f (Manikidza kutarisa kunyangwe faira system yakanzi yakachena):
Tevere, kushandisa chishandiso dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep yakatariswa) unogona kuona kuti cheki yacho yakaitwa here:
eeeva inotaura kuti hapana matambudziko akawanikwa paext4 file system level, zvinoreva kuti unogona kuramba uchiedza kudzoreredza dhatabhesi, kana kuti dzokera vacuum yakazara (zvechokwadi, iwe unofanirwa kukwirisa iyo faira system kumashure uye wotanga dhatabhesi).
Kana iwe uine sevha yemuviri, ita shuwa yekutarisa mamiriro emadhisiki (kuburikidza smartctl -a /dev/XXX) kana RAID controller kuti ave nechokwadi chekuti dambudziko harisi padanho rehardware. Mune yangu, iyo RAID yakave "hardware", saka ndakakumbira admin wenzvimbo kuti atarise mamiriro eRAID (sevha yaive mazana emakiromita kubva kwandiri). Akataura kuti pakanga pasina zvikanganiso, zvinoreva kuti tinogona kutanga kudzoreredza.
Kuedza 1: zero_damaged_mapeji
Isu tinobatana kune dhatabhesi kuburikidza ne psql neakaundi ine superuser kodzero. Tinoda superuser, nekuti... sarudzo zero_damaged_pages ndiye chete anogona kuchinja. Mune yangu iyo postgres:
psql -h 127.0.0.1 -U postgres -s [database_name]
Sarudzo zero_damaged_pages inodiwa kuitira kufuratira zvikanganiso zvekuverenga (kubva kune postgrespro webhusaiti):
Kana PostgreSQL yaona ine huwori peji musoro, inowanzo shuma kukanganisa uye inobvisa iyo yazvino kutengeserana. Kana zero_damaged_mapeji akagoneswa, sisitimu yacho inoburitsa yambiro, zeros kunze peji yakakuvadzwa mundangariro, uye inoenderera mberi nekugadzirisa. Maitiro aya anoparadza data, kureva mitsetse yese iri papeji yakakanganisika.
Isu tinogonesa sarudzo uye edza kuita yakazara vacuum yematafura:
VACUUM FULL VERBOSE
Zvinosuruvarisa, rombo rakaipa.
Tasangana nedambudziko rakafanana:
INFO: vacuuming "“public.ws_log_smevlog”
WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page
ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070- nzira yekuchengetedza "data refu" muPoetgres kana isingakwane pane rimwe peji (8kb nekusingaperi).
Kuedza 2: reindex
Zano rekutanga kubva kuGoogle harina kubatsira. Mushure memaminitsi mashoma ekutsvaga, ndakawana yechipiri zano - kugadzira reindex tafura yakakuvadzwa. Iri zano ndakariona munzvimbo dzakawanda, asi harina kukurudzira chivimbo. Ngatitarisei zvakare:
reindex table ws_log_smevlog
reindex inopera pasina matambudziko.
Zvisinei, izvi hazvina kubatsira, VACUUM FULL yarovera nechikanganiso chakafanana. Sezvo ini ndajaira kutadza, ndakatanga kutsvagazve zano paInternet uye ndakawana rinonakidza .
Kuedza 3: SARUDZA, LIMIT, OFFSET
Chinyorwa chiri pamusoro chakurudzira kutarisa patafura mutsara nemutsara uye kubvisa dambudziko data. Kutanga, tinofanira kutarisa mitsetse yose:
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; doneKana ndiri ini, tafura yaivemo 1 628 991 mitsetse! Zvakanga zvakakodzera kutarisira zvakanaka , asi uyu ndiwo musoro wehurukuro yakasiyana. Wakanga uri Mugovera, ndakamhanya iyi command mutmux ndokuenda kunorara:
for ((i=0; i<1628991; i++ )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog LIMIT 1 offset $i" >/dev/null || echo $i; doneMangwanani ndakafunga kumbotarisa kuti zvinhu zvaifamba sei. Kukushamisika kwangu, ndakaona kuti mushure memaawa makumi maviri, chete 20% yedata yakanga yaongororwa! Handina kuda kumirira mazuva makumi mashanu. Kumwe kukundikana zvachose.
Asi handina kukanda mapfumo pasi. Zvakandinetsa kuti sei scanning yakatora nguva yakareba kudaro. Kubva pane zvinyorwa (zvakare pa postgrespro) ndakaziva:
OFFSET inotsanangura kusvetuka nhamba yakatarwa yemitsara isati yatanga kuburitsa mitsara.
Kana zvese OFFSET neLIMIT zvatsanangurwa, sisitimu inotanga yasvetuka mitsara yeOFFSET yobva yatanga kuverenga mitsara yeLIMIT constraint.Paunenge uchishandisa LIMIT, zvakakosha kushandisa zvakare ORDER BY clause kuitira kuti mitsara yemhedzisiro idzoserwe mune yakatarwa. Zvikasadaro, zvikamu zvisingatarisike zvemitsara zvichadzoserwa.
Zviripachena, murairo wepamusoro wakanga usina kururama: chekutanga, pakanga pasina order by, mugumisiro wacho unogona kuva usina kururama. Kechipiri, Postgres yakatanga kutarisisa uye kusvetuka mitsara yeOFFSET, uye nekuwedzera OFFSET kubereka kwaizoderera zvakanyanya.
Kuedza 4: tora kurasa mune zvinyorwa zvinyorwa
Ipapo pfungwa yaiita seyakajeka yakauya mumusoro mangu: tora dope muchimiro chemavara uye ongorora mutsara wekupedzisira wakarekodhwa.
Asi kutanga, ngatitarisei chimiro chetafura. ws_log_smevlog:
Muchiitiko chedu tine mbiru "Id", iyo yaive neyakasarudzika identifier (counter) yemutsetse. Hurongwa hwakanga hwakadai:
- Isu tinotanga kutora dump mune zvinyorwa zvinyorwa (nechimiro chemirairo sql)
- Pane imwe nguva nekufamba kwenguva, kurasira kwaizovhiringwa nekuda kwekukanganisa, asi iwo mameseji faira airamba achichengetwa pa diski.
- Isu tinotarisa kumagumo kwefaira remavara, nokudaro tinowana chiziviso (id) chemutsara wekupedzisira wakabviswa zvinobudirira.
Ndakatanga kutora dump in text form:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpKuraswa, sezvaitarisirwa, kwakavhiringwa nechikanganiso chimwe chete:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
Kuwedzera kuburikidza muswe Ndakatarisa kumagumo ekurasirwa (muswe -5 ./my_dump.dump) yakaona kuti kuraswa kwakakanganiswa pamutsetse une id 186 525. "Saka dambudziko riri mumutsara une id 186 526, yakatyoka, uye inoda kudzimwa!" - Ndaifunga. Asi, kuita mubvunzo kune database:
«sarudza * kubva ws_log_smevlog uko id=186529"Zvakaitika kuti zvinhu zvose zvakanga zvakanaka nemutsara uyu ... Mitsetse ine indices 186 - 530 yakashandawo pasina matambudziko. Imwe “pfungwa yakanaka” yakakundikana. Gare gare ndakanzwisisa kuti sei izvi zvaitika: pakudzima nekushandura data kubva patafura, ivo havadzimirwe mumuviri, asi vanoiswa se "vakafa tuples", vobva vauya. autovacuum uye inomaka mitsara iyi seyadzimwa uye inobvumira mitsetse iyi kuti ishandiswezve. Kuti unzwisise, kana iyo data iri patafura ichichinja uye autovacuum yakagoneswa, saka haina kuchengetwa sequentially.
Kuedza 5: SARUDZA, KUBVA, KUPI id=
Kukundikana kunotisimbisa. Iwe haufanirwe kukanda mapfumo pasi, unofanirwa kuenda kumagumo uye utende mauri uye nezvaunokwanisa. Saka ndakafunga kuyedza imwe sarudzo: ingo tarisa kuburikidza ese marekodhi mudhatabhesi rimwe nerimwe. Kuziva chimiro chetafura yangu (ona pamusoro), isu tine id ndima iyo yakasarudzika (kiyi yekutanga). Tine mitsara 1 mutafura uye id zviri muhurongwa, zvinoreva kuti tinogona kungopfuura nepakati pavo chimwe nechimwe:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneKana paine asinganzwisise, murairo unoshanda sezvizvi: inotarisa mutsara wetafura nemutsara uye inotumira stdout ku. / dev / null, asi kana iyo SELECT command ikatadza, ipapo iwo mameseji ekukanganisa anodhindwa (stderr inotumirwa kuconsole) uye mutsara une chikanganiso unodhindwa ( thanks to ||, zvinoreva kuti iyo yakasarudzwa yaive nematambudziko (iyo kodhi yekudzoka yekuraira. haisi 0)).
Ndakaita rombo rakanaka, ndaive nemaindex akagadzirwa kumunda id:
Izvi zvinoreva kuti kutsvaga mutsara une id yaunoda hakufanire kutora nguva yakawanda. Mukutaura kunofanira kushanda. Zvakanaka, ngatimhanyei murairo mukati tmux handei tinorara.
Mangwanani ndakaona kuti vanenge 90 vapinda vakanga vaonekwa, izvo zvinongopfuura 000%. Mhedzisiro yakanaka kana ichienzaniswa neyekare nzira (5%)! Asi handina kuda kumirira mazuva makumi maviri...
Kuedza 6: SARUDZA, KUBVA, PANE id > = uye id
Mutengi aive neyakanakisa server yakatsaurirwa kune dhatabhesi: mbiri-processor Intel Xeon E5-2697 v2, paiva neshinda dzinosvika 48 panzvimbo yedu! Kuremerwa pasevha yaive pakati; taigona kudhawunirodha dzinenge 20 shinda pasina matambudziko. Paive zvakare ne RAM yakakwana: yakawanda se384 gigabytes!
Naizvozvo, murairo waifanira kufananidzwa:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; donePano zvaigoneka kunyora chinyorwa chakanaka uye chinoyevedza, asi ndakasarudza nzira inokurumidza kuenzanisa: nemaoko patsanura huwandu 0-1628991 muzvikamu zve100 rekodhi uye kumhanya zvakasiyana mirairo gumi nematanhatu yefomu:
for ((i=N; i<M; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneAsi handizvo zvoga. Mune dzidziso, kubatana kune dhatabhesi zvakare kunotora imwe nguva uye system zviwanikwa. Kubatanidza 1 kwanga kusiri kungwara, unobvuma. Naizvozvo, ngatitorei mitsara 628 pane imwe pane imwe yekubatanidza. Nekuda kweizvozvo, timu yakashandurwa kuita iyi:
for ((i=N; i<M; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; doneVhura 16 windows mumusangano wetmux uye mhanyisa mirairo:
1) for ((i=0; i<100000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 2) for ((i=100000; i<200000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done … 15) for ((i=1400000; i<1500000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 16) for ((i=1500000; i<1628991; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done
Kwapera zuva ndakatambira maresults ekutanga! Kureva (makoshero XXX neZZZ haasisina kuchengetedzwa):
ERROR: missing chunk number 0 for toast value 37837571 in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value XXX in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value ZZZ in pg_toast_106070
146000Izvi zvinoreva kuti mitsetse mitatu ine kukanganisa. Iwo maID ekutanga uye echipiri dambudziko rekodhi aive pakati pe829 ne000, maID echitatu aive pakati pe830 ne000. Tevere, isu taingofanira kuwana chaiyo id kukosha kweiyo dambudziko rekodhi. Kuti tiite izvi, tinotarisa kuburikidza nehuwandu hwedu nemarekodhi ane dambudziko ane nhanho ye146 uye toona id:
for ((i=829000; i<830000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number 2 (expected 0) for toast value 37837843 in pg_toast_106070 829449 for ((i=146000; i<147000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number ZZZ (expected 0) for toast value XXX in pg_toast_106070 146911
Kupera kunofadza
Takawana mitsara ine dambudziko. Isu tinopinda mudhatabhesi kuburikidza ne psql uye edza kuvadzima:
my_database=# delete from ws_log_smevlog where id=829417;
DELETE 1
my_database=# delete from ws_log_smevlog where id=829449;
DELETE 1
my_database=# delete from ws_log_smevlog where id=146911;
DELETE 1Chakandishamisa ndechekuti zvinyorwa zvakadzimwa pasina kana dambudziko kunyangwe pasina sarudzo zero_damaged_pages.
Ipapo ndakabatanidza kune database, ndakaita VACUUM FULL (Ini ndinofunga zvaive zvisina basa kuita izvi), uye pakupedzisira ndakabudirira kubvisa backup ndichishandisa pg_dump. Kuraswa kwakatorwa pasina kana kukanganisa! Dambudziko rakagadziriswa nenzira yehupenzi. Mufaro wacho waisaziva magumo, mushure mekukundikana kwakawanda takakwanisa kuwana mhinduro!
Kutenda uye Mhedziso
Aya ndiwo maitiro angu ekutanga ruzivo rwekudzoreredza chaiyo Postgres dhatabhesi. Ndicharangarira chiitiko ichi kwenguva refu.
Uye pakupedzisira, ndinoda kuti mazvita kuna PostgresPro nekuturikira zvinyorwa muRussia uye zve , iyo yakabatsira zvikuru panguva yekuongorora dambudziko.
Source: www.habr.com