PostgreSQL मा ठूलो मात्रा मा एक पैसा बचत गर्नुहोस्

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

हामी बारेमा कुरा गर्नेछौं TOAST सेटिङहरू र डेटा पङ्क्तिबद्धता। "औसतमा," यी विधिहरूले धेरै स्रोतहरू बचत गर्दैनन्, तर एप्लिकेसन कोड परिमार्जन नगरी नै।

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

ठूलो डाटा को सानो रहस्य

कार्य प्रोफाइल द्वारा हाम्रो सेवा, तिनीहरूले नियमित रूपमा lairs बाट उहाँलाई उडान पाठ प्याकेजहरू.

र पछि VLSI जटिलजसको डाटाबेस हामीले मोनिटर गर्छौं जटिल डेटा संरचनाहरू भएको बहु-घटक उत्पादन हो, त्यसपछि प्रश्नहरू अधिकतम प्रदर्शन को लागी यसरी बाहिर निस्कनुहोस् जटिल एल्गोरिदमिक तर्क संग "बहु-भोल्युम"। त्यसोभए अनुरोधको प्रत्येक व्यक्तिगत उदाहरणको भोल्युम वा लगमा नतिजा कार्यान्वयन योजना जुन हामीलाई आउँछ "औसत" धेरै ठूलो हुन्छ।

एउटा तालिकाको संरचना हेरौं जसमा हामीले "कच्चा" डाटा लेख्छौं - त्यो हो, लग प्रविष्टिको मूल पाठ यहाँ छ:

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) लेख्न, प्रयोग गर्नुहोस् TOAST प्रविधि:

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

वास्तवमा, "सम्भावित रूपमा ठूलो" क्षेत्रहरू भएको प्रत्येक तालिकाको लागि, स्वचालित रूपमा "स्लाइसिङ" संग एक जोडी तालिका सिर्जना गरिएको छ 2KB खण्डहरूमा प्रत्येक "ठूलो" रेकर्ड:

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

त्यो हो, यदि हामीले "ठूलो" मानको साथ स्ट्रिङ लेख्नु पर्छ data, त्यसपछि वास्तविक रेकर्डिङ हुनेछ मुख्य तालिका र यसको PK मा मात्र होइन, तर TOAST र यसको PK लाई पनि.

TOAST प्रभाव कम गर्दै

तर हाम्रा धेरैजसो रेकर्डहरू अझै ठूला छैनन्, 8KB मा फिट हुनुपर्छ - म कसरी यसमा पैसा बचत गर्न सक्छु? ..

यो जहाँ विशेषता हाम्रो सहायतामा आउँछ STORAGE तालिका स्तम्भमा:

• विस्तारित दुवै सङ्कुचन र अलग भण्डारण अनुमति दिन्छ। यो मानक विकल्प धेरै जसो TOAST अनुरूप डेटा प्रकारहरूको लागि। यसले पहिले कम्प्रेसन प्रदर्शन गर्ने प्रयास गर्छ, त्यसपछि पङ्क्ति अझै धेरै ठूलो छ भने तालिका बाहिर भण्डारण गर्दछ।
• हात कम्प्रेसन अनुमति दिन्छ तर अलग भण्डारण छैन। (वास्तवमा, छुट्टै भण्डारण अझै पनि त्यस्ता स्तम्भहरूको लागि प्रदर्शन गरिनेछ, तर मात्र अन्तिम उपायको रूपमा, जब त्यहाँ स्ट्रिङ संकुचन गर्न कुनै अन्य तरिका छैन ताकि यो पृष्ठमा फिट हुन्छ।)

वास्तवमा, यो वास्तवमा हामीलाई पाठको लागि आवश्यक छ - यसलाई सकेसम्म कम्प्रेस गर्नुहोस्, र यदि यो फिट छैन भने, यसलाई 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 बाइट (सामान्यतया २ KB) भन्दा ठूलो हुन्छ। TOAST कोडले पङ्क्तिको मान TOAST_TUPLE_TARGET बाइट्स (चर मान, सामान्यतया २ 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) र integer फिल्डहरू मिलाउनुभयो भने, त्यहाँ सामान्यतया 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