PostgreSQL-də böyük həcmdə bir qəpik qənaət edin

tərəfindən gündəmə gətirilən böyük məlumat axınlarının qeyd mövzusunun davamı bölmə haqqında əvvəlki məqalə, bunda edə biləcəyiniz yollara baxacağıq saxlanan "fiziki" ölçüsünü azaltmaq PostgreSQL-də və onların server performansına təsiri.

haqqında danışacağıq TOAST parametrləri və məlumatların uyğunlaşdırılması. "Orta hesabla" bu üsullar çox resursa qənaət etməyəcək, lakin tətbiq kodunu ümumiyyətlə dəyişdirmədən.

Bununla belə, təcrübəmiz bu baxımdan çox məhsuldar oldu, çünki demək olar ki, hər hansı bir monitorinqin saxlanması təbiətinə görədir əsasən yalnız əlavə olunur qeydə alınan məlumatlar baxımından. Verilənlər bazasını diskə yazmağı necə öyrədə biləcəyinizlə maraqlanırsınızsa 200MB / s yarısı qədər - lütfən, pişik altında.

Böyük verilənlərin kiçik sirləri

İş profilinə görə xidmətimiz, onlar mütəmadi olaraq yuvalardan ona uçurlar mətn paketləri.

Və o vaxtdan bəri VLSI kompleksikimin monitorinq etdiyimiz verilənlər bazası mürəkkəb məlumat strukturlarına, sonra sorğulara malik çoxkomponentli məhsuldur maksimum performans üçün tamamilə belə çıxır Mürəkkəb alqoritmik məntiqlə “çoxhəcmli”. Beləliklə, bizə gələn jurnalda sorğunun hər bir fərdi nümunəsinin həcmi və ya nəticədə icra planı "orta hesabla" olduqca böyük olur.

Gəlin "xam" məlumatları yazdığımız cədvəllərdən birinin strukturuna baxaq - yəni log girişindən orijinal mətn budur:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

Tipik bir işarə (artıq bölünmüşdür, əlbəttə ki, bu bölmə şablonudur), burada ən vacib şey mətndir. Bəzən olduqca həcmlidir.

Xatırladaq ki, PG-də bir qeydin "fiziki" ölçüsü birdən çox məlumat səhifəsini tuta bilməz, lakin "məntiqi" ölçü tamamilə fərqli bir məsələdir. Sahəyə həcmli dəyər (varchar/text/bytea) yazmaq üçün istifadə edin TOAST texnologiyası:

PostgreSQL sabit bir səhifə ölçüsündən (adətən 8 KB) istifadə edir və çubuqların birdən çox səhifəni əhatə etməsinə icazə vermir. Buna görə də, çox böyük sahə dəyərlərini birbaşa saxlamaq mümkün deyil. Bu məhdudiyyəti aradan qaldırmaq üçün böyük sahə dəyərləri sıxılır və/və ya çoxlu fiziki xətlər üzrə bölünür. Bu, istifadəçi tərəfindən nəzərə alınmadan baş verir və əksər server koduna az təsir edir. Bu üsul TOAST kimi tanınır...

Əslində, "potensial olaraq böyük" sahələri olan hər bir cədvəl üçün avtomatik olaraq "dilimləmə" ilə qoşalaşmış cədvəl yaradılır 2KB seqmentlərdə hər bir "böyük" qeyd:

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Yəni “böyük” dəyəri olan bir sətir yazmalıyıqsa data, sonra real qeyd baş verəcək yalnız əsas masaya və onun PK-ya deyil, həm də TOAST və onun PK-ya.

TOAST təsirinin azaldılması

Amma rekordlarımızın çoxu hələ o qədər də böyük deyil, 8KB-a uyğun olmalıdır - Buna necə qənaət edə bilərəm?..

Burada atribut yardımımıza gəlir STORAGE masa sütununda:

• Uzadıldı həm sıxılma, həm də ayrı saxlama imkanı verir. Bu standart variant əksər TOAST uyğun məlumat növləri üçün. O, əvvəlcə sıxılma yerinə yetirməyə çalışır, sonra cərgə hələ də çox böyükdürsə, onu cədvəldən kənarda saxlayır.
• ANA sıxışdırmağa imkan verir, lakin ayrı saxlama deyil. (Əslində, bu cür sütunlar üçün hələ də ayrıca saxlama yerinə yetiriləcək, lakin yalnız son çarə kimi, sətri səhifəyə uyğunlaşdırmaq üçün kiçiltməyin başqa yolu olmadıqda.)

Əslində, mətn üçün bizə lazım olan budur - mümkün qədər sıxın və heç uyğun gəlmirsə, TOAST-a qoyun. Bu, bir əmrlə birbaşa olaraq edilə bilər:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Təsiri necə qiymətləndirmək olar

Məlumat axını hər gün dəyişdiyi üçün biz mütləq rəqəmləri müqayisə edə bilmərik, lakin nisbi mənada daha kiçik pay Biz bunu TOAST-da yazdıq - bir o qədər yaxşıdır. Ancaq burada bir təhlükə var - hər bir fərdi qeydin "fiziki" həcmi nə qədər böyükdürsə, indeks bir o qədər "geniş" olur, çünki biz daha çox məlumat səhifəsini əhatə etməliyik.

Bölmə dəyişikliklərdən əvvəl:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

Bölmə dəyişikliklərdən sonra:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Əslində biz TOAST-a 2 dəfə az yazmağa başladı, yalnız diski deyil, həm də CPU-nu boşaltdı:

Qeyd edim ki, biz diski “oxumaqda” da kiçilmişik, nəinki “yazmaq” – çünki cədvələ qeyd daxil edərkən, biz də onun hansı indeksini təyin etmək üçün onun bir hissəsini “oxumalıyıq”. onlarda gələcək mövqe.

PostgreSQL 11-də kim yaxşı yaşaya bilər

PG11-ə yenilədikdən sonra biz TOAST-ı "tənzimləməyə" davam etmək qərarına gəldik və bu versiyadan başlayaraq parametrin tənzimləmək üçün əlçatan olduğunu gördük. toast_tuple_target:

TOAST emal kodu yalnız cədvəldə saxlanacaq sıra dəyəri TOAST_TUPLE_THRESHOLD baytdan (adətən 2 KB) böyük olduqda işə düşür. TOAST kodu sətir dəyəri TOAST_TUPLE_TARGET baytından (dəyişən dəyər, həmçinin adətən 2 KB) az olana və ya ölçü kiçilə bilməyənə qədər sahə dəyərlərini sıxacaq və/yaxud cədvəldən kənara köçürür.

Biz qərara gəldik ki, adətən əlimizdə olan məlumat ya “çox qısa” və ya “çox uzun” olsun, ona görə də özümüzü minimum mümkün dəyərlə məhdudlaşdırmağa qərar verdik:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Yeni parametrlərin yenidən konfiqurasiyadan sonra diskin yüklənməsinə necə təsir etdiyini görək:

Pis deyil! Orta diskin növbəsi azalıb təxminən 1.5 dəfə, disk "məşğul" isə 20 faizdir! Ancaq bəlkə bu, CPU-ya birtəhər təsir etdi?

Ən azından daha da pisləşmədi. Baxmayaraq ki, hətta bu cür həcmlərin hələ də orta CPU yükünü artıra bilməyəcəyini mühakimə etmək çətindir 5%.

Şərtlərin yerlərini dəyişdirməklə cəmi... dəyişir!

Bildiyiniz kimi, bir qəpik bir rubla qənaət edir və saxlama həcmlərimizlə bu, təxminən 10TB/ay hətta kiçik optimallaşdırma yaxşı qazanc verə bilər. Buna görə də, məlumatlarımızın fiziki quruluşuna diqqət yetirdik - necə dəqiq Qeydin içərisində "yığılmış" sahələr masaların hər biri.

Çünki ona görə məlumatların uyğunlaşdırılması bu düz irəlidir yaranan həcmə təsir edir:

Bir çox arxitektura məlumatların maşın sözü sərhədlərində uyğunlaşdırılmasını təmin edir. Məsələn, 32 bitlik x86 sistemində tam ədədlər (tam ədəd növü, 4 bayt) 4 baytlıq söz sərhədində düzüləcək, eyni zamanda ikiqat dəqiqlikli üzən nöqtəli ədədlər də (ikiqat dəqiqlikli üzən nöqtə, 8 bayt). 64 bitlik sistemdə isə ikiqat dəyərlər 8 baytlıq söz sərhədlərinə uyğunlaşdırılacaq. Bu, uyğunsuzluğun başqa bir səbəbidir.

Hizalanmaya görə, cədvəl cərgəsinin ölçüsü sahələrin sırasından asılıdır. Adətən bu təsir çox nəzərə çarpan deyil, lakin bəzi hallarda ölçüdə əhəmiyyətli bir artıma səbəb ola bilər. Məsələn, char(1) və tam ədəd sahələrini qarışdırsanız, onlar arasında adətən 3 bayt sərf olunacaq.

Sintetik modellərdən başlayaq:

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 байт

Birinci halda bir neçə əlavə bayt haradan gəldi? Bu sadədir - 2 baytlıq sərhəddə düzülmüş 4 bayt kiçik növbəti sahədən əvvəl və sonuncu olanda heç bir şey yoxdur və hizalamağa ehtiyac yoxdur.

Teorik olaraq, hər şey yaxşıdır və sahələri istədiyiniz kimi yenidən təşkil edə bilərsiniz. Gündəlik bölməsi 10-15 GB olan cədvəllərdən birinin nümunəsindən istifadə edərək, onu real məlumatlar üzərində yoxlayaq.

İlkin quruluş:

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)

Sütun sırasını dəyişdirdikdən sonra bölmə - tam olaraq eyni sahələr, sadəcə fərqli sıra:

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)

Bölmənin ümumi həcmi "faktların" sayı ilə müəyyən edilir və yalnız xarici proseslərdən asılıdır, buna görə yığının ölçüsünü bölmək (pg_relation_size) içindəki qeydlərin sayına görə - yəni alırıq faktiki saxlanılan qeydin orta ölçüsü:

Mənfi 6% həcm, Əla!

Ancaq hər şey, əlbəttə ki, o qədər də çəhrayı deyil - axı, indekslərdə sahələrin sırasını dəyişə bilmərik, və buna görə də "ümumiyyətlə" (pg_total_relation_size) ...

...hələ də burada 1.5% qənaət etdikodun bir sətrini dəyişdirmədən. Hə hə!

Qeyd edim ki, sahələrin təşkili üçün yuxarıda göstərilən variant onun ən optimal olması faktı deyil. Çünki siz estetik səbəblərə görə bəzi sahə bloklarını "yırtmaq" istəmirsiniz - məsələn, bir cüt (pack, recno), bu cədvəl üçün PK olan.

Ümumiyyətlə, tarlaların “minimum” təşkilini müəyyən etmək kifayət qədər sadə “kobud qüvvə” işidir. Buna görə də, məlumatlarınızdan bizimkindən daha yaxşı nəticələr əldə edə bilərsiniz - cəhd edin!

Mənbə: www.habr.com