Ndikufuna kugawana nanu zomwe ndakumana nazo zopambana pakubwezeretsanso database ya Postgres kuti igwire ntchito bwino. Ndinadziwana ndi Postgres DBMS theka la chaka chapitacho; kale ndinalibe chidziwitso pa kayendetsedwe ka database konse.
Ndimagwira ntchito ngati injiniya wa semi-DevOps mukampani yayikulu ya IT. Kampani yathu imapanga mapulogalamu a ntchito zolemetsa kwambiri, ndipo ine ndimayang'anira magwiridwe antchito, kukonza ndi kutumiza. Ndinapatsidwa ntchito yokhazikika: kukonzanso pulogalamu pa seva imodzi. Ntchitoyi idalembedwa ku Django, panthawi yosinthira zosintha zimachitika (kusintha kwadongosolo la database), ndipo izi zisanachitike timachotsa zonse zotayika kudzera mu pulogalamu ya pg_dump, ngati zingachitike.
Cholakwika chosayembekezereka chinachitika potaya (Postgres version 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
cholakwa "tsamba losavomerezeka mu block" amalankhula za zovuta pamtundu wa fayilo, zomwe ndi zoyipa kwambiri. Pamabwalo osiyanasiyana adalangizidwa kuti achite VACUUM YONSE ndi mwayi masamba_ziro_zowonongeka kuthetsa vutoli. Chabwino, tiyeni tiyese ...
Kukonzekera kuchira
ZOCHITIKA! Onetsetsani kuti mwatenga zosunga zobwezeretsera za Postgres musanayese kubwezeretsanso database yanu. Ngati muli ndi makina enieni, imitsani database ndikutenga chithunzithunzi. Ngati sizingatheke kutenga chithunzithunzi, imitsani nkhokwe ndikujambula zomwe zili mu bukhu la Postgres (kuphatikizapo mafayilo a wal) kumalo otetezeka. Chinthu chachikulu mu bizinesi yathu si kupangitsa zinthu kuipiraipira. Werengani .
Popeza nkhokwe nthawi zambiri imandigwirira ntchito, ndidangodziyika ndekha kumalo osungiramo zinthu zakale, koma sindinaphatikizepo tebulo lomwe lili ndi data yowonongeka (njira -T, --exclude-table=TABLE mu pg_dump).
Sevayo inali yakuthupi, sikunali kotheka kutenga chithunzithunzi. Zosunga zobwezeretsera zachotsedwa, tiyeni tipitirire.
Kufufuza kwa fayilo
Tisanayambe kubwezeretsanso database, tiyenera kuonetsetsa kuti zonse zili bwino ndi fayilo yokhayo. Ndipo zikachitika zolakwika, ziwongolereni, chifukwa mwina mutha kupangitsa kuti zinthu ziipireipire.
Kwa ine, mawonekedwe a fayilo omwe ali ndi database adayikidwamo "/srv" ndipo mtunduwo unali ext4.
Kuyimitsa database: systemctl kuyimitsa [imelo ndiotetezedwa] ndipo fufuzani kuti mafayilo sakugwiritsidwa ntchito ndi aliyense ndipo akhoza kutsitsa pogwiritsa ntchito lamulo lsof:
lsof +D /srv
Ndinayeneranso kuyimitsa nkhokwe ya redis, popeza inalinso kugwiritsa ntchito "/srv". Kenako ndinatsika / srv (kukwera).
Dongosolo la fayilo lidawunikidwa pogwiritsa ntchito zofunikira e2f ndi kusintha -f (Limbikitsani kuyang'ana ngakhale mafayilo amafayilo alembedwa kuti ndi oyera):
Kuphatikiza apo, kugwiritsa ntchito dumbe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep kufufuzidwa) mutha kutsimikizira kuti chekecho chidachitikadi:
e2f akuti palibe mavuto omwe adapezeka pamtundu wa fayilo ya ext4, zomwe zikutanthauza kuti mutha kupitiliza kuyesa kubwezeretsanso database, kapena kubwerera ku vacuum yodzaza (zowona, muyenera kuyikanso fayilo ndikuyambitsanso database).
Ngati muli ndi seva yakuthupi, onetsetsani kuti mwawona momwe ma disks alili (kudzera smartctl -a /dev/XXX) kapena wolamulira wa RAID kuti atsimikizire kuti vutoli siliri pamlingo wa hardware. Kwa ine, RAID inakhala "hardware", kotero ndinapempha woyang'anira wakomweko kuti awone momwe RAID ilili (seva inali makilomita mazana angapo kutali ndi ine). Iye adanena kuti panalibe zolakwika, zomwe zikutanthauza kuti tikhoza kuyamba kubwezeretsa.
Yesani 1: zero_damaged_masamba
Timalumikizana ndi database kudzera pa psql ndi akaunti yomwe ili ndi ufulu wa superuser. Tikufuna superuser, chifukwa ... mwina masamba_ziro_zowonongeka iye yekha angasinthe. Kwa ine ndi postgres:
psql -h 127.0.0.1 -U postgres -s [database_name]
Yankho masamba_ziro_zowonongeka zofunikira kuti musanyalanyaze zolakwika zowerengera (kuchokera patsamba la postgrespro):
PostgreSQL ikazindikira mutu watsamba wachinyengo, nthawi zambiri imanena zolakwika ndikuchotsa zomwe zikuchitika. Ngati zero_damaged_pages yayatsidwa, makinawo m'malo mwake amapereka chenjezo, amachotsa tsamba lowonongeka pamtima, ndikupitiliza kukonza. Khalidweli limawononga deta, yomwe ndi mizere yonse patsamba lowonongeka.
Timatsegula mwayiwu ndikuyesera kuchotseratu magome:
VACUUM FULL VERBOSE
Tsoka ilo, tsoka.
Tidakumana ndi vuto lofananira:
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- njira yosungira "deta yayitali" mu Poetgres ngati siyikukwanira patsamba limodzi (8kb mwachisawawa).
Kuyesera 2: reindex
Malangizo oyamba ochokera ku Google sanathandize. Pambuyo pofufuza kwa mphindi zingapo, ndidapeza nsonga yachiwiri - kupanga reindex tebulo lowonongeka. Uphungu umenewu ndinauona m’malo ambiri, koma sunandilimbikitse. Tiyeni tibwerezenso:
reindex table ws_log_smevlog
reindex anamaliza popanda mavuto.
Komabe, izi sizinathandize, VACUUM YONSE idagwa ndi cholakwika chofanana. Popeza ndidazolowera zolephera, ndidayamba kuyang'ananso upangiri pa intaneti ndipo ndidapeza zosangalatsa .
Yesani 3: SINANI, LIMIT, OFFSET
Nkhani yomwe ili pamwambayi ikupereka lingaliro la kuyang'ana pa tebulo mzere ndi mzere ndikuchotsa zovuta. Choyamba tiyenera kuyang'ana mizere yonse:
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; doneKwa ine, tebulo ili 1 628 991 mizere! Zinali zofunika kuzisamalira bwino , koma uwu ndi mutu wa zokambirana zosiyana. Linali Loweruka, ndinayendetsa lamulo ili mu tmux ndikugona:
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; donePofika m’maŵa ndinaganiza zofufuza mmene zinthu zinalili. Ndinadabwitsidwa, ndidazindikira kuti patatha maola 20, 2% yokha ya data idasinthidwa! Sindinafune kudikirira masiku 50. Kulephera kwina kotheratu.
Koma sindinasiye. Ndinadabwa chifukwa chake kusanthula kunatenga nthawi yayitali. Kuchokera pazolembedwa (kachiwiri pa postgrespro) ndapeza:
OFFSET imanena kuti mudumpha mizere yotchulidwa musanayambe kutulutsa mizere.
Ngati zonse za OFFSET ndi LIMIT zatchulidwa, makinawo amayamba kudumpha mizere ya OFFSET kenako nkuyamba kuwerengera mizere ya LIMIT contraindication.Mukamagwiritsa ntchito LIMIT, ndikofunikanso kugwiritsa ntchito ORDER BY clause kuti mizere yotsatila ibwezedwe mwatsatanetsatane. Kupanda kutero, magawo amizere osayembekezereka abwezedwa.
Mwachiwonekere, lamulo ili pamwambali linali lolakwika: choyamba, panalibe dongosolo ndi, zotsatira zake zingakhale zolakwika. Kachiwiri, Postgres poyamba adayenera kusanthula ndikudumpha mizere ya OFFSET, ndikuwonjezeka OFFSET zokolola zidzacheperachepera.
Yesani 4: tsitsani m'malemba
Kenako lingaliro lowoneka ngati lanzeru lidabwera m'maganizo mwanga: tayani m'mawu ndikusanthula mzere womaliza wojambulidwa.
Koma choyamba, tiyeni tione kamangidwe ka tebulolo. ws_log_smevlog:
M'malo mwathu tili ndi gawo "Id", yomwe inali ndi chizindikiritso chapadera (chotsutsa) cha mzere. Plan inali motere:
- Timayamba kutayira m'malemba (monga mawonekedwe a sql)
- Panthawi ina, kutaya kungasokonezedwe chifukwa cha zolakwika, koma fayiloyo idzasungidwabe pa disk.
- Timayang'ana kumapeto kwa fayilo, potero timapeza chizindikiritso (id) cha mzere womaliza womwe unachotsedwa bwino.
Ndidayamba kuponya m'malemba:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpKutaya, monga momwe amayembekezeredwa, kudasokonezedwa ndi cholakwika chomwechi:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
Kupitilira mchira Ndinayang'ana kumapeto kwa dambo (mchira -5 ./my_dump.dump) adapeza kuti kutayako kudasokonekera pamzere wokhala ndi id 186 525. "Ndiye vuto lili pamzere wa id 186 526, yasweka, ndipo ikufunika kuchotsedwa!" - Ndinaganiza. Koma, kupanga funso ku database:
«sankhani * kuchokera ku ws_log_smevlog pomwe id=186529"Zinapezeka kuti zonse zinali bwino ndi mzerewu ... Mizere yokhala ndi ma indices 186 - 530 inagwiranso ntchito popanda mavuto. “Lingaliro lanzeru” lina linalephera. Pambuyo pake ndinamvetsetsa chifukwa chake izi zidachitika: pochotsa ndikusintha deta patebulo, samachotsedwa mwakuthupi, koma amalembedwa ngati "tuples akufa", kenako amabwera. autovacuum ndikuyika mizere iyi ngati yachotsedwa ndikulola mizere iyi kugwiritsidwanso ntchito. Kuti mumvetsetse, ngati zomwe zili patebulo zikusintha ndipo autovacuum imayatsidwa, ndiye kuti sizisungidwa motsatizana.
Kuyesera 5: SANKANI, KUCHOKERA, KUTI id=
Zolephera zimatipangitsa kukhala amphamvu. Musataye mtima, muyenera kupita kumapeto ndikudzikhulupirira nokha ndi kuthekera kwanu. Chifukwa chake ndidaganiza zoyesa njira ina: ingoyang'ana zolemba zonse zomwe zili munkhokwe imodzi ndi imodzi. Podziwa kapangidwe ka tebulo langa (onani pamwambapa), tili ndi gawo la id lomwe ndi lapadera (kiyi choyambirira). Tili ndi mizere 1 patebulo ndi id zili mu dongosolo, zomwe zikutanthauza kuti tikhoza kungodutsamo imodzi ndi imodzi:
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; doneNgati wina sakumvetsa, lamuloli limagwira ntchito motere: limayang'ana mzere wa tebulo ndi mzere ndikutumiza stdout kwa / dev / null, koma ngati lamulo la SELECT likulephera, ndiye kuti malemba olakwika amasindikizidwa (stderr imatumizidwa ku console) ndipo mzere womwe uli ndi zolakwika umasindikizidwa (zikomo kwa ||, zomwe zikutanthauza kuti wosankhidwayo anali ndi mavuto (code yobwereza ya lamulo ayi 0)).
Ndinali ndi mwayi, ndinali ndi ma index omwe adapangidwa pamunda id:
Izi zikutanthauza kuti kupeza mzere wokhala ndi id yomwe mukufuna sikuyenera kutenga nthawi. Mwachidziwitso ziyenera kugwira ntchito. Chabwino, tiyeni tiyendetse lamulo mkati tmux ndipo tiyeni tikagone.
Pofika m'mawa ndidapeza kuti zolembedwa pafupifupi 90 zidawonedwa, zomwe zangopitilira 000%. Zotsatira zabwino kwambiri poyerekeza ndi njira yapitayi (5%)! Koma sindinafune kudikirira masiku 2 ...
Yesani 6: SINANI, KUCHOKERA, KUTI id > = ndi id
Makasitomala anali ndi seva yabwino kwambiri yoperekedwa ku database: dual-processor Intel Xeon E5-2697 v2, panali ulusi wokwana 48 pamalo athu! Katundu pa seva anali wapakati; titha kutsitsa pafupifupi ulusi 20 popanda vuto lililonse. Panalinso RAM yokwanira: mpaka 384 gigabytes!
Chifukwa chake, lamuloli liyenera kufananizidwa:
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; doneApa zinali zotheka kulemba cholembera chokongola komanso chokongola, koma ndidasankha njira yofulumira kwambiri yofananira: kugawanitsa pamanja 0-1628991 m'migawo ya ma 100 ndikuyendetsa padera malamulo 000 a mawonekedwe:
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; doneKoma si zokhazo. Mwachidziwitso, kulumikiza ku database kumatenganso nthawi komanso zida zamakina. Kulumikiza 1 sikunali kwanzeru kwambiri, mungavomereze. Chifukwa chake, tiyeni titenge mizere 628 m'malo mwa kulumikizana kumodzi. Chifukwa chake, gululi lidasintha motere:
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; doneTsegulani mawindo a 16 mu gawo la tmux ndikuyendetsa malamulo:
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
Patatha tsiku ndinalandira zotsatira zoyamba! Izi (zofunika XXX ndi ZZZ sizikusungidwanso):
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
146000Izi zikutanthauza kuti mizere itatu ili ndi zolakwika. Ma ID a zolemba zovuta zoyamba ndi zachiwiri anali pakati pa 829 ndi 000, ma ID achitatu anali pakati pa 830 ndi 000. Kuti tichite izi, timayang'ana mndandanda wathu ndi zolemba zovuta ndi sitepe ya 146 ndikuzindikira 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
Mapeto osangalatsa
Tinapeza mizere yovuta. Timapita ku database kudzera pa psql ndikuyesera kuwachotsa:
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 1Ndinadabwa, zolembazo zinachotsedwa popanda vuto ngakhale popanda mwayi masamba_ziro_zowonongeka.
Kenako ndidalumikiza ku database, ndidatero VACUUM YONSE (Ndikuganiza kuti sikunali kofunikira kuchita izi), ndipo pomaliza ndidachotsa bwino zosunga zobwezeretsera pg_kuta. Kutayako kudatengedwa popanda cholakwika chilichonse! Vutoli linathetsedwa mopusa. Chisangalalocho chinalibe malire, pambuyo pa zolephera zambiri tinatha kupeza yankho!
Kuyamikira ndi Kumaliza
Umu ndi momwe chondichitikira changa choyamba chobwezeretsanso nkhokwe yeniyeni ya Postgres zidakhalira. Ndidzakumbukira chochitikachi kwa nthawi yaitali.
Ndipo potsiriza, ndikufuna kunena zikomo kwa PostgresPro chifukwa chomasulira zolembazo mu Chirasha komanso , zomwe zinathandiza kwambiri pofufuza vutoli.
Source: www.habr.com