Ou te fia faasoa atu ia te oe la'u uluai aafiaga faamanuiaina o le toe faafo'isia o se Postgres database i le atoatoaga o galuega. Na ou masani i le Postgres DBMS i le afa tausaga talu ai; ae leʻi i ai saʻu poto masani i le pulega o faʻamaumauga.
Ou te galue o se enisinia semi-DevOps i se kamupani tele IT. O la matou kamupani e atiaʻe polokalame mo auaunaga maualuga, ma o aʻu e nafa ma le faʻatinoga, tausiga ma le faʻapipiʻiina. Na tuʻuina mai ia te aʻu se galuega masani: ia faʻafouina se talosaga i luga o le tasi server. O loʻo tusia le talosaga i Django, i le taimi o le faʻafouina o femalagaiga o loʻo faia (suiga i le fausaga o faʻamaumauga), ma aʻo leʻi faia lenei faagasologa, matou te ave se faʻamaumauga tuʻufaʻatasia atoa e ala i le polokalame pg_dump masani, i le tulaga.
Na tupu se mea sese fa'afuase'i a'o ave le lafoa'i (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
sesē "itulau le aoga ile poloka" tautala i faʻafitauli i le tulaga faila faila, lea e matua leaga lava. I luga o fono eseese sa fautuaina e fai VACUUM ATUA ma le filifiliga zero_damaged_pages e foia ai lenei faafitauli. Ia, tatou taumafai...
Saunia mo le toe faʻaleleia
FAʻATASI! Ia mautinoa e ave se faʻamaumauga Postgres aʻo leʻi taumafai e toe faʻafoʻi lau faʻamaumauga. Afai ei ai sau masini komepiuta, taofi le database ma ave se ata. Afai e le mafai ona ave se ata, taofi le database ma kopi mea o loʻo i totonu o le Postgres directory (e aofia ai faila faila) i se nofoaga saogalemu. O le mea autu i la tatou pisinisi e le o le faʻaleagaina o mea. Faitau .
Talu ai e masani ona aoga le faʻamaumauga mo aʻu, na ou faʻatapulaʻaina aʻu lava i se tuʻufaʻatasiga faʻamaumauga masani, ae le aofia ai le laulau ma faʻamaumauga faʻaleagaina (filifiliga -T, --exclude-table= LAFOGA i le pg_dump).
O le 'au'aunaga sa tino, sa le mafai ona pu'eina se ata. Ua aveese le faaleoleo, tatou agai i luma.
Siaki faiga faila
Aʻo leʻi taumafai e toe faʻaleleia le faʻamaumauga, e manaʻomia ona tatou mautinoa o loʻo lelei mea uma ma le faila faila lava ia. Ma i tulaga o mea sese, faasaʻo i latou, aua a leai e mafai ona e faʻaleagaina mea.
I loʻu tulaga, o le faila faila ma le database na faʻapipiʻiina i totonu "/srv" ma o le ituaiga o ext4.
Taofi le fa'amaumauga: systemctl taofi [imeli puipuia] ma siaki o le faila faila e le o faʻaaogaina e se tasi ma e mafai ona faʻaaogaina e faʻaaoga ai le poloaiga lsof:
lsof +D /srv
Sa tatau foi ona ou taofi le redis database, talu ai sa faʻaaogaina foi "/srv". Ona ou tatalaina lea / srv (umount).
Na siaki le faiga faila e faʻaaoga ai le aoga e2fsck ma le ki -f (Fa'amalosi le siakiina e tusa lava pe fa'amama le faiga faila):
Le isi, faʻaaoga le aoga lafoa'i2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep siaki) e mafai ona e faʻamaonia na faia moni le siaki:
e2fsck fai mai e leai ni faʻafitauli na maua i le ext4 file system level, o lona uiga e mafai ona e faʻaauau le taumafai e toe faʻaleleia le database, pe toe foʻi i fa'amama fa'atumu (ioe, e tatau ona e faʻapipiʻi le faila faila i tua ma amata le database).
Afai ei ai sau 'auʻaunaga faaletino, ia mautinoa e siaki le tulaga o tisiki (via smartctl -a /dev/XXX) poʻo le RAID pule e faʻamautinoa e le o iai le faʻafitauli i le tulaga o meafaigaluega. I loʻu tulaga, o le RAID na avea ma "meafaigaluega", o lea na ou fesili ai i le pulega i le lotoifale e siaki le tulaga o le RAID (o le server e tele selau kilomita le mamao mai ia te aʻu). Na ia fai mai e leai ni mea sese, o lona uiga e mautinoa lava e mafai ona tatou toe faʻaleleia.
Taumafai 1: zero_damaged_pages
Matou te faʻafesoʻotaʻi i le database e ala i le psql ma se faʻamatalaga o loʻo i ai aia tatau superuser. Matou te manaʻomia se superuser, aua ... filifiliga zero_damaged_pages e na o ia e mafai ona sui. I loʻu tulaga o le postgres:
psql -h 127.0.0.1 -U postgres -s [igoa_faamatalaga]
Filifiliga zero_damaged_pages manaʻomia ina ia le amanaʻiaina mea sese (mai le upega tafaʻilagi postgrespro):
A iloa e PostgreSQL se ulutala itulau leaga, e masani lava ona lipotia se mea sese ma faʻaumatia le fefaʻatauaiga o loʻo iai nei. Afai ole zero_damaged_pages e mafai, o le faiga e tu'uina atu se lapataiga, fa'asolo ese le itulau ua faaleagaina i le manatua, ma fa'aauau le faiga. O lenei amio e fa'aleagaina ai fa'amaumauga, e ta'ua o laina uma i le itulau fa'aleagaina.
Matou te faʻatagaina le filifiliga ma taumafai e fai se vacuum atoa o laulau:
VACUUM FULL VERBOSE
Ae paga lea, leaga.
Na matou fetaiai ma se mea sese faapena:
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- se masini mo le teuina o "faʻamatalaga umi" i Poetgres pe a le fetaui i luga o le itulau e tasi (8kb e le mafai).
Taumafaiga 2: toe faasino igoa
O le fautuaga muamua mai Google e leʻi fesoasoani. Ina ua maeʻa ni nai minute o suʻesuʻega, na ou mauaina le faʻamatalaga lona lua - e fai toe fa'avasega laulau ua faaleagaina. Sa ou vaai i lenei fautuaga i le tele o nofoaga, ae e lei faaosofia ai le mautinoa. Sei o tatou toe faasino igoa:
reindex table ws_log_smevlog
toe fa'avasega mae'a e aunoa ma ni fa'afitauli.
Ae peitai, e lei fesoasoani lenei mea, VACUUM TUMU pa'u ma se mea sese faapena. Talu ai ua ou masani i toilalo, na amata ona ou suʻeina atili mo fautuaga ile Initaneti ma maua ai se mea manaia .
Taumafai 3: FILIFILI, LIMIT, OFFSET
O le tusiga o loʻo i luga o loʻo fautua mai e vaʻavaʻai i le laulau laina i lea laina ma aveese faʻamatalaga faʻafitauli. Muamua e tatau ona tatou vaʻai i laina uma:
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 lo'u tulaga, o le laulau sa i ai 1 628 991 laina! Sa tatau ona tausia lelei , ae o se autu lenei mo se talanoaga ese. O le Aso Toʻonaʻi, na ou faʻatautaia lenei poloaiga i le tmux ma ou moe:
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; doneE oo atu i le taeao sa ou filifili e siaki pe o a mai mea. I loʻu teʻi, na ou iloa ai ina ua uma le 20 itula, naʻo le 2% o faʻamaumauga na suʻeina! Ou te lei manao e faatali mo le 50 aso. O le isi toilalo atoatoa.
Ae ou te lei fiu. Sa ou mafaufau pe aisea na umi ai le sueina. Mai faʻamaumauga (toe i luga o le postgrespro) na ou iloa ai:
OFFSET o lo'o fa'amaoti mai e fa'ase'e le numera fa'amaonia o laina a'o le'i amata ona fa'asolo atu laina.
Afai e fa'amaonia uma le OFFSET ma le LIMIT, e fa'ase'e muamua e le faiga le laina OFFSET ona amata lea ona faitau laina mo le fa'atapula'aina o le LIMIT.A fa'aogaina le LIMIT, e taua tele le fa'aogaina o le ORDER BY clause ina ia toe fa'afo'i laina fa'ai'uga i se fa'atonuga fa'apitoa. A leai, o le a toe fa'afo'i atu vaega e le'i taumateina o laina.
E manino lava, o le poloaiga i luga e sese: muamua, e leai oka e, o le taunuuga e ono sese. Lona lua, e tatau i Postgres ona suʻe muamua ma faaseʻe laina OFFSET, ma faʻateleina FA'AFIA o le a atili fa'aitiitia le gaosiga.
Taumafaiga 4: ave se lafoa'i i tusitusiga
Ona oo mai lea o se manatu manaia tele i lo'u mafaufau: ave se lafoa'i i tusitusiga ma iloilo le laina mulimuli na pu'eina.
Ae muamua, sei o tatou tilotilo i le fausaga o le laulau. ws_log_smevlog:
I la matou tulaga ei ai le matou koluma "Id", lea sa i ai le fa'ailoga tulaga ese (counter) o le laina. O le fuafuaga e pei o lenei:
- Ua amata ona matou ave se lafoaʻi i tusitusiga (i le tulaga o sql poloaiga)
- I se taimi patino, o le a faʻalavelaveina le lafoaia ona o se mea sese, ae o le faila faila o le a faʻasaoina pea i luga o le disk.
- Matou te vaʻavaʻai i le pito o le faila faila, ma matou maua ai le faʻamatalaga (id) o le laina mulimuli na aveese ma le manuia.
Na amata ona ou lafoa'i i se faiga tusitusia:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpO le lapisi, e pei ona faʻamoemoeina, na faʻalavelaveina i le mea lava e tasi:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
Fa'asolo atu siʻuʻu Na ou tilotilo i le pito o le lapisi (si'usi'u -5 ./my_dump.dump) na iloa ua motusia le lapisi i luga o le laina ma le id 186 525. “O lea la o le fa’afitauli o lo’o i le laina ma le id 186 526, ua gau, ma e tatau ona tape!” - Faapea lava a'u. Ae, faia se fesili i le database:
«filifili * mai ws_log_smevlog where id=186529"Na aliali mai o loʻo lelei mea uma i lenei laina... O laina ma faʻailoga 186 - 530 na galue foi e aunoa ma ni faʻafitauli. O le isi "manatu atamai" na le manuia. Mulimuli ane na ou malamalama pe aisea na tupu ai lenei mea: pe a tape ma suia faʻamatalaga mai se laulau, e le o tapeina faaletino, ae faʻailogaina o "tuples mate", ona sau ai lea. masini masini ma fa'ailoga laina nei ua tape ma fa'atagaina nei laina e toe fa'aoga. Ina ia malamalama, afai o faʻamatalaga i le laulau e suia ma faʻaogaina le autovacuum, ona le teuina faʻasolosolo.
Taumafai 5: FILIFILI, MAI, WHERE id=
O toilalo e faamalosia ai i tatou. E le tatau ona e fiu, e tatau ona e alu i le iuga ma talitonu ia te oe lava ma ou gafatia. O lea na ou filifili ai e taumafai se isi filifiliga: naʻo le vaʻavaʻai i faʻamaumauga uma i totonu o faʻamaumauga taʻitasi. I le iloaina o le fausaga o laʻu laulau (silasila i luga), o loʻo i ai se matou id fanua e tulaga ese (ki autu). E 1 laina i le laulau ma id o loʻo faʻatulagaina, o lona uiga e mafai ona tatou faʻavasegaina taʻitasi:
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; doneAfai e le malamalama se tasi, o le poloaiga e galue e pei ona taua i lalo: e suʻesuʻe le laulau laina i lea laina ma auina atu le stdout i / dev / null, ae afai e le manuia le SELECT poloaiga, ona lolomi lea o le mea sese (stderr e auina atu i le faʻamafanafanaga) ma se laina o loʻo i ai le mea sese e lolomi (faʻafetai i le ||, o lona uiga o le filifilia na i ai faʻafitauli (o le toe faafoi code o le poloaiga e le 0)).
Sa ou laki, sa i ai au faasino igoa na faia i luga o le fanua id:
O lona uiga o le sailia o se laina ma le id mana'omia e le tatau ona tele se taimi. I le talitonuga e tatau ona aoga. Ia, tatou fa'agasolo le fa'atonuga i totonu tmux ma ta o e momoe.
E oo atu i le taeao na ou maua ai e tusa ma le 90 tala na matamata i ai, lea e na o le 000%. O se taunuuga lelei pe a faʻatusatusa i le auala muamua (5%)! Ae ou te lei manao e faatali 2 aso...
Taumafai 6: FILIFILI, MAI, WHERE id >= ma id <
O le tagata fa'atau sa i ai se 'au'aunaga sili ona lelei na tu'ufa'atasia i le fa'amaumauga: dual-processor Intel Xeon E5-2697 v2, e tusa ma le 48 filo i lo matou nofoaga! O le uta i luga o le 'auʻaunaga sa averesi; e mafai ona matou sii maia pe a ma le 20 filo e aunoa ma ni faʻafitauli. Sa lava foi le RAM: e tusa ma le 384 gigabytes!
O le mea lea, e manaʻomia le faʻatulagaina o le poloaiga:
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; doneO iinei na mafai ai ona tusia se tusitusiga matagofie ma matagofie, ae na ou filifilia le auala sili ona vave faʻatusa: vaelua lima le laina 0-1628991 i vaeluaga o le 100 faʻamaumauga ma taʻavale eseese 000 poloaiga o le fomu:
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; doneAe e le ona pau lea. I le talitonuga, o le faʻafesoʻotaʻi i se faʻamaumauga e manaʻomia ai foi sina taimi ma punaoa faʻaoga. Fa'afeso'ota'i le 1 e le'i atamai tele, e te ioe. O le mea lea, se'i o tatou toe aumai 628 laina nai lo le tasi i le tasi feso'ota'iga. O se taunuuga, na suia le 'au i lenei:
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; doneTatala 16 windows i se tmux session ma faʻatonu tulafono:
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
O se aso mulimuli ane na ou maua ai le taunuuga muamua! O lona uiga (ua le toe fa'asaoina le tau XXX ma 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
146000O lona uiga e tolu laina o lo'o i ai se mea sese. O id o faʻamaumauga faʻafitauli muamua ma lona lua o loʻo i le va o le 829 ma le 000, o id o le lona tolu i le va o le 830 ma le 000. O le isi, na na ona matou suʻeina le tau saʻo id o faʻamaumauga faʻafitauli. Ina ia faia lenei mea, matou te vaʻavaʻai i la matou laina faʻatasi ma faʻamaumauga faʻafitauli ma se laasaga o le 146 ma faʻailoa le 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
Manuia lou iʻuga
Na matou mauaina laina faʻafitauli. Matou te o atu i totonu o le database e ala i le psql ma taumafai e tape:
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 1Na ou teʻi, na tapeina faʻamaumauga e aunoa ma se faʻafitauli e tusa lava pe leai se filifiliga zero_damaged_pages.
Ona ou fesootai lea i le database, faia VACUUM TUMU (Ou te manatu e le tatau ona faia lenei mea), ma mulimuli ane na ou aveesea lelei le faaleoleo faaaogaina pg_dump. Na ave le otaota e aunoa ma se mea sese! Na foia le faafitauli i se auala faavalevalea. E leai se tuaoi o le olioli, ina ua mavae le tele o toilalo na mafai ona matou maua se fofo!
Fa'afetai ma Fa'ai'uga
O le auala lea na maua ai loʻu aafiaga muamua i le toe faʻaleleia o se faʻamaumauga moni a Postgres. O le a ou manatua lenei aafiaga mo se taimi umi.
Ma le mea mulimuli, ou te fia fai atu faafetai ia PostgresPro mo le faʻaliliuina o faʻamaumauga i le gagana Rusia ma mo , lea na fesoasoani tele i le taimi o le auiliiliga o le faafitauli.
puna: www.habr.com