पोस्टग्रेस: ​​ब्लोट, pg_repack र स्थगित अवरोधहरू

तालिका र अनुक्रमणिकाहरूमा ब्लोटको प्रभाव व्यापक रूपमा ज्ञात छ र पोस्टग्रेसमा मात्र होइन। VACUUM FULL वा CLUSTER जस्ता बाकस बाहिर यसलाई व्यवहार गर्ने तरिकाहरू छन्, तर तिनीहरूले सञ्चालनको क्रममा तालिकाहरू लक गर्छन् र त्यसैले सधैं प्रयोग गर्न सकिँदैन।

लेखमा ब्लोट कसरी हुन्छ, तपाइँ यसलाई कसरी लड्न सक्नुहुन्छ, स्थगित अवरोधहरू र pg_repack विस्तारको प्रयोगमा उनीहरूले ल्याउने समस्याहरूको बारेमा थोरै सिद्धान्त समावेश गर्दछ।

को आधारमा यो लेख लेखिएको हो मेरो बोली PgConf.Russia 2020 मा।

ब्लोट किन हुन्छ?

Postgres बहु-संस्करण मोडेलमा आधारित छ (MVCC)। यसको सार यो हो कि तालिकाको प्रत्येक पङ्क्तिमा धेरै संस्करणहरू हुन सक्छन्, जबकि लेनदेनहरूले यी संस्करणहरू मध्ये एक भन्दा बढी देख्दैनन्, तर आवश्यक छैन कि एउटै हो। यसले धेरै लेनदेनहरूलाई एकै साथ काम गर्न अनुमति दिन्छ र एक अर्कामा वस्तुतः कुनै प्रभाव पार्दैन।

जाहिर छ, यी सबै संस्करणहरू भण्डारण गर्न आवश्यक छ। Postgres ले मेमोरी पृष्ठसँग पृष्ठद्वारा काम गर्दछ र पृष्ठ भनेको डिस्कबाट पढ्न वा लिखित डेटाको न्यूनतम मात्रा हो। यो कसरी हुन्छ बुझ्नको लागि एउटा सानो उदाहरण हेरौं।

मानौं हामीसँग एउटा तालिका छ जसमा हामीले धेरै रेकर्डहरू थपेका छौं। नयाँ डाटा फाइलको पहिलो पृष्ठमा देखा परेको छ जहाँ तालिका भण्डारण गरिएको छ। यी पङ्क्तिहरूका प्रत्यक्ष संस्करणहरू हुन् जुन प्रतिबद्धता पछि अन्य लेनदेनहरूमा उपलब्ध छन् (सरलताको लागि, हामी अलगाव स्तर पढ्न प्रतिबद्ध छ भनेर मान्नेछौं)।

हामीले त्यसपछि एउटा प्रविष्टिलाई अद्यावधिक गर्‍यौं, जसले गर्दा पुरानो संस्करण अब सान्दर्भिक छैन भनी चिन्ह लगाइयो।

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

Postgres को एक संयन्त्र छ भ्याक्यूम, जसले अप्रचलित संस्करणहरू सफा गर्छ र नयाँ डेटाको लागि ठाउँ बनाउँछ। तर यदि यो आक्रामक रूपमा पर्याप्त कन्फिगर गरिएको छैन वा अन्य तालिकाहरूमा काम गर्न व्यस्त छ भने, "फोहोर डाटा" रहन्छ, र हामीले नयाँ डाटाको लागि थप पृष्ठहरू प्रयोग गर्नुपर्नेछ।

त्यसोभए हाम्रो उदाहरणमा, कुनै समयमा तालिकामा चार पृष्ठहरू हुन्छन्, तर यसको आधा मात्र प्रत्यक्ष डेटा समावेश हुनेछ। नतिजाको रूपमा, तालिका पहुँच गर्दा, हामी आवश्यक भन्दा धेरै डाटा पढ्नेछौं।

VACUUM ले अब सबै अप्रासंगिक पङ्क्ति संस्करणहरू मेटाए पनि, स्थिति नाटकीय रूपमा सुधार हुनेछैन। हामीसँग नयाँ पङ्क्तिहरूका लागि पृष्ठहरूमा वा सम्पूर्ण पृष्ठहरूमा खाली ठाउँ हुनेछ, तर हामी अझै आवश्यक भन्दा बढी डेटा पढ्ने छौं।
खैर, यदि पूर्ण रूपमा खाली पृष्ठ (हाम्रो उदाहरणमा दोस्रो) फाइलको अन्त्यमा थियो, तब VACUUM ले यसलाई ट्रिम गर्न सक्षम हुनेछ। तर अहिले उनी बीचमा छिन्, त्यसैले उनलाई केही गर्न सकिँदैन।

जब त्यस्ता खाली वा अत्यधिक विरल पृष्ठहरूको संख्या ठूलो हुन्छ, जसलाई ब्लोट भनिन्छ, यसले कार्यसम्पादनलाई असर गर्न थाल्छ।

माथि वर्णन गरिएको सबै कुरा तालिकाहरूमा ब्लोटको घटनाको मेकानिक्स हो। अनुक्रमणिकाहरूमा यो धेरै उस्तै तरिकामा हुन्छ।

के म ब्लोट छ?

यदि तपाइँ ब्लोट छ भने निर्धारण गर्न धेरै तरिकाहरू छन्। पहिलोको विचार भनेको आन्तरिक पोस्टग्रेस तथ्याङ्कहरू प्रयोग गर्नु हो, जसमा तालिकाहरूमा पङ्क्तिहरूको संख्या, "प्रत्यक्ष" पङ्क्तिहरूको संख्या, इत्यादि बारे अनुमानित जानकारी समावेश छ। तपाईंले इन्टरनेटमा तयार-निर्मित स्क्रिप्टहरूको धेरै भिन्नताहरू फेला पार्न सक्नुहुन्छ। हामीले आधारको रूपमा लिएका छौं लिपि PostgreSQL विशेषज्ञहरूबाट, जसले टोस्ट र ब्लोट btree अनुक्रमणिकाहरूको साथमा ब्लोट टेबलहरूको मूल्याङ्कन गर्न सक्छ। हाम्रो अनुभव मा, यसको त्रुटि 10-20% छ।

अर्को तरिका विस्तार प्रयोग गर्न हो pgstattuple, जसले तपाईंलाई पृष्ठहरू भित्र हेर्न र अनुमानित र सही ब्लोट मान दुवै प्राप्त गर्न अनुमति दिन्छ। तर दोस्रो अवस्थामा, तपाईंले सम्पूर्ण तालिका स्क्यान गर्नुपर्नेछ।

हामी एक सानो ब्लोट मान मान्दछौं, 20% सम्म, स्वीकार्य। यसलाई फिलफ्याक्टरको एनालगको रूपमा मान्न सकिन्छ तालिकाहरू и अनुक्रमणिकाहरू। 50% र माथि, प्रदर्शन समस्याहरू सुरु हुन सक्छ।

ब्लोट लड्ने तरिकाहरू

Postgres सँग बक्स बाहिर ब्लोटसँग व्यवहार गर्ने धेरै तरिकाहरू छन्, तर तिनीहरू सधैं सबैका लागि उपयुक्त हुँदैनन्।

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

AUTOVACUUM ले तालिकाहरू राख्न नसक्ने अर्को सामान्य कारण हो किनभने त्यहाँ लामो समयदेखि चलिरहेका लेनदेनहरू छन् जसले यसलाई ती लेनदेनहरूमा उपलब्ध डाटा सफा गर्नबाट रोक्छ। यहाँको सिफारिस पनि स्पष्ट छ - "झुल्काउने" लेनदेनहरूबाट छुटकारा पाउनुहोस् र सक्रिय लेनदेनको समयलाई कम गर्नुहोस्। तर यदि तपाइँको एप्लिकेसनमा लोड OLAP र OLTP को हाइब्रिड हो भने, तपाइँ एकै साथ धेरै बारम्बार अद्यावधिकहरू र छोटो प्रश्नहरू, साथै दीर्घकालीन कार्यहरू - उदाहरणका लागि, रिपोर्ट निर्माण गर्न सक्नुहुन्छ। यस्तो अवस्थामा, यो विभिन्न आधारहरूमा लोड फैलाउने बारे सोच्न लायक छ, जसले तिनीहरूलाई प्रत्येकको थप फाइन-ट्यूनिङको लागि अनुमति दिनेछ।

अर्को उदाहरण - प्रोफाइल एकसमान छ भने, तर डाटाबेस धेरै उच्च लोड अन्तर्गत छ, तब पनि सबैभन्दा आक्रामक AUTOVACUUM सामना गर्न सक्दैन, र ब्लोट हुनेछ। स्केलिंग (ठाडो वा तेर्सो) मात्र समाधान हो।

तपाईंले AUTOVACUUM सेटअप गर्नुभएको अवस्थामा के गर्ने, तर ब्लोट बढ्न जारी छ।

टोली भ्याकुम फुल तालिका र अनुक्रमणिकाहरूको सामग्रीहरू पुनर्निर्माण गर्दछ र तिनीहरूमा सान्दर्भिक डेटा मात्र छोड्छ। ब्लोट हटाउनको लागि, यो पूर्ण रूपमा काम गर्दछ, तर यसको कार्यान्वयनको क्रममा तालिकामा एक विशेष लक क्याप्चर हुन्छ (AccessExclusiveLock), जसले यस तालिकामा प्रश्नहरू कार्यान्वयन गर्न अनुमति दिँदैन, चयन पनि गर्दछ। यदि तपाइँ केहि समयको लागि तपाइँको सेवा वा यसको अंश रोक्न खर्च गर्न सक्नुहुन्छ (डेटाबेस र तपाइँको हार्डवेयरको आकारमा आधारित दस मिनेट देखि धेरै घण्टा सम्म), तब यो विकल्प सबै भन्दा राम्रो हो। दुर्भाग्यवश, हामीसँग निर्धारित मर्मतसम्भारको समयमा VACUUM FULL चलाउने समय छैन, त्यसैले यो विधि हाम्रो लागि उपयुक्त छैन।

टोली क्लस्टर VACUUM FULL को रूपमा तालिकाहरूको सामग्रीहरू पुन: निर्माण गर्दछ, तर तपाइँलाई एक अनुक्रमणिका निर्दिष्ट गर्न अनुमति दिन्छ जुन अनुसार डाटा भौतिक रूपमा डिस्कमा अर्डर गरिनेछ (तर भविष्यमा नयाँ पङ्क्तिहरूको लागि अर्डर ग्यारेन्टी छैन)। केहि परिस्थितिहरूमा, यो धेरै प्रश्नहरूको लागि राम्रो अनुकूलन हो - अनुक्रमणिका द्वारा धेरै रेकर्डहरू पढ्नको साथ। आदेशको बेफाइदा VACUUM FULL को जस्तै हो - यसले सञ्चालनको क्रममा तालिका लक गर्दछ।

टोली REINDEX अघिल्लो दुई जस्तै, तर एक विशिष्ट अनुक्रमणिका वा तालिकाको सबै अनुक्रमणिकाहरू पुन: निर्माण गर्दछ। तालाहरू थोरै कमजोर छन्: तालिकामा ShareLock (परिमार्जनहरू रोक्छ, तर चयन गर्न अनुमति दिन्छ) र पुन: निर्माण भइरहेको अनुक्रमणिकामा AccessExclusiveLock (यस अनुक्रमणिका प्रयोग गरेर प्रश्नहरू रोक्नुहोस्)। यद्यपि, Postgres को 12 औं संस्करणमा एक प्यारामिटर देखा पर्‍यो संगै, जसले तपाइँलाई समवर्ती थप, परिमार्जन, वा अभिलेखहरू मेटाउन रोक नगरी अनुक्रमणिका पुन: निर्माण गर्न अनुमति दिन्छ।

Postgres को अघिल्लो संस्करणहरूमा, तपाइँ REINDEX CONCURRENTLY प्रयोग गरी समान परिणाम प्राप्त गर्न सक्नुहुन्छ एकैसाथ अनुक्रमणिका सिर्जना गर्नुहोस्। यसले तपाईंलाई कडा लक नगरी अनुक्रमणिका सिर्जना गर्न अनुमति दिन्छ (ShareUpdateExclusiveLock, जसले समानान्तर प्रश्नहरूमा हस्तक्षेप गर्दैन), त्यसपछि पुरानो अनुक्रमणिकालाई नयाँसँग बदल्नुहोस् र पुरानो अनुक्रमणिका मेटाउनुहोस्। यसले तपाइँलाई तपाइँको अनुप्रयोगमा हस्तक्षेप नगरी अनुक्रमणिका ब्लोट हटाउन अनुमति दिन्छ। यो विचार गर्न महत्त्वपूर्ण छ कि अनुक्रमणिका पुन: निर्माण गर्दा डिस्क उपप्रणालीमा अतिरिक्त भार हुनेछ।

यसैले, यदि अनुक्रमणिकाहरूको लागि "फ्लाइमा" ब्लोट हटाउने तरिकाहरू छन् भने, त्यहाँ टेबलहरूको लागि कुनै पनि छैन। यो जहाँ विभिन्न बाह्य विस्तारहरू खेलमा आउँछन्: pg_repack (पहिले pg_reorg), pgcompact, pgcompacttable र अन्य। यस लेखमा, म तिनीहरूलाई तुलना गर्दिन र केवल pg_repack को बारेमा कुरा गर्नेछु, जुन, केहि परिमार्जन पछि, हामी आफैं प्रयोग गर्छौं।

कसरी pg_repack काम गर्दछ

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

अर्को, pg_repack ले पुरानो तालिकाबाट डाटालाई नयाँ तालिकामा स्थानान्तरण गर्दछ, स्वचालित रूपमा सबै अप्रासंगिक पङ्क्तिहरू फिल्टर गर्दै, र त्यसपछि नयाँ तालिकाको लागि अनुक्रमणिकाहरू सिर्जना गर्दछ। यी सबै कार्यहरूको कार्यान्वयनको क्रममा, परिवर्तनहरू लग तालिकामा जम्मा हुन्छन्।

अर्को चरण नयाँ तालिकामा परिवर्तनहरू स्थानान्तरण गर्नु हो। माइग्रेसन धेरै पुनरावृत्तिहरूमा गरिन्छ, र जब लग तालिकामा 20 भन्दा कम प्रविष्टिहरू बाँकी छन्, pg_repack ले बलियो लक प्राप्त गर्दछ, नवीनतम डाटा माइग्रेट गर्दछ, र Postgres प्रणाली तालिकाहरूमा पुरानो तालिकालाई नयाँसँग बदल्छ। यो एक मात्र र धेरै छोटो समय हो जब तपाईं टेबल संग काम गर्न सक्षम हुनुहुने छैन। यसपछि, पुरानो तालिका र लगहरू भएको तालिका मेटाइन्छ र फाइल प्रणालीमा ठाउँ खाली हुन्छ। प्रक्रिया पूरा भएको छ।

सिद्धान्तमा सबै राम्रो देखिन्छ, तर व्यवहारमा के हुन्छ? हामीले pg_repack बिना लोड र लोड अन्तर्गत परीक्षण गर्यौं, र समयपूर्व रोकिएको अवस्थामा यसको सञ्चालन जाँच गर्‍यौं (अर्को शब्दमा, Ctrl+C प्रयोग गरेर)। सबै परीक्षण सकारात्मक थिए।

हामी खाना पसलमा गयौं - र त्यसपछि सबै कुरा हामीले अपेक्षा गरेअनुसार भएन।

पहिलो प्यानकेक बिक्रीमा

पहिलो क्लस्टरमा हामीले एउटा अद्वितीय अवरोधको उल्लङ्घनको बारेमा त्रुटि प्राप्त गर्यौं:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

यो सीमामा स्वत: उत्पन्न नाम index_16508 थियो - यो pg_repack द्वारा सिर्जना गरिएको थियो। यसको संरचनामा समावेश गरिएका विशेषताहरूको आधारमा, हामीले "हाम्रो" बाधा निर्धारण गर्यौं जुन योसँग मेल खान्छ। समस्या यो एक पूर्ण सामान्य सीमा होइन, तर एक स्थगित एक (स्थगित अवरोध), अर्थात् यसको प्रमाणिकरण sql आदेश भन्दा पछि गरिन्छ, जसले अप्रत्याशित परिणामहरू निम्त्याउँछ।

स्थगित बाधाहरू: तिनीहरू किन आवश्यक छन् र तिनीहरूले कसरी काम गर्छन्

स्थगित प्रतिबन्धहरूको बारेमा सानो सिद्धान्त।
एउटा साधारण उदाहरण विचार गरौं: हामीसँग कारहरूको तालिका-सन्दर्भ पुस्तक दुई विशेषताहरू छन् - डाइरेक्टरीमा कारको नाम र अर्डर।

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

मानौं हामीले पहिलो र दोस्रो कारहरू स्वैप गर्न आवश्यक छ। सीधा समाधान भनेको पहिलो मानलाई दोस्रोमा र दोस्रोलाई पहिलोमा अद्यावधिक गर्नु हो:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

तर जब हामी यो कोड चलाउँछौं, हामी अवरोध उल्लङ्घनको आशा गर्छौं किनभने तालिकामा मानहरूको क्रम अद्वितीय छ:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

म यसलाई कसरी फरक तरिकाले गर्न सक्छु? विकल्प एक: तालिकामा अवस्थित नहुने ग्यारेन्टी गरिएको अर्डरमा अतिरिक्त मूल्य प्रतिस्थापन थप्नुहोस्, उदाहरणका लागि "-1"। प्रोग्रामिङमा, यसलाई "तिहाइ मार्फत दुई चरको मानहरू आदानप्रदान" भनिन्छ। यस विधिको एकमात्र कमजोरी अतिरिक्त अपडेट हो।

विकल्प दुई: पूर्णांकको सट्टा अर्डर मानका लागि फ्लोटिंग पोइन्ट डेटा प्रकार प्रयोग गर्न तालिकालाई पुन: डिजाइन गर्नुहोस्। त्यसपछि, 1 बाट मान अद्यावधिक गर्दा, उदाहरणका लागि, 2.5 मा, पहिलो प्रविष्टि स्वचालित रूपमा दोस्रो र तेस्रो बीचमा "स्ट्यान्ड" हुनेछ। यो समाधान काम गर्दछ, तर त्यहाँ दुई सीमाहरू छन्। पहिले, यदि मान इन्टरफेसमा कतै प्रयोग गरिन्छ भने यसले तपाईंको लागि काम गर्दैन। दोस्रो, डाटा प्रकारको शुद्धतामा निर्भर गर्दै, तपाइँसँग सबै रेकर्डहरूको मानहरू पुन: गणना गर्नु अघि सम्भावित सम्मिलितहरूको सीमित संख्या हुनेछ।

विकल्प तीन: बाधालाई स्थगित गर्नुहोस् ताकि यो कमिटको समयमा मात्र जाँच गरिन्छ:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

हाम्रो प्रारम्भिक अनुरोधको तर्कले प्रतिबद्धताको समयमा सबै मानहरू अद्वितीय छन् भनेर सुनिश्चित गर्दछ, यो सफल हुनेछ।

माथि चर्चा गरिएको उदाहरण, निस्सन्देह, धेरै सिंथेटिक छ, तर यसले विचार प्रकट गर्दछ। हाम्रो अनुप्रयोगमा, हामी तर्क लागू गर्नका लागि स्थगित अवरोधहरू प्रयोग गर्छौं जुन विवादहरू समाधान गर्न जिम्मेवार हुन्छ जब प्रयोगकर्ताहरूले बोर्डमा साझा विजेट वस्तुहरूसँग काम गर्छन्। त्यस्ता प्रतिबन्धहरू प्रयोग गरेर हामीलाई अनुप्रयोग कोड अलि सरल बनाउन अनुमति दिन्छ।

सामान्यतया, बाधाको प्रकारमा निर्भर गर्दै, पोस्टग्रेससँग तिनीहरूलाई जाँच गर्नको लागि तीन स्तरहरू छन्: पङ्क्ति, लेनदेन, र अभिव्यक्ति स्तरहरू।

स्रोत: begriffs

CHECK र NOT NULL सधैं पङ्क्ति स्तरमा जाँच गरिन्छ; अन्य प्रतिबन्धहरूको लागि, तालिकाबाट देख्न सकिन्छ, त्यहाँ विभिन्न विकल्पहरू छन्। तपाईं थप पढ्न सक्नुहुन्छ यहाँ.

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

pg_repack को सुधार

हामीले स्थगित अवरोधहरू के हो भनेर कभर गरेका छौं, तर तिनीहरू हाम्रो समस्यासँग कसरी सम्बन्धित छन्? हामीले पहिले प्राप्त गरेको त्रुटि सम्झौं:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

लग तालिकाबाट नयाँ तालिकामा डेटा प्रतिलिपि गर्दा यो हुन्छ। यो अनौठो देखिन्छ किनकि ... लग तालिकाको डेटा स्रोत तालिकाको डेटासँगै प्रतिबद्ध छ। यदि तिनीहरूले मूल तालिकाको बाधाहरू पूरा गर्छन् भने, तिनीहरूले नयाँ तालिकामा समान बाधाहरू कसरी उल्लङ्घन गर्न सक्छन्?

यो बाहिर जान्छ, समस्याको जड pg_repack को अघिल्लो चरणमा छ, जसले केवल अनुक्रमणिकाहरू सिर्जना गर्दछ, तर बाधाहरू होइन: पुरानो तालिकामा एक अद्वितीय अवरोध थियो, र नयाँले यसको सट्टामा एक अद्वितीय अनुक्रमणिका सिर्जना गर्यो।

यहाँ यो नोट गर्न महत्त्वपूर्ण छ कि यदि बाधा सामान्य छ र स्थगित गरिएको छैन भने, त्यसको सट्टा सिर्जना गरिएको अद्वितीय अनुक्रमणिका यस बाधासँग बराबर छ, किनभने Postgres मा अद्वितीय अवरोधहरू एक अद्वितीय अनुक्रमणिका सिर्जना गरेर लागू गरिन्छ। तर स्थगित अवरोध को मामला मा, व्यवहार समान छैन, किनभने अनुक्रमणिका स्थगित गर्न सकिदैन र sql आदेश कार्यान्वयन भएको समयमा सधैं जाँच गरिन्छ।

तसर्थ, समस्याको सार चेकको "ढिलाइ" मा निहित छ: मूल तालिकामा यो कमिटको समयमा हुन्छ, र नयाँ तालिकामा sql आदेश कार्यान्वयन भएको समयमा। यसको मतलब हामीले जाँचहरू दुवै अवस्थामा उस्तै गरिएको छ भनी सुनिश्चित गर्न आवश्यक छ: या त सधैं ढिलो हुन्छ, वा सधैं तुरुन्तै।

त्यसोभए हामीसँग के विचारहरू थिए?

स्थगित जस्तै अनुक्रमणिका सिर्जना गर्नुहोस्

पहिलो विचार तत्काल मोडमा दुबै जाँचहरू प्रदर्शन गर्न हो। यसले धेरै झूटा सकारात्मक प्रतिबन्धहरू उत्पन्न गर्न सक्छ, तर यदि तिनीहरूमध्ये थोरै छन् भने, यसले प्रयोगकर्ताहरूको कामलाई असर गर्दैन, किनकि त्यस्ता विवादहरू तिनीहरूका लागि सामान्य अवस्था हो। तिनीहरू हुन्छन्, उदाहरणका लागि, जब दुई प्रयोगकर्ताहरूले एकै समयमा एउटै विजेट सम्पादन गर्न थाल्छन्, र दोस्रो प्रयोगकर्ताको क्लाइन्टसँग जानकारी प्राप्त गर्न समय हुँदैन कि विजेट पहिले नै पहिलो प्रयोगकर्ताद्वारा सम्पादनको लागि अवरुद्ध छ। यस्तो अवस्थामा, सर्भरले दोस्रो प्रयोगकर्तालाई अस्वीकार गर्दछ, र यसको क्लाइन्टले परिवर्तनहरू रोल ब्याक गर्छ र विजेट ब्लक गर्दछ। केहि समय पछि, जब पहिलो प्रयोगकर्ताले सम्पादन पूरा गर्दछ, दोस्रोले जानकारी प्राप्त गर्नेछ कि विजेट अब अवरुद्ध छैन र तिनीहरूको कार्य दोहोर्याउन सक्षम हुनेछ।

जाँचहरू सधैं गैर-स्थगन मोडमा छन् भनेर सुनिश्चित गर्न, हामीले मूल स्थगित अवरोध जस्तै नयाँ अनुक्रमणिका सिर्जना गर्यौं:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

परीक्षण वातावरणमा, हामीले केवल केहि अपेक्षित त्रुटिहरू प्राप्त गर्यौं। सफलता! हामीले पुन: उत्पादनमा pg_repack चलायौं र कामको एक घण्टामा पहिलो क्लस्टरमा 5 त्रुटिहरू पायौं। यो एक स्वीकार्य परिणाम हो। यद्यपि, पहिले नै दोस्रो क्लस्टरमा त्रुटिहरूको संख्या उल्लेखनीय रूपमा बढेको छ र हामीले pg_repack रोक्नु पर्यो।

किन यस्तो भयो ? त्रुटि हुने सम्भावना एकै समयमा कति प्रयोगकर्ताहरूले एउटै विजेटहरूसँग काम गरिरहेका छन् भन्नेमा निर्भर गर्दछ। स्पष्ट रूपमा, त्यो क्षणमा पहिलो क्लस्टरमा भण्डारण गरिएको डाटासँग अरूको तुलनामा धेरै कम प्रतिस्पर्धात्मक परिवर्तनहरू थिए, अर्थात्। हामी केवल "भाग्यशाली" थियौं।

आइडियाले काम गरेन। त्यस बिन्दुमा, हामीले दुई अन्य समाधानहरू देख्यौं: स्थगित बाधाहरू हटाउन हाम्रो अनुप्रयोग कोड पुन: लेख्नुहोस्, वा तिनीहरूसँग काम गर्न pg_repack लाई "सिकाउनुहोस्"। हामीले दोस्रो रोज्यौं।

नयाँ तालिकामा अनुक्रमणिकाहरूलाई मूल तालिकाबाट स्थगित बाधाहरूसँग बदल्नुहोस्

संशोधनको उद्देश्य स्पष्ट थियो - यदि मूल तालिकामा स्थगित अवरोध छ भने, त्यसपछि नयाँको लागि तपाईंले यस्तो अवरोध सिर्जना गर्न आवश्यक छ, र अनुक्रमणिका होइन।

हाम्रा परिवर्तनहरू परीक्षण गर्न, हामीले एउटा साधारण परीक्षण लेख्यौं:

• स्थगित बाधा र एक रेकर्ड संग तालिका;
• अवस्थित रेकर्डसँग बाझिएको लुपमा डाटा घुसाउनुहोस्;
• अपडेट गर्नुहोस् - डाटा अब विवाद गर्दैन;
• परिवर्तनहरू प्रतिबद्ध गर्नुहोस्।

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

pg_repack को मूल संस्करण सधैं पहिलो इन्सर्टमा क्र्यास हुन्छ, परिमार्जित संस्करण त्रुटि बिना काम गर्यो। महान।

हामी उत्पादनमा जान्छौं र लग तालिकाबाट नयाँमा डाटा प्रतिलिपि गर्ने एउटै चरणमा फेरि त्रुटि पाउँछौं:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

क्लासिक अवस्था: सबै कुरा परीक्षण वातावरणमा काम गर्दछ, तर उत्पादनमा होइन?!

APPLY_COUNT र दुई ब्याचहरूको जंक्शन

हामीले कोडलाई शाब्दिक रूपमा लाइनद्वारा लाइन विश्लेषण गर्न थाल्यौं र एउटा महत्त्वपूर्ण बिन्दु पत्ता लगायौं: डेटा लग तालिकाबाट ब्याचहरूमा नयाँमा स्थानान्तरण गरिएको छ, APPLY_COUNT स्थिरताले ब्याचको आकारलाई संकेत गर्‍यो:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

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

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

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

त्यसोभए, अर्को कार्य भनेको मूल तालिकाबाट डाटा, जुन एक लेनदेनमा परिवर्तन गरिएको थियो, नयाँ तालिकामा पनि एक लेनदेन भित्र समाप्त हुन्छ भन्ने कुरा सुनिश्चित गर्नु हो।

ब्याचिङबाट इन्कार

र फेरि हामीसँग दुईवटा समाधानहरू थिए। पहिलो: ब्याचहरूमा विभाजन र एक लेनदेनमा डेटा स्थानान्तरणलाई पूर्ण रूपमा त्यागौं। यस समाधानको फाइदा यसको सरलता थियो - आवश्यक कोड परिवर्तनहरू न्यूनतम थिए (यसैले, पुरानो संस्करणहरूमा pg_reorg ले ठीक त्यस्तै काम गर्यो)। तर त्यहाँ एक समस्या छ - हामी एक लामो-चलिरहेको लेनदेन सिर्जना गर्दैछौं, र यो, पहिले नै भनिएको छ, नयाँ ब्लोटको उदयको लागि खतरा हो।

दोस्रो समाधान अधिक जटिल छ, तर सम्भवतः अझ सही छ: तालिकामा डेटा थप्ने लेनदेनको पहिचानकर्ताको साथ लग तालिकामा एउटा स्तम्भ सिर्जना गर्नुहोस्। त्यसपछि, जब हामी डेटा प्रतिलिपि गर्छौं, हामी यसलाई यो विशेषता द्वारा समूहबद्ध गर्न सक्छौं र सम्बन्धित परिवर्तनहरू सँगै स्थानान्तरण गरिएको छ भनेर सुनिश्चित गर्न सक्छौं। ब्याच धेरै लेनदेनहरू (वा एउटा ठूलो) बाट बनाइनेछ र यसको साइज यी लेनदेनहरूमा कति डाटा परिवर्तन गरिएको थियो भन्ने आधारमा भिन्न हुनेछ। यो नोट गर्न महत्त्वपूर्ण छ कि विभिन्न लेनदेनहरूबाट डेटा अनियमित क्रममा लग तालिकामा प्रवेश गर्ने हुनाले, यो पहिले जस्तै क्रमिक रूपमा पढ्न सम्भव हुनेछैन। tx_id द्वारा फिल्टरिंगको साथ प्रत्येक अनुरोधको लागि seqscan धेरै महँगो छ, एक अनुक्रमणिका आवश्यक छ, तर यसले यसलाई अद्यावधिक गर्ने ओभरहेडको कारणले विधिलाई पनि ढिलो गर्नेछ। सामान्यतया, सधैं जस्तै, तपाईंले केहि बलिदान गर्न आवश्यक छ।

त्यसैले, हामीले पहिलो विकल्पको साथ सुरु गर्ने निर्णय गर्यौं, किनकि यो सरल छ। पहिले, यो एक लामो लेनदेन एक वास्तविक समस्या हुनेछ कि बुझ्न आवश्यक थियो। पुरानो तालिकाबाट नयाँमा डेटाको मुख्य स्थानान्तरण पनि एक लामो लेनदेनमा हुने भएकोले, प्रश्न "हामीले यो लेनदेनलाई कति बढाउनेछौं?" मा रूपान्तरित भयो। पहिलो लेनदेनको अवधि मुख्यतया तालिकाको आकारमा निर्भर गर्दछ। नयाँको अवधि डेटा स्थानान्तरणको क्रममा तालिकामा कति परिवर्तनहरू जम्मा हुन्छन् भन्नेमा निर्भर गर्दछ, अर्थात्। भार को तीव्रता मा। pg_repack रन न्यूनतम सेवा लोडको समयमा भएको थियो, र परिवर्तनहरूको मात्रा तालिकाको मूल आकारको तुलनामा असमान रूपमा सानो थियो। हामीले निर्णय गर्यौं कि हामी नयाँ लेनदेनको समयलाई बेवास्ता गर्न सक्छौं (तुलनाको लागि, औसतमा यो 1 घण्टा र 2-3 मिनेट हो)।

प्रयोगहरू सकारात्मक थिए। उत्पादन पनि सुरु गर्नुहोस्। स्पष्टताको लागि, यहाँ दौड पछि डाटाबेस मध्ये एक को आकार संग तस्वीर छ:

हामी यस समाधानसँग पूर्ण रूपमा सन्तुष्ट भएकाले, हामीले दोस्रो कार्यान्वयन गर्ने प्रयास गरेनौं, तर हामी विस्तार विकासकर्ताहरूसँग छलफल गर्ने सम्भावनालाई विचार गर्दैछौं। हाम्रो हालको संशोधन, दुर्भाग्यवश, प्रकाशनको लागि अझै तयार छैन, किनकि हामीले अनौठो स्थगित प्रतिबन्धहरूका साथ मात्र समस्या समाधान गर्यौं, र पूर्ण-प्याचको लागि अन्य प्रकारका लागि समर्थन प्रदान गर्न आवश्यक छ। भविष्यमा यो गर्न सक्षम हुने आशा राख्दछौं।

सायद तपाईसँग प्रश्न छ, किन हामी यस कथामा pg_repack को परिमार्जनको साथ संलग्न भयौं, र उदाहरणका लागि, यसको एनालॉगहरू प्रयोग गरेनौं? केहि बिन्दुमा हामीले यस बारे पनि सोच्यौं, तर यसलाई पहिले नै प्रयोग गर्ने सकारात्मक अनुभवले, विलम्बित अवरोधहरू बिना टेबलहरूमा, हामीलाई समस्याको सार बुझ्न र यसलाई समाधान गर्न प्रयास गर्न उत्प्रेरित गर्‍यो। थप रूपमा, अन्य समाधानहरू प्रयोग गरेर पनि परीक्षणहरू सञ्चालन गर्न समय चाहिन्छ, त्यसैले हामीले निर्णय गर्यौं कि हामी पहिले यसमा समस्या समाधान गर्ने प्रयास गर्नेछौं, र यदि हामीले यो उचित समयमा गर्न सक्दैनौं भन्ने महसुस गर्यौं भने, हामी एनालगहरू हेर्न थाल्छौं। ।

निष्कर्ष

हामीले हाम्रो आफ्नै अनुभवको आधारमा के सिफारिस गर्न सक्छौं:

1. तपाईंको ब्लोट निगरानी गर्नुहोस्। अनुगमन डाटाको आधारमा, तपाईले बुझ्न सक्नुहुन्छ कि अटोभ्याकुम कत्तिको राम्रोसँग कन्फिगर गरिएको छ।
2. ब्लोटलाई स्वीकार्य स्तरमा राख्न AUTOVACUUM समायोजन गर्नुहोस्।
3. यदि ब्लोट अझै बढ्दै छ र तपाईले बाहिरी-बक्स उपकरणहरू प्रयोग गरेर यसलाई हटाउन सक्नुहुन्न भने, बाह्य विस्तारहरू प्रयोग गर्न नडराउनुहोस्। मुख्य कुरा सबै कुरा राम्रोसँग परीक्षण गर्न हो।
4. आफ्नो आवश्यकता अनुरूप बाह्य समाधानहरू परिमार्जन गर्न नडराउनुहोस् - कहिलेकाहीँ यो तपाईंको आफ्नै कोड परिवर्तन गर्नु भन्दा बढी प्रभावकारी र अझ सजिलो हुन सक्छ।

स्रोत: www.habr.com