Bloatning jadvallar va indekslarga ta'siri keng ma'lum va nafaqat Postgresda mavjud. VACUUM FULL yoki CLUSTER kabi qutidan tashqarida uni hal qilishning usullari mavjud, ammo ular ish paytida jadvallarni qulflaydi va shuning uchun har doim ham foydalana olmaydi.
Maqolada shishish qanday paydo bo'lishi, u bilan qanday kurashish mumkinligi, kechiktirilgan cheklovlar va pg_repack kengaytmasidan foydalanishga olib keladigan muammolar haqida bir oz nazariya mavjud.
Ushbu maqola asosida yozilgan PgConf.Russia 2020 da.
Nima uchun shish paydo bo'ladi?
Postgres ko'p versiyali modelga asoslangan (). Uning mohiyati shundaki, jadvaldagi har bir satr bir nechta versiyalarga ega bo'lishi mumkin, tranzaktsiyalar esa ushbu versiyalarning bir nechtasini ko'rmaydi, lekin bir xil bo'lishi shart emas. Bu bir vaqtning o'zida bir nechta tranzaktsiyalarni amalga oshirishga imkon beradi va bir-biriga deyarli ta'sir qilmaydi.
Shubhasiz, bu versiyalarning barchasi saqlanishi kerak. Postgres xotira sahifasi bilan ishlaydi va sahifa diskdan o'qilishi yoki yozilishi mumkin bo'lgan ma'lumotlarning minimal miqdoridir. Bu qanday sodir bo'lishini tushunish uchun kichik bir misolni ko'rib chiqaylik.
Aytaylik, bizda bir nechta yozuvlar qo'shilgan jadval mavjud. Jadval saqlanadigan faylning birinchi sahifasida yangi ma'lumotlar paydo bo'ldi. Bular qatorlarning jonli versiyalari boʻlib, ular majburiyatdan keyin boshqa tranzaktsiyalar uchun mavjud (oddiylik uchun biz izolyatsiya darajasi oʻqilgan deb taxmin qilamiz).
Keyin biz yozuvlardan birini yangiladik, shu bilan eski versiyani endi tegishli emas deb belgiladik.
Bosqichma-bosqich, satr versiyalarini yangilash va o'chirish, biz ma'lumotlarning taxminan yarmi "axlat" bo'lgan sahifaga ega bo'ldik. Bu maʼlumotlar hech qanday tranzaksiyaga koʻrinmaydi.
Postgresning mexanizmi bor , bu eskirgan versiyalarni tozalaydi va yangi ma'lumotlar uchun joy ochadi. Ammo agar u etarlicha agressiv tarzda sozlanmagan bo'lsa yoki boshqa jadvallarda ishlash bilan band bo'lsa, unda "axlat ma'lumotlari" qoladi va biz yangi ma'lumotlar uchun qo'shimcha sahifalardan foydalanishimiz kerak.
Shunday qilib, bizning misolimizda, bir vaqtning o'zida jadval to'rt sahifadan iborat bo'ladi, lekin uning faqat yarmi jonli ma'lumotlarni o'z ichiga oladi. Natijada, jadvalga kirishda biz kerak bo'lgandan ko'ra ko'proq ma'lumotlarni o'qiymiz.
VACUUM endi barcha ahamiyatsiz qator versiyalarini o'chirib tashlasa ham, vaziyat keskin yaxshilanmaydi. Bizda yangi qatorlar uchun sahifalarda yoki hatto butun sahifalarda bo'sh joy bo'ladi, lekin biz hali ham kerak bo'lgandan ko'ra ko'proq ma'lumotlarni o'qiymiz.
Aytgancha, agar fayl oxirida butunlay bo'sh sahifa (bizning misolimizda ikkinchisi) bo'lsa, VACUUM uni kesishi mumkin edi. Ammo hozir u o'rtada, shuning uchun u bilan hech narsa qilish mumkin emas.
Bunday bo'sh yoki juda siyrak sahifalar soni kattalashganda, bu bloat deb ataladi, bu ishlashga ta'sir qila boshlaydi.
Yuqorida tavsiflangan hamma narsa jadvallarda shish paydo bo'lishining mexanikasi. Indekslarda bu xuddi shunday tarzda sodir bo'ladi.
Menda shish bormi?
Sizda shishiradi yoki yo'qligini aniqlashning bir necha yo'li mavjud. Birinchisining g'oyasi - jadvallardagi qatorlar soni, "jonli" qatorlar soni va boshqalar haqida taxminiy ma'lumotlarni o'z ichiga olgan ichki Postgres statistikasidan foydalanish. Internetda siz tayyor skriptlarning ko'plab variantlarini topishingiz mumkin. Biz asos qilib oldik PostgreSQL ekspertlaridan, ular tost va bloat btree indekslari bilan birga bloat jadvallarini baholashi mumkin. Bizning tajribamizga ko'ra, uning xatosi 10-20% ni tashkil qiladi.
Yana bir usul - kengaytmadan foydalanish , bu sizga sahifalarni ko'rib chiqish va taxminiy va aniq shishgan qiymatni olish imkonini beradi. Ammo ikkinchi holda, siz butun jadvalni skanerlashingiz kerak bo'ladi.
Biz 20% gacha bo'lgan kichik shishiruvchi qiymatni maqbul deb hisoblaymiz. Uni to'ldiruvchining analogi sifatida ko'rib chiqish mumkin и . 50% va undan yuqori bo'lsa, ishlash muammolari boshlanishi mumkin.
Shish bilan kurashish usullari
Postgresda shishishni bartaraf etishning bir necha usullari mavjud, ammo ular har doim ham hamma uchun mos emas.
Shishish paydo bo'lmasligi uchun AUTOVAKUUMni sozlang. Yoki aniqrog'i, uni siz uchun maqbul darajada ushlab turish uchun. Bu "kapitan" maslahatiga o'xshaydi, lekin aslida bunga erishish har doim ham oson emas. Misol uchun, sizda ma'lumotlar sxemasiga muntazam o'zgarishlar kiritilgan faol rivojlanish mavjud yoki ma'lumotlarning qandaydir ko'chishi amalga oshirilmoqda. Natijada, sizning yuk profilingiz tez-tez o'zgarishi mumkin va odatda jadvaldan jadvalga o'zgaradi. Bu siz doimo bir oz oldinda ishlashingiz va AUTOVACUUMni har bir jadvalning o'zgaruvchan profiliga moslashingiz kerakligini anglatadi. Ammo buni amalga oshirish oson emasligi aniq.
AUTOVACUUM jadvallar bilan ishlay olmasligining yana bir keng tarqalgan sababi shundaki, bu tranzaktsiyalar uchun mavjud bo'lgan ma'lumotlarni tozalashga to'sqinlik qiladigan uzoq muddatli tranzaktsiyalar mavjud. Bu erda tavsiya ham yaqqol ko'rinib turibdi - "o'ralgan" tranzaktsiyalardan xalos bo'ling va faol tranzaktsiyalar vaqtini minimallashtiring. Ammo agar sizning ilovangizdagi yuk OLAP va OLTP gibrid bo'lsa, siz bir vaqtning o'zida ko'plab tez-tez yangilanishlar va qisqa so'rovlarga, shuningdek, uzoq muddatli operatsiyalarga ega bo'lishingiz mumkin - masalan, hisobot yaratish. Bunday vaziyatda yukni turli xil bazalar bo'ylab tarqatish haqida o'ylash arziydi, bu ularning har birini yanada nozik sozlash imkonini beradi.
Yana bir misol - profil bir hil bo'lsa ham, lekin ma'lumotlar bazasi juda yuqori yuk ostida bo'lsa, hatto eng tajovuzkor AUTOVACUUM ham bardosh bera olmaydi va shish paydo bo'ladi. Masshtablash (vertikal yoki gorizontal) yagona yechimdir.
AUTOVACUUMni o'rnatgan vaziyatda nima qilish kerak, ammo shishish o'sishda davom etmoqda.
komanda VAKUUM TO'LI jadvallar va indekslar tarkibini qayta tiklaydi va ularda faqat tegishli ma'lumotlarni qoldiradi. Shishishni yo'q qilish uchun u mukammal ishlaydi, lekin uni bajarish paytida stolda eksklyuziv qulf olinadi (AccessExclusiveLock), bu jadvalda so'rovlarni bajarishga ruxsat bermaydi, hatto tanlaydi. Agar siz o'z xizmatingizni yoki uning bir qismini bir muddat to'xtatib qo'ysangiz (ma'lumotlar bazasi va uskunangiz hajmiga qarab o'nlab daqiqalardan bir necha soatgacha), unda bu variant eng yaxshisidir. Afsuski, rejalashtirilgan texnik xizmat ko'rsatish vaqtida VACUUM FULL ni ishga tushirishga vaqtimiz yo'q, shuning uchun bu usul biz uchun mos emas.
komanda KLUSTER Jadvallar mazmunini VACUUM FULL bilan bir xil tarzda qayta tiklaydi, lekin sizga indeksni belgilash imkonini beradi, unga ko'ra ma'lumotlar diskda jismoniy tartiblanadi (lekin kelajakda yangi qatorlar uchun tartib kafolatlanmaydi). Muayyan holatlarda, bu bir qator so'rovlar uchun yaxshi optimallashtirish - indeks bo'yicha bir nechta yozuvlarni o'qish bilan. Buyruqning kamchiliklari VACUUM FULL bilan bir xil - u ish paytida stolni qulflaydi.
komanda REINDEX oldingi ikkitasiga o'xshash, lekin ma'lum bir indeksni yoki jadvalning barcha indekslarini qayta tiklaydi. Qulflar biroz zaifroq: jadvaldagi ShareLock (oʻzgartirishlarni oldini oladi, lekin tanlashga ruxsat beradi) va qayta tiklanayotgan indeksdagi AccessExclusiveLock (ushbu indeks yordamida soʻrovlarni bloklaydi). Biroq, Postgresning 12-versiyasida parametr paydo bo'ldi , bu sizga bir vaqtning o'zida yozuvlarni qo'shish, o'zgartirish yoki o'chirishni bloklamasdan indeksni qayta tiklashga imkon beradi.
Postgres-ning oldingi versiyalarida siz REINDEX CONCURRENTLY-dan foydalangan holda natijaga erishishingiz mumkin. . Bu sizga indeksni qat'iy blokirovkasiz yaratishga imkon beradi (Paralel so'rovlarga xalaqit bermaydigan ShareUpdateExclusiveLock), keyin eski indeksni yangisiga almashtiring va eski indeksni o'chiring. Bu sizning ilovangizga aralashmasdan indeks shishishini yo'q qilish imkonini beradi. Indekslarni qayta tiklashda disk quyi tizimida qo'shimcha yuk bo'lishini hisobga olish kerak.
Shunday qilib, agar indekslar uchun shishishni "tezda" yo'q qilish usullari mavjud bo'lsa, jadvallar uchun yo'q. Bu erda turli xil tashqi kengaytmalar o'ynaydi: (ilgari pg_reorg), , va boshqalar. Ushbu maqolada men ularni taqqoslamayman va faqat pg_repack haqida gapiraman, ba'zi o'zgarishlardan keyin biz o'zimiz foydalanamiz.
pg_repack qanday ishlaydi
Aytaylik, bizda mutlaqo oddiy jadval bor - indekslar, cheklovlar va, afsuski, shishish bilan. pg_repack ning birinchi bosqichi ishlayotgan vaqtda barcha o'zgarishlar haqida ma'lumotlarni saqlash uchun jurnal jadvalini yaratishdir. Trigger bu o'zgarishlarni har bir qo'shish, yangilash va o'chirish uchun takrorlaydi. Keyin ma'lumotlarni kiritish jarayonini sekinlashtirmaslik uchun tuzilishi bo'yicha asl jadvalga o'xshash, lekin indekslar va cheklovlarsiz jadval tuziladi.
Keyinchalik, pg_repack eski jadvaldan ma'lumotlarni yangi jadvalga o'tkazadi, avtomatik ravishda barcha ahamiyatsiz qatorlarni filtrlaydi va keyin yangi jadval uchun indekslarni yaratadi. Ushbu barcha operatsiyalarni bajarish jarayonida o'zgarishlar jurnallar jadvalida to'planadi.
Keyingi qadam o'zgarishlarni yangi jadvalga o'tkazishdir. Migratsiya bir nechta iteratsiyalar davomida amalga oshiriladi va jurnal jadvalida 20 dan kam yozuvlar qolsa, pg_repack kuchli qulfga ega bo'ladi, eng so'nggi ma'lumotlarni ko'chiradi va Postgres tizim jadvallarida eski jadvalni yangisiga almashtiradi. Bu stol bilan ishlay olmaydigan yagona va juda qisqa vaqt. Shundan so'ng, eski jadval va jurnallar bilan jadval o'chiriladi va fayl tizimida bo'sh joy bo'shatiladi. Jarayon tugallandi.
Nazariy jihatdan hamma narsa ajoyib ko'rinadi, lekin amalda nima sodir bo'ladi? Biz pg_repackni yuksiz va yuk ostida sinovdan o'tkazdik va muddatidan oldin to'xtab qolganda (boshqacha aytganda, Ctrl+C yordamida) uning ishlashini tekshirdik. Barcha testlar ijobiy chiqdi.
Biz oziq-ovqat do'koniga bordik - keyin hamma narsa biz kutgandek bo'lmadi.
Birinchi krep sotuvda
Birinchi klasterda biz noyob cheklovning buzilishi haqida xatolik oldik:
$ ./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.
Ushbu cheklov avtomatik ravishda yaratilgan index_16508 nomiga ega edi - u pg_repack tomonidan yaratilgan. Uning tarkibiga kiritilgan atributlarga asoslanib, biz unga mos keladigan "bizning" cheklovimizni aniqladik. Muammo shundaki, bu mutlaqo oddiy cheklov emas, balki kechiktirilgan (), ya'ni. uning tekshiruvi sql buyrug'idan kechroq amalga oshiriladi, bu esa kutilmagan oqibatlarga olib keladi.
Kechiktirilgan cheklovlar: nima uchun ular kerak va ular qanday ishlaydi
Kechiktirilgan cheklovlar haqida bir oz nazariya.
Oddiy misolni ko'rib chiqaylik: bizda ikkita atributga ega bo'lgan mashinalar jadvali-ma'lumotnomasi mavjud - katalogdagi avtomobilning nomi va tartibi.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Aytaylik, birinchi va ikkinchi mashinani almashtirishimiz kerak edi. To'g'ridan-to'g'ri yechim birinchi qiymatni ikkinchisiga, ikkinchisini esa birinchisiga yangilashdir:
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Ammo biz ushbu kodni ishga tushirganimizda, biz cheklovlar buzilishini kutamiz, chunki jadvaldagi qiymatlarning tartibi noyobdir:
[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.
Qanday qilib buni boshqacha qilishim mumkin? Birinchi variant: jadvalda mavjud emasligi kafolatlangan buyurtmaga qo'shimcha qiymat o'rnini qo'shing, masalan, "-1". Dasturlashda bu "ikki o'zgaruvchining qiymatlarini uchdan biriga almashish" deb ataladi. Ushbu usulning yagona kamchiliklari qo'shimcha yangilanishdir.
Ikkinchi variant: Butun sonlar oʻrniga buyurtma qiymati uchun suzuvchi nuqta maʼlumotlar turidan foydalanish uchun jadvalni qayta loyihalash. Keyin, qiymatni 1 dan, masalan, 2.5 ga yangilashda, birinchi yozuv avtomatik ravishda ikkinchi va uchinchi o'rtasida "turadi". Bu yechim ishlaydi, lekin ikkita cheklov mavjud. Birinchidan, agar qiymat interfeysning biror joyida ishlatilsa, u siz uchun ishlamaydi. Ikkinchidan, ma'lumotlar turining aniqligiga qarab, barcha yozuvlarning qiymatlarini qayta hisoblashdan oldin sizda cheklangan miqdordagi mumkin bo'lgan qo'shimchalar bo'ladi.
Uchinchi variant: cheklovni faqat bajarilish vaqtida tekshirilishi uchun kechiktiring:
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Bizning dastlabki so'rovimiz mantig'i barcha qiymatlar qabul qilish vaqtida yagona bo'lishini ta'minlaganligi sababli, u muvaffaqiyatli bo'ladi.
Yuqorida muhokama qilingan misol, albatta, juda sintetik, ammo bu fikrni ochib beradi. Ilovamizda biz foydalanuvchilar bir vaqtning o'zida doskadagi umumiy vidjet ob'ektlari bilan ishlaganda nizolarni hal qilish uchun mas'ul bo'lgan mantiqni amalga oshirish uchun kechiktirilgan cheklovlardan foydalanamiz. Bunday cheklovlardan foydalanish dastur kodini biroz soddalashtirishga imkon beradi.
Umuman olganda, cheklash turiga qarab, Postgres ularni tekshirish uchun uchta granülerlik darajasiga ega: qator, tranzaksiya va ifoda darajalari.
Manba:
CHECK va NOT NULL har doim satr darajasida tekshiriladi; jadvaldan ko'rinib turibdiki, boshqa cheklovlar uchun turli xil variantlar mavjud. Siz ko'proq o'qishingiz mumkin .
Qisqacha xulosa qilish uchun, bir qator vaziyatlarda kechiktirilgan cheklovlar o'qilishi mumkin bo'lgan kod va kamroq buyruqlarni beradi. Biroq, buning uchun disk raskadrovka jarayonini murakkablashtirish orqali to'lashingiz kerak, chunki xatolik yuzaga kelgan paytdan boshlab va siz bu haqda bilib olgan vaqtingiz o'z vaqtida ajratiladi. Yana bir mumkin bo'lgan muammo shundaki, agar so'rov kechiktirilgan cheklovni o'z ichiga olsa, rejalashtiruvchi har doim ham optimal rejani tuza olmaydi.
pg_repackni takomillashtirish
Biz kechiktirilgan cheklovlar nima ekanligini ko'rib chiqdik, ammo ular bizning muammomiz bilan qanday bog'liq? Oldin olgan xatoni eslaylik:
$ ./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.Ma'lumotlar jurnal jadvalidan yangi jadvalga ko'chirilganda paydo bo'ladi. Bu g'alati ko'rinadi, chunki ... jurnal jadvalidagi ma'lumotlar manba jadvalidagi ma'lumotlar bilan birga amalga oshiriladi. Agar ular asl jadvalning cheklovlarini qondirsa, qanday qilib ular yangi jadvalda bir xil cheklovlarni buzishi mumkin?
Ma'lum bo'lishicha, muammoning ildizi pg_repack ning oldingi bosqichida yotadi, u faqat indekslarni yaratadi, lekin cheklovlar emas: eski jadvalda o'ziga xos cheklov bor edi, yangisi esa o'rniga noyob indeks yaratdi.
Bu erda shuni ta'kidlash kerakki, agar cheklov normal bo'lsa va kechiktirilmasa, uning o'rniga yaratilgan noyob indeks ushbu cheklovga ekvivalent bo'ladi, chunki Postgres-dagi noyob cheklovlar noyob indeks yaratish orqali amalga oshiriladi. Ammo kechiktirilgan cheklov holatida xatti-harakatlar bir xil emas, chunki indeksni kechiktirish mumkin emas va har doim sql buyrug'i bajarilganda tekshiriladi.
Shunday qilib, muammoning mohiyati tekshirishning "kechikishida" yotadi: asl jadvalda u bajarilish vaqtida va yangi jadvalda sql buyrug'i bajarilganda sodir bo'ladi. Bu shuni anglatadiki, biz tekshiruvlar ikkala holatda ham bir xil bajarilishiga ishonch hosil qilishimiz kerak: yoki har doim kechiktiriladi yoki har doim darhol.
Xo'sh, bizda qanday g'oyalar bor edi?
Kechiktirilganga o'xshash indeks yarating
Birinchi g'oya ikkala tekshiruvni darhol rejimda bajarishdir. Bu bir nechta noto'g'ri ijobiy cheklovlarni keltirib chiqarishi mumkin, ammo agar ular kam bo'lsa, bu foydalanuvchilarning ishiga ta'sir qilmasligi kerak, chunki bunday nizolar ular uchun odatiy holdir. Ular, masalan, ikkita foydalanuvchi bir vaqtning o'zida bir xil vidjetni tahrirlashni boshlaganda va ikkinchi foydalanuvchining mijozi birinchi foydalanuvchi tomonidan tahrirlash uchun vidjet allaqachon bloklanganligi haqida ma'lumot olishga ulgurmaganida paydo bo'ladi. Bunday holatda, server ikkinchi foydalanuvchini rad etadi va uning mijozi o'zgarishlarni orqaga qaytaradi va vidjetni bloklaydi. Biroz vaqt o'tgach, birinchi foydalanuvchi tahrirlashni tugatgandan so'ng, ikkinchisi vidjet endi bloklanmaganligi va o'z harakatlarini takrorlashi mumkinligi haqida ma'lumot oladi.
Tekshiruvlar har doim kechiktirilmagan rejimda bo'lishini ta'minlash uchun biz asl kechiktirilgan cheklovga o'xshash yangi indeks yaratdik:
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;Sinov muhitida biz faqat bir nechta kutilgan xatolarni oldik. Muvaffaqiyat! Biz pg_repack-ni ishlab chiqarishda yana ishga tushirdik va bir soatlik ishda birinchi klasterda 5 ta xatoga yo'l qo'ydik. Bu maqbul natijadir. Biroq, allaqachon ikkinchi klasterda xatolar soni sezilarli darajada oshdi va biz pg_repackni to'xtatishga majbur bo'ldik.
Nima uchun bu sodir bo'ldi? Xatolik yuzaga kelishi ehtimoli bir vaqtning o'zida bir xil vidjetlar bilan qancha foydalanuvchi ishlayotganiga bog'liq. Ko'rinishidan, o'sha paytda birinchi klasterda saqlangan ma'lumotlar bilan raqobatbardosh o'zgarishlar boshqalarga qaraganda ancha kam edi, ya'ni. biz shunchaki "omadli" edik.
Fikr ish bermadi. O'sha paytda biz yana ikkita echimni ko'rdik: kechiktirilgan cheklovlardan voz kechish uchun dastur kodimizni qayta yozing yoki pg_repack-ni ular bilan ishlashni "o'rgating". Biz ikkinchisini tanladik.
Yangi jadvaldagi indekslarni asl jadvaldagi kechiktirilgan cheklovlar bilan almashtiring
Qayta ko'rib chiqishning maqsadi aniq edi - agar asl jadvalda kechiktirilgan cheklov bo'lsa, unda yangisi uchun indeks emas, balki bunday cheklovni yaratish kerak.
O'zgarishlarimizni sinab ko'rish uchun biz oddiy test yozdik:
- kechiktirilgan cheklov va bitta yozuv bilan jadval;
- mavjud yozuvga zid bo'lgan tsiklga ma'lumotlarni kiritish;
- yangilashni amalga oshiring - ma'lumotlar endi ziddiyatli emas;
- o'zgarishlarni amalga oshirish.
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;pg_repack-ning asl versiyasi har doim birinchi qo'shimchada ishdan chiqdi, o'zgartirilgan versiya xatosiz ishladi. Ajoyib.
Biz ishlab chiqarishga o'tamiz va ma'lumotlarni jurnal jadvalidan yangisiga nusxalashning xuddi shu bosqichida yana xatoga yo'l qo'yamiz:
$ ./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.Klassik vaziyat: hamma narsa sinov muhitida ishlaydi, lekin ishlab chiqarishda emas?!
APPLY_COUNT va ikkita partiyaning birlashmasi
Biz kodni tom ma'noda satr bo'yicha tahlil qilishni boshladik va muhim nuqtani aniqladik: ma'lumotlar jurnal jadvalidan yangisiga partiyalarda uzatiladi, APPLY_COUNT konstantasi partiyaning hajmini ko'rsatdi:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* there might be still some tuples, repeat. */
...
}Muammo shundaki, bir nechta operatsiyalar cheklovni buzishi mumkin bo'lgan dastlabki tranzaksiya ma'lumotlari uzatilganda, ikkita partiyaning birlashmasida tugashi mumkin - buyruqlarning yarmi birinchi partiyada, qolgan yarmi esa bajariladi. ikkinchisida. Va bu erda, omadingizga qarab: agar jamoalar birinchi partiyada hech narsani buzmasa, unda hamma narsa yaxshi, lekin agar ular bajarsa, xatolik yuz beradi.
APPLY_COUNT 1000 ta yozuvga teng, bu bizning sinovlarimiz nega muvaffaqiyatli bo'lganini tushuntiradi - ular "to'plamli ulanish" holatini qamrab olmagan. Biz ikkita buyruqdan foydalandik - kiritish va yangilash, shuning uchun ikkita buyruqning aniq 500 ta tranzaksiyalari har doim to'plamga joylashtirildi va biz hech qanday muammoga duch kelmadik. Ikkinchi yangilanishni qo'shgandan so'ng, tahririmiz ishlashni to'xtatdi:
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;Shunday qilib, keyingi vazifa bitta tranzaksiyada o'zgartirilgan asl jadvaldagi ma'lumotlar yangi jadvalda ham bitta tranzaksiya doirasida tugashiga ishonch hosil qilishdir.
To'plamdan voz kechish
Va yana ikkita yechimimiz bor edi. Birinchidan: keling, partiyalarga bo'lishdan butunlay voz kechaylik va ma'lumotlarni bitta tranzaksiyada o'tkazamiz. Ushbu yechimning afzalligi uning soddaligi edi - kerakli kod o'zgarishlari minimal edi (Aytgancha, pg_reorg eski versiyalarida xuddi shunday ishlagan). Ammo muammo bor - biz uzoq muddatli tranzaktsiyani yaratmoqdamiz va bu, avval aytib o'tilganidek, yangi shish paydo bo'lishi uchun tahdiddir.
Ikkinchi yechim murakkabroq, lekin, ehtimol, to'g'riroq: jurnallar jadvalida jadvalga ma'lumotlarni qo'shgan tranzaktsiya identifikatori bilan ustun yarating. Keyin, ma'lumotlarni nusxalashda, biz ularni ushbu atribut bo'yicha guruhlashimiz va tegishli o'zgarishlar birgalikda uzatilishini ta'minlashimiz mumkin. Partiya bir nechta tranzaktsiyalardan (yoki bitta yirik) tuziladi va uning hajmi ushbu tranzaktsiyalarda qancha ma'lumotlar o'zgartirilganiga qarab o'zgaradi. Shuni ta'kidlash kerakki, turli tranzaktsiyalar ma'lumotlari jurnal jadvaliga tasodifiy tartibda kirganligi sababli, uni avvalgidek ketma-ket o'qish mumkin bo'lmaydi. tx_id tomonidan filtrlangan har bir so'rov uchun seqscan juda qimmat, indeks kerak, lekin uni yangilash uchun ortiqcha xarajatlar tufayli usulni sekinlashtiradi. Umuman olganda, har doimgidek, siz biror narsani qurbon qilishingiz kerak.
Shunday qilib, biz birinchi variantdan boshlashga qaror qildik, chunki u oddiyroq. Birinchidan, uzoq tranzaktsiya haqiqiy muammo bo'ladimi yoki yo'qligini tushunish kerak edi. Ma'lumotlarning eski jadvaldan yangisiga asosiy uzatilishi ham bitta uzoq tranzaksiyada sodir bo'lganligi sababli, savol "bu tranzaksiyani qancha ko'paytiramiz?" ga aylandi. Birinchi tranzaksiyaning davomiyligi asosan jadval hajmiga bog'liq. Yangisining davomiyligi ma'lumotlarni uzatish paytida jadvalda qancha o'zgarishlar to'planishiga bog'liq, ya'ni. yukning intensivligi bo'yicha. pg_repack ishga tushirilishi minimal xizmat yuki vaqtida sodir bo'ldi va o'zgarishlar hajmi jadvalning asl hajmiga nisbatan nomutanosib ravishda kichik edi. Biz yangi tranzaksiya vaqtini e'tiborsiz qoldirishga qaror qildik (taqqoslash uchun, o'rtacha 1 soat 2-3 daqiqa).
Tajribalar ijobiy bo'ldi. Ishlab chiqarishni ham ishga tushiring. Aniqlik uchun bu erda ishlagandan keyin ma'lumotlar bazalaridan birining o'lchamiga ega rasm:
Ushbu yechim bizni to'liq qoniqtirganligi sababli, biz ikkinchisini amalga oshirishga harakat qilmadik, lekin biz uni kengaytmani ishlab chiquvchilar bilan muhokama qilish imkoniyatini ko'rib chiqmoqdamiz. Bizning joriy tahririmiz, afsuski, hali nashrga tayyor emas, chunki biz muammoni faqat kechiktirilgan noyob cheklovlar bilan hal qildik va to'liq yamoq uchun boshqa turlarni qo'llab-quvvatlash kerak. Kelajakda buni amalga oshirishga umid qilamiz.
Ehtimol sizda savol tug'ilishi mumkin, nega biz pg_repack modifikatsiyasi bilan ushbu voqeaga aralashdik va, masalan, uning analoglaridan foydalanmadik? Bir lahzada biz ham bu haqda o'yladik, lekin undan oldinroq, kechiktirilgan cheklovlarsiz jadvallarda foydalanishning ijobiy tajribasi bizni muammoning mohiyatini tushunishga va uni tuzatishga harakat qilishga undadi. Bundan tashqari, boshqa echimlardan foydalanish ham testlarni o'tkazish uchun vaqt talab etadi, shuning uchun biz birinchi navbatda undagi muammoni hal qilishga harakat qilishga qaror qildik va agar biz buni oqilona vaqt ichida qila olmasligimizni tushunsak, biz analoglarni ko'rib chiqishni boshlaymiz. .
topilmalar
O'z tajribamiz asosida nimani tavsiya qilishimiz mumkin:
- Oshqozoningizni kuzatib boring. Monitoring ma'lumotlariga asoslanib, siz avtovakuum qanchalik yaxshi tuzilganligini tushunishingiz mumkin.
- Shishishni maqbul darajada ushlab turish uchun AUTOVAKUUMni sozlang.
- Agar shishish hali ham o'sib borayotgan bo'lsa va siz tayyor vositalar yordamida uni bartaraf eta olmasangiz, tashqi kengaytmalardan foydalanishdan qo'rqmang. Asosiysi, hamma narsani yaxshi sinab ko'rish.
- Tashqi echimlarni ehtiyojlaringizga mos ravishda o'zgartirishdan qo'rqmang - ba'zida bu o'z kodingizni o'zgartirishdan ko'ra samaraliroq va hatto osonroq bo'lishi mumkin.
Manba: www.habr.com