🚞 👍🏻 🐽 PostgreSQL Antipatterns: लोड के तहत एक बड़ी तालिका को अपडेट करना 🃏 🔼 🆖

यदि आपको "मल्टी-मिलियन" सक्रिय रूप से उपयोग किए गए पोस्टग्रेक्यूएल टेबल में बड़ी संख्या में रिकॉर्ड अपडेट करने की आवश्यकता है, तो मुझे क्या करना चाहिए (और निश्चित रूप से नहीं) - नए फ़ील्ड के मान को चालू करें या मौजूदा रिकॉर्ड में त्रुटियों को सही करें? और एक ही समय में अपना समय बचाएं और डाउनटाइम के कारण कंपनी के पैसे न खोएं।

परीक्षण डेटा तैयार करें:

CREATE TABLE tbl(k text, v integer); INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k , (random() * 100)::integer v FROM generate_series(1, 1000000) i; --  ,   ! CREATE INDEX ON tbl(k, v);

मान लीजिए कि हम सिर्फ just q ’..’ z ’श्रेणी में k के साथ सभी रिकॉर्ड के लिए v के मान को 1 से बढ़ाना चाहते हैं।

लेकिन, प्रयोगों को शुरू करने से पहले, हम हर बार "साफ" परिणाम देने के लिए मूल डेटासेट को सहेजेंगे:

 CREATE TABLE _tbl AS TABLE tbl;

अद्यतन: सभी के लिए एक, और सभी एक के लिए

सबसे आसान विकल्प जो तुरंत दिमाग में आता है वह सब कुछ "एक अद्यतन" में करना है:

 UPDATE tbl SET v = v + 1 WHERE k BETWEEN 'q' AND 'z';

[स्पष्टीकरण पर देखें ।ensor.ru]

बल्कि सरल, यह प्रतीत होता है, पूरी तरह से "छोटी" लाइनों पर ऑपरेशन 2.5 सेकंड से अधिक समय लगा। और यदि आपकी अभिव्यक्ति अधिक जटिल है, तो रेखा अधिक प्रामाणिक है, अधिक रिकॉर्ड हैं, और यहां तक कि कुछ ट्रिगर भी हस्तक्षेप करते हैं - समय मिनटों तक नहीं, बल्कि घंटों तक भी बढ़ सकता है। मान लीजिए कि आप प्रतीक्षा करने के लिए तैयार हैं, और आपका शेष सिस्टम, इस आधार से बंधा हुआ है, अगर इसमें सक्रिय ओएलटीपी लोड है?

समस्या यह है कि जैसे ही UPDATE एक विशेष रिकॉर्ड के लिए जाता है, यह निष्पादन के अंत तक इसे अवरुद्ध करता है । यदि एक साथ एक ही रिकॉर्ड के साथ वह समानांतर रूप से लॉन्च किए गए "स्पॉट" अपडेट पर काम करना चाहता है, तो वह अभी भी अपडेट रिक्वेस्ट के लिए एक ब्लॉक का इंतजार करते हुए "पकड़" लेगा, और अपने काम के बहुत अंत तक शिथिल हो जाएगा।

© wumo.com/wumo

सबसे खराब स्थिति वेब-सिस्टम है, जहां डेटाबेस से कनेक्शन आवश्यकतानुसार निर्मित किए जाते हैं - आखिरकार, ऐसे "झूलने" कनेक्शन जमा होते हैं और डेटाबेस और क्लाइंट दोनों के संसाधनों का उपभोग करेंगे, यदि आप इससे अलग रक्षा तंत्र नहीं बनाते हैं।

विभाजन लेनदेन

सामान्य तौर पर, सब कुछ बहुत अच्छा नहीं है अगर सब कुछ एक अनुरोध में किया जाता है। हां, और भले ही हम एक बड़े UPDATE को कई छोटे-छोटे हिस्सों में बांट दें, लेकिन यह सब एक लेन-देन में काम करना छोड़ दें, लॉकिंग की समस्या समान रहेगी, क्योंकि पूरे लेनदेन के अंत तक परस्पर रिकॉर्ड लॉक होते हैं।

इसलिए, हमें एक बड़े लेनदेन को कई हिस्सों में विभाजित करना होगा। ऐसा करने के लिए, हम या तो बाहरी साधनों का उपयोग कर सकते हैं, और कुछ प्रकार की स्क्रिप्ट लिख सकते हैं जो अलग-अलग लेनदेन उत्पन्न करती हैं, या उन अवसरों का उपयोग करती हैं जो डेटाबेस स्वयं हमें प्रदान कर सकते हैं।

कॉल और लेनदेन प्रबंधन

PostgreSQL 11 से शुरू होकर, प्रक्रियात्मक कोड के अंदर लेनदेन का प्रबंधन करना संभव है :

कॉल कमांड द्वारा, साथ ही साथ कोड के अनाम ब्लॉकों में (डीओ कमांड में) प्रक्रियाओं में, आप COMMIT और ROLLBACK निष्पादित करके लेनदेन पूरा कर सकते हैं। इन आदेशों के द्वारा लेन-देन पूरा होने के बाद, एक नया स्वचालित रूप से शुरू किया जाएगा।

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

उसी कारण से, हम pg_background के माध्यम से स्वायत्त लेनदेन के आयोजन के विकल्प पर विचार नहीं करेंगे।

आधार "अंदर" कनेक्शन का प्रबंधन

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

 CREATE EXTENSION dblink;

"... और कई, कई पी घृणित बच्चों को लाया"

लेकिन डब्लिंक बाइंडिंग बनाने से पहले, आइए पहले पता करें कि एक "नियमित डेवलपर" एक बड़े डेटासेट को कैसे तोड़ता है, जिसे उसे छोटे लोगों में अपडेट करने की आवश्यकता होती है।

Naive LIMIT ... OFFSET

पहला विचार एक "पेजिनेशन" खोज करने का है: प्रत्येक नए अनुरोध में OFFSET को बढ़ाकर "प्रत्येक बार अगले हजार रिकॉर्ड का चयन करें" :

 UPDATE tbl T SET v = Tv + 1 FROM ( SELECT k , v FROM tbl WHERE k BETWEEN 'q' AND 'z' ORDER BY --       k, v --     ! LIMIT $1 OFFSET $2 * $1 ) S WHERE (Tk, Tv) = (Sk, Sv);

इस समाधान के प्रदर्शन का परीक्षण करने से पहले, हम डेटासेट को पुनर्स्थापित करेंगे:

 TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tbl;

जैसा कि हमने ऊपर की योजना में देखा था, हमें लगभग 384K रिकॉर्ड अपडेट करने की आवश्यकता होगी। इसलिए, आइए तुरंत देखें कि अपडेट को अंत के करीब कैसे किया जाएगा - 1000 प्रविष्टियों के 300 वें पुनरावृत्ति के क्षेत्र में :

[स्पष्टीकरण पर देखें ।ensor.ru]

ओह ... पूरे 1K रिकॉर्ड के अंत में सैंपल को अपडेट करना हमें लगभग पूरे मूल संस्करण जितना ही समय लगेगा!

यह हमारी पसंद नहीं है। यदि आप कुछ पुनरावृत्तियों और छोटे OFFSET मान प्राप्त करते हैं तो भी इसका उपयोग किसी भी तरह किया जा सकता है। क्योंकि डेटाबेस के लिए LIMIT X OFFSET Y " X + Y रिकॉर्ड्स को घटाने / चुनने / फॉर्म करने के बराबर है , और फिर पहले Y को कूड़ेदान में फेंक देता है ", जो Y के बड़े मूल्यों के लिए दुखद लगता है।

वास्तव में, इस विधि को लागू नहीं किया जा सकता है ! न केवल हम चयन के लिए अद्यतन मूल्यों पर भरोसा करते हैं, हम रिकॉर्ड के भाग को छोड़ देने का भी जोखिम रखते हैं, और यदि समान कुंजियों वाले ब्लॉक पृष्ठ सीमा पर आते हैं तो दूसरे भाग को दो बार अपडेट करते हैं:

इस उदाहरण में, हमने दो बार हरा रिकॉर्ड अपडेट किया, और लाल रिकॉर्ड कभी नहीं। सिर्फ इसलिए कि सॉर्ट कीज़ के समान मानों के साथ, ऐसे ब्लॉक के अंदर रिकॉर्ड्स का क्रम खुद तय नहीं होता है।

उदास आदेश द्वारा ... सीमा

आइए कार्य को थोड़ा संशोधित करें - एक नया फ़ील्ड जोड़ें जिसमें हम अपना मान v + 1 लिखेंगे:

 ALTER TABLE tbl ADD COLUMN x integer;

कृपया ध्यान दें कि यह डिज़ाइन लगभग पूरी तरह से काम करता है, बिना पूरी तालिका को लिखे। लेकिन अगर आप एक DEFAULT मान जोड़ते हैं, तो - केवल 11 वें संस्करण से शुरू होता है ।

पहले से ही कड़वे अनुभव से सिखाया जाता है, चलो तुरंत एक सूचकांक बनाते हैं जिसमें केवल अनधिकृत प्रविष्टियाँ रहेंगी:

 CREATE INDEX CONCURRENTLY ON tbl(k, v) WHERE x IS NULL;

CONCURRENTLY इंडेक्स तालिका के साथ रीड-राइट काम को ब्लॉक नहीं करता है, जबकि यह धीरे-धीरे एक विशाल डेटासेट पर भी रोल करता है।

अब विचार यह है कि "इस सूचकांक से हर बार केवल पहले हजार रिकॉर्ड का चयन करें" :

 UPDATE tbl T SET x = Tv + 1 FROM ( SELECT k, v FROM tbl WHERE k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1000 --   OFFSET! ) S WHERE (Tk, Tv) = (Sk, Sv) AND Tx IS NULL;

[स्पष्टीकरण पर देखें ।ensor.ru]

पहले से बहुत बेहतर - प्रत्येक व्यक्तिगत लेनदेन की अवधि अब लगभग 6 गुना कम है।

लेकिन फिर देखते हैं कि 200 वें पुनरावृत्ति की योजना क्या होगी:

 Update on tbl t (actual time=530.591..530.591 rows=0 loops=1) Buffers: shared hit=789337 read=1 dirtied=1

समय फिर से खराब हो गया (केवल 25%, हालांकि), और बफ़र्स में वृद्धि हुई - लेकिन क्यों?
तथ्य यह है कि PostgreSQL में MVCC सूचकांक में "मृत आत्माओं" को छोड़ देता है - पहले से अपडेट किए गए रिकॉर्ड के संस्करण, अब सूचकांक के लिए उपयुक्त नहीं है। यही है, 200 वें पुनरावृत्ति पर केवल पहले 1000 रिकॉर्ड लेते हुए, हम अभी भी स्कैन करते हैं , भले ही बाद में, हम पहले से बदले गए ट्यूपल्स के पिछले 199K संस्करणों को छोड़ देते हैं।

यदि हमारे लिए पुनरावृत्तियों को कई सैकड़ों नहीं, बल्कि कई सैकड़ों हजार की आवश्यकता है, तो प्रत्येक बाद के क्वेरी निष्पादन के साथ गिरावट अधिक ध्यान देने योग्य होगी।

खंड द्वारा अद्यतन

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

एक अनुक्रमित जोड़ी (k, v) हमारे कार्य के लिए उत्कृष्ट है। आइए एक क्वेरी बनाएं ताकि यह अंतिम संसाधित जोड़ी पर निर्माण कर सके:

 WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1;

पहली पुनरावृत्ति में, क्वेरी पैरामीटर को "शून्य" मान ('', 0) पर सेट करने के लिए पर्याप्त है, और प्रत्येक अगले पुनरावृत्ति के लिए हम पिछली क्वेरी का परिणाम लेते हैं।

[स्पष्टीकरण पर देखें ।ensor.ru]

लेन-देन / लॉक का समय एक मिलीसेकंड से कम है, पुनरावृत्तियों की संख्या में कोई गिरावट नहीं है, तालिका में सभी डेटा की एक पूर्ण प्रारंभिक स्कैन की आवश्यकता नहीं है। बहुत बढ़िया!

डब्लिंक के साथ अंतिम संस्करण लाना

 DO $$ DECLARE k text = ''; v integer = 0; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port')); --  PREPARED STATEMENT,      PERFORM dblink($q$ PREPARE _q(text, integer) AS WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1; $q$); -- ,    LOOP SELECT * INTO k, v FROM dblink($p$EXECUTE _q('$p$ || k || $p$',$p$ || v || $p$)$p$) T(k text, v integer); RAISE NOTICE '(k,v) = (''%'',%)', k, v; --   ,     EXIT WHEN (k, v) IS NULL; END LOOP; PERFORM dblink_disconnect(); END; $$ LANGUAGE plpgsql;

इस पद्धति का एक अतिरिक्त लाभ किसी भी समय इस स्क्रिप्ट के निष्पादन को बाधित करने की क्षमता है, और फिर वांछित बिंदु से फिर से शुरू करना है।

UPDATE में जटिल गणना

जब आप लिंक किए गए तालिकाओं से कुछ गणना करने की आवश्यकता होती है, तो मैं अलग से निर्दिष्ट मान की कठिन गणना के साथ स्थिति का उल्लेख करूंगा।

कंप्यूटिंग पर खर्च किया गया समय लेनदेन की अवधि को भी बढ़ाता है। इसलिए, सबसे अच्छा विकल्प यह होगा कि आप इन मूल्यों की गणना UPDATE से आगे करें।

उदाहरण के लिए, हम अपने नए फ़ील्ड x को उन रिकॉर्डों की संख्या के साथ आबाद करना चाहते हैं जिनका मूल्य (k, v) समान है। चलो एक "अस्थायी" तालिका बनाते हैं, जिसकी पीढ़ी अतिरिक्त ताले नहीं लगाती है:

 CREATE TABLE tmp AS SELECT k, v, count(*) x FROM tbl GROUP BY 1, 2; CREATE INDEX ON tmp(k, v);

अब हम इस तालिका के अनुसार ऊपर वर्णित मॉडल के अनुसार लक्ष्य को अद्यतन कर सकते हैं:

 UPDATE tbl T SET x = Sx FROM tmp S WHERE (Tk, Tv) = (Sk, Sv) AND (Sk, Sv) = ($1, $2);

जैसा कि आप देख सकते हैं, कोई जटिल गणना की आवश्यकता नहीं है।

बस बाद में सहायक तालिका को हटाने के लिए याद रखें।

PostgreSQL Antipatterns: लोड के तहत एक बड़ी तालिका को अपडेट करना