E hiahia ana ahau ki te whakapuaki ki a koe taku wheako angitu tuatahi mo te whakahoki mai i te putunga raraunga Postgres ki te mahi katoa. I mohio ahau ki te Postgres DBMS i te haurua o te tau ki muri; i mua i tera kaore au i mohio ki te whakahaerenga raraunga.
Kei te mahi ahau hei miihini ahua-DevOps i tetahi kamupene IT nui. Ka whakawhanakehia e to taatau kamupene he rorohiko mo nga ratonga kawenga nui, a ko ahau te kawenga mo te mahi, te tiaki me te tuku. I whakawhiwhia ahau ki tetahi mahi paerewa: ki te whakahou i tetahi tono i runga i te tūmau kotahi. Ka tuhia te tono ki Django, i te wa e mahia ana nga hekenga whakahou (nga huringa i roto i te hanganga raraunga), a, i mua i tenei tukanga ka tangohia e matou he putunga putunga raraunga katoa na roto i te kaupapa pg_dump paerewa, mo te keehi.
I puta he hapa ohorere i te wa e tango ana i te putunga (Postgres putanga 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
hapa "Wharangi muhu i te paraka" e korero ana mo nga raruraru i te taumata o te punaha konae, he tino kino. I runga i nga momo huinga i whakaarohia kia mahia KAUPAPA KATOA me te kōwhiringa nga wharangi_kore hei whakaoti i tenei raruraru. Na, me whakamatau ...
Te whakarite mo te whakaora
WHAKATOKANGA! Kia mahara ki te tango i tetahi taapiri Postgres i mua i te ngana ki te whakaora i to papaunga raraunga. Mēnā he mihini mariko koe, kātihia te pātengi raraunga ka tango whakaahua. Ki te kore e taea te tango whakaahua, whakamutua te paataka raraunga ka kape i nga ihirangi o te whaiaronga Postgres (tae atu ki nga konae wal) ki te waahi haumaru. Ko te mea nui i roto i ta maatau pakihi ko te kore e kino nga mea. Panui .
I te nuinga o te wa e mahi ana te patengi raraunga ki ahau, i whakawhäitihia e au ki te putunga putunga raraunga, engari ka whakakorehia te ripanga me nga raraunga pakaru (kowhiringa -T, --exclude-table=TAPA i roto i te pg_dump).
He tinana te tūmau, kaore e taea te tango whakaahua. Kua tangohia te taapiri, me haere tonu.
Taki pūnaha kōnae
I mua i te ngana ki te whakaora i te paataka korero, me whakarite kia pai nga mea katoa me te punaha konae ake. A, ki te he, whakatikahia, na te mea ka taea e koe te kino ake.
I roto i taku keehi, i whakauruhia te punaha konae me te papaa raraunga "/srv" a ko te momo ext4.
Whakamutua te pātengi raraunga: mutu systemctl [email tiakina] me te tirotiro ko te punaha konae kaore i te whakamahia e tetahi ka taea te tango ma te whakamahi i te whakahau Tuhinga o mua:
lsof +D /srv
Me whakamutu ano e au te redis database, na te mea kei te whakamahi ano "/srv". I muri mai ka wetekina e ahau / srv (umount).
I takina te punaha konae ma te whakamahi i te taputapu e2fsck me te whakakā -f (Me kaha te tirotiro ahakoa kua tohua te punaha konae he ma):
I muri mai, ma te whakamahi i te taputapu tuku2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep tirohia) ka taea e koe te manatoko i mahia te haki:
e2fsck e kii ana karekau he raru i kitea i te taumata o te punaha konae ext4, ko te tikanga ka taea e koe te ngana tonu ki te whakaora i te putunga raraunga, me hoki ki te ki tonu te korehau (o te akoranga, me whakairi koe i te punaha konae ki muri ka tiimata te papaa raraunga).
Mēnā he tūmau ā-tinana koe, tirohia te tūnga o ngā kōpae (mā smartctl -a /dev/XXX) ranei te kaiwhakahaere RAID kia mohio kaore te raruraru i te taumata taputapu. I roto i taku keehi, ko te RAID he "pūmārō", no reira ka tono ahau ki te kaiwhakahaere o te rohe ki te tirotiro i te mana o te RAID (he maha nga rau kiromita te tawhiti atu i ahau te tūmau). I kii ia kaore he hapa, ko te tikanga ka taea e taatau te timata ki te whakaora.
Nganatanga 1: zero_damaged_pages
Ka hono atu matou ki te patengi raraunga ma te psql me tetahi kaute kei a ia nga mana kaiwhakamahi super. Kei te hiahia matou ki tetahi superuser, na te mea ... kōwhiringa nga wharangi_kore ka taea e ia anake te huri. I roto i taku take he postgres:
psql -h 127.0.0.1 -U postgres -s [ingoa_papa raraunga]
Kōwhiringa nga wharangi_kore e hiahiatia ana kia kore e aro ki nga hapa panui (mai i te paetukutuku postgrespro):
Ina kite a PostgreSQL i tetahi pane wharangi kino, ka whakaatu he hapa me te whakakore i te tauwhitinga o naianei. Mena ka whakahohea te zero_damaged_pages, ka tukuna e te punaha he whakatupato, ka whakakorea te wharangi pakaru i roto i te mahara, ka haere tonu te mahi. Ko tenei whanonga ka whakangaro i nga raraunga, ara nga rarangi katoa o te wharangi kua pakaru.
Ka taea e matou te kowhiringa ka ngana ki te mahi i te korehau katoa o nga ripanga:
VACUUM FULL VERBOSE
Kia aroha mai, he waimarie kino.
He rite tonu te hapa:
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– he tikanga mo te penapena “raraunga roa” ki Poetgres mena karekau e uru ki te wharangi kotahi (8kb taunoa).
Nganatanga 2: taupae hou
Ko nga tohutohu tuatahi mai i a Google kaore i awhina. I muri i etahi meneti o te rapu, ka kitea e au te tohu tuarua - kia mahia reindex tepu pakaru. I kite ahau i tenei tohutohu i nga waahi maha, engari kaore i whakahihiri i te maia. Kia taurangi ano tatou:
reindex table ws_log_smevlog
reindex ka oti kaore he raruraru.
Heoi, kaore tenei i awhina, KAUPAPA KIA tuki ki te hapa rite. I te mea kua waia ahau ki nga rahunga, ka tiimata ahau ki te rapu tohutohu i runga i te Ipurangi ka kitea e au tetahi mea tino pai .
Nganatanga 3: TIKA, TETEA, TE WHAKAMAHI
Ko te tuhinga i runga ake nei i whakaaro te titiro ki te rarangi rarangi ma ia rarangi me te tango i nga raraunga raru. Tuatahi me titiro tatou ki nga rarangi katoa:
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; doneI roto i taku take, kei roto te tepu 1 628 991 raina! I tika ki te tiaki pai , engari he kaupapa tenei mo te korerorero motuhake. Ko te Hatarei, i whakahaere ahau i tenei whakahau i roto i te tmux ka moe ahau:
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; doneI te ata ka whakatau ahau ki te tirotiro i te haere o nga mea. Ki taku ohorere, i kitea e au i muri i te 20 haora, 2% noa iho o nga raraunga kua karapahia! Kaore au i pai ki te tatari mo te 50 ra. Ko tetahi atu rahunga katoa.
Engari kaore au i tuku. I miharo ahau he aha i roa ai te karapa. Mai i nga tuhinga (i runga ano i te postgrespro) I kitea e au:
OFFSET ka tohua kia pekehia te maha o nga rarangi i mua i te tiimata ki te whakaputa rarangi.
Mena ka tohua te OFFSET me te LIMIT, ka pekehia e te punaha nga rarangi OFFSET ka timata ki te tatau i nga rarangi mo te herenga LIMIT.I te wa e whakamahi ana i te LIMIT, he mea nui ki te whakamahi i te ORDER BY rara kia whakahokia mai nga rarangi hua ki roto i te raupapa motuhake. Ki te kore, ka whakahokia mai nga huinga haupae ohorere.
Ko te tikanga, he he te whakahau i runga ake nei: tuatahi, karekau ota mā, he pohehe pea te hua. Tuarua, ko Postgres te tuatahi ki te matawai me te peke i nga rarangi OFFSET, me te piki haere OFFSET ka heke haere te hua.
Nganatanga 4: tango i te putunga i roto i te puka kuputuhi
Katahi ka puta mai he whakaaro maramara ki toku hinengaro: tango i te putunga i roto i te puka tuhinga ka tātarihia te rarangi rekoata whakamutunga.
Engari ko te tuatahi, me titiro ki te hanganga o te tepu. ws_log_smevlog:
I roto i to maatau he pou ta maatau "Id", kei roto ko te tautohu ahurei (counter) o te haupae. I penei te mahere:
- Ka timata tatou ki te tango i te putunga i roto i te puka tuhinga (i te ahua o nga whakahau sql)
- I tetahi wa, ka haukotia te putunga na te hapa, engari ka tiakina tonu te konae tuhinga ki runga kōpae
- Ka titiro tatou ki te mutunga o te konae tuhinga, na reira ka kitea te tohu (id) o te rarangi whakamutunga i tangohia paitia
I timata ahau ki te tango i tetahi putunga i roto i te puka kuputuhi:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpKo te putunga, pera i te mea i tumanakohia, i haukotia me te hapa ano:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
I tua atu hiku I titiro ahau ki te pito o te putunga (hiku -5 ./my_dump.dump) i kitea kua haukotia te putunga i runga i te raina me te id 186 525. "Na ko te raru kei roto i te raina me te id 186 526, kua pakaru, me whakakorea!" – I whakaaro ahau. Engari, he patai ki te paataka raraunga:
«tīpako * mai i ws_log_smevlog kei hea id=186529"I puta mai he pai nga mea katoa me tenei raina... Ko nga rarangi me nga tohu tohu 186 - 530 i mahi me te kore raru. Ko tetahi atu "whakaaro marama" i rahua. I muri mai ka mohio ahau he aha tenei i tupu ai: ka mukua me te whakarereke i nga raraunga mai i te ripanga, kaore i te whakakorehia a tinana, engari ka tohua hei "tuples mate", katahi ka haere mai. mokohau aunoa me te tohu i enei rarangi kua mukua ka whakaaetia kia whakamahia ano enei rarangi. Kia mohio ai, mena ka huri nga raraunga o te ripanga ka whakahoehia te autovacuum, karekau e penapena raupapa.
Nganatanga 5: SELECT, FROM, WHERE id=
Ko nga ngoikoretanga ka kaha ake tatou. Kaua rawa koe e tuku, me haere koe ki te mutunga me te whakapono ki a koe ano me o kaha. Na ka whakatau ahau ki te whakamatau i tetahi atu whiringa: tirohia noa nga rekoata katoa i roto i te paataka raraunga takitahi. Ma te mohio ki te hanganga o taku ripanga (tirohia i runga ake nei), he mara id to tatou he mea ahurei (matua matua). E 1 nga rarangi kei te tepu me te id kei te raupapa, ko te tikanga ka taea e taatau te haere takitahi:
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; doneKi te kore tetahi e marama, ka penei te mahi o te whakahau: ka matawai i te rarangi ripanga ma ia rarangi ka tukuna te stdout ki / dev / null, engari ki te rahua te whakahau SELECT, ka taia te tuhinga hapa (ka tukuna te stderr ki te papatohu) ka taia he raina kei roto te hapa (he mihi ki ||, ko te tikanga he raruraru te hunga whiriwhiri (ko te waehere whakahoki o te whakahau ehara i te 0)).
I waimarie ahau, i hangahia e au nga tohu tohu i runga i te mara id:
Ko te tikanga ko te rapu raina me te id e hiahiatia ana kia kaua e nui te wa. I roto i te ariā me mahi. Ana, me whakahaere te whakahau ki roto tmux a ka haere tatou ki te moenga.
I te ata ka kitea e 90 nga whakaurunga kua tirohia, he 000% noa iho. He hua pai ina whakaritea ki te tikanga o mua (5%)! Engari kaore au i pai ki te tatari mo nga ra 2 ...
Nganatanga 6: KŌWHIWHI, MAI, WHERE id >= and id
He tūmau pai te kaihoko i whakatapua ki te pātengi raraunga: pūtukatuka-rua Intel Xeon E5-2697 v2, e 48 nga miro i to matou waahi! He toharite te kawenga i runga i te tūmau; ka taea e matou te tango i te 20 nga miro kaore he raru. He nui hoki te RAM: tae atu ki te 384 gigabytes!
No reira, me whakarara te whakahau:
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; doneI konei ka taea te tuhi i tetahi tuhinga ataahua me te huatau, engari i whiriwhiria e au te tikanga whakarara tere: wehea a ringa te awhe 0-1628991 ki nga waahi o nga rekoata 100 ka whakahaere wehe i nga whakahau 000 o te puka:
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; doneEngari ehara ko tera anake. I roto i te ariā, ko te hono atu ki te pātengi raraunga ka roa te wa me nga rauemi punaha. Ko te hono i te 1 kaore i tino mohio, ka whakaae koe. Na reira, me tiki 628 rarangi hei utu mo te hononga kotahi ki te kotahi. Ko te mutunga, ka huri te kapa ki tenei:
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; doneWhakatuwherahia nga matapihi 16 i roto i te huihuinga tmux ka whakahaere i nga whakahau:
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
He ra i muri mai ka whiwhi ahau i nga hua tuatahi! Ara (kaore e tiakina nga uara XXX me ZZZ):
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
146000Ko te tikanga e toru nga rarangi kei roto he hapa. Ko nga id o nga rekoata raruraru tuatahi me te tuarua kei waenganui i te 829 me te 000, ko nga id o te tuatoru kei waenganui i te 830 me te 000. I muri mai, me rapu noa i te uara id tika o nga rekoata raruraru. Ki te mahi i tenei, ka tirohia e matou to maatau awhe me nga rekoata raru me te taahiraa o te 146 me te tautuhi i te 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
Ka mutu te koa
I kitea e matou nga raina raruraru. Ka haere matou ki te papaaarangi ma te psql ka ngana ki te whakakore i a raatau:
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 1Ki taku ohorere, i whakakorehia nga whakaurunga kaore he raru ahakoa kaore he whiringa nga wharangi_kore.
Na ka hono ahau ki te pātengi raraunga, i KAUPAPA KIA (Ki taku whakaaro ehara i te mea tika ki te mahi i tenei), ka mutu kua pai taku tango i te taapiri ma te whakamahi pg_putunga. I tangohia te putunga kaore he hapa! I whakatauhia te raruraru i roto i te huarahi poauau. Ko te hari kaore he rohe, i muri i te maha o nga rahunga ka taea e matou te kimi otinga!
Nga Mihi me te Whakamutunga
Koinei te ahua o taku wheako tuatahi mo te whakahoki mai i te putunga korero a Postgres. Ka maumahara ahau ki tenei wheako mo te wa roa.
Ka mutu, ka mihi au ki a PostgresPro mo te whakamaori i nga tuhinga ki te reo Ruhia me te mo , i awhina nui i te wa o te tātaritanga o te raru.
Source: will.com