🤸🏾 🤱🏽 💃🏽 DBA: जब VACUUM गुजरता है - हम तालिका को मैन्युअल रूप से साफ करते हैं 👨🏿‍🌾 🧑🏽‍🤝‍🧑🏽 ◻️

डाकघरों में VACUUM एक मेज से "सफाई" कर सकता है, जो कोई भी नहीं देख सकता है - अर्थात, इन रिकॉर्डों को बदलने से पहले शुरू की गई एक भी सक्रिय क्वेरी नहीं है।

लेकिन अगर ऐसा कोई अप्रिय प्रकार (ओएलटीपी आधार पर दीर्घकालिक ओएलएपी लोड) है? लंबे प्रश्नों से घिरे एक सक्रिय रूप से बदलते टेबल को कैसे साफ करें और एक रेक पर कदम न रखें?

हमने एक रेक फैलाया

सबसे पहले, हम यह निर्धारित करते हैं कि यह क्या है और जिस समस्या को हम हल करना चाहते हैं वह कैसे उत्पन्न हो सकती है।

आमतौर पर यह स्थिति अपेक्षाकृत छोटी मेज पर होती है , लेकिन इसमें बहुत सारे बदलाव होते हैं । आमतौर पर, ये या तो अलग-अलग काउंटर / एग्रीगेट / रेटिंग होते हैं , जिन पर UPDATE को अक्सर-अक्सर निष्पादित किया जाता है, या किसी प्रकार की घटनाओं की लगातार चलने वाली स्ट्रीम को संसाधित करने के लिए एक बफर कतार होती है, जिसके बारे में रिकॉर्ड हमेशा INSERT / DELETE होते हैं।

आइए रेटिंग के साथ विकल्प को पुन: पेश करने का प्रयास करें:

CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC); --       INSERT INTO tbl SELECT chr(ascii('a'::text) + i) k , 0 v FROM generate_series(0, 25) i;

और समानांतर में, एक अलग कनेक्शन में, एक लंबी-लंबी क्वेरी शुरू होती है, कुछ जटिल आंकड़े एकत्र करते हैं, लेकिन हमारी तालिका को प्रभावित नहीं करते हैं :

 SELECT pg_sleep(10000);

अब हम कई बार काउंटरों में से एक के मूल्य को अपडेट करते हैं। प्रयोग की शुद्धता के लिए, हम इसे अलग-अलग लेन-देन में dblink का उपयोग करके करेंगे , क्योंकि यह वास्तविकता में होगा:

 DO $$ DECLARE i integer; tsb timestamp; tse timestamp; d double precision; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port')); FOR i IN 1..10000 LOOP tsb = clock_timestamp(); PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$); tse = clock_timestamp(); IF i % 1000 = 0 THEN d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000; RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5); END IF; END LOOP; PERFORM dblink_disconnect(); END; $$ LANGUAGE plpgsql;

 NOTICE: i = 1000, exectime = 0.524 NOTICE: i = 2000, exectime = 0.739 NOTICE: i = 3000, exectime = 1.188 NOTICE: i = 4000, exectime = 2.508 NOTICE: i = 5000, exectime = 1.791 NOTICE: i = 6000, exectime = 2.658 NOTICE: i = 7000, exectime = 2.318 NOTICE: i = 8000, exectime = 2.572 NOTICE: i = 9000, exectime = 2.929 NOTICE: i = 10000, exectime = 3.808

क्या हुआ था? क्यों, एकल रिकॉर्ड के सरलतम अद्यतन के लिए भी , रनटाइम 7 गुना - 0.524ms से 3.808ms तक घटाया गया? और हमारी रेटिंग को और अधिक धीरे-धीरे और धीरे-धीरे बनाया जा रहा है।

MVCC को दोष देना है

यह MVCC तंत्र के बारे में है , जो रिकॉर्ड के सभी पिछले संस्करणों को देखने के लिए अनुरोध करता है। तो आइए "मृत" संस्करणों से अपनी तालिका साफ करें:

 VACUUM VERBOSE tbl;

 INFO: vacuuming "public.tbl" INFO: "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages DETAIL: 10000 dead row versions cannot be removed yet, oldest xmin: 597439602

ओह, साफ करने के लिए कुछ भी नहीं है! एक समानांतर क्वेरी हमें परेशान कर रही है - आखिरकार, एक दिन वह इन संस्करणों को संदर्भित करना चाहता है (क्या हो सकता है?), और वे उसके लिए उपलब्ध होना चाहिए। और इसलिए VACUUM FULL भी हमारी मदद नहीं करेगा।

"क्लैम्पिंग" तालिका

लेकिन हम यह सुनिश्चित करने के लिए जानते हैं कि हमारी तालिका को हमारी क्वेरी की आवश्यकता नहीं है। इसलिए, VACUUM पास होने के बाद, सिस्टम के प्रदर्शन को एक पर्याप्त ढांचे में वापस करने की कोशिश करें, जिससे मेज से सब कुछ कम-से-कम "मैन्युअल" से बाहर हो जाए।

इसे स्पष्ट करने के लिए, आइए हम एक बफर टेबल का एक उदाहरण मानते हैं। अर्थात्, एक बड़ी INSERT / DELETE स्ट्रीम है, और कभी-कभी तालिका पूरी तरह से खाली होती है। लेकिन अगर यह वहां खाली नहीं है, तो हमें इसकी वर्तमान सामग्री को सहेजना होगा।

# 0: स्थिति का मूल्यांकन करें

यह स्पष्ट है कि आप प्रत्येक ऑपरेशन के बाद भी तालिका के साथ कुछ करने की कोशिश कर सकते हैं, लेकिन यह बहुत मायने नहीं रखता है - रखरखाव ओवरहेड स्पष्ट रूप से लक्षित अनुरोधों के प्रवाह से अधिक होगा।

हम मानदंड बनाते हैं - "कार्य करने का समय" यदि:

वैक्यूम लंबे समय से चल रहा है
हम एक बड़े भार की उम्मीद करते हैं, इसलिए इसे अंतिम [ऑटो] VACUUM से 60 सेकंड तक रहने दें।
भौतिक तालिका का आकार लक्ष्य से बड़ा है
हम इसे न्यूनतम आकार के सापेक्ष पृष्ठों की दोगुनी संख्या (8KB ब्लॉक) के रूप में परिभाषित करते हैं - प्रत्येक रिक्तिका के लिए 1 ढेर प्रति + + 1 blk - एक संभावित खाली तालिका के लिए। यदि हम उम्मीद करते हैं कि डेटा की एक निश्चित मात्रा हमेशा "सामान्य रूप से" बफर में रहेगी, तो इस सूत्र को कसने के लिए उचित है।

सत्यापन का अनुरोध

 SELECT relpages , (( SELECT count(*) FROM pg_index WHERE indrelid = cl.oid ) + 1) << 13 size_norm --    * current_setting('block_size')::bigint,     ?.. , pg_total_relation_size(oid) size , coalesce(extract('epoch' from (now() - greatest( pg_stat_get_last_vacuum_time(oid) , pg_stat_get_last_autovacuum_time(oid) ))), 1 << 30) vaclag FROM pg_class cl WHERE oid = $1::regclass -- tbl LIMIT 1;

 relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 1105920 | 3392.484835

# 1: वैसे भी

हम पहले से नहीं जान सकते कि क्या समानांतर क्वेरी वास्तव में हमें रोक रही है - अपनी स्थापना के बाद से कितने रिकॉर्ड "पुराने" हैं। इसलिए, जब हम किसी भी तरह से तालिका को संसाधित करने का निर्णय लेते हैं, तो किसी भी स्थिति में, आपको पहले उस पर VACUUM निष्पादित करना चाहिए - यह, VACUUM FULL के विपरीत, रीड-राइट डेटा के साथ समानांतर प्रक्रियाओं में हस्तक्षेप नहीं करता है।

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

# 2: क्या कोई घर पर है?

आइए देखें - क्या तालिका में कुछ भी है:

 TABLE tbl LIMIT 1;

यदि एक भी रिकॉर्ड नहीं बचा है, तो हम प्रसंस्करण पर बहुत कुछ बचा सकते हैं - सिर्फ TRUNCATE कर के:

यह उसी तरह से कार्य करता है जैसे प्रत्येक टेबल के लिए बिना शर्त DELETE कमांड, लेकिन बहुत तेज, क्योंकि यह वास्तव में टेबल को स्कैन नहीं करता है। इसके अलावा, यह तुरंत डिस्क स्थान को मुक्त करता है, इसलिए इसके बाद एक VACUUM ऑपरेशन करने की आवश्यकता नहीं है।

चाहे आपको एक ही समय में तालिका अनुक्रम (RESTART IDENTITY) के काउंटर को रीसेट करने की आवश्यकता हो - अपने लिए निर्णय लें।

# 3: सब कुछ - बदले में!

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

ऐसा करने के लिए, हमें अपने लेन-देन के लिए SERIALIZABLE अलगाव को सक्षम करने की आवश्यकता है (हां, यहां हम लेनदेन शुरू करते हैं) और तालिका को "कसकर" लॉक करें:

 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

अवरुद्ध करने का यह स्तर उन कार्यों के कारण है जो हम उस पर प्रदर्शन करना चाहते हैं।

# 4: हितों का टकराव

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

ऐसा होने से रोकने के लिए, "खुद को बलिदान करें" - अगर हम अभी भी एक निश्चित (अनुमेय छोटे) समय के लिए ताला नहीं लगा पाएंगे, तो हमें डेटाबेस से एक अपवाद मिलेगा, लेकिन कम से कम हम बाकी को परेशान नहीं करेंगे।

ऐसा करने के लिए, सत्र चर lock_timeout (संस्करण 9.3+ के लिए) या / और statement_timeout सेट करें । याद रखने वाली मुख्य बात यह है कि statement_time का मान केवल अगले कथन से लागू होता है। अर्थात्, इस तरह gluing में, यह काम नहीं करेगा :

 SET statement_timeout = ...;LOCK TABLE ...;

चर के "पुराने" मूल्य की बाद की बहाली से निपटने के लिए नहीं, हम SET LOCAL फॉर्म का उपयोग करते हैं, जो सेटिंग्स के दायरे को वर्तमान लेनदेन तक सीमित करता है।

याद रखें कि statement_timeout बाद के सभी अनुरोधों पर लागू होता है ताकि लेनदेन तालिका में बहुत अधिक डेटा होने पर अस्वीकार्य मूल्यों तक नहीं पहुंच सके।

# 5: कॉपी डेटा

यदि तालिका पूरी तरह से खाली नहीं हुई है, तो डेटा को सहायक अस्थायी लेबल के माध्यम से फिर से सहेजना होगा:

 CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

ON COMMIT DROP हस्ताक्षर का मतलब है कि जिस समय लेन-देन समाप्त होगा, अस्थायी तालिका समाप्त हो जाएगी, और आपको कनेक्शन के संदर्भ में इसे मैन्युअल रूप से हटाने की आवश्यकता नहीं है।

चूंकि हम मानते हैं कि बहुत सारे "लाइव" डेटा नहीं हैं, इसलिए यह ऑपरेशन जल्दी से पर्याप्त होना चाहिए।

खैर, यह सब! यदि आवश्यक हो तो तालिका के आंकड़ों को सामान्य करने के लिए लेनदेन पूरा होने के बाद ANALYZE को चलाना याद रखें।

हम अंतिम स्क्रिप्ट एकत्र करते हैं

हम ऐसे "छद्म अजगर" का उपयोग करते हैं:

 #     stat <- SELECT relpages , (( SELECT count(*) FROM pg_index WHERE indrelid = cl.oid ) + 1) << 13 size_norm , pg_total_relation_size(oid) size , coalesce(extract('epoch' from (now() - greatest( pg_stat_get_last_vacuum_time(oid) , pg_stat_get_last_autovacuum_time(oid) ))), 1 << 30) vaclag FROM pg_class cl WHERE oid = $1::regclass -- table_name LIMIT 1; #      VACUUM   if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60: -> VACUUM %table; try: -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; #         1s -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s'; -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE; #          row <- TABLE %table LIMIT 1; #       ""  -   ,    - ""      if row is None: -> TRUNCATE TABLE %table RESTART IDENTITY; else: #      - -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table; #      -> TRUNCATE TABLE %table; #         -> INSERT INTO %table TABLE _tmp_swap; -> COMMIT; except Exception as e: #    ,     "" -   if not isinstance(e, InterfaceError): -> ROLLBACK;

और क्या आप डेटा को दूसरी बार कॉपी नहीं कर सकते हैं?

सिद्धांत रूप में, यह संभव है यदि तालिका की ओड स्वयं डीबी से बीएल पक्ष या एफके से किसी अन्य गतिविधियों से बंधे नहीं है:

 CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL); INSERT INTO _swap_%table TABLE %table; DROP TABLE %table; ALTER TABLE _swap_%table RENAME TO %table;

चलो स्रोत तालिका पर स्क्रिप्ट चलाते हैं और मैट्रिक्स की जाँच करते हैं:

 VACUUM tbl; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s'; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE; CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tmp_swap; COMMIT;

 relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 49152 | 32.705771

सब कुछ काम कर गया! तालिका 50 गुना सिकुड़ गई है, और सभी अद्यतन फिर से तेजी से चलते हैं।

DBA: जब VACUUM गुजरता है - हम तालिका को मैन्युअल रूप से साफ करते हैं