पोस्टग्रेस: ​​ब्लोट, pg_repack आणि स्थगित मर्यादा

टेबल्स आणि इंडेक्सेसवर ब्लोटचा प्रभाव व्यापकपणे ज्ञात आहे आणि केवळ पोस्टग्रेसमध्येच नाही. व्हॅक्यूम फुल किंवा क्लस्टर सारख्या बॉक्सच्या बाहेर हाताळण्याचे मार्ग आहेत, परंतु ते ऑपरेशन दरम्यान टेबल लॉक करतात आणि म्हणून नेहमी वापरले जाऊ शकत नाहीत.

लेखात ब्लोट कसा होतो, तुम्ही त्याचा कसा सामना करू शकता, लांबणीवर टाकलेल्या मर्यादांबद्दल आणि pg_repack एक्स्टेंशनच्या वापरात येणाऱ्या समस्यांबद्दल थोडा सिद्धांत असेल.

यावर आधारित हा लेख लिहिला आहे माझे भाषण PgConf.Russia 2020 येथे.

सूज का येते?

पोस्टग्रेस बहु-आवृत्ती मॉडेलवर आधारित आहे (MVCC). त्याचे सार असे आहे की सारणीतील प्रत्येक पंक्तीमध्ये अनेक आवृत्त्या असू शकतात, तर व्यवहारांमध्ये यापैकी एकापेक्षा जास्त आवृत्त्या दिसत नाहीत, परंतु समान असणे आवश्यक नाही. हे अनेक व्यवहारांना एकाच वेळी कार्य करण्यास अनुमती देते आणि त्यांचा एकमेकांवर अक्षरशः कोणताही प्रभाव पडत नाही.

अर्थात, या सर्व आवृत्त्या संग्रहित करणे आवश्यक आहे. पोस्टग्रेस मेमरी पृष्ठासह पृष्ठानुसार कार्य करते आणि पृष्ठ म्हणजे डिस्कवरून वाचता किंवा लिखित डेटाची किमान रक्कम असते. हे कसे घडते हे समजून घेण्यासाठी एक लहान उदाहरण पाहू.

समजा आमच्याकडे एक टेबल आहे ज्यामध्ये आम्ही अनेक रेकॉर्ड जोडल्या आहेत. नवीन डेटा फाइलच्या पहिल्या पानावर दिसू लागला आहे जेथे टेबल संग्रहित आहे. या पंक्तींच्या थेट आवृत्त्या आहेत ज्या कमिटनंतर इतर व्यवहारांसाठी उपलब्ध असतात (साधेपणासाठी, आम्ही असे गृहीत धरू की अलगाव पातळी वाचनीय आहे).

त्यानंतर आम्ही एक प्रविष्टी अद्यतनित केली, ज्यामुळे जुनी आवृत्ती यापुढे संबंधित नाही म्हणून चिन्हांकित केली.

टप्प्याटप्प्याने, पंक्तीच्या आवृत्त्या अद्यतनित करणे आणि हटवणे, आम्ही एका पृष्ठासह समाप्त केले ज्यामध्ये अंदाजे अर्धा डेटा "कचरा" आहे. हा डेटा कोणत्याही व्यवहारासाठी दिसत नाही.

पोस्टग्रेसची एक यंत्रणा आहे पोकळी, जे अप्रचलित आवृत्त्या साफ करते आणि नवीन डेटासाठी जागा बनवते. परंतु जर ते पुरेसे आक्रमकपणे कॉन्फिगर केलेले नसेल किंवा इतर सारण्यांमध्ये व्यस्त असेल तर "कचरा डेटा" शिल्लक राहतो आणि आम्हाला नवीन डेटासाठी अतिरिक्त पृष्ठे वापरावी लागतील.

तर आमच्या उदाहरणात, काही वेळा टेबलमध्ये चार पृष्ठे असतील, परंतु त्यातील फक्त अर्धा थेट डेटा असेल. परिणामी, टेबलमध्ये प्रवेश करताना, आम्ही आवश्यकतेपेक्षा जास्त डेटा वाचू.

VACUUM ने आता सर्व असंबद्ध पंक्ती आवृत्त्या हटवल्या तरीही, परिस्थिती नाटकीयरित्या सुधारणार नाही. आमच्याकडे नवीन पंक्तींसाठी पृष्ठांमध्ये किंवा अगदी संपूर्ण पृष्ठांमध्ये मोकळी जागा असेल, परंतु तरीही आम्ही आवश्यकतेपेक्षा अधिक डेटा वाचत आहोत.
तसे, जर पूर्णपणे रिक्त पृष्ठ (आमच्या उदाहरणातील दुसरे) फाईलच्या शेवटी असेल तर VACUUM ते ट्रिम करण्यास सक्षम असेल. पण आता ती मध्येच आहे, त्यामुळे तिच्यासोबत काहीच करता येत नाही.

जेव्हा अशा रिकाम्या किंवा अत्यंत विरळ पानांची संख्या मोठी होते, ज्याला ब्लोट म्हणतात, तेव्हा त्याचा परिणाम कार्यक्षमतेवर होऊ लागतो.

वर वर्णन केलेली प्रत्येक गोष्ट टेबलमध्ये ब्लोट होण्याचे यांत्रिकी आहे. इंडेक्समध्ये हे त्याच प्रकारे घडते.

मला सूज आहे का?

तुम्हाला सूज आहे की नाही हे निर्धारित करण्याचे अनेक मार्ग आहेत. पहिली कल्पना म्हणजे अंतर्गत पोस्टग्रेस आकडेवारी वापरणे, ज्यामध्ये टेबलमधील पंक्तींची संख्या, "लाइव्ह" पंक्तींची संख्या इ. बद्दल अंदाजे माहिती असते. तुम्हाला इंटरनेटवर तयार स्क्रिप्टचे बरेच भिन्नता आढळू शकतात. आम्ही आधार म्हणून घेतला स्क्रिप्ट PostgreSQL तज्ञांकडून, जे टोस्ट आणि ब्लोट बीट्री इंडेक्ससह ब्लोट टेबल्सचे मूल्यांकन करू शकतात. आमच्या अनुभवात, त्याची त्रुटी 10-20% आहे.

दुसरा मार्ग म्हणजे विस्तार वापरणे pgstattuple, जे तुम्हाला पृष्ठांच्या आत पाहण्याची आणि अंदाजे आणि अचूक ब्लोट मूल्य दोन्ही मिळविण्यास अनुमती देते. परंतु दुसऱ्या प्रकरणात, तुम्हाला संपूर्ण टेबल स्कॅन करावे लागेल.

आम्ही एक लहान ब्लोट मूल्य मानतो, 20% पर्यंत, स्वीकार्य. साठी फिलफॅक्टरचे एनालॉग मानले जाऊ शकते टेबल и निर्देशांक. 50% आणि त्यावरील, कार्यप्रदर्शन समस्या सुरू होऊ शकतात.

ब्लोटचा सामना करण्याचे मार्ग

पोस्टग्रेसकडे बॉक्सच्या बाहेर ब्लोट हाताळण्याचे अनेक मार्ग आहेत, परंतु ते नेहमीच प्रत्येकासाठी योग्य नसतात.

ऑटोव्हॅक्युम कॉन्फिगर करा जेणेकरून ब्लोट होणार नाही. किंवा अधिक तंतोतंत, ते आपल्यासाठी स्वीकार्य पातळीवर ठेवण्यासाठी. हे "कर्णधार" सल्ल्यासारखे दिसते, परंतु प्रत्यक्षात हे साध्य करणे नेहमीच सोपे नसते. उदाहरणार्थ, तुमचा डेटा स्कीमामध्ये नियमित बदलांसह सक्रिय विकास आहे किंवा काही प्रकारचे डेटा स्थलांतर होत आहे. परिणामी, तुमचे लोड प्रोफाइल वारंवार बदलू शकते आणि सामान्यत: टेबल ते टेबल बदलू शकते. याचा अर्थ तुम्हाला सतत थोडे पुढे काम करावे लागेल आणि प्रत्येक टेबलच्या बदलत्या प्रोफाइलमध्ये ऑटोव्हॅक्यूम समायोजित करावे लागेल. पण हे करणे सोपे नाही हे उघड आहे.

AUTOVACUUM टेबल्स सोबत ठेवू शकत नाही याचे आणखी एक सामान्य कारण म्हणजे तेथे दीर्घकाळ चालणारे व्यवहार आहेत जे त्यास त्या व्यवहारांसाठी उपलब्ध असलेला डेटा साफ करण्यापासून प्रतिबंधित करतात. येथे शिफारस देखील स्पष्ट आहे - "लटकत" व्यवहारांपासून मुक्त व्हा आणि सक्रिय व्यवहारांचा वेळ कमी करा. परंतु जर तुमच्या अर्जावरील भार OLAP आणि OLTP चा संकरीत असेल, तर तुमच्याकडे एकाच वेळी अनेक वारंवार अद्यतने आणि लहान क्वेरी, तसेच दीर्घकालीन ऑपरेशन्स असू शकतात - उदाहरणार्थ, अहवाल तयार करणे. अशा परिस्थितीत, भार वेगवेगळ्या तळांवर पसरविण्याबद्दल विचार करणे योग्य आहे, जे त्या प्रत्येकाला अधिक बारीक-ट्यूनिंग करण्यास अनुमती देईल.

दुसरे उदाहरण - जरी प्रोफाइल एकसंध असले तरी, परंतु डेटाबेस खूप जास्त भाराखाली असेल, तर सर्वात आक्रमक ऑटोव्हॅक्युम देखील सामना करू शकत नाही आणि ब्लोट होईल. स्केलिंग (उभ्या किंवा क्षैतिज) हा एकमेव उपाय आहे.

अशा परिस्थितीत काय करावे जेथे आपण ऑटोव्हॅक्युम सेट केले आहे, परंतु फुगणे सुरूच आहे.

संघ व्हॅक्यूम पूर्ण टेबल्स आणि इंडेक्सेसची सामग्री पुन्हा तयार करते आणि त्यात फक्त संबंधित डेटा ठेवतो. ब्लोट दूर करण्यासाठी, ते उत्तम प्रकारे कार्य करते, परंतु त्याच्या अंमलबजावणीदरम्यान टेबलवरील एक अनन्य लॉक कॅप्चर केले जाते (AccessExclusiveLock), जे या टेबलवर क्वेरी कार्यान्वित करण्यास अनुमती देणार नाही, अगदी निवडते. जर तुम्हाला तुमची सेवा किंवा त्याचा काही भाग काही काळ थांबवता येत असेल (डेटाबेस आणि तुमच्या हार्डवेअरच्या आकारानुसार दहा मिनिटांपासून ते कित्येक तासांपर्यंत), तर हा पर्याय सर्वोत्तम आहे. दुर्दैवाने, आमच्याकडे नियोजित देखभाल दरम्यान व्हॅक्यूम फुल चालवण्यासाठी वेळ नाही, त्यामुळे ही पद्धत आमच्यासाठी योग्य नाही.

संघ क्लस्टर सारण्यांची सामग्री व्हॅक्यूम फुल प्रमाणेच पुनर्बांधणी करते, परंतु आपल्याला एक निर्देशांक निर्दिष्ट करण्याची परवानगी देते ज्यानुसार डेटा डिस्कवर भौतिकरित्या ऑर्डर केला जाईल (परंतु भविष्यात नवीन पंक्तींसाठी ऑर्डरची हमी दिली जात नाही). काही विशिष्ट परिस्थितींमध्ये, अनेक प्रश्नांसाठी हे एक चांगले ऑप्टिमायझेशन आहे - निर्देशांकानुसार अनेक रेकॉर्ड वाचून. कमांडचा तोटा व्हॅक्यूम फुल सारखाच आहे - ते ऑपरेशन दरम्यान टेबल लॉक करते.

संघ REINDEX मागील दोन प्रमाणेच, परंतु विशिष्ट निर्देशांक किंवा सारणीच्या सर्व अनुक्रमणिका पुन्हा तयार करतात. लॉक किंचित कमकुवत आहेत: टेबलवरील ShareLock (फेरफार प्रतिबंधित करते, परंतु निवड करण्यास अनुमती देते) आणि पुन्हा तयार होत असलेल्या निर्देशांकावरील AccessExclusiveLock (या निर्देशांकाचा वापर करून क्वेरी अवरोधित करते). तथापि, पोस्टग्रेसच्या 12 व्या आवृत्तीमध्ये एक पॅरामीटर दिसला एकाच वेळी, जे तुम्हाला समवर्ती जोडणे, बदल करणे किंवा रेकॉर्ड हटवणे अवरोधित न करता अनुक्रमणिका पुन्हा तयार करण्याची परवानगी देते.

Postgres च्या पूर्वीच्या आवृत्त्यांमध्ये, तुम्ही वापरून REINDEX सारखाच परिणाम मिळवू शकता एकाचवेळी निर्देशांक तयार करा. हे तुम्हाला कठोर लॉकिंगशिवाय अनुक्रमणिका तयार करण्यास अनुमती देते (ShareUpdateExclusiveLock, जे समांतर प्रश्नांमध्ये व्यत्यय आणत नाही), नंतर जुना निर्देशांक नवीनसह बदला आणि जुना निर्देशांक हटवा. हे तुम्हाला तुमच्या अर्जामध्ये हस्तक्षेप न करता इंडेक्स ब्लोट दूर करण्यास अनुमती देते. हे लक्षात घेणे आवश्यक आहे की निर्देशांकांची पुनर्बांधणी करताना डिस्क सबसिस्टमवर अतिरिक्त भार असेल.

अशा प्रकारे, जर निर्देशांकांसाठी “माशीवर” ब्लोट दूर करण्याचे मार्ग असतील तर टेबलसाठी काहीही नाही. येथे विविध बाह्य विस्तार कार्यात येतात: pg_repack (पूर्वी pg_reorg), pgcompact, pgcompacttable आणि इतर. या लेखात, मी त्यांची तुलना करणार नाही आणि फक्त pg_repack बद्दल बोलेन, जे काही बदल केल्यानंतर, आम्ही स्वतः वापरतो.

pg_repack कसे कार्य करते

समजा आमच्याकडे पूर्णपणे सामान्य सारणी आहे - निर्देशांक, निर्बंध आणि दुर्दैवाने, ब्लोटसह. pg_repack ची पहिली पायरी म्हणजे चालू असताना सर्व बदलांबद्दल डेटा संग्रहित करण्यासाठी लॉग टेबल तयार करणे. ट्रिगर प्रत्येक घाला, अपडेट आणि हटवण्यासाठी या बदलांची प्रतिकृती तयार करेल. मग एक सारणी तयार केली जाते, मूळ संरचनेप्रमाणेच, परंतु निर्देशांक आणि निर्बंधांशिवाय, जेणेकरून डेटा घालण्याची प्रक्रिया मंद होऊ नये.

पुढे, pg_repack जुन्या सारणीतून नवीन सारणीवर डेटा हस्तांतरित करते, स्वयंचलितपणे सर्व असंबद्ध पंक्ती फिल्टर करते आणि नंतर नवीन सारणीसाठी अनुक्रमणिका तयार करते. या सर्व ऑपरेशन्सच्या अंमलबजावणीदरम्यान, लॉग टेबलमध्ये बदल जमा होतात.

पुढील पायरी म्हणजे बदल नवीन सारणीमध्ये हस्तांतरित करणे. स्थलांतर अनेक पुनरावृत्त्यांमध्ये केले जाते, आणि लॉग टेबलमध्ये 20 पेक्षा कमी नोंदी शिल्लक असताना, pg_repack एक मजबूत लॉक प्राप्त करते, नवीनतम डेटा स्थलांतरित करते आणि पोस्टग्रेस सिस्टम टेबलमधील जुन्या टेबलला नवीनसह बदलते. हा एकमेव आणि अतिशय लहान वेळ आहे जेव्हा आपण टेबलसह कार्य करण्यास सक्षम नसाल. यानंतर, जुने टेबल आणि लॉग असलेले टेबल हटवले जातात आणि फाइल सिस्टममध्ये जागा मोकळी केली जाते. प्रक्रिया पूर्ण झाली आहे.

सिद्धांतामध्ये सर्व काही छान दिसते, परंतु व्यवहारात काय होते? आम्ही 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 च्या मागील चरणात आहे, जे केवळ अनुक्रमणिका तयार करते, परंतु मर्यादा नाहीत: जुन्या टेबलमध्ये एक अद्वितीय मर्यादा होती आणि त्याऐवजी नवीनने एक अद्वितीय अनुक्रमणिका तयार केली.

येथे हे लक्षात घेणे महत्त्वाचे आहे की जर मर्यादा सामान्य असेल आणि पुढे ढकलली नसेल, तर त्याऐवजी तयार केलेला अद्वितीय निर्देशांक या निर्बंधाच्या समतुल्य आहे, कारण पोस्टग्रेसमधील अद्वितीय मर्यादा एक अद्वितीय निर्देशांक तयार करून लागू केल्या जातात. परंतु विलंबित मर्यादांच्या बाबतीत, वर्तन समान नसते, कारण निर्देशांक पुढे ढकलला जाऊ शकत नाही आणि 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. स्वीकृत स्तरावर ब्लोट ठेवण्यासाठी ऑटोव्हॅक्युम समायोजित करा.
3. जर ब्लोट अजूनही वाढत असेल आणि आपण बॉक्सच्या बाहेरील साधनांचा वापर करून त्यावर मात करू शकत नसाल, तर बाह्य विस्तार वापरण्यास घाबरू नका. मुख्य गोष्ट म्हणजे सर्वकाही चांगले तपासणे.
4. आपल्या गरजेनुसार बाह्य उपाय सुधारण्यास घाबरू नका - काहीवेळा हे आपला स्वतःचा कोड बदलण्यापेक्षा अधिक प्रभावी आणि अगदी सोपे असू शकते.

स्त्रोत: www.habr.com