tomonidan ko'tarilgan katta ma'lumotlar oqimlarini yozib olish mavzusini davom ettirish , bunda biz qanday usullarni ko'rib chiqamiz saqlangan "jismoniy" hajmini kamaytirish PostgreSQL-da va ularning server ishlashiga ta'siri.
haqida gaplashamiz TOAST sozlamalari va ma'lumotlarni moslashtirish. "O'rtacha" bu usullar juda ko'p resurslarni saqlamaydi, lekin dastur kodini umuman o'zgartirmasdan.
Biroq, bizning tajribamiz bu borada juda samarali bo'ldi, chunki deyarli har qanday monitoring o'z tabiatiga ko'ra saqlanadi asosan faqat qo'shiladi qayd etilgan ma'lumotlar nuqtai nazaridan. Va agar siz ma'lumotlar bazasini diskka yozishni qanday o'rgatishingiz mumkinligi haqida qiziqsangiz 200MB / s yarmi ko'p - mushuk ostida iltimos.
Katta ma'lumotlarning kichik sirlari
Ish profili bo'yicha , ular muntazam ravishda inidan unga uchib ketishadi matn paketlari.
Va shundan beri kimning ma'lumotlar bazasi biz kuzatib boruvchi murakkab ma'lumotlar tuzilmalariga ega bo'lgan ko'p komponentli mahsulot, so'ngra so'rovlar maksimal ishlash uchun juda shunday chiqadi . Shunday qilib, bizga kelgan jurnaldagi so'rovning har bir alohida nusxasining hajmi yoki natijada bajarilish rejasi "o'rtacha" juda katta bo'lib chiqadi.
Keling, biz "xom" ma'lumotlarni yozadigan jadvallardan birining tuzilishini ko'rib chiqaylik, ya'ni bu erda jurnal yozuvidagi asl matn:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);Oddiy belgi (albatta bo'limga bo'lingan, shuning uchun bu bo'lim shablonidir), bu erda eng muhim narsa matn. Ba'zan juda katta hajmli.
Eslatib o'tamiz, PG-dagi bitta yozuvning "jismoniy" o'lchami bir sahifadan ortiq ma'lumotlarni egallashi mumkin emas, ammo "mantiqiy" o'lcham butunlay boshqa masala. Maydonga hajmli qiymatni (varchar/matn/bayt) yozish uchun foydalaning :
PostgreSQL qat'iy sahifa hajmidan foydalanadi (odatda 8 KB) va kortejlar bir nechta sahifalarni qamrab olishga ruxsat bermaydi. Shuning uchun, juda katta maydon qiymatlarini bevosita saqlash mumkin emas. Ushbu cheklovni bartaraf etish uchun katta maydon qiymatlari bir nechta jismoniy chiziqlar bo'ylab siqiladi va/yoki bo'linadi. Bu foydalanuvchi tomonidan sezilmasdan sodir bo'ladi va ko'pchilik server kodlariga ozgina ta'sir qiladi. Bu usul TOAST deb nomlanadi...
Aslida, "potentsial katta" maydonlarga ega har bir jadval uchun avtomatik ravishda 2KB segmentlardagi har bir "katta" yozuv:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Ya'ni, agar biz "katta" qiymatga ega satr yozishimiz kerak bo'lsa data, keyin haqiqiy yozish sodir bo'ladi nafaqat asosiy stolga va uning PK ga, balki TOAST va uning PK ga ham.
TOAST ta'sirini kamaytirish
Ammo bizning rekordlarimizning aksariyati hali ham unchalik katta emas, 8 KB ga mos kelishi kerak - Qanday qilib pulni tejashim mumkin?..
Bu atribut bizning yordamimizga keladi jadval ustunida:
- Uzaytirildi ham siqishni, ham alohida saqlash imkonini beradi. Bu standart variant ko'pchilik TOAST mos ma'lumotlar turlari uchun. U birinchi navbatda siqishni amalga oshirishga harakat qiladi, keyin qator hali ham juda katta bo'lsa, uni jadvaldan tashqarida saqlaydi.
- xanda siqish imkonini beradi, lekin alohida saqlashga ruxsat bermaydi. (Aslida, bunday ustunlar uchun alohida saqlash hali ham amalga oshiriladi, lekin faqat oxirgi chora sifatida, satrni sahifaga sig'ishi uchun qisqartirishning boshqa usuli bo'lmaganda.)
Aslida, bu bizga matn uchun kerak bo'lgan narsa - uni iloji boricha siqib qo'ying va agar u umuman mos kelmasa, uni TOASTga qo'ying. Buni bitta buyruq bilan to'g'ridan-to'g'ri bajarish mumkin:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Effektni qanday baholash mumkin
Ma'lumotlar oqimi har kuni o'zgarganligi sababli, biz mutlaq raqamlarni taqqoslay olmaymiz, lekin nisbiy jihatdan kichikroq ulush Biz buni TOASTda yozdik - shuncha yaxshi. Ammo bu erda xavf bor - har bir alohida yozuvning "jismoniy" hajmi qanchalik katta bo'lsa, indeks shunchalik "kengroq" bo'ladi, chunki biz ko'proq ma'lumotlar sahifalarini qamrab olishimiz kerak.
Bo'lim o'zgarishlardan oldin:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Bo'lim o'zgarishlardan keyin:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Aslida, biz TOASTga 2 marta kamroq yozishni boshladi, bu nafaqat diskni, balki protsessorni ham tushirdi:
Shuni ta'kidlab o'tamanki, biz diskni "o'qishda" nafaqat "yozish"da ham kichikroq bo'lib qoldik, chunki jadvalga yozuv qo'shganda, uning qaysi ekanligini aniqlash uchun biz har bir indeks daraxtining bir qismini "o'qishimiz" kerak. ulardagi kelajakdagi pozitsiya.
Kim PostgreSQL 11 da yaxshi yashashi mumkin
PG11 ga yangilangandan so'ng, biz TOASTni "sozlash" ni davom ettirishga qaror qildik va ushbu versiyadan boshlab parametr o'zgarganligini payqadik. :
TOAST ishlov berish kodi faqat jadvalda saqlanadigan satr qiymati TOAST_TUPLE_THRESHOLD baytdan (odatda 2 KB) katta bo'lganda ishga tushadi. TOAST kodi satr qiymati TOAST_TUPLE_TARGET baytdan kam bo'lguncha (o'zgaruvchan qiymat, odatda 2 KB) yoki o'lchamni qisqartirib bo'lmaguncha maydon qiymatlarini jadvaldan siqib chiqaradi va/yoki ko'chiradi.
Biz odatda bizda mavjud bo'lgan ma'lumotlar "juda qisqa" yoki "juda uzoq" deb qaror qildik, shuning uchun biz o'zimizni minimal mumkin bo'lgan qiymat bilan cheklashga qaror qildik:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Keling, yangi sozlamalar qayta konfiguratsiyadan keyin diskni yuklashga qanday ta'sir qilganini ko'rib chiqaylik:
Yomon emas! O'rtacha diskdagi navbat kamaydi taxminan 1.5 marta, disk esa "band" 20 foizni tashkil qiladi! Ammo bu qandaydir tarzda protsessorga ta'sir qilgandir?
Hech bo'lmaganda yomonlashmadi. Shunga qaramay, hatto bunday hajmlar ham o'rtacha protsessor yukini ko'tarolmaydimi, degan xulosaga kelish qiyin 5%.
Shartlar joylarini o'zgartirish orqali yig'indisi... o'zgaradi!
Ma'lumki, bir tiyin bir rublni tejaydi va bizning saqlash hajmlarimiz bilan bu haqida 10TB/oy hatto bir oz optimallashtirish yaxshi foyda berishi mumkin. Shuning uchun biz ma'lumotlarimizning jismoniy tuzilishiga e'tibor qaratdik - aniq qanday Yozuv ichidagi "to'plangan" maydonlar jadvallarning har biri.
Chunki, chunki bu to'g'ridan-to'g'ri oldinga :
Ko'pgina arxitekturalar mashina so'zlari chegaralarida ma'lumotlarni tekislashni ta'minlaydi. Misol uchun, 32-bitli x86 tizimida butun sonlar (butun son turi, 4 bayt) 4 baytlik so'z chegarasida, shuningdek, ikki barobar aniqlikdagi suzuvchi nuqta raqamlari (ikki marta aniqlikdagi suzuvchi nuqta, 8 bayt) tekislanadi. Va 64-bitli tizimda ikki tomonlama qiymatlar 8 baytlik so'z chegaralariga moslashtiriladi. Bu mos kelmaslikning yana bir sababi.
Hizalama tufayli jadval qatorining o'lchami maydonlar tartibiga bog'liq. Odatda bu ta'sir juda sezilarli emas, lekin ba'zi hollarda bu hajmning sezilarli darajada oshishiga olib kelishi mumkin. Misol uchun, agar siz char(1) va butun son maydonlarini aralashtirsangiz, ular orasida odatda 3 bayt sarflanadi.
Sintetik modellardan boshlaylik:
SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байтBirinchi holatda bir nechta qo'shimcha bayt qaerdan kelgan? Hammasi oddiy - 2 bayt chegarasida tekislangan 4 baytlik kichik keyingi maydondan oldin va u oxirgi bo'lganda, hech narsa yo'q va tekislashning hojati yo'q.
Nazariy jihatdan, hamma narsa yaxshi va siz maydonlarni xohlaganingizcha o'zgartirishingiz mumkin. Keling, kundalik bo'limi 10-15 Gb ni egallagan jadvallardan birining misolidan foydalanib, uni haqiqiy ma'lumotlarda tekshiramiz.
Dastlabki tuzilishi:
CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan: pack uuid NOT NULL,
-- Унаследована from table plan: recno smallint NOT NULL,
-- Унаследована from table plan: host uuid,
-- Унаследована from table plan: ts timestamp with time zone,
-- Унаследована from table plan: exectime numeric(32,3),
-- Унаследована from table plan: duration numeric(32,3),
-- Унаследована from table plan: bufint bigint,
-- Унаследована from table plan: bufmem bigint,
-- Унаследована from table plan: bufdsk bigint,
-- Унаследована from table plan: apn uuid,
-- Унаследована from table plan: ptr uuid,
-- Унаследована from table plan: dt date,
CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)Ustun tartibini o'zgartirgandan keyingi bo'lim - aniq bir xil maydonlar, faqat boshqacha tartib:
CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan: dt date NOT NULL,
-- Унаследована from table plan: ts timestamp with time zone,
-- Унаследована from table plan: pack uuid NOT NULL,
-- Унаследована from table plan: recno smallint NOT NULL,
-- Унаследована from table plan: host uuid,
-- Унаследована from table plan: apn uuid,
-- Унаследована from table plan: ptr uuid,
-- Унаследована from table plan: bufint bigint,
-- Унаследована from table plan: bufmem bigint,
-- Унаследована from table plan: bufdsk bigint,
-- Унаследована from table plan: exectime numeric(32,3),
-- Унаследована from table plan: duration numeric(32,3),
CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)
Bo'limning umumiy hajmi "faktlar" soni bilan belgilanadi va faqat tashqi jarayonlarga bog'liq, shuning uchun uyning hajmini ajratamiz (pg_relation_size) undagi yozuvlar soni bo'yicha - ya'ni biz olamiz haqiqiy saqlangan yozuvning o'rtacha hajmi:
Minus 6% hajm, Ajoyib!
Ammo hamma narsa, albatta, unchalik qizg'ish emas - axir, indekslarda biz maydonlar tartibini o'zgartira olmaymiz, va shuning uchun "umuman" (pg_total_relation_size) ...
...hali ham shu yerda 1.5% tejaldibitta kod satrini o'zgartirmasdan. Ha, ha!
Shuni ta'kidlaymanki, maydonlarni tartibga solishning yuqoridagi varianti bu eng maqbul emas. Chunki siz estetik sabablarga ko'ra ba'zi maydonlarni "yirtib tashlashni" xohlamaysiz - masalan, er-xotin (pack, recno), bu jadval uchun PK hisoblanadi.
Umuman olganda, dalalarning "minimal" tartibini aniqlash juda oddiy "qo'pol kuch" vazifasidir. Shunday qilib, siz o'zingizning ma'lumotlaringizdan biznikidan ham yaxshiroq natijalarga erishishingiz mumkin - sinab ko'ring!
Manba: www.habr.com