PostgreSQL में बड़ी मात्रा में एक पैसा बचाएं

द्वारा उठाए गए बड़े डेटा स्ट्रीम को रिकॉर्ड करने के विषय को जारी रखना विभाजन के बारे में पिछला लेख, इसमें हम उन तरीकों पर गौर करेंगे जिनसे आप कर सकते हैं संग्रहीत के "भौतिक" आकार को कम करें PostgreSQL में, और सर्वर प्रदर्शन पर उनका प्रभाव।

हम बात करेंगे टोस्ट सेटिंग्स और डेटा संरेखण. "औसतन," ये विधियां बहुत अधिक संसाधनों को नहीं बचाएंगी, लेकिन एप्लिकेशन कोड को बिल्कुल भी संशोधित किए बिना।

हालाँकि, इस संबंध में हमारा अनुभव बहुत उपयोगी साबित हुआ, क्योंकि इसकी प्रकृति से लगभग किसी भी निगरानी का भंडारण होता है अधिकतर केवल संलग्न करें रिकॉर्ड किए गए डेटा के संदर्भ में. और यदि आप सोच रहे हैं कि आप इसके बजाय डेटाबेस को डिस्क पर लिखना कैसे सिखा सकते हैं 200MB / s आधा जितना - कृपया बिल्ली के नीचे।

बड़े डेटा के छोटे रहस्य

जॉब प्रोफ़ाइल के अनुसार हमारी सेवा, वे नियमित रूप से मांद से उसके पास उड़ते हैं पाठ संकुल.

और तब से वीएलएसआई कॉम्प्लेक्सजिसका डेटाबेस हम मॉनिटर करते हैं वह जटिल डेटा संरचनाओं वाला एक बहु-घटक उत्पाद है, फिर क्वेरीज़ अधिकतम प्रदर्शन के लिए बिल्कुल इस तरह बनो जटिल एल्गोरिथम तर्क के साथ "मल्टी-वॉल्यूम"।. इसलिए हमारे पास आने वाले लॉग में अनुरोध के प्रत्येक व्यक्तिगत उदाहरण या परिणामी निष्पादन योजना की मात्रा "औसतन" काफी बड़ी हो जाती है।

आइए उन तालिकाओं में से एक की संरचना को देखें जिसमें हम "कच्चा" डेटा लिखते हैं - यानी, यहां लॉग प्रविष्टि से मूल पाठ है:

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

एक विशिष्ट चिह्न (बेशक, पहले से ही खंडित है, इसलिए यह एक खंड टेम्पलेट है), जहां सबसे महत्वपूर्ण चीज पाठ है। कभी-कभी काफ़ी भारी।

याद रखें कि पीजी में एक रिकॉर्ड का "भौतिक" आकार डेटा के एक पृष्ठ से अधिक नहीं ले सकता है, लेकिन "तार्किक" आकार पूरी तरह से अलग मामला है। किसी फ़ील्ड में वॉल्यूमेट्रिक मान (varchar/text/bytea) लिखने के लिए, उपयोग करें टोस्ट तकनीक:

PostgreSQL एक निश्चित पृष्ठ आकार (आमतौर पर 8 KB) का उपयोग करता है, और टुपल्स को कई पृष्ठों तक फैलने की अनुमति नहीं देता है। इसलिए, बहुत बड़े फ़ील्ड मानों को सीधे संग्रहीत करना असंभव है। इस सीमा को पार करने के लिए, बड़े फ़ील्ड मानों को संपीड़ित किया जाता है और/या कई भौतिक रेखाओं में विभाजित किया जाता है। यह उपयोगकर्ता द्वारा ध्यान दिए बिना होता है और अधिकांश सर्वर कोड पर इसका बहुत कम प्रभाव पड़ता है। इस विधि को टोस्ट के नाम से जाना जाता है...

वास्तव में, "संभावित रूप से बड़े" फ़ील्ड वाली प्रत्येक तालिका के लिए, स्वचालित रूप से "स्लाइसिंग" के साथ एक युग्मित तालिका बनाई गई है 2KB खंडों में प्रत्येक "बड़ा" रिकॉर्ड:

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

यानी, अगर हमें "बड़े" मान के साथ एक स्ट्रिंग लिखनी है data, तब वास्तविक रिकॉर्डिंग होगी न केवल मुख्य टेबल और उसके PK को, बल्कि TOAST और उसके PK को भी.

टोस्ट प्रभाव को कम करना

लेकिन हमारे अधिकांश रिकॉर्ड अभी भी उतने बड़े नहीं हैं, 8KB में फ़िट होना चाहिए - मैं इस पर पैसे कैसे बचा सकता हूँ?

यहीं पर विशेषता हमारी सहायता के लिए आती है STORAGE टेबल कॉलम पर:

• विस्तारित संपीड़न और अलग भंडारण दोनों की अनुमति देता है। यह मानक विकल्प अधिकांश TOAST अनुरूप डेटा प्रकारों के लिए। यह पहले संपीड़न करने का प्रयास करता है, फिर यदि पंक्ति अभी भी बहुत बड़ी है तो इसे तालिका के बाहर संग्रहीत करता है।
• मुख्य संपीड़न की अनुमति देता है लेकिन अलग भंडारण की नहीं। (वास्तव में, ऐसे स्तंभों के लिए अभी भी अलग भंडारण किया जाएगा, लेकिन केवल एक अंतिम उपाय के रूप में, जब स्ट्रिंग को छोटा करने का कोई अन्य तरीका नहीं है ताकि वह पृष्ठ पर फ़िट हो जाए।)

वास्तव में, यह वही है जो हमें पाठ के लिए चाहिए - इसे जितना संभव हो सके संपीड़ित करें, और यदि यह बिल्कुल फिट नहीं होता है, तो इसे टोस्ट में डाल दें. यह सीधे एक आदेश से तुरंत किया जा सकता है:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

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

चूँकि डेटा प्रवाह हर दिन बदलता है, हम निरपेक्ष संख्याओं की तुलना नहीं कर सकते, बल्कि सापेक्ष रूप में कर सकते हैं छोटा हिस्सा हमने इसे टोस्ट में लिखा - इतना बेहतर। लेकिन यहां एक खतरा है - प्रत्येक व्यक्तिगत रिकॉर्ड की "भौतिक" मात्रा जितनी बड़ी होगी, सूचकांक उतना ही "व्यापक" हो जाएगा, क्योंकि हमें डेटा के अधिक पृष्ठों को कवर करना होगा।

अनुभाग परिवर्तन से पहले:

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

अनुभाग परिवर्तन के बाद:

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

वास्तव में, हम TOAST को 2 बार कम बार लिखना शुरू किया, जिसने न केवल डिस्क, बल्कि सीपीयू को भी अनलोड किया:

मैं ध्यान दूंगा कि हम डिस्क को "पढ़ने" में भी छोटे हो गए हैं, न कि केवल "लिखने" में - क्योंकि किसी तालिका में रिकॉर्ड डालते समय, हमें उसका निर्धारण करने के लिए प्रत्येक इंडेक्स के पेड़ के हिस्से को भी "पढ़ना" पड़ता है। उनमें भविष्य की स्थिति.

PostgreSQL 11 पर कौन अच्छे से रह सकता है

पीजी11 में अपडेट करने के बाद, हमने टोस्ट को "ट्यूनिंग" जारी रखने का फैसला किया और देखा कि पैरामीटर इस संस्करण से शुरू हो रहा है 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 प्रतिशत "व्यस्त" है! लेकिन शायद इससे किसी तरह सीपीयू पर असर पड़ा?

कम से कम यह और खराब नहीं हुआ. हालाँकि, यह आंकना मुश्किल है कि क्या इतनी मात्रा भी औसत सीपीयू लोड को अधिक नहीं बढ़ा सकती है 5%.

पदों के स्थान बदलने से योग... बदल जाता है!

जैसा कि आप जानते हैं, एक पैसा एक रूबल बचाता है, और यह हमारे भंडारण की मात्रा के बारे में है 10टीबी/माह थोड़ा सा अनुकूलन भी अच्छा लाभ दे सकता है। इसलिए, हमने अपने डेटा की भौतिक संरचना पर ध्यान दिया - वास्तव में कैसे रिकॉर्ड के अंदर "स्टैक्ड" फ़ील्ड प्रत्येक टेबल.

क्योंकि की वजह से डेटा संरेखण यह सीधा-सीधा है परिणामी मात्रा को प्रभावित करता है:

कई आर्किटेक्चर मशीन शब्द सीमाओं पर डेटा संरेखण प्रदान करते हैं। उदाहरण के लिए, 32-बिट x86 सिस्टम पर, पूर्णांक (पूर्णांक प्रकार, 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
));
-- 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