PostgreSQL मधील मोठ्या खंडांवर एक पैसा वाचवा

द्वारे उठविलेले मोठे डेटा प्रवाह रेकॉर्ड करण्याचा विषय चालू ठेवणे विभाजन बद्दल मागील लेख, यामध्ये आपण कोणत्या मार्गांनी हे करू शकता संग्रहित "भौतिक" आकार कमी करा PostgreSQL मध्ये, आणि सर्व्हर कार्यक्षमतेवर त्यांचा प्रभाव.

आम्ही याबद्दल बोलू TOAST सेटिंग्ज आणि डेटा संरेखन. "सरासरी," या पद्धती खूप संसाधने जतन करणार नाहीत, परंतु अनुप्रयोग कोड अजिबात बदलल्याशिवाय.

तथापि, या संदर्भात आमचा अनुभव खूप फलदायी ठरला, कारण जवळजवळ कोणत्याही देखरेखीचे स्टोरेज त्याच्या स्वभावानुसार आहे. बहुतेक फक्त जोडलेले रेकॉर्ड केलेल्या डेटाच्या बाबतीत. आणि जर तुम्ही विचार करत असाल की तुम्ही डेटाबेसला डिस्कवर लिहायला कसे शिकवू शकता 200MB / से अर्धा जास्त - कृपया मांजरीच्या खाली.

मोठ्या डेटाची छोटी रहस्ये

जॉब प्रोफाईलनुसार आमची सेवा, ते नियमितपणे lairs पासून त्याच्याकडे उडता मजकूर पॅकेजेस.

आणि तेव्हापासून व्हीएलएसआय कॉम्प्लेक्सज्या डेटाबेसचे आम्ही निरीक्षण करतो ते जटिल डेटा स्ट्रक्चर्ससह एक बहु-घटक उत्पादन आहे, नंतर क्वेरी जास्तीत जास्त कामगिरीसाठी यासारखे बाहेर चालू जटिल अल्गोरिदमिक तर्कासह “मल्टी-व्हॉल्यूम”. त्यामुळे आमच्याकडे येणाऱ्या लॉगमधील विनंतीच्या प्रत्येक वैयक्तिक उदाहरणाचे किंवा परिणामी अंमलबजावणी योजनेचे प्रमाण “सरासरी” खूप मोठे असल्याचे दिसून येते.

आपण "कच्चा" डेटा ज्या टेबलमध्ये लिहितो त्यापैकी एकाची रचना पाहू - म्हणजेच लॉग एंट्रीमधील मूळ मजकूर येथे आहे:

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

एक सामान्य चिन्ह (आधीच विभागलेले आहे, अर्थातच, म्हणून हे विभाग टेम्पलेट आहे), जिथे सर्वात महत्वाची गोष्ट मजकूर आहे. कधी कधी जोरदार विपुल.

लक्षात ठेवा की PG मधील एका रेकॉर्डचा "भौतिक" आकार डेटाच्या एका पृष्ठापेक्षा जास्त व्यापू शकत नाही, परंतु "लॉजिकल" आकार पूर्णपणे भिन्न बाब आहे. फील्डवर व्हॉल्यूमेट्रिक मूल्य (varchar/text/bytea) लिहिण्यासाठी, वापरा टोस्ट तंत्रज्ञान:

PostgreSQL एक निश्चित पृष्ठ आकार (सामान्यत: 8 KB) वापरते, आणि ट्यूपल्सना एकाधिक पृष्ठे पसरण्याची परवानगी देत ​​नाही. म्हणून, खूप मोठ्या फील्ड मूल्ये थेट संचयित करणे अशक्य आहे. या मर्यादेवर मात करण्यासाठी, मोठ्या फील्ड मूल्ये संकुचित केली जातात आणि/किंवा एकाधिक भौतिक रेषांमध्ये विभाजित केली जातात. हे वापरकर्त्याच्या लक्षात न आल्याने घडते आणि बहुतेक सर्व्हर कोडवर त्याचा फारसा प्रभाव पडत नाही. ही पद्धत TOAST म्हणून ओळखली जाते...

खरं तर, "संभाव्य मोठ्या" फील्डसह प्रत्येक टेबलसाठी, आपोआप "स्लाइसिंग" सह जोडलेले टेबल तयार केले आहे 2KB विभागांमध्ये प्रत्येक "मोठा" रेकॉर्ड:

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

म्हणजे, जर आपल्याला “मोठ्या” व्हॅल्यूसह स्ट्रिंग लिहायची असेल data, नंतर वास्तविक रेकॉर्डिंग होईल केवळ मुख्य टेबल आणि त्याच्या पीकेसाठीच नाही तर TOAST आणि त्याच्या पीकेसाठी देखील.

TOAST प्रभाव कमी करणे

पण आमचे बहुतेक रेकॉर्ड अजूनही इतके मोठे नाहीत, 8KB मध्ये बसावे - मी यावर पैसे कसे वाचवू शकतो? ..

इथेच विशेषता आपल्या मदतीला येते STORAGE टेबल स्तंभावर:

• विस्तारित कॉम्प्रेशन आणि वेगळे स्टोरेज दोन्हीला अनुमती देते. या मानक पर्याय बहुतेक TOAST अनुरूप डेटा प्रकारांसाठी. ते प्रथम कॉम्प्रेशन करण्याचा प्रयत्न करते, नंतर पंक्ती अद्याप खूप मोठी असल्यास ते टेबलच्या बाहेर संग्रहित करते.
• हाती कॉम्प्रेशनला अनुमती देते परंतु वेगळे स्टोरेज नाही. (खरं तर, अशा स्तंभांसाठी स्वतंत्र स्टोरेज अजूनही केले जाईल, परंतु केवळ शेवटचा उपाय म्हणून, जेव्हा स्ट्रिंग लहान करण्याचा दुसरा कोणताही मार्ग नसतो जेणेकरून ते पृष्ठावर बसेल.)

खरं तर, आपल्याला मजकूरासाठी नेमके हेच हवे आहे - ते शक्य तितके संकुचित करा आणि जर ते अजिबात बसत नसेल तर ते टोस्टमध्ये ठेवा. हे एका आदेशाने थेट फ्लायवर केले जाऊ शकते:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

प्रभावाचे मूल्यांकन कसे करावे

डेटा प्रवाह दररोज बदलत असल्याने, आम्ही निरपेक्ष संख्यांची तुलना करू शकत नाही, परंतु सापेक्ष दृष्टीने लहान वाटा आम्ही ते TOAST मध्ये लिहून ठेवले आहे - तितके चांगले. परंतु येथे एक धोका आहे - प्रत्येक वैयक्तिक रेकॉर्डचा "भौतिक" खंड जितका मोठा असेल तितका निर्देशांक "विस्तृत" होईल, कारण आपल्याला डेटाची अधिक पृष्ठे कव्हर करावी लागतील.

विभाग बदल करण्यापूर्वी:

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

विभाग बदलांनंतर:

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

खरं तर, आम्ही TOAST ला 2 वेळा कमी वेळा लिहायला सुरुवात केली, ज्याने केवळ डिस्कच नाही तर CPU देखील अनलोड केली:

मी लक्षात घेईन की आपण डिस्कचे “वाचन” करण्यात देखील लहान झालो आहोत, केवळ “लेखन”च नाही - कारण टेबलमध्ये रेकॉर्ड घालताना, प्रत्येक निर्देशांकाच्या झाडाचा भाग निश्चित करण्यासाठी आपल्याला “वाचणे” आवश्यक आहे. त्यांच्यामध्ये भविष्यातील स्थान.

PostgreSQL 11 वर कोण चांगले जगू शकते

PG11 वर अपडेट केल्यानंतर, आम्ही TOAST "ट्यूनिंग" सुरू ठेवण्याचा निर्णय घेतला आणि लक्षात आले की या आवृत्तीपासून पॅरामीटर toast_tuple_target:

TOAST प्रोसेसिंग कोड फक्त तेव्हाच सक्रिय होतो जेव्हा टेबलमध्ये संग्रहित करायचे पंक्ती मूल्य TOAST_TUPLE_THRESHOLD बाइट्स (सामान्यतः 2 KB) पेक्षा मोठे असते. जोपर्यंत पंक्तीचे मूल्य TOAST_TUPLE_TARGET बाइट्सपेक्षा कमी होत नाही तोपर्यंत TOAST कोड संकुचित करेल आणि/किंवा फील्ड मूल्ये सारणीच्या बाहेर हलवेल (व्हेरिएबल मूल्य, सामान्यतः 2 KB) किंवा आकार कमी केला जाऊ शकत नाही.

आम्ही ठरवले की आमच्याकडे असलेला डेटा एकतर "अत्यंत लहान" किंवा "खूप लांब" असतो, म्हणून आम्ही स्वतःला किमान संभाव्य मूल्यापर्यंत मर्यादित ठेवण्याचा निर्णय घेतला:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

पुन्हा कॉन्फिगरेशननंतर नवीन सेटिंग्जचा डिस्क लोडिंगवर कसा परिणाम झाला ते पाहूया:

वाईट नाही! सरासरी डिस्कवरील रांग कमी झाली आहे अंदाजे 1.5 पट, आणि डिस्क "व्यस्त" 20 टक्के आहे! पण कदाचित याचा CPU वर कसा तरी परिणाम झाला असेल?

किमान ते आणखी वाईट झाले नाही. तरीही, असे व्हॉल्यूम अजूनही सरासरी CPU लोड वाढवू शकत नाहीत का हे ठरवणे कठीण आहे 5%.

पदांची ठिकाणे बदलून, बेरीज... बदलते!

तुम्हाला माहिती आहेच की, एक पैसा रुबल वाचवतो आणि आमच्या स्टोरेज व्हॉल्यूमसह ते सुमारे आहे 10TB/महिना थोडेसे ऑप्टिमायझेशन देखील चांगला नफा देऊ शकते. म्हणून, आम्ही आमच्या डेटाच्या भौतिक संरचनेकडे लक्ष दिले - नेमके कसे रेकॉर्डमधील "स्टॅक केलेले" फील्ड प्रत्येक टेबल.

कारण मुळे डेटा संरेखन हे सरळ पुढे आहे परिणामी व्हॉल्यूमवर परिणाम करते:

अनेक आर्किटेक्चर मशीन शब्द सीमांवर डेटा संरेखन प्रदान करतात. उदाहरणार्थ, 32-बिट x86 सिस्टमवर, पूर्णांक (पूर्णांक प्रकार, 4 बाइट्स) 4-बाइट शब्दाच्या सीमारेषेवर संरेखित केले जातील, तसेच दुप्पट अचूक फ्लोटिंग पॉइंट संख्या (दुहेरी अचूक फ्लोटिंग पॉइंट, 8 बाइट्स) असतील. आणि 64-बिट सिस्टमवर, दुहेरी मूल्ये 8-बाइट शब्द सीमांवर संरेखित केली जातील. हे असंगततेचे आणखी एक कारण आहे.

संरेखनामुळे, टेबल पंक्तीचा आकार फील्डच्या क्रमावर अवलंबून असतो. सहसा हा प्रभाव फारसा लक्षात येण्यासारखा नसतो, परंतु काही प्रकरणांमध्ये तो आकारात लक्षणीय वाढ होऊ शकतो. उदाहरणार्थ, जर तुम्ही char(1) आणि पूर्णांक फील्ड मिक्स केले तर त्यांच्यामध्ये सामान्यतः 3 बाइट्स वाया जातील.

चला सिंथेटिक मॉडेल्ससह प्रारंभ करूया:

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

पहिल्या प्रकरणात काही अतिरिक्त बाइट्स कोठून आले? हे सोपं आहे - 2-बाइट स्मॉलिंट 4-बाइट सीमेवर संरेखित पुढील फील्डच्या आधी, आणि जेव्हा ते शेवटचे असेल तेव्हा काहीही नाही आणि संरेखित करण्याची आवश्यकता नाही.

सिद्धांततः, सर्वकाही ठीक आहे आणि आपण आपल्या आवडीनुसार फील्डची पुनर्रचना करू शकता. एका टेबलचे उदाहरण वापरून ते वास्तविक डेटावर तपासूया, ज्याचा दैनिक विभाग 10-15GB व्यापतो.

प्रारंभिक रचना:

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)

स्तंभ क्रम बदलल्यानंतर विभाग - नक्की समान फील्ड, फक्त भिन्न क्रम:

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)

विभागाची एकूण मात्रा "तथ्ये" च्या संख्येद्वारे निर्धारित केली जाते आणि केवळ बाह्य प्रक्रियांवर अवलंबून असते, म्हणून आपण ढिगाचा आकार विभाजित करूया (pg_relation_size) त्यातील रेकॉर्डच्या संख्येनुसार - म्हणजे, आम्हाला मिळते वास्तविक संग्रहित रेकॉर्डचा सरासरी आकार:

उणे 6% व्हॉल्यूम, छान!

परंतु सर्व काही, अर्थातच, इतके गुलाबी नाही - शेवटी, इंडेक्सेसमध्ये आम्ही फील्डचा क्रम बदलू शकत नाही, आणि म्हणून "सर्वसाधारणपणे" (pg_total_relation_size) ...

...अजूनही इथे 1.5% बचतकोडची एक ओळ न बदलता. होय, होय!

मी लक्षात घेतो की फील्ड व्यवस्थित करण्यासाठी वरील पर्याय हा सर्वात इष्टतम आहे असे नाही. कारण तुम्हाला सौंदर्याच्या कारणास्तव फील्डचे काही ब्लॉक्स "फाडायचे" नाहीत - उदाहरणार्थ, एक जोडपे (pack, recno), जे या टेबलसाठी PK आहे.

सर्वसाधारणपणे, फील्डची "किमान" व्यवस्था निश्चित करणे हे अगदी सोपे "ब्रूट फोर्स" कार्य आहे. म्हणून, तुम्ही आमच्या डेटापेक्षा तुमच्या डेटावरून आणखी चांगले परिणाम मिळवू शकता - प्रयत्न करा!

स्त्रोत: www.habr.com