Postgres: bloat, pg_repack ati awọn ihamọ idaduro

Ipa ti bloat lori awọn tabili ati awọn atọka jẹ olokiki pupọ ati pe kii ṣe ni Postgres nikan. Awọn ọna wa lati koju rẹ lati inu apoti, bii VACUUM FULL tabi CLUSTER, ṣugbọn wọn tii awọn tabili titiipa lakoko iṣẹ ati nitorinaa ko le ṣee lo nigbagbogbo.

Nkan naa yoo ni imọ-jinlẹ kekere kan nipa bii bloat ṣe waye, bii o ṣe le ja rẹ, nipa awọn idiwọ ti a da duro ati awọn iṣoro ti wọn mu wa si lilo pg_repack itẹsiwaju.

Yi article ti kọ da lori oro mi ni PgConf.Russia 2020.

Kini idi ti bloat waye?

Postgres da lori awoṣe ẹya pupọ (MVCC). Awọn oniwe-lodi ni wipe kọọkan kana ni tabili le ni orisirisi awọn ẹya, nigba ti lẹkọ ri ko siwaju sii ju ọkan ninu awọn wọnyi awọn ẹya, sugbon ko dandan kanna. Eyi n gba ọpọlọpọ awọn iṣowo laaye lati ṣiṣẹ ni nigbakannaa ati pe ko ni ipa lori ara wọn.

O han ni, gbogbo awọn ẹya wọnyi nilo lati wa ni ipamọ. Postgres n ṣiṣẹ pẹlu oju-iwe iranti nipasẹ oju-iwe ati oju-iwe kan jẹ iye ti o kere ju ti data ti o le ka lati disk tabi kọ. Jẹ ki a wo apẹẹrẹ kekere kan lati ni oye bi eyi ṣe ṣẹlẹ.

Jẹ ki a sọ pe a ni tabili ti a ti ṣafikun ọpọlọpọ awọn igbasilẹ. Awọn data tuntun ti han ni oju-iwe akọkọ ti faili nibiti o ti fipamọ tabili naa. Iwọnyi jẹ awọn ẹya ifiwe ti awọn ori ila ti o wa si awọn iṣowo miiran lẹhin ifarabalẹ kan (fun ayedero, a yoo ro pe ipele ipinya jẹ Ka Ifaramọ).

Lẹhinna a ṣe imudojuiwọn ọkan ninu awọn titẹ sii, nitorinaa samisi ẹya atijọ bi ko ṣe pataki mọ.

Igbesẹ nipasẹ igbese, mimu dojuiwọn ati piparẹ awọn ẹya ila, a pari pẹlu oju-iwe kan ninu eyiti o to idaji data jẹ “idoti”. Yi data ni ko han si eyikeyi idunadura.

Postgres ni ẹrọ kan ỌRỌ, eyi ti o wẹ awọn ẹya ti ko ti kọja kuro ati ki o ṣe aaye fun data titun. Ṣugbọn ti ko ba tunto ni ibinu tabi ti n ṣiṣẹ lọwọ ni awọn tabili miiran, lẹhinna “data idoti” wa, ati pe a ni lati lo awọn oju-iwe afikun fun data tuntun.

Nitorinaa ninu apẹẹrẹ wa, ni aaye diẹ ninu akoko tabili yoo ni awọn oju-iwe mẹrin, ṣugbọn idaji nikan yoo ni data laaye. Bi abajade, nigbati o ba n wọle si tabili, a yoo ka data pupọ diẹ sii ju iwulo lọ.

Paapaa ti VACUUM ba paarẹ gbogbo awọn ẹya ila ti ko ṣe pataki, ipo naa kii yoo ni ilọsiwaju pupọ. A yoo ni aaye ọfẹ ni awọn oju-iwe tabi paapaa gbogbo awọn oju-iwe fun awọn ori ila tuntun, ṣugbọn a yoo tun ka data diẹ sii ju iwulo lọ.
Nipa ọna, ti oju-iwe ti o ṣofo patapata (ọkan ninu apẹẹrẹ wa) wa ni opin faili naa, lẹhinna VACUUM yoo ni anfani lati gee. Ṣugbọn nisisiyi o wa ni arin, nitorina ko si nkan ti a le ṣe pẹlu rẹ.

Nigbati nọmba iru awọn oju-iwe ti o ṣofo tabi ti o ga julọ di nla, eyiti a pe ni bloat, o bẹrẹ lati ni ipa lori iṣẹ.

Ohun gbogbo ti salaye loke ni awọn isiseero ti awọn iṣẹlẹ ti bloat ni awọn tabili. Ni awọn atọka yi ṣẹlẹ ni Elo ni ọna kanna.

Ṣe Mo ni bloat?

Awọn ọna pupọ lo wa lati pinnu boya o ni bloat. Ero ti akọkọ ni lati lo awọn iṣiro Postgres ti inu, eyiti o ni alaye isunmọ nipa nọmba awọn ori ila ni awọn tabili, nọmba awọn ori ila “ifiwe”, bbl O le wa ọpọlọpọ awọn iyatọ ti awọn iwe afọwọkọ ti a ti ṣetan lori Intanẹẹti. A mu bi ipilẹ akosile lati PostgreSQL amoye, eyi ti o le akojopo bloat tabili pẹlu tositi ati bloat btree atọka. Ninu iriri wa, aṣiṣe rẹ jẹ 10-20%.

Ona miiran ni lati lo itẹsiwaju pgstattuple, eyiti o fun ọ laaye lati wo inu awọn oju-iwe ati gba mejeeji ifoju ati iye bloat gangan. Ṣugbọn ninu ọran keji, iwọ yoo ni lati ṣayẹwo gbogbo tabili naa.

A ṣe akiyesi iye kekere bloat, to 20%, itẹwọgba. O le ṣe akiyesi bi afọwọṣe ti fillfactor fun tabili и awọn atọka. Ni 50% ati loke, awọn iṣoro iṣẹ le bẹrẹ.

Awọn ọna lati dojuko bloat

Postgres ni awọn ọna pupọ lati koju bloat lati inu apoti, ṣugbọn wọn ko dara nigbagbogbo fun gbogbo eniyan.

Tunto AUTOVACUUM ki bloat ko ba waye. Tabi diẹ sii ni deede, lati tọju rẹ ni ipele itẹwọgba fun ọ. Eyi dabi imọran "balogun", ṣugbọn ni otitọ eyi kii ṣe rọrun nigbagbogbo lati ṣaṣeyọri. Fun apẹẹrẹ, o ni idagbasoke ti nṣiṣe lọwọ pẹlu awọn ayipada deede si ero data, tabi diẹ ninu iru ijira data n waye. Bi abajade, profaili fifuye rẹ le yipada nigbagbogbo ati pe yoo yatọ lati tabili si tabili. Eyi tumọ si pe o nilo lati ṣiṣẹ nigbagbogbo siwaju diẹ ati ṣatunṣe AUTOVACUUM si profaili iyipada ti tabili kọọkan. Ṣugbọn o han gbangba pe eyi ko rọrun lati ṣe.

Idi miiran ti o wọpọ ti AUTOVACUUM ko le tọju awọn tabili jẹ nitori pe awọn iṣowo ti n ṣiṣẹ pipẹ wa ti o ṣe idiwọ lati nu data ti o wa fun awọn iṣowo yẹn. Iṣeduro nibi tun han gbangba - yọkuro awọn iṣowo “ikọkọ” ati dinku akoko awọn iṣowo ti nṣiṣe lọwọ. Ṣugbọn ti ẹru lori ohun elo rẹ jẹ arabara ti OLAP ati OLTP, lẹhinna o le ni ọpọlọpọ awọn imudojuiwọn loorekoore ati awọn ibeere kukuru, ati awọn iṣẹ ṣiṣe igba pipẹ - fun apẹẹrẹ, kikọ ijabọ kan. Ni iru ipo bẹẹ, o tọ lati ronu nipa titan ẹru naa kọja awọn ipilẹ oriṣiriṣi, eyiti yoo gba laaye diẹ sii-tuntun-titun ti ọkọọkan wọn.

Apeere miiran - paapaa ti profaili ba jẹ isokan, ṣugbọn data data wa labẹ ẹru giga pupọ, lẹhinna paapaa AUTOVACUUM ibinu julọ le ma koju, ati bloat yoo waye. Iwọnwọn (inaro tabi petele) jẹ ojutu nikan.

Kini lati ṣe ni ipo kan nibiti o ti ṣeto AUTOVACUUM, ṣugbọn bloat tẹsiwaju lati dagba.

Egbe VACUM FULL tun ṣe awọn akoonu ti awọn tabili ati awọn atọka ati fi awọn data ti o yẹ nikan silẹ ninu wọn. Lati yọkuro bloat, o ṣiṣẹ ni pipe, ṣugbọn lakoko ipaniyan rẹ ti gba titiipa iyasoto lori tabili (AccessExclusiveLock), eyiti kii yoo gba laaye ṣiṣe awọn ibeere lori tabili yii, paapaa yan. Ti o ba ni anfani lati da iṣẹ rẹ duro tabi apakan rẹ fun igba diẹ (lati awọn iṣẹju mẹwa si awọn wakati pupọ ti o da lori iwọn data data ati ohun elo rẹ), lẹhinna aṣayan yii dara julọ. Laanu, a ko ni akoko lati ṣiṣẹ VACUUM FULL lakoko itọju eto, nitorina ọna yii ko dara fun wa.

Egbe ÒKÚRÙN Tun ṣe awọn akoonu ti awọn tabili ni ọna kanna bi VACUUM FULL, ṣugbọn ngbanilaaye lati ṣalaye atọka kan ni ibamu si eyiti data naa yoo paṣẹ ni ti ara lori disiki (ṣugbọn ni ọjọ iwaju aṣẹ ko ni iṣeduro fun awọn ori ila tuntun). Ni awọn ipo kan, eyi jẹ iṣapeye to dara fun nọmba awọn ibeere - pẹlu kika awọn igbasilẹ pupọ nipasẹ atọka. Aila-nfani ti aṣẹ naa jẹ kanna bi ti VACUUM FULL - o tilekun tabili lakoko iṣẹ.

Egbe REINDEX iru si awọn meji ti tẹlẹ, ṣugbọn tun ṣe atọka kan pato tabi gbogbo awọn atọka ti tabili. Awọn titiipa jẹ alailagbara diẹ: ShareLock lori tabili (idilọwọ awọn iyipada, ṣugbọn ngbanilaaye yiyan) ati AccessExclusiveLock lori atọka ti a tun kọ (awọn ibeere dina nipa lilo atọka yii). Sibẹsibẹ, ni ẹya 12th ti Postgres paramita kan han NIKANKAN, eyiti o fun ọ laaye lati tun atọka kọ laisi idilọwọ afikun nigbakanna, iyipada, tabi piparẹ awọn igbasilẹ.

Ni awọn ẹya iṣaaju ti Postgres, o le ṣaṣeyọri abajade ti o jọra si REINDEX LẸKANKAN lilo Ṣẹda atọka LỌkankan. O gba ọ laaye lati ṣẹda atọka laisi titiipa ti o muna (ShareUpdateExclusiveLock, eyiti ko dabaru pẹlu awọn ibeere ti o jọra), lẹhinna rọpo atọka atijọ pẹlu ọkan tuntun ki o paarẹ atọka atijọ. Eyi n gba ọ laaye lati yọkuro bloat atọka laisi kikọlu pẹlu ohun elo rẹ. O ṣe pataki lati ronu pe nigbati o ba tun awọn atọka tun ṣe, fifuye afikun yoo wa lori eto inu disiki naa.

Nitorinaa, ti awọn atọka ba wa awọn ọna lati yọkuro bloat “lori fo,” lẹhinna ko si fun awọn tabili. Eyi ni ibiti ọpọlọpọ awọn amugbooro ita wa sinu ere: pg_repack (pg_reorg tẹlẹ), pgcompact, pgcompacttable ati awọn miiran. Ninu nkan yii, Emi kii yoo ṣe afiwe wọn ati pe yoo sọrọ nipa pg_repack nikan, eyiti, lẹhin iyipada diẹ, a lo ara wa.

Bawo ni pg_repack ṣiṣẹ

Jẹ ki a sọ pe a ni tabili lasan patapata - pẹlu awọn atọka, awọn ihamọ ati, laanu, pẹlu bloat. Igbesẹ akọkọ ti pg_repack ni lati ṣẹda tabili log lati tọju data nipa gbogbo awọn ayipada lakoko ti o nṣiṣẹ. Awọn okunfa yoo tun ṣe awọn ayipada wọnyi fun gbogbo ifibọ, imudojuiwọn ati paarẹ. Lẹhinna a ṣẹda tabili kan, ti o jọra si atilẹba ti o wa ninu eto, ṣugbọn laisi awọn atọka ati awọn ihamọ, ki o má ba fa fifalẹ ilana fifi data sii.

Nigbamii, pg_repack n gbe data naa lati tabili atijọ si tabili tuntun, ṣe sisẹ gbogbo awọn ori ila ti ko ṣe pataki, lẹhinna ṣẹda awọn atọka fun tabili tuntun. Lakoko ipaniyan ti gbogbo awọn iṣẹ wọnyi, awọn ayipada kojọpọ ninu tabili log.

Igbesẹ ti o tẹle ni lati gbe awọn ayipada si tabili tuntun. Iṣiwa naa ni a ṣe lori ọpọlọpọ awọn iterations, ati nigbati o ba kere ju awọn titẹ sii 20 ti o ku ninu tabili log, pg_repack gba titiipa to lagbara, gbejade data tuntun, ati rọpo tabili atijọ pẹlu tuntun ni awọn tabili eto Postgres. Eyi nikan ni akoko kukuru pupọ nigbati iwọ kii yoo ni anfani lati ṣiṣẹ pẹlu tabili naa. Lẹhin eyi, tabili atijọ ati tabili pẹlu awọn akọọlẹ ti paarẹ ati aaye ti ni ominira ninu eto faili naa. Ilana naa ti pari.

Ohun gbogbo dabi ẹni nla ni imọran, ṣugbọn kini o ṣẹlẹ ni iṣe? A ṣe idanwo pg_repack laisi fifuye ati labẹ fifuye, ati ṣayẹwo iṣẹ rẹ ni ọran ti idaduro ti tọjọ (ni awọn ọrọ miiran, lilo Konturolu + C). Gbogbo awọn idanwo jẹ rere.

A lọ si ile itaja ounjẹ - lẹhinna ohun gbogbo ko lọ bi a ti nireti.

Pancake akọkọ lori tita

Lori iṣupọ akọkọ a gba aṣiṣe kan nipa irufin idiwọ alailẹgbẹ kan:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Idiwọn yii ni orukọ ti ipilẹṣẹ aladaaṣe index_16508 - o ṣẹda nipasẹ pg_repack. Da lori awọn abuda ti o wa ninu akopọ rẹ, a pinnu idiwọ “wa” ti o baamu rẹ. Iṣoro naa yipada lati jẹ pe eyi kii ṣe aropin lasan patapata, ṣugbọn ọkan ti a da duro (ihamọ idaduro), i.e. ijerisi rẹ ni a ṣe nigbamii ju aṣẹ sql, eyiti o yori si awọn abajade airotẹlẹ.

Awọn ihamọ ti a da duro: idi ti wọn nilo ati bii wọn ṣe n ṣiṣẹ

Imọran kekere kan nipa awọn ihamọ idaduro.
Jẹ ki a ṣe akiyesi apẹẹrẹ ti o rọrun: a ni iwe-itọkasi tabili ti awọn ọkọ ayọkẹlẹ pẹlu awọn abuda meji - orukọ ati aṣẹ ti ọkọ ayọkẹlẹ ninu itọsọna naa.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

Jẹ ki a sọ pe a nilo lati paarọ awọn ọkọ ayọkẹlẹ akọkọ ati keji. Ojutu taara ni lati ṣe imudojuiwọn iye akọkọ si ekeji, ati ekeji si akọkọ:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

Ṣugbọn nigba ti a ba ṣiṣẹ koodu yii, a nireti irufin idiwọ nitori aṣẹ ti awọn iye ninu tabili jẹ alailẹgbẹ:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

Bawo ni MO ṣe le ṣe yatọ? Aṣayan ọkan: ṣafikun aropo iye afikun si aṣẹ ti o jẹ ẹri pe ko si ninu tabili, fun apẹẹrẹ “-1”. Ninu siseto, eyi ni a pe ni “paṣipaarọ awọn iye ti awọn oniyipada meji nipasẹ ẹkẹta.” Iyatọ nikan ti ọna yii jẹ imudojuiwọn afikun.

Aṣayan meji: Tun tabili ṣe lati lo iru data aaye lilefoofo fun iye aṣẹ dipo awọn odidi. Lẹhinna, nigbati o ba n ṣe imudojuiwọn iye lati 1, fun apẹẹrẹ, si 2.5, titẹsi akọkọ yoo "duro" laifọwọyi laarin keji ati kẹta. Ojutu yii ṣiṣẹ, ṣugbọn awọn idiwọn meji wa. Ni akọkọ, kii yoo ṣiṣẹ fun ọ ti o ba lo iye ni ibikan ni wiwo. Keji, da lori konge ti iru data, iwọ yoo ni nọmba to lopin ti awọn ifibọ ti o ṣeeṣe ṣaaju ṣiṣe atunto awọn iye ti gbogbo awọn igbasilẹ.

Aṣayan mẹta: jẹ ki ihamọ naa da duro ki o ṣayẹwo nikan ni akoko ifaramọ:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

Niwọn igba ti ọgbọn ti ibeere akọkọ wa ni idaniloju pe gbogbo awọn iye jẹ alailẹgbẹ ni akoko ifaramọ, yoo ṣaṣeyọri.

Apẹẹrẹ ti a sọ loke jẹ, dajudaju, sintetiki pupọ, ṣugbọn o ṣafihan ero naa. Ninu ohun elo wa, a lo awọn ihamọ ti a da duro lati ṣe imuse ọgbọn ti o ni iduro fun ipinnu awọn ija nigbati awọn olumulo ṣiṣẹ ni nigbakannaa pẹlu awọn ohun elo ailorukọ pinpin lori igbimọ. Lilo iru awọn ihamọ gba wa laaye lati jẹ ki koodu ohun elo jẹ diẹ rọrun.

Ni gbogbogbo, da lori iru idiwo, Postgres ni awọn ipele mẹta ti granularity fun ṣiṣe ayẹwo wọn: ila, idunadura, ati awọn ipele ikosile.

orisun: begriffs

Ṣayẹwo ati NOT NULL nigbagbogbo ṣayẹwo ni ipele ila; fun awọn ihamọ miiran, bi a ṣe le rii lati tabili, awọn aṣayan oriṣiriṣi wa. O le ka diẹ ẹ sii nibi.

Lati ṣe akopọ ni ṣoki, awọn idiwọ ti a da duro ni nọmba awọn ipo n pese koodu kika diẹ sii ati awọn aṣẹ diẹ. Sibẹsibẹ, o ni lati sanwo fun eyi nipa didaju ilana ilana n ṣatunṣe aṣiṣe, niwọn igba ti aṣiṣe ba waye ati akoko ti o rii nipa rẹ ti yapa ni akoko. Iṣoro miiran ti o ṣee ṣe ni pe oluṣeto le ma ni anfani nigbagbogbo lati ṣe agbero eto ti o dara julọ ti ibeere naa ba ni idiwọ idaduro.

Ilọsiwaju ti pg_repack

A ti bo kini awọn idiwọ idaduro jẹ, ṣugbọn bawo ni wọn ṣe ni ibatan si iṣoro wa? Jẹ ki a ranti aṣiṣe ti a gba tẹlẹ:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

O waye nigbati a daakọ data lati tabili log si tabili tuntun kan. Eyi dabi ajeji nitori ... awọn data ninu awọn log tabili ti wa ni ileri pẹlú pẹlu awọn data ninu awọn orisun tabili. Ti wọn ba ni itẹlọrun awọn idiwọ ti tabili atilẹba, bawo ni wọn ṣe le rú awọn idiwọ kanna ni tuntun naa?

Bi o ti wa ni jade, root ti iṣoro naa wa ni igbesẹ ti tẹlẹ ti pg_repack, eyiti o ṣẹda awọn atọka nikan, ṣugbọn kii ṣe awọn idiwọ: tabili atijọ ni ihamọ alailẹgbẹ, ati pe titun ṣẹda atọka alailẹgbẹ dipo.

O ṣe pataki lati ṣe akiyesi nibi pe ti idiwọ naa ba jẹ deede ati pe ko da duro, lẹhinna itọka alailẹgbẹ ti a ṣẹda dipo jẹ deede si idiwọ yii, nitori Awọn idiwọ alailẹgbẹ ni Postgres jẹ imuse nipasẹ ṣiṣẹda atọka alailẹgbẹ kan. Ṣugbọn ninu ọran ti ihamọ idaduro, ihuwasi naa kii ṣe kanna, nitori atọka ko le da duro ati pe a ṣayẹwo nigbagbogbo ni akoko ti aṣẹ sql ti ṣiṣẹ.

Nitorinaa, pataki ti iṣoro naa wa ni “idaduro” ti ṣayẹwo: ninu tabili atilẹba o waye ni akoko ifaramọ, ati ninu tabili tuntun ni akoko ti aṣẹ sql ti ṣiṣẹ. Eyi tumọ si pe a nilo lati rii daju pe awọn sọwedowo ṣe kanna ni awọn ọran mejeeji: boya idaduro nigbagbogbo, tabi nigbagbogbo lẹsẹkẹsẹ.

Nitorinaa awọn imọran wo ni a ni?

Ṣẹda atọka ti o jọra si idaduro

Ero akọkọ ni lati ṣe awọn sọwedowo mejeeji ni ipo lẹsẹkẹsẹ. Eyi le ṣe agbekalẹ ọpọlọpọ awọn ihamọ rere eke, ṣugbọn ti o ba jẹ diẹ ninu wọn, eyi ko yẹ ki o kan iṣẹ awọn olumulo, nitori iru awọn ija jẹ ipo deede fun wọn. Wọn waye, fun apẹẹrẹ, nigbati awọn olumulo meji bẹrẹ ṣiṣatunṣe ẹrọ ailorukọ kanna ni akoko kanna, ati alabara ti olumulo keji ko ni akoko lati gba alaye ti ẹrọ ailorukọ ti dina tẹlẹ fun ṣiṣatunṣe nipasẹ olumulo akọkọ. Ni iru ipo bẹẹ, olupin naa kọ olumulo keji, ati alabara rẹ yipo awọn ayipada pada ati dina ẹrọ ailorukọ naa. Ni igba diẹ, nigbati olumulo akọkọ ba pari atunṣe, ekeji yoo gba alaye pe ẹrọ ailorukọ ko ni dina mọ ati pe yoo ni anfani lati tun ṣe iṣẹ wọn.

Lati rii daju pe awọn sọwedowo wa nigbagbogbo ni ipo ti kii da duro, a ṣẹda atọka tuntun kan ti o jọra si idiwọ idaduro atilẹba:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

Ni agbegbe idanwo, a gba awọn aṣiṣe diẹ ti a nireti. Aseyori! A ran pg_repack lẹẹkansi lori iṣelọpọ ati ni awọn aṣiṣe 5 lori iṣupọ akọkọ ni wakati iṣẹ kan. Eyi jẹ abajade itẹwọgba. Sibẹsibẹ, tẹlẹ lori iṣupọ keji nọmba awọn aṣiṣe pọ si ni pataki ati pe a ni lati da pg_repack duro.

Kí nìdí tó fi ṣẹlẹ̀? O ṣeeṣe ti aṣiṣe waye da lori iye awọn olumulo ti n ṣiṣẹ pẹlu awọn ẹrọ ailorukọ kanna ni akoko kanna. Nkqwe, ni akoko yẹn awọn iyipada ifigagbaga pupọ wa pẹlu data ti o fipamọ sori iṣupọ akọkọ ju awọn miiran lọ, ie. a wà o kan "orire".

Ero naa ko ṣiṣẹ. Ni aaye yẹn, a rii awọn ojutu miiran meji: tun kọ koodu ohun elo wa lati pin pẹlu awọn idiwọ ti a da duro, tabi “kọ” pg_repack lati ṣiṣẹ pẹlu wọn. A yan keji.

Rọpo awọn atọka ninu tabili titun pẹlu awọn idiwọ ti a da duro lati tabili atilẹba

Idi ti atunyẹwo jẹ kedere - ti tabili atilẹba ba ni idiwọ ti a da duro, lẹhinna fun tuntun o nilo lati ṣẹda iru idiwo kan, kii ṣe atọka.

Lati ṣe idanwo awọn ayipada wa, a kọ idanwo ti o rọrun:

• tabili pẹlu idiwọ idaduro ati igbasilẹ kan;
• fi data sii ni lupu ti o tako pẹlu igbasilẹ ti o wa tẹlẹ;
• ṣe imudojuiwọn kan - data ko ni ija mọ;
• ṣe awọn ayipada.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

Ẹya atilẹba ti pg_repack nigbagbogbo kọlu lori ifibọ akọkọ, ẹya ti a tunṣe ṣiṣẹ laisi awọn aṣiṣe. Nla.

A lọ si iṣelọpọ ati lẹẹkansi gba aṣiṣe ni ipele kanna ti didakọ data lati tabili log si ọkan tuntun:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Ipo Ayebaye: ohun gbogbo n ṣiṣẹ ni awọn agbegbe idanwo, ṣugbọn kii ṣe ni iṣelọpọ ?!

APPLY_COUNT àti ìpapọ̀ ìpele méjì

A bẹrẹ lati ṣe itupalẹ koodu naa laini laini ati ṣe awari aaye pataki kan: a gbe data lati tabili log si ọkan tuntun ni awọn ipele, igbagbogbo APPLY_COUNT tọka iwọn ti ipele naa:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

Iṣoro naa ni pe data lati idunadura atilẹba, ninu eyiti ọpọlọpọ awọn iṣẹ le ṣe idiwọ idiwọ, nigbati o ba gbe lọ, le pari ni ipade ti awọn ipele meji - idaji awọn aṣẹ yoo ṣe ni ipele akọkọ, ati idaji miiran. ninu keji. Ati nihin, da lori orire rẹ: ti awọn ẹgbẹ ko ba ṣẹ ohunkohun ni ipele akọkọ, lẹhinna ohun gbogbo dara, ṣugbọn ti wọn ba ṣe, aṣiṣe waye.

APPLY_COUNT jẹ dogba si awọn igbasilẹ 1000, eyiti o ṣalaye idi ti awọn idanwo wa ṣe ṣaṣeyọri - wọn ko bo ọran ti “ipapọ ipele”. A lo awọn ofin meji - fi sii ati imudojuiwọn, nitorinaa awọn iṣowo 500 gangan ti awọn aṣẹ meji ni a gbe nigbagbogbo sinu ipele kan ati pe a ko ni iriri eyikeyi awọn iṣoro. Lẹhin fifi imudojuiwọn keji kun, atunṣe wa duro ṣiṣẹ:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

Nitorinaa, iṣẹ ti o tẹle ni lati rii daju pe data lati tabili atilẹba, eyiti o yipada ni idunadura kan, pari ni tabili tuntun tun laarin iṣowo kan.

Kiko lati batching

Ati lẹẹkansi a ni meji solusan. Ni akọkọ: jẹ ki a kọ patapata ti ipin sinu awọn ipele ati gbe data ni idunadura kan. Anfani ti ojutu yii jẹ ayedero rẹ - awọn iyipada koodu ti a beere jẹ iwonba (nipasẹ ọna, ni awọn ẹya agbalagba pg_reorg ṣiṣẹ gangan bii iyẹn). Ṣugbọn iṣoro kan wa - a n ṣẹda idunadura pipẹ, ati eyi, gẹgẹbi a ti sọ tẹlẹ, jẹ irokeke ewu si ifarahan ti bloat tuntun kan.

Ojutu keji jẹ eka sii, ṣugbọn o ṣee ṣe pe o tọ: ṣẹda iwe kan ninu tabili log pẹlu idanimọ ti idunadura ti o ṣafikun data si tabili. Lẹhinna, nigba ti a daakọ data, a le ṣe akojọpọ nipasẹ abuda yii ati rii daju pe awọn iyipada ti o jọmọ ti gbe papọ. Ipele naa yoo ṣẹda lati awọn iṣowo pupọ (tabi ọkan nla) ati iwọn rẹ yoo yatọ si da lori iye data ti a yipada ninu awọn iṣowo wọnyi. O ṣe pataki lati ṣe akiyesi pe niwọn igba ti data lati oriṣiriṣi awọn iṣowo wọ inu tabili log ni aṣẹ laileto, kii yoo ṣee ṣe lati ka ni lẹsẹsẹ, bi o ti jẹ tẹlẹ. seqscan fun ibeere kọọkan pẹlu sisẹ nipasẹ tx_id jẹ gbowolori pupọ, a nilo atọka kan, ṣugbọn yoo tun fa fifalẹ ọna naa nitori oke ti imudojuiwọn rẹ. Ni gbogbogbo, bi nigbagbogbo, o nilo lati rubọ nkankan.

Nitorina, a pinnu lati bẹrẹ pẹlu aṣayan akọkọ, bi o ṣe rọrun. Ni akọkọ, o jẹ dandan lati ni oye boya idunadura pipẹ yoo jẹ iṣoro gidi kan. Niwọn igba ti gbigbe data akọkọ lati tabili atijọ si tuntun tun waye ni iṣowo gigun kan, ibeere naa yipada si “Elo ni a yoo mu idunadura yii pọ si?” Iye akoko idunadura akọkọ da lori iwọn ti tabili. Iye akoko tuntun kan da lori iye awọn ayipada ti o ṣajọpọ ninu tabili lakoko gbigbe data, ie. lori awọn kikankikan ti awọn fifuye. Ṣiṣe pg_repack waye lakoko akoko fifuye iṣẹ ti o kere ju, ati iwọn didun awọn ayipada jẹ kekere ni afiwera si iwọn atilẹba ti tabili naa. A pinnu pe a le gbagbe akoko idunadura tuntun kan (fun lafiwe, ni apapọ o jẹ wakati 1 ati awọn iṣẹju 2-3).

Awọn adanwo jẹ rere. Lọlẹ lori iṣelọpọ tun. Fun wípé, eyi ni aworan pẹlu iwọn ọkan ninu awọn data data lẹhin ṣiṣe:

Niwọn bi a ti ni itẹlọrun patapata pẹlu ojutu yii, a ko gbiyanju lati ṣe ọkan keji, ṣugbọn a n gbero boya o ṣeeṣe lati jiroro pẹlu awọn olupilẹṣẹ itẹsiwaju. Atunyẹwo lọwọlọwọ wa, laanu, ko ti ṣetan fun atẹjade, nitori a yanju iṣoro naa nikan pẹlu awọn ihamọ idaduro alailẹgbẹ, ati fun alemo kikun o jẹ dandan lati pese atilẹyin fun awọn iru miiran. A nireti lati ni anfani lati ṣe eyi ni ọjọ iwaju.

Boya o ni ibeere kan, kilode ti a paapaa ni ipa ninu itan yii pẹlu iyipada ti pg_repack, ati pe ko, fun apẹẹrẹ, lo awọn analogues rẹ? Ni aaye kan a tun ronu nipa eyi, ṣugbọn iriri rere ti lilo rẹ tẹlẹ, lori awọn tabili laisi awọn idiwọ ti a da duro, ni iwuri fun wa lati gbiyanju lati ni oye pataki ti iṣoro naa ati ṣatunṣe rẹ. Ni afikun, lilo awọn solusan miiran tun nilo akoko lati ṣe awọn idanwo, nitorinaa a pinnu pe a yoo gbiyanju akọkọ lati ṣatunṣe iṣoro naa ninu rẹ, ati pe ti a ba rii pe a ko le ṣe eyi ni akoko ti o tọ, lẹhinna a yoo bẹrẹ wiwo awọn analogues. .

awari

Ohun ti a le ṣeduro da lori iriri tiwa:

1. Bojuto bloat rẹ. Da lori data ibojuwo, o le loye bawo ni a ṣe tunto autovacuum daradara.
2. Ṣatunṣe AUTOVACUUM lati tọju bloat ni ipele itẹwọgba.
3. Ti bloat naa ba tun dagba ati pe o ko le bori rẹ nipa lilo awọn irinṣẹ ti ita-apoti, maṣe bẹru lati lo awọn amugbooro ita. Ohun akọkọ ni lati ṣe idanwo ohun gbogbo daradara.
4. Maṣe bẹru lati yipada awọn solusan ita lati baamu awọn iwulo rẹ - nigbakan eyi le munadoko diẹ sii ati paapaa rọrun ju yiyipada koodu tirẹ lọ.

orisun: www.habr.com