Makemake au e kaʻana like me ʻoukou i kaʻu ʻike mua i kūleʻa i ka hoʻihoʻi ʻana i kahi waihona ʻikepili Postgres i ka hana piha. Ua ʻike mua au iā Postgres i ʻeono mahina i hala; ma mua o kēlā, ʻaʻohe oʻu ʻike i ka hoʻokele ʻana i nā waihona ʻikepili.
Hana wau ma ke ʻano he ʻenekinia semi-DevOps ma kahi ʻoihana IT nui. Hoʻomohala kā mākou ʻoihana i nā polokalamu no nā lawelawe ukana kiʻekiʻe, a naʻu ke kuleana no ka hilinaʻi hana, ka mālama ʻana, a me ka hoʻolaha ʻana. Ua hāʻawi ʻia mai iaʻu kahi hana maʻamau: ka hoʻohou ʻana i kahi noi ma kahi kikowaena hoʻokahi. Ua kākau ʻia ka noi ma Django, a hana ʻia nā neʻe ʻana (nā hoʻololi i ke ʻano o ka waihona ʻikepili) i ka wā o ka hoʻohou ʻana. Ma mua o kēia kaʻina hana, hana mākou i kahi dump waihona ʻikepili piha me ka hoʻohana ʻana i ka polokalamu pg_dump maʻamau, inā paha.
Ua loaʻa kahi hewa i manaʻo ʻole ʻia i ka wā e hoʻolei ana (Postgres mana 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 kikowaena "ʻAoʻao kūpono ʻole i loko o ka poloka" hōʻike i nā pilikia ma ka pae ʻōnaehana faila, he mea maikaʻi ʻole loa ia. Ua manaʻo nā ʻaha kūkā like ʻole e hana Hoʻomaʻemaʻe piha me ke koho nā ʻaoʻao_i_hōʻino_ʻole e hoʻoponopono i kēia pilikia. ʻAe, poprrobeum...
Ke hoʻomākaukau nei no ka ho'ōla ʻana
NĀHUI! E hōʻoia e kākoʻo i kāu hoʻonohonoho ʻana o Postgres ma mua o ka hoʻāʻo ʻana e hoʻihoʻi i kāu waihona ʻikepili. Inā ʻoe e hoʻohana ana i kahi mīkini virtual, e hoʻōki i ka waihona ʻikepili a lawe i kahi kiʻi paʻi. Inā ʻaʻole hiki iā ʻoe ke lawe i kahi kiʻi paʻi, e hoʻōki i ka waihona ʻikepili a kope i nā ʻike o ka papa kuhikuhi Postgres (me nā faila .wal) i kahi palekana. ʻO ka mea nui loa, ʻo ia ka pale ʻana i ka hoʻonui ʻana i nā mea. E heluhelu hou. .
ʻOiai ke hana nei kaʻu waihona ʻikepili ma ke ʻano laulā, ua kaupalena wau iaʻu iho i kahi waihona ʻikepili maʻamau, akā ua kāpae i ka papa me ka ʻikepili i hōʻino ʻia (koho -T, --exclude-table=PAPA ma loko o pg_dump).
He kino ke kikowaena, no laila ʻaʻole hiki ke kiʻi i kahi kiʻi. Ua paʻa ke kākoʻo, e neʻe kākou.
Ke nānā nei i ka ʻōnaehana faila
Ma mua o ka hoʻāʻo ʻana e hoʻihoʻi i ka waihona ʻikepili, pono mākou e hōʻoia i ka holo pono ʻana o ka ʻōnaehana faila ponoʻī. A inā he mau hewa, pono mākou e hoʻoponopono iā lākou, no ka mea, inā ʻaʻole, hiki iā mākou ke hoʻonui wale i ka ʻino o nā mea.
I koʻu hihia, ua kau ʻia ka ʻōnaehana faila me ka waihona ʻikepili i loko "/srv" a ʻo ke ʻano he ext4.
E hoʻōki i ka waihona ʻikepili: e ho'ōki i ka postgresql@9.5-main.service systemctl a nānā mākou ʻaʻole hoʻohana ʻia ka ʻōnaehana faila e kekahi a hiki ke wehe ʻia me ka hoʻohana ʻana i ke kauoha lsof:
lsof +D /srv
Pono nō hoʻi au e hoʻōki i ka waihona ʻikepili redis no ka mea ke hoʻohana nei hoʻi ia "/srv"A laila wehe au iā ia. /srv (nui).
Ua hana ʻia ka nānā ʻana o ka ʻōnaehana faila me ka hoʻohana ʻana i ka pono hana e2fsck me ke kī -f (E koi i ka nānā ʻana ʻoiai inā ua hōʻailona ʻia ka ʻōnaehana faila he maʻemaʻe):
ʻO ka mea aʻe, me ka hoʻohana ʻana i ka mea hana dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep i nānā ʻia) hiki iā ʻoe ke hōʻoia ua hana maoli ʻia ka nānā ʻana:
e2fsck ʻōlelo ʻia ʻaʻohe pilikia i loaʻa ma ka pae ʻōnaehana faila ext4, ʻo ia hoʻi hiki iā ʻoe ke hoʻomau i ka hoʻāʻo ʻana e hoʻihoʻi i ka waihona ʻikepili, a ʻoi aku ka pololei, e hoʻi i hoʻopiha piha i ka mīkini hoʻomaʻemaʻe (ʻoiaʻiʻo, pono ʻoe e hoʻouka i ka ʻōnaehana faila a hoʻomaka i ka waihona ʻikepili).
Inā he kikowaena kino kāu, e nānā pono i ke kūlana o nā disks (ma o ʻo smartctl -a /dev/XXX) a i ʻole ka mea hoʻokele RAID e hōʻoia ʻaʻole pili ka pilikia i ka lako. I koʻu hihia, ua ʻike ʻia ua pili ka RAID i ka lako, no laila ua noi au i ka luna hoʻomalu kūloko e nānā i ke kūlana RAID (aia ke kikowaena he mau haneli kilomita ka mamao). Ua ʻōlelo ʻo ia ʻaʻohe hewa, ʻo ia hoʻi hiki iā mākou ke hoʻomaka i ka hoʻōla.
Hoʻāʻo 1: zero_damaged_pages
E hoʻopili i ka waihona ʻikepili ma o psql me ka hoʻohana ʻana i kahi moʻokāki me nā kuleana superuser. Pono mākou i kahi superuser no ka mea ʻo ke koho nā ʻaoʻao_i_hōʻino_ʻole ʻO ia wale nō ke hiki ke hoʻololi. I koʻu hihia, ʻo ia ka postgres:
psql -h 127.0.0.1 -U postgres -s [inoa_ʻikepili]
Koho nā ʻaoʻao_i_hōʻino_ʻole pono e hoʻowahāwahā i nā hewa heluhelu (mai ka pūnaewele postgrespro):
Ke ʻike ʻia kahi poʻomanaʻo ʻaoʻao i hōʻino ʻia, hōʻike pinepine ʻo PostgreSQL i kahi hewa a hoʻōki i ka hana o kēia manawa. Inā hoʻā ʻia ka palena zero_damaged_pages, hoʻopuka ka ʻōnaehana i kahi ʻōlelo aʻo, hoʻopau i ka ʻaoʻao i hōʻino ʻia, a hoʻomau i ka hana ʻana. Hoʻopōʻino kēia ʻano i ka ʻikepili, ʻo ia hoʻi nā lālani āpau ma ka ʻaoʻao i hōʻino ʻia.
Hoʻā mākou i ke koho a hoʻāʻo e hana i kahi vacuum piha o ka papaʻaina:
VACUUM FULL VERBOSE 
ʻO ka mea pōʻino, ka hāʻule ʻana.
Ua loaʻa iā mākou kahi hewa like:
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 ʻano hana no ka mālama ʻana i ka “ʻikepili lōʻihi” ma Poetgres inā ʻaʻole lākou e kūpono ma kahi ʻaoʻao hoʻokahi (8kb paʻamau).
Hoʻāʻo 2: kuhikuhi hou
ʻAʻole i kōkua ka ʻōlelo aʻoaʻo mua mai Google mai. Ma hope o kekahi mau minuke o ka ʻimi ʻana, ua loaʻa iaʻu ka ʻōlelo aʻoaʻo ʻelua: kuhikuhi hou Papa ʻaina i hōʻino ʻia. Ua ʻike au i kēia ʻōlelo aʻoaʻo ma nā wahi he nui, akā ʻaʻole ia i hoʻoulu i ka hilinaʻi. E hana kākou i kahi reindex:
reindex table ws_log_smevlog 
kuhikuhi hou i hoʻopau ʻia me ka ʻole o nā pilikia.
Eia naʻe, ʻaʻole i kōkua, PAHA PAHA ua hāʻule me kahi hewa like. No ka mea ua maʻa wau i ka hāʻule ʻana, ua hoʻomau wau i ka ʻimi ʻana i nā ʻōlelo aʻoaʻo ma ka pūnaewele a ua loaʻa iaʻu kahi mea hoihoi loa .
Hoʻāʻo 3: KOHO, PALENA, OFFSET
Ua manaʻo ʻia ma ka ʻatikala ma luna e nānā hou i ka papa ma ka lālani a holoi i ka ʻikepili pilikia. ʻO ka mea mua, pono e nānā hou i nā lālani āpau:
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 koʻu hihia, aia ka papa i loko 1 628 991 nā laina! He mea pono e mālama i , akā he kumuhana kēlā no kekahi kūkākūkā ʻē aʻe. He Pōʻaono ia, ua holo au i kēia kauoha ma tmux a moe ihola:
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 ke kakahiaka, ua hoʻoholo wau e nānā i ke ʻano o nā mea. I koʻu kahaha, ua ʻike au ma hope o 20 mau hola, ʻo 2% wale nō o ka ʻikepili i scan ʻia! ʻAʻole au makemake e kali i 50 mau lā. ʻO kekahi hemahema piha.
Akā, ʻaʻole au i haʻalele. Ua noʻonoʻo au no ke aha i lōʻihi ai ka nānā ʻana. Mai ka palapala (ma postgrespro), ua aʻo au:
Hōʻike ʻo OFFSET iā ia e lele i ka helu i kuhikuhi ʻia o nā lālani ma mua o ka hoʻomaka ʻana e hoʻopuka i nā lālani.
Inā ua kuhikuhi ʻia ʻo OFFSET a me LIMIT, lele mua ka ʻōnaehana i nā lālani OFFSET a laila hoʻomaka e helu i nā lālani no LIMIT.I ka hoʻohana ʻana iā LIMIT, he mea nui hoʻi e hoʻohana i ka paukū ORDER BY e hōʻoia i ka hoʻihoʻi ʻia ʻana o nā lālani hopena ma kahi kauoha kikoʻī. A i ʻole, e hoʻihoʻi ʻia nā ʻāpana lālani i manaʻo ʻole ʻia.
Ua maopopo he hewa ke kauoha ma luna: ʻo ka mea mua, ʻaʻohe kauoha ʻia e, hiki ke hewa ka hopena. ʻO ka lua, pono mua ʻo Postgres e scan a hōʻalo i nā lālani OFFSET, a me ka hoʻonui ʻana KAHELE e emi hou aku ka huahana.
Hoʻāʻo 4: Hopu i kahi palapala hoʻoili
A laila hiki mai kahi manaʻo akamai loa iaʻu: e lawe i kahi hoʻolei i ke ʻano kikokikona a kālailai i ka laina hope loa i hoʻopaʻa ʻia.
Akā ʻo ka mea mua, e hoʻomaʻamaʻa kākou i ke ʻano o ka papaʻaina. ws_log_smevlog:
I ko mākou hihia, loaʻa iā mākou kahi kolamu "Id", nona kahi mea hōʻike kū hoʻokahi (counter) no ka lālani. Penei ka hoʻolālā:
- Hoʻomaka mākou e hoʻolei i ka ʻikepili ma ke ʻano kikokikona (ma ke ʻano o nā kauoha SQL)
- I kekahi manawa, e hoʻopau ʻia ka dump ma muli o kahi hewa, akā e mālama ʻia nō ka faila kikokikona ma ka disk.
- Nānā mākou i ka hopena o ka faila kikokikona, no laila ke ʻike nei mākou i ka mea hōʻike (id) o ka laina hope loa i wehe pono ʻia
Ua hoʻomaka wau e hoʻolei i ke ʻano kikokikona:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpʻO ka dump, e like me ka mea i manaʻo ʻia, ua hāʻule me ka hewa like:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989 Ma hope aku ka huelo Ua nānā au i ka hopena o ka ʻōpala (huelo -5 ./my_dump.dump) ua ʻike ʻia ua hoʻopau ʻia ka dump ma ka laina me id 186 525"No laila, ʻo ka pilikia me ka laina ID 186 526, ua haki, a pono e holoi ʻia!" i manaʻo ai au. Akā ma hope o ka nīnau ʻana i ka waihona ʻikepili:
«koho * mai ws_log_smevlog kahi id=186529"Ua ʻike ʻia ua maikaʻi nā mea āpau me kēia lālani... Ua hana pū nā lālani me nā helu kuhikuhi 186,530 - 186,540 me ka ʻole o nā pilikia. Ua hāʻule kekahi "manaʻo akamai". Ma hope mai, ua maopopo iaʻu ke kumu o kēia: i ka holoi ʻana/hoʻololi ʻana i ka ʻikepili mai kahi papaʻaina, ʻaʻole ia e holoi kino ʻia, akā ua hōʻailona ʻia he "tuples make", a laila hele mai autovacuum a hōʻailona i kēia mau lālani ua holoi ʻia a ʻae iā lākou e hoʻohana hou ʻia. No ka maopopo, inā loli ka ʻikepili i loko o kahi papaʻaina a ua hoʻā ʻia ka autovacuum, ʻaʻole ia e mālama ʻia ma ke ʻano he kaʻina.
Hoʻāʻo 5: KOHO, MAI, MA HEA id=
Hoʻoikaika mai nā hāʻule ʻana iā kākou. ʻAʻole pono ʻoe e haʻalele, pono ʻoe e hoʻomau a manaʻoʻiʻo iā ʻoe iho a me kou mau hiki. No laila ua hoʻoholo wau e hoʻāʻo i kahi koho ʻē aʻe: e nānā wale i nā moʻolelo āpau i loko o ka waihona ʻikepili pākahi. Ma ka ʻike ʻana i ke ʻano o kaʻu papa (e nānā ma luna), loaʻa iā mākou kahi kahua id, he mea kū hoʻokahi (ke kī mua). Loaʻa iā mākou he 1,628,991 mau lālani ma ka papa a id Aia lākou ma ke kaʻina, ʻo ia hoʻi hiki iā mākou ke hana hou iā lākou pākahi:
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; doneNo ka poʻe i maopopo ʻole, hana ke kauoha penei: nānā ia i ka papa ma ka laina a hoʻouna iā stdout i / dev / null, akā inā hāʻule ke kauoha SELECT, a laila paʻi ʻia ka kikokikona hewa (hoʻouna ʻia ʻo stderr i ka console) a paʻi ʻia ka laina e loaʻa ana ka hewa (mahalo i ka ||, ʻo ia hoʻi ua loaʻa nā pilikia o ke koho (ʻaʻole 0 ke code hoʻihoʻi kauoha)).
Ua laki wau, ua hana ʻia nā papa kuhikuhi ma ke kahua id:
ʻO ke ʻano kēia, ʻaʻole pono e lōʻihi ka manawa e loaʻa ai ka lālani me ka id i koi ʻia. Ma ke kumumanaʻo, pono ia e hana. No laila, e holo kākou i ke kauoha i loko tmux a e hele kāua e moe.
I ke kakahiaka, ua ʻike au ma kahi o 90,000 mau pou, ʻoi aku ma mua o 5%. He hopena maikaʻi loa i hoʻohālikelike ʻia me ke ʻano hana ma mua (2%)! Akā, ʻaʻole au makemake e kali i 20 mau lā...
Hoʻāʻo 6: KOHO, MAI, MA HEA id >= a me id
Ua hoʻokaʻawale ʻia kahi kikowaena maikaʻi loa no ka waihona ʻikepili i ka mea kūʻai aku: kahi kikowaena pālua-mea hana. Intel Xeon E5-2697 v2He 48 mau kaula i loaʻa iā mākou! He awelika ka ukana o ke kikowaena, no laila hiki iā mākou ke lawelawe maʻalahi ma kahi o 20 mau kaula. He nui nō hoʻi kā mākou RAM: he 384 gigabytes nui!
No laila, pono e hoʻohālikelike ʻia ka hui:
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; doneUa hiki iaʻu ke kākau i kahi palapala nani a nani hoʻi ma aneʻi, akā ua koho wau i ke ʻano hoʻohālikelike wikiwiki loa: e hoʻokaʻawale lima i ka laulā 0-1628991 i loko o nā waena o 100,000 mau moʻolelo a holo i 16 mau kauoha o kēia ʻano ma ke kaʻawale:
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; doneAkā, ʻaʻole ia wale nō. ʻO ka hoʻopili ʻana i ka waihona ʻikepili e pono ai kekahi manawa a me nā kumuwaiwai ʻōnaehana. ʻAʻole akamai loa ka hoʻopili ʻana i 1,628,991, e ʻae ʻoe. No laila e kiʻi kākou i 1000 lālani no kēlā me kēia pilina ma kahi o hoʻokahi wale nō. Ua like ke ʻano o ke kauoha me kēia:
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; doneE wehe i 16 mau puka makani i loko o kahi kau tmux a holo i nā kauoha penei:
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
I hoʻokahi lā ma hope mai, ua loaʻa iaʻu nā hopena mua! ʻO ke kikoʻī (ʻaʻole i mālama hou ʻia nā waiwai XXX a 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
146000ʻO ke ʻano kēia, he ʻekolu lālani me nā hewa. ʻO nā ID o nā moʻolelo pilikia mua a me ka lua ma waena o 829,000 a me 830,000, a ʻo ka ID o ke kolu ma waena o 146,000 a me 147,000. A laila, pono mākou e ʻimi i nā waiwai ID pololei o nā moʻolelo pilikia. No ka hana ʻana i kēia, nānā mākou i kā mākou laulā o nā moʻolelo pilikia ma nā ʻāpana o 1 a ʻike i nā 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
Hauʻoli hauʻoli
Ua loaʻa iā mākou nā lālani pilikia. E komo kākou i ka waihona ʻikepili me ka hoʻohana ʻana i ka psql a hoʻāʻo e holoi iā lākou:
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 1I koʻu kahaha, ua holoi ʻia nā moʻolelo me ka ʻole o nā pilikia ʻoiai me ke koho ʻole nā ʻaoʻao_i_hōʻino_ʻole.
A laila ua hoʻopili wau i ka waihona ʻikepili, ua hana PAHA PAHA (Manaʻo wau ʻaʻole pono e hana i kēia), a ma ka hopena ua lawe maikaʻi ʻia kahi kope me ka hoʻohana ʻana pg_puʻuUa lawe ʻia ka ʻōpala me ka ʻole o nā hewa! Ua hoʻoponopono ʻia ka pilikia me kēia ʻano hana lapuwale loa. Ua hauʻoli nui wau i ka loaʻa ʻana o kahi hopena ma hope o nā hemahema he nui!
Nā Mahalo a me ka Hopena
ʻO kēia koʻu ʻike mua ʻana i ka hoʻihoʻi ʻana i kahi waihona ʻikepili Postgres maoli. E hoʻomanaʻo wau i kēia ʻike no ka manawa lōʻihi.
A ʻo ka mea hope loa, makemake wau e hoʻomaikaʻi iā PostgresPro no ka unuhi ʻana i nā palapala i ka ʻōlelo Lūkini a no , he mea kōkua nui ia i ka wā o ka nānā ʻana i ka pilikia.
Source: www.habr.com
