🥇Postgres: bloat, pg_repack na vikwazo vilivyoahirishwa

Athari ya uvimbe kwenye meza na fahirisi inajulikana sana na haipo kwenye Postgres pekee. Kuna njia za kukabiliana nayo nje ya boksi, kama vile VACUUM FULL au CLUSTER, lakini hufunga meza wakati wa operesheni na kwa hivyo haziwezi kutumika kila wakati.

Nakala hiyo itakuwa na nadharia kidogo juu ya jinsi bloat inatokea, jinsi unavyoweza kupigana nayo, kuhusu vikwazo vilivyoahirishwa na matatizo wanayoleta kwa matumizi ya pg_repack ugani.

Makala hii imeandikwa kwa kuzingatia hotuba yangu katika PgConf.Russia 2020.

Kwa nini bloat hutokea?

Postgres inategemea muundo wa matoleo mengi (MVCC) Kiini chake ni kwamba kila safu kwenye jedwali inaweza kuwa na matoleo kadhaa, wakati shughuli hazioni zaidi ya moja ya matoleo haya, lakini sio lazima kuwa sawa. Hii inaruhusu miamala kadhaa kufanya kazi kwa wakati mmoja na haina athari kwa kila mmoja.

Kwa wazi, matoleo haya yote yanahitaji kuhifadhiwa. Postgres hufanya kazi na ukurasa wa kumbukumbu kwa ukurasa na ukurasa ni kiwango cha chini cha data ambacho kinaweza kusomwa kutoka kwa diski au kuandikwa. Hebu tuangalie mfano mdogo ili kuelewa jinsi hii hutokea.

Wacha tuseme tunayo meza ambayo tumeongeza rekodi kadhaa. Data mpya imeonekana katika ukurasa wa kwanza wa faili ambapo jedwali limehifadhiwa. Haya ni matoleo ya moja kwa moja ya safu mlalo ambayo yanapatikana kwa miamala mingine baada ya ahadi (kwa urahisi, tutachukulia kuwa kiwango cha kutengwa kimesomwa Imetumwa).

Kisha tukasasisha mojawapo ya maingizo, na hivyo kuashiria toleo la zamani kuwa halifai tena.

Hatua kwa hatua, kusasisha na kufuta matoleo ya safu, tulimaliza na ukurasa ambao takriban nusu ya data ni "takataka". Data hii haionekani kwa muamala wowote.

Postgres ina utaratibu VACUUM, ambayo husafisha matoleo ya kizamani na kutoa nafasi kwa data mpya. Lakini ikiwa haijasanidiwa kwa ukali wa kutosha au inashughulika kufanya kazi katika meza zingine, basi "data ya takataka" inabaki, na tunapaswa kutumia kurasa za ziada kwa data mpya.

Kwa hivyo katika mfano wetu, kwa wakati fulani jedwali litakuwa na kurasa nne, lakini nusu yake tu itakuwa na data ya moja kwa moja. Matokeo yake, wakati wa kufikia meza, tutasoma data nyingi zaidi kuliko lazima.

Hata kama VACUUM sasa itafuta matoleo yote ya safu mlalo yasiyohusika, hali haitaboreka sana. Tutakuwa na nafasi ya bure katika kurasa au hata kurasa nzima kwa safu mlalo mpya, lakini bado tutakuwa tunasoma data zaidi kuliko inavyohitajika.
Kwa njia, ikiwa ukurasa tupu kabisa (wa pili katika mfano wetu) ulikuwa mwisho wa faili, basi VACUUM itaweza kuipunguza. Lakini sasa yuko katikati, kwa hivyo hakuna kinachoweza kufanywa naye.

Wakati idadi ya kurasa hizo tupu au chache sana inakuwa kubwa, ambayo inaitwa bloat, huanza kuathiri utendaji.

Kila kitu kilichoelezwa hapo juu ni mechanics ya tukio la bloat katika meza. Katika faharisi hii hufanyika kwa njia sawa.

Je, nina uvimbe?

Kuna njia kadhaa za kuamua ikiwa una uvimbe. Wazo la kwanza ni kutumia takwimu za ndani za Postgres, ambazo zina takriban habari kuhusu idadi ya safu katika majedwali, idadi ya safu "moja kwa moja", nk. Unaweza kupata tofauti nyingi za maandishi yaliyotengenezwa tayari kwenye Mtandao. Tulichukua kama msingi hati kutoka kwa Wataalam wa PostgreSQL, ambayo inaweza kutathmini jedwali la bloat pamoja na toast na faharisi za btree za bloat. Katika uzoefu wetu, kosa lake ni 10-20%.

Njia nyingine ni kutumia ugani pgstattuple, ambayo hukuruhusu kutazama ndani ya kurasa na kupata makadirio na thamani halisi ya bloat. Lakini katika kesi ya pili, utalazimika kuchambua meza nzima.

Tunazingatia thamani ndogo ya bloat, hadi 20%, inayokubalika. Inaweza kuzingatiwa kama analog ya fillfactor kwa meza и fahirisi. Kwa 50% na zaidi, matatizo ya utendaji yanaweza kuanza.

Njia za kupambana na bloat

Postgres ina njia kadhaa za kukabiliana na bloat nje ya boksi, lakini siofaa kila wakati kwa kila mtu.

Sanidi AUTOVACUUM ili bloat isitokee. Au kwa usahihi zaidi, ili kuiweka katika kiwango kinachokubalika kwako. Hii inaonekana kama ushauri wa "nahodha", lakini kwa kweli hii sio rahisi kila wakati kufikia. Kwa mfano, una uendelezaji unaoendelea na mabadiliko ya mara kwa mara kwenye schema ya data, au aina fulani ya uhamishaji wa data unafanyika. Kwa hivyo, wasifu wako wa upakiaji unaweza kubadilika mara kwa mara na kwa kawaida utatofautiana kutoka kwa jedwali hadi jedwali. Hii ina maana kwamba unahitaji daima kufanya kazi mbele kidogo na kurekebisha AUTOVACUUM kwa wasifu unaobadilika wa kila jedwali. Lakini ni wazi hii si rahisi kufanya.

Sababu nyingine ya kawaida kwa nini AUTOVACUUM haiwezi kufuata majedwali ni kwa sababu kuna shughuli za muda mrefu zinazoizuia kusafisha data inayopatikana kwa miamala hiyo. Pendekezo hapa pia ni dhahiri - ondoa shughuli za "dangling" na upunguze wakati wa shughuli zinazoendelea. Lakini ikiwa mzigo kwenye programu yako ni mseto wa OLAP na OLTP, basi unaweza wakati huo huo kuwa na sasisho nyingi za mara kwa mara na maswali mafupi, pamoja na uendeshaji wa muda mrefu - kwa mfano, kujenga ripoti. Katika hali kama hiyo, inafaa kufikiria juu ya kueneza mzigo kwenye besi tofauti, ambayo itaruhusu urekebishaji mzuri zaidi wa kila mmoja wao.

Mfano mwingine - hata ikiwa wasifu ni sawa, lakini hifadhidata iko chini ya mzigo mkubwa sana, basi hata AUTOVACUUM yenye fujo zaidi haiwezi kukabiliana, na bloat itatokea. Kuongeza (wima au usawa) ndio suluhisho pekee.

Nini cha kufanya katika hali ambapo umeanzisha AUTOVACUUM, lakini bloat inaendelea kukua.

Timu UTUPU UMEJAA huunda upya yaliyomo kwenye majedwali na faharasa na kuacha tu data muhimu ndani yao. Ili kuondokana na bloat, inafanya kazi kikamilifu, lakini wakati wa utekelezaji wake lock ya kipekee kwenye meza inachukuliwa (AccessExclusiveLock), ambayo haitaruhusu kutekeleza maswali kwenye meza hii, hata kuchagua. Ikiwa unaweza kumudu kuacha huduma yako au sehemu yake kwa muda fulani (kutoka makumi ya dakika hadi saa kadhaa kulingana na ukubwa wa database na vifaa vyako), basi chaguo hili ni bora zaidi. Kwa bahati mbaya, hatuna muda wa kukimbia VACUUM FULL wakati wa matengenezo yaliyopangwa, kwa hiyo njia hii haifai kwetu.

Timu DARASA Hujenga upya yaliyomo kwenye jedwali kwa njia sawa na VACUUM FULL, lakini inakuwezesha kutaja index kulingana na ambayo data itaagizwa kimwili kwenye diski (lakini katika siku zijazo utaratibu haujahakikishiwa kwa safu mpya). Katika hali fulani, hii ni uboreshaji mzuri kwa idadi ya maswali - kwa kusoma rekodi nyingi kwa faharisi. Hasara ya amri ni sawa na ile ya VACUUM FULL - inafunga meza wakati wa operesheni.

Timu REINDEX sawa na mbili zilizopita, lakini hujenga upya fahirisi maalum au faharisi zote za jedwali. Kufuli ni dhaifu kidogo: ShareLock kwenye jedwali (huzuia marekebisho, lakini inaruhusu kuchagua) na AccessExclusiveLock kwenye faharasa inayojengwa upya (huzuia maswali kwa kutumia faharasa hii). Walakini, katika toleo la 12 la Postgres paramu ilionekana SAWA NA MOJA, ambayo hukuruhusu kuunda upya faharasa bila kuzuia nyongeza, urekebishaji, au ufutaji wa rekodi kwa wakati mmoja.

Katika matoleo ya awali ya Postgres, unaweza kupata matokeo sawa na REINDEX CONCURRENTLY ukitumia UTENGENEZA INDEX KWA WAKATI MMOJA. Inakuruhusu kuunda faharisi bila kufuli kali (ShareUpdateExclusiveLock, ambayo haiingilii na maswali yanayofanana), kisha ubadilishe faharisi ya zamani na mpya na ufute faharisi ya zamani. Hii hukuruhusu kuondoa bloat ya index bila kuingilia programu yako. Ni muhimu kuzingatia kwamba wakati wa kujenga upya indexes kutakuwa na mzigo wa ziada kwenye mfumo mdogo wa disk.

Kwa hivyo, ikiwa kwa faharisi kuna njia za kuondoa bloat "kwenye kuruka," basi hakuna kwa meza. Hapa ndipo viendelezi mbalimbali vya nje vinapotumika: pg_repack (zamani pg_reorg), pgcompact, pgcompacttable na wengine. Katika makala hii, sitawafananisha na nitazungumza tu juu ya pg_repack, ambayo, baada ya marekebisho fulani, tunajitumia wenyewe.

Jinsi pg_repack inavyofanya kazi

Hebu sema tuna meza ya kawaida kabisa - na indexes, vikwazo na, kwa bahati mbaya, na bloat. Hatua ya kwanza ya pg_repack ni kuunda jedwali la kumbukumbu ili kuhifadhi data kuhusu mabadiliko yote inapoendelea. Kichochezi kitaiga mabadiliko haya kwa kila ingizo, sasisho na kufuta. Kisha meza imeundwa, sawa na ya awali katika muundo, lakini bila indexes na vikwazo, ili usipunguze mchakato wa kuingiza data.

Ifuatayo, pg_repack huhamisha data kutoka kwa jedwali kuu hadi jedwali jipya, na kuchuja kiotomatiki safu mlalo zote zisizo na umuhimu, na kisha kuunda faharasa za jedwali jipya. Wakati wa utekelezaji wa shughuli hizi zote, mabadiliko hujilimbikiza kwenye jedwali la logi.

Hatua inayofuata ni kuhamisha mabadiliko kwenye jedwali jipya. Uhamishaji unafanywa kwa marudio kadhaa, na kunapokuwa na maingizo chini ya 20 yaliyosalia kwenye jedwali la kumbukumbu, pg_repack hupata kufuli imara, kuhamisha data ya hivi punde, na kubadilisha jedwali la zamani na jipya katika jedwali za mfumo wa Postgres. Huu ndio wakati pekee na mfupi sana wakati hautaweza kufanya kazi na meza. Baada ya hayo, meza ya zamani na meza iliyo na magogo hufutwa na nafasi imetolewa kwenye mfumo wa faili. Mchakato umekamilika.

Kila kitu kinaonekana kizuri katika nadharia, lakini ni nini kinachotokea katika mazoezi? Tulijaribu pg_repack bila mzigo na chini ya mzigo, na kuangalia uendeshaji wake katika kesi ya kuacha mapema (kwa maneno mengine, kwa kutumia Ctrl + C). Vipimo vyote vilikuwa vyema.

Tulikwenda kwenye duka la chakula - na kisha kila kitu hakikwenda kama tulivyotarajia.

Pancake ya kwanza inauzwa

Kwenye kundi la kwanza tulipokea hitilafu kuhusu ukiukaji wa kizuizi cha kipekee:

$ ./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.

Kizuizi hiki kilikuwa na faharisi ya jina inayozalishwa kiotomatiki_16508 - iliundwa na pg_repack. Kulingana na sifa zilizojumuishwa katika muundo wake, tuliamua kizuizi "yetu" ambacho kinalingana nayo. Shida iligeuka kuwa hii sio kizuizi cha kawaida kabisa, lakini kilichoahirishwa (kizuizi kilichoahirishwa), yaani. uthibitishaji wake unafanywa baadaye kuliko amri ya sql, ambayo inaongoza kwa matokeo yasiyotarajiwa.

Vikwazo vilivyoahirishwa: kwa nini zinahitajika na jinsi zinavyofanya kazi

Nadharia kidogo kuhusu vikwazo vilivyoahirishwa.
Hebu fikiria mfano rahisi: tuna kitabu cha kumbukumbu ya meza ya magari yenye sifa mbili - jina na utaratibu wa gari katika saraka.

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

Wacha tuseme tulihitaji kubadilisha gari la kwanza na la pili. Suluhisho la moja kwa moja ni kusasisha thamani ya kwanza hadi ya pili, na ya pili hadi ya kwanza:

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

Lakini tunapoendesha nambari hii, tunatarajia ukiukaji wa kizuizi kwa sababu mpangilio wa maadili kwenye jedwali ni wa kipekee:

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

Ninawezaje kuifanya kwa njia tofauti? Chaguo la kwanza: ongeza ubadilishaji wa ziada wa thamani kwa agizo ambalo limehakikishwa kuwa halitakuwepo kwenye jedwali, kwa mfano "-1". Katika upangaji, hii inaitwa "kubadilishana kwa maadili ya anuwai mbili hadi ya tatu." Upungufu pekee wa njia hii ni sasisho la ziada.

Chaguo la pili: Panga upya jedwali ili kutumia aina ya data ya sehemu inayoelea kwa thamani ya mpangilio badala ya nambari kamili. Kisha, wakati wa kusasisha thamani kutoka 1, kwa mfano, hadi 2.5, kuingia kwa kwanza "kusimama" moja kwa moja kati ya pili na ya tatu. Suluhisho hili linafanya kazi, lakini kuna mapungufu mawili. Kwanza, haitafanya kazi kwako ikiwa thamani inatumiwa mahali fulani kwenye kiolesura. Pili, kulingana na usahihi wa aina ya data, utakuwa na idadi ndogo ya kuingiza iwezekanavyo kabla ya kuhesabu tena maadili ya rekodi zote.

Chaguo la tatu: fanya kizuizi kicheleweshwe ili kikaguliwe tu wakati wa ahadi:

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

Kwa kuwa mantiki ya ombi letu la awali inahakikisha kuwa maadili yote ni ya kipekee wakati wa kujitolea, itafaulu.

Mfano uliojadiliwa hapo juu ni, bila shaka, wa syntetisk sana, lakini unaonyesha wazo. Katika programu yetu, tunatumia vizuizi vilivyoahirishwa kutekeleza mantiki ambayo inawajibika kusuluhisha mizozo wakati watumiaji wanafanya kazi kwa wakati mmoja na vitu vya wijeti vilivyoshirikiwa kwenye ubao. Kutumia vizuizi kama hivyo huturuhusu kufanya nambari ya programu iwe rahisi kidogo.

Kwa ujumla, kulingana na aina ya kizuizi, Postgres ina viwango vitatu vya uzito wa kuziangalia: safu mlalo, muamala na viwango vya kujieleza.

Chanzo: mafisadi

ANGALIA na SIYO NULL huangaliwa kila wakati katika kiwango cha safu; kwa vizuizi vingine, kama inavyoonekana kutoka kwa jedwali, kuna chaguzi tofauti. Unaweza kusoma zaidi hapa.

Kwa muhtasari mfupi, vikwazo vilivyoahirishwa katika hali kadhaa hutoa msimbo unaosomeka zaidi na amri chache. Walakini, lazima ulipe kwa hii kwa kugumu mchakato wa kurekebisha, kwani wakati kosa linatokea na wakati unapogundua juu yake hutenganishwa kwa wakati. Shida nyingine inayowezekana ni kwamba mpangaji ratiba hawezi kila wakati kuunda mpango mzuri ikiwa ombi linajumuisha kizuizi kilichoahirishwa.

Uboreshaji wa pg_repack

Tumeshughulikia vikwazo vilivyoahirishwa, lakini vinahusiana vipi na shida yetu? Hebu tukumbuke kosa tulilopokea hapo awali:

$ ./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.

Inatokea wakati data inakiliwa kutoka kwa jedwali la kumbukumbu hadi jedwali mpya. Hii inaonekana ya kushangaza kwa sababu ... data katika jedwali la kumbukumbu imejitolea pamoja na data kwenye jedwali la chanzo. Ikiwa wanakidhi vikwazo vya jedwali la asili, wanawezaje kukiuka vikwazo sawa katika mpya?

Kama inavyotokea, mzizi wa shida iko katika hatua ya awali ya pg_repack, ambayo huunda indexes tu, lakini sio vikwazo: meza ya zamani ilikuwa na kizuizi cha pekee, na mpya iliunda index ya kipekee badala yake.

Ni muhimu kutambua hapa kwamba ikiwa kizuizi ni cha kawaida na hakijaahirishwa, basi faharisi ya kipekee iliyoundwa badala yake ni sawa na kizuizi hiki, kwa sababu. Vikwazo vya kipekee katika Postgres vinatekelezwa kwa kuunda faharisi ya kipekee. Lakini katika kesi ya kizuizi kilichoahirishwa, tabia si sawa, kwa sababu index haiwezi kuahirishwa na inaangaliwa kila wakati wakati amri ya sql inatekelezwa.

Kwa hivyo, kiini cha tatizo kiko katika "kuchelewa" kwa hundi: katika meza ya awali hutokea wakati wa kujitolea, na katika meza mpya wakati amri ya sql inatekelezwa. Hii inamaanisha tunahitaji kuhakikisha kuwa ukaguzi unafanywa sawa katika hali zote mbili: ama huchelewa kila wakati, au mara moja mara moja.

Kwa hiyo tulikuwa na mawazo gani?

Unda faharasa sawa na iliyoahirishwa

Wazo la kwanza ni kufanya ukaguzi wote katika hali ya haraka. Hii inaweza kuzalisha vikwazo kadhaa vyema vya uongo, lakini ikiwa kuna wachache wao, hii haipaswi kuathiri kazi ya watumiaji, kwani migogoro hiyo ni hali ya kawaida kwao. Zinatokea, kwa mfano, wakati watumiaji wawili wanaanza kuhariri wijeti sawa kwa wakati mmoja, na mteja wa mtumiaji wa pili hawana wakati wa kupokea habari ambayo wijeti tayari imezuiwa kwa kuhaririwa na mtumiaji wa kwanza. Katika hali kama hiyo, seva inakataa mtumiaji wa pili, na mteja wake hurudisha nyuma mabadiliko na kuzuia wijeti. Baadaye kidogo, mtumiaji wa kwanza anapomaliza kuhariri, wa pili atapokea taarifa kwamba wijeti haijazuiwa tena na ataweza kurudia kitendo chake.

Ili kuhakikisha kuwa hundi ziko katika hali isiyoahirishwa kila wakati, tuliunda faharasa mpya sawa na kikwazo cha awali kilichoahirishwa:

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

Katika mazingira ya majaribio, tulipokea makosa machache tu yaliyotarajiwa. Mafanikio! Tuliendesha pg_repack tena kwenye uzalishaji na tukapata hitilafu 5 kwenye nguzo ya kwanza katika saa moja ya kazi. Haya ni matokeo yanayokubalika. Walakini, tayari kwenye nguzo ya pili idadi ya makosa iliongezeka sana na tulilazimika kuacha pg_repack.

Kwa nini ilitokea? Uwezekano wa hitilafu kutokea unategemea ni watumiaji wangapi wanafanya kazi na wijeti sawa kwa wakati mmoja. Inavyoonekana, wakati huo kulikuwa na mabadiliko machache ya ushindani na data iliyohifadhiwa kwenye nguzo ya kwanza kuliko kwa wengine, i.e. tulikuwa na "bahati" tu.

Wazo hilo halikufaulu. Wakati huo, tuliona masuluhisho mengine mawili: andika upya msimbo wetu wa maombi ili kuondoa vikwazo vilivyoahirishwa, au "fundisha" pg_repack kufanya kazi navyo. Tulichagua ya pili.

Badilisha faharasa katika jedwali jipya na vizuizi vilivyoahirishwa kutoka kwa jedwali asili

Madhumuni ya marekebisho yalikuwa dhahiri - ikiwa meza ya asili ina kizuizi kilichoahirishwa, basi kwa mpya unahitaji kuunda kizuizi kama hicho, na sio index.

Ili kujaribu mabadiliko yetu, tuliandika jaribio rahisi:

meza yenye kizuizi kilichoahirishwa na rekodi moja;
ingiza data katika kitanzi kinachokinzana na rekodi iliyopo;
fanya sasisho - data haipingani tena;
kufanya mabadiliko.

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;

Toleo la asili la pg_repack kila wakati lilianguka kwenye ingizo la kwanza, toleo lililorekebishwa lilifanya kazi bila makosa. Kubwa.

Tunaenda kwenye uzalishaji na tena tunapata hitilafu katika hatua sawa ya kunakili data kutoka kwa jedwali la kumbukumbu hadi mpya:

$ ./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.

Hali ya kawaida: kila kitu hufanya kazi katika mazingira ya mtihani, lakini sio katika uzalishaji?!

APPLY_COUNT na makutano ya bechi mbili

Tulianza kuchanganua msimbo kihalisi kwa mstari na tukagundua jambo muhimu: data huhamishwa kutoka jedwali la kumbukumbu hadi mpya katika vikundi, APPLY_COUNT mara kwa mara ilionyesha ukubwa wa kundi:

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

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

Shida ni kwamba data kutoka kwa shughuli ya asili, ambayo shughuli kadhaa zinaweza kukiuka kizuizi, wakati zinahamishwa, zinaweza kuishia kwenye makutano ya vikundi viwili - nusu ya amri itafanywa katika kundi la kwanza, na nusu nyingine. katika pili. Na hapa, kulingana na bahati yako: ikiwa timu hazikiuka chochote katika kundi la kwanza, basi kila kitu ni sawa, lakini ikiwa watafanya, kosa hutokea.

APPLY_COUNT ni sawa na rekodi 1000, ambayo inaeleza kwa nini majaribio yetu yalifaulu - hayakujumuisha kesi ya "makutano ya bechi". Tulitumia amri mbili - kuingiza na kusasisha, kwa hivyo shughuli 500 za amri mbili ziliwekwa kila wakati kwenye kundi na hatukupata shida yoyote. Baada ya kuongeza sasisho la pili, hariri yetu iliacha kufanya kazi:

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;

Kwa hivyo, kazi inayofuata ni kuhakikisha kwamba data kutoka kwa meza ya awali, ambayo ilibadilishwa katika shughuli moja, inaishia kwenye jedwali jipya pia ndani ya shughuli moja.

Kukataa kutoka kwa batch

Na tena tulikuwa na suluhisho mbili. Kwanza: wacha tuachane kabisa na ugawaji katika vikundi na uhamishe data katika shughuli moja. Faida ya suluhisho hili ilikuwa unyenyekevu wake - mabadiliko ya nambari yaliyohitajika yalikuwa madogo (kwa njia, katika matoleo ya zamani pg_reorg ilifanya kazi kama hiyo). Lakini kuna shida - tunaunda shughuli ya muda mrefu, na hii, kama ilivyosemwa hapo awali, ni tishio kwa kuibuka kwa uvimbe mpya.

Suluhisho la pili ni ngumu zaidi, lakini labda ni sahihi zaidi: tengeneza safu kwenye jedwali la logi na kitambulisho cha shughuli iliyoongeza data kwenye jedwali. Kisha, tunaponakili data, tunaweza kupanga kulingana na sifa hii na kuhakikisha kuwa mabadiliko yanayohusiana yanahamishwa pamoja. Kundi litaundwa kutokana na miamala kadhaa (au moja kubwa) na ukubwa wake utatofautiana kulingana na kiasi gani cha data kilibadilishwa katika shughuli hizi. Ni muhimu kutambua kwamba kwa kuwa data kutoka kwa shughuli tofauti huingia kwenye meza ya logi kwa utaratibu wa random, haitawezekana tena kuisoma kwa mfululizo, kama ilivyokuwa hapo awali. seqscan kwa kila ombi kwa kuchuja kwa tx_id ni ghali sana, faharisi inahitajika, lakini pia itapunguza kasi ya njia kwa sababu ya uboreshaji wa kuisasisha. Kwa ujumla, kama kawaida, unahitaji kutoa kitu.

Kwa hiyo, tuliamua kuanza na chaguo la kwanza, kwa kuwa ni rahisi zaidi. Kwanza, ilikuwa ni lazima kuelewa kama shughuli ya muda mrefu itakuwa tatizo halisi. Kwa kuwa uhamishaji mkuu wa data kutoka kwa jedwali la zamani hadi jipya pia hutokea katika shughuli moja ndefu, swali linabadilishwa kuwa "tutaongeza kiasi gani cha muamala huu?" Muda wa shughuli ya kwanza inategemea hasa ukubwa wa meza. Muda wa mpya inategemea jinsi mabadiliko mengi yanajilimbikiza kwenye meza wakati wa uhamisho wa data, i.e. juu ya ukubwa wa mzigo. Uendeshaji wa pg_repack ulifanyika wakati wa mzigo mdogo wa huduma, na kiasi cha mabadiliko kilikuwa kidogo sana ikilinganishwa na ukubwa wa awali wa jedwali. Tuliamua kwamba tunaweza kupuuza wakati wa shughuli mpya (kwa kulinganisha, kwa wastani ni saa 1 na dakika 2-3).

Majaribio yalikuwa mazuri. Zindua kwenye uzalishaji pia. Kwa uwazi, hapa kuna picha iliyo na saizi ya hifadhidata moja baada ya kukimbia:

Kwa kuwa tuliridhika kabisa na suluhisho hili, hatukujaribu kutekeleza la pili, lakini tunazingatia uwezekano wa kujadiliana na watengenezaji wa ugani. Marekebisho yetu ya sasa, kwa bahati mbaya, bado hayajawa tayari kuchapishwa, kwani tulitatua shida tu na vizuizi vya kipekee vilivyoahirishwa, na kwa kiraka kamili ni muhimu kutoa msaada kwa aina zingine. Tunatumai kuwa na uwezo wa kufanya hivi katika siku zijazo.

Labda una swali, kwa nini tulihusika katika hadithi hii na marekebisho ya pg_repack, na hatukutumia, kwa mfano, analogues zake? Wakati fulani sisi pia tulifikiri juu ya hili, lakini uzoefu mzuri wa kutumia mapema, kwenye meza bila vikwazo vilivyoahirishwa, ulituchochea kujaribu kuelewa kiini cha tatizo na kurekebisha. Kwa kuongezea, kutumia suluhisho zingine pia kunahitaji wakati wa kufanya vipimo, kwa hivyo tuliamua kwamba tutajaribu kwanza kurekebisha shida ndani yake, na ikiwa tutagundua kuwa hatuwezi kufanya hivi kwa wakati unaofaa, basi tutaanza kuangalia analogues. .

Matokeo

Tunachoweza kupendekeza kulingana na uzoefu wetu wenyewe:

Fuatilia uvimbe wako. Kulingana na data ya ufuatiliaji, unaweza kuelewa jinsi otomatiki inavyosanidiwa.
Rekebisha AUTOVACUUM ili kudumisha uvimbe katika kiwango kinachokubalika.
Ikiwa bloat bado inakua na huwezi kuondokana nayo kwa kutumia zana za nje ya sanduku, usiogope kutumia upanuzi wa nje. Jambo kuu ni kupima kila kitu vizuri.
Usiogope kurekebisha suluhu za nje ili kukidhi mahitaji yako - wakati mwingine hii inaweza kuwa bora zaidi na hata rahisi zaidi kuliko kubadilisha msimbo wako mwenyewe.

Chanzo: mapenzi.com

Postgres: bloat, pg_repack na vikwazo vilivyoahirishwa