विभाजन पर पिछले लेख द्वारा उठाए गए डेटा की बड़ी धाराओं को रिकॉर्ड करने के विषय को जारी रखते हुए , इसमें हम उन तरीकों पर विचार करते हैं जिनमें आप पोस्टग्रेक्यूएल में संग्रहीत "भौतिक" आकार और सर्वर के प्रदर्शन पर उनके प्रभाव को कम कर सकते हैं ।यह टोस्ट सेटिंग्स और डेटा संरेखण के बारे में है । "औसतन", ये तरीके बहुत सारे संसाधनों को नहीं बचाएंगे, लेकिन बिना किसी संशोधन के आवेदन कोड में।हालाँकि, हमारा अनुभव इस संबंध में बहुत उपयोगी रहा, क्योंकि लगभग किसी भी निगरानी का भंडार अपनी प्रकृति के अनुसार ज्यादातर दर्ज आंकड़ों के अनुसार ही है। और यदि आप रुचि रखते हैं कि आप 200 एमबी / एस के बजाय डिस्क पर लिखने के लिए एक डेटाबेस को कैसे सिखा सकते हैं - मैं एक कटौती के लिए कहता हूं।बिग डेटा का छोटा राज
हमारी सेवा
के प्रोफाइल के अनुसार , वह नियमित रूप से लॉग से टेक्स्ट पैकेट प्राप्त करता है ।और चूंकि वीएलएसआई कॉम्प्लेक्स , जिनके डेटाबेस की हम निगरानी कर रहे हैं, जटिल डेटा संरचनाओं के साथ एक बहुउद्देशीय उत्पाद है, अधिकतम प्रदर्शन प्राप्त करने के लिए प्रश्न ऐसे "मल्टी-वॉल्यूम" द्वारा जटिल एल्गोरिथम तर्क के साथ प्राप्त किए जाते हैं । इसलिए अनुरोध के प्रत्येक व्यक्तिगत उदाहरण या हमारे पास आने वाले लॉग में परिणामी निष्पादन योजना की मात्रा "औसत" काफी बड़ी हो जाती है।आइए उन तालिकाओं में से एक की संरचना को देखें जिसमें हम "कच्चा" डेटा लिखते हैं - अर्थात, यहाँ लॉग इन से मूल पाठ है:CREATE TABLE rawdata_orig(
pack
uuid NOT NULL
, recno
smallint NOT NULL
, dt
date
, data
text
, PRIMARY KEY(pack, recno)
);
इस तरह की एक विशिष्ट प्लेट (पहले से ही विभाजित, निश्चित रूप से, इसलिए यह एक अनुभाग टेम्पलेट है), जहां पाठ सबसे महत्वपूर्ण है। कभी-कभी काफी प्रफुल्लित।याद रखें कि पीजी में एक रिकॉर्ड का "भौतिक" आकार डेटा के एक से अधिक पृष्ठ पर कब्जा नहीं कर सकता है, लेकिन "तार्किक" आकार पूरी तरह से अलग मामला है। फ़ील्ड में वॉल्यूम मान (varchar / text / bytea) लिखने के लिए, TOAST तकनीक का उपयोग किया जाता है :PostgreSQL एक निश्चित पृष्ठ आकार (आमतौर पर 8 KB) का उपयोग करता है, और कई पृष्ठों को टपल करने की अनुमति नहीं देता है। इसलिए, बहुत बड़े क्षेत्र मूल्यों को सीधे स्टोर करना असंभव है। इस सीमा को पार करने के लिए, बड़े क्षेत्र मान संकुचित होते हैं और / या कई भौतिक रेखाओं में विभाजित होते हैं। यह उपयोगकर्ता द्वारा किसी का ध्यान नहीं दिया जाता है और सर्वर कोड के अधिकांश हिस्से को थोड़ा प्रभावित करता है। इस विधि को टोस्ट के रूप में जाना जाता है ...
वास्तव में, "संभावित रूप से बड़े" फ़ील्ड के साथ प्रत्येक तालिका के लिए , एक जोड़ा तालिका स्वचालित रूप से 2KB खंडों में प्रत्येक "बड़े" रिकॉर्ड के "स्लाइसिंग" के साथ बनाई जाती है:TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
यही है, अगर हमें "बड़े" मान के साथ एक पंक्ति लिखना है data
, तो वास्तविक रिकॉर्ड न केवल मुख्य तालिका और उसके पीके में होगा, बल्कि टोस्ट और उसके पीके में भी होगा ।टोस्ट प्रभाव को कम करें
लेकिन यहां के अधिकांश रिकॉर्ड अभी भी इतने बड़े नहीं हैं, उन्हें 8KB में फिट होना चाहिए - आप इस पर कैसे बचत करेंगे? ..यहां STORAGE
टेबल कॉलम में विशेषता हमारी सहायता के लिए आती है :- अतिरिक्त दोनों संपीड़न और अलग भंडारण की अनुमति देता है। यह सबसे अधिक संगत डेटा प्रकारों के लिए मानक विकल्प है । सबसे पहले, संपीड़न करने का प्रयास किया जाता है, फिर तालिका के बाहर सहेजा जाता है यदि पंक्ति अभी भी बहुत बड़ी है।
- MAIN संपीड़न की अनुमति देता है, लेकिन अलग भंडारण नहीं। (वास्तव में, अलग-अलग भंडारण, हालांकि, ऐसे स्तंभों के लिए किया जाएगा, लेकिन केवल एक अंतिम उपाय के रूप में , जब पंक्ति को कम करने का कोई अन्य तरीका नहीं है ताकि यह पृष्ठ पर फिट हो सके।)
वास्तव में, यह वही है जो हमें पाठ के लिए आवश्यक है - इसे जितना संभव हो उतना निचोड़ें, और भले ही यह बिल्कुल फिट न हो - इसे टोस्ट में रखें । आप इसे सीधे "फ्लाई पर" कर सकते हैं, एक कमांड के साथ: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%)
वास्तव में, हमने 2 बार टोस्ट में कम बार लिखना शुरू किया , जो न केवल डिस्क, बल्कि सीपीयू को भी लोड करता है:मैं ध्यान देता हूं कि हमने डिस्क को "पढ़ना" शुरू किया, न केवल "लिखना" - क्योंकि जब आप किसी तालिका में एक रिकॉर्ड डालते हैं, तो आपको उनमें से प्रत्येक की स्थिति का निर्धारण करने के लिए प्रत्येक सूचक के पेड़ के हिस्से को "घटाना" करना होगा।जो PostgreSQL 11 पर अच्छी तरह से रहते हैं
PG11 में अपग्रेड करने के बाद, हमने "ट्यूनिंग" जारी रखने का फैसला किया और देखा कि इस संस्करण से शुरू होकर, कॉन्फ़िगरेशन के लिए पैरामीटर उपलब्ध हो गया toast_tuple_target
:TOAST प्रसंस्करण कोड तभी ट्रिगर किया जाता है जब तालिका में संग्रहीत की जाने वाली पंक्ति मान TOAST_TUPLE_THRESHOLD बाइट्स (आमतौर पर 2 Kb) से बड़ी हो। TOAST कोड तालिका मानों को संपीड़ित और / या स्थानांतरित कर देगा, जब तक कि पंक्ति मान TOAST_TUPLE_TARGET बाइट्स से कम न हो (चर, आमतौर पर 2 KB और साथ ही) या आकार को कम करना असंभव हो जाता है।
हमने तय किया कि हमारे पास जो डेटा है वह या तो "बहुत कम" है या तुरंत "बहुत लंबा" है, इसलिए हमने खुद को सबसे कम संभव तक सीमित करने का फैसला किया है:ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);
आइए देखें कि माइग्रेशन के बाद नई सेटिंग्स ने डिस्क लोडिंग को कैसे प्रभावित किया:बुरा नहीं! एक डिस्क के लिए औसत कतार लगभग 1.5 गुना कम हो गई , और डिस्क "अधिभोग" - 20 प्रतिशत से! लेकिन शायद यह किसी भी तरह से CPU को प्रभावित करता है?कम से कम, यह निश्चित रूप से खराब नहीं हुआ। हालांकि, यह निर्धारित करना मुश्किल है कि क्या इस तरह के वॉल्यूम अभी भी औसत सीपीयू लोड को 5% से ऊपर नहीं बढ़ा सकते हैं ।स्थिति के परिवर्तन से, योग ... परिवर्तन!
जैसा कि आप जानते हैं, एक पैसा एक रूबल बचाता है, और हमारे भंडारण संस्करणों के साथ लगभग 10 टीबी / महीना, यहां तक कि एक छोटा अनुकूलन भी अच्छा लाभ दे सकता है। इसलिए, हमने अपने डेटा की भौतिक संरचना पर ध्यान आकर्षित किया - प्रत्येक तालिका के रिकॉर्ड के अंदर "फ़ील्ड" को कैसे ठीक से रखा गया है ।क्योंकि डेटा संरेखण के कारण , यह सीधे परिणामी मात्रा को प्रभावित करता है :कई आर्किटेक्चर मशीन शब्द सीमाओं के पार डेटा संरेखण प्रदान करते हैं। उदाहरण के लिए, एक x86 32-बिट सिस्टम पर, पूर्णांक (पूर्णांक प्रकार, 4 बाइट्स) को 4-बाइट शब्दों की सीमा पर संरेखित किया जाएगा, साथ ही डबल-सटीक फ़्लोटिंग-पॉइंट नंबर (डबल सटीक प्रकार, 8 बाइट्स)। और 64-बिट सिस्टम पर, 8-बाइट शब्दों की सीमा पर दोहरे मूल्यों को संरेखित किया जाएगा। यह असंगति का एक और कारण है।
संरेखण के कारण, तालिका पंक्ति का आकार खेतों के क्रम पर निर्भर करता है। आमतौर पर यह प्रभाव बहुत ध्यान देने योग्य नहीं होता है, लेकिन कुछ मामलों में यह आकार में उल्लेखनीय वृद्धि कर सकता है। उदाहरण के लिए, यदि आप एक प्रकार के चार (1) और पूर्णांक मिश्रित के क्षेत्रों को रखते हैं, तो उनके बीच, एक नियम के रूप में, 3 बाइट्स कुछ भी नहीं के लिए बर्बाद हो जाएंगे।
चलो सिंथेटिक मॉडल के साथ शुरू करते हैं:SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
पहले मामले में बाइट्स की अतिरिक्त जोड़ी कहां से आई? सब कुछ सरल है - एक 2-बाइट स्मालिंट अगले फ़ील्ड से पहले 4-बाइट की सीमा पर संरेखित है , और जब यह अंतिम है, तो कुछ भी नहीं है और इसे संरेखित करने की कोई आवश्यकता नहीं है।सिद्धांत रूप में, सब कुछ ठीक है और आप अपनी इच्छानुसार खेतों को पुनर्व्यवस्थित कर सकते हैं। आइए तालिकाओं में से एक के उदाहरण पर वास्तविक डेटा की जांच करें, जिसमें से दैनिक अनुभाग 10-15GB लेता है।स्रोत संरचना:CREATE TABLE public.plan_20190220
(
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
(
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)
, जो इस तालिका के लिए पीके है।सामान्य तौर पर, "न्यूनतम" फ़ील्ड व्यवस्था की परिभाषा एक काफी सरल "संपूर्ण" कार्य है। इसलिए, आप अपने डेटा पर हमारे से भी बेहतर परिणाम प्राप्त कर सकते हैं - इसे आज़माएं!