🐱 🛌🏼 👩🏾‍🍳 कैसे हम एक बाइट जोड़ के बिना PostgreSQL में स्मृति समस्या को हल किया ♿️ 🚡 🏠

समस्या के "भारी" अनुरोध और सुरुचिपूर्ण समाधान के बारे में एक छोटी कहानी

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

ETL कार्य एक तालिका में किया गया था जहां बाइनरी रिकॉर्ड और डंप संग्रहीत किए जाते हैं। हर रात, यह कार्य डुप्लिकेट डंप को हटाने और स्थान खाली करने के लिए था।

डंप डंप की खोज करने के लिए, हमने इस क्वेरी का उपयोग किया:

id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps

क्वेरी BLOB फ़ील्ड द्वारा समान डंप को जोड़ती है। विंडो फ़ंक्शन का उपयोग करके, हम प्रत्येक डंप की पहली उपस्थिति का पहचानकर्ता प्राप्त करते हैं। फिर इस अनुरोध के साथ हम सभी डुप्लिकेट डंप हटाते हैं।

अनुरोध को कुछ समय के लिए निष्पादित किया गया था, और, जैसा कि लॉग से देखा जा सकता है, बहुत सारी मेमोरी खा ली। ग्राफ़ दिखाता है कि उसने हर रात मुफ्त डिस्क स्थान कैसे बनाया:

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

  Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms

छंटनी में बहुत सी मेमोरी लगती है। निष्पादन के संदर्भ में, परीक्षण डेटा सेट से लगभग 30 एमबी मेमोरी की आवश्यकता होती है।

ऐसा क्यों?

PostgreSQL हैशिंग और सॉर्टिंग के लिए मेमोरी आवंटित करता है। मेमोरी की मात्रा को work_mem पैरामीटर द्वारा नियंत्रित किया जाता है। Work_mem का डिफ़ॉल्ट आकार 4 MB है। यदि हैशिंग या सॉर्टिंग के लिए 4 एमबी से अधिक की आवश्यकता होती है, तो PostgreSQL अस्थायी रूप से डिस्क स्थान की खपत करता है।

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

आर्थिक छँटाई

"कितनी छँटाई खाएगा डेटा सेट के आकार और सॉर्ट कुंजी पर निर्भर करता है। आप डेटा सेट को कम नहीं कर सकते, लेकिन कुंजी का आकार संभव है ।

संदर्भ बिंदु के लिए, हम सॉर्ट कुंजी का औसत आकार लेते हैं:

  avg ---------- 780

प्रत्येक कुंजी का वजन 780 है। द्विआधारी कुंजी को कम करने के लिए, इसे हैश किया जा सकता है। PostgreSQL में इसके लिए md5 है (हाँ, सुरक्षा नहीं, लेकिन हमारे उद्देश्य के लिए यह करेगा)। आइए देखें कि md5 के वजन के साथ BLOB हैशेड कितना है:

  avg ----------- 36

Md5 के माध्यम से कुंजी हैशेड का आकार 36 बाइट्स है। एक हैशेड कुंजी का मूल विकल्प केवल 4% होता है ।

अगला, हमने हैश की के साथ मूल अनुरोध लॉन्च किया:

  id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps;

और कार्यान्वयन योजना:

  Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms

हैशेड कुंजी के साथ, अनुरोध केवल 4 अतिरिक्त मेगाबाइट का उपभोग करता है, अर्थात पिछले 30 एमबी के 10% से थोड़ा अधिक है। तो सॉर्ट कुंजी का आकार बहुत स्मृति को प्रभावित करता है कि सॉर्ट कितना खाती है ।

आगे और भी

इस उदाहरण में, हमने md5 का उपयोग करते हुए BLOB को md5 । MD5 के साथ बनाई गई हैश का वजन 16 बाइट होना चाहिए। और हम और अधिक:

 md5_size ------------- 32

हमारा हैश बिल्कुल दुगना था, क्योंकि md5 हेक्साडेसिमल पाठ के रूप में एक हैश का उत्पादन करता है।

PostgreSQL में, आप pgcrypto एक्सटेंशन के साथ हैशिंग के लिए MD5 का उपयोग कर सकते हैं। pgcrypto प्रकार के bytea MD5 (बाइनरी में) बनाता है:

 select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20

हैश अभी भी 4 बाइट्स की तुलना में अधिक होना चाहिए। यह सिर्फ इतना है कि मूल्य की लंबाई को संग्रहीत करने के लिए bytea प्रकार इन 4 बाइट्स का उपयोग करता है, लेकिन हमने इसे इस तरह नहीं छोड़ा।

यह पता चला है कि PostgreSQL में uuid प्रकार का वजन लगभग 16 बाइट्स होता है और किसी भी मनमाने मूल्य का समर्थन करता है, इसलिए हम शेष चार बाइट्स से छुटकारा पा लेते हैं:

 uuid_size --------------- 16

वह सब है। Md5 के साथ 32 बाइट्स uuid साथ 16 में बदल जाते हैं।

मैंने एक बड़ा डेटासेट लेकर परिवर्तन के प्रभावों की जाँच की। डेटा स्वयं नहीं दिखाया जा सकता है, लेकिन मैं परिणाम साझा करूंगा:

जैसा कि आप तालिका से देख सकते हैं, मूल समस्याग्रस्त अनुरोध का वजन 300 एमबी (और रात के मध्य में हमें उठाता है)। uuid कुंजी के साथ, छँटाई केवल 7 एमबी की हुई।

अनुवर्ती विचार

एक हैशेड मेमोरी छँटाई कुंजी के साथ एक अनुरोध कम खपत करता है, लेकिन यह बहुत धीमी गति से काम करता है:

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

यह इस तथ्य का एक शानदार उदाहरण है कि आपको डेटाबेस प्रश्नों को गति देने के लिए हमेशा प्रयास करने की आवश्यकता नहीं है । संतुलित संसाधनों का अधिकतम उपयोग करना और उन्हें निचोड़ना बेहतर है।

कैसे हम एक बाइट जोड़ के बिना PostgreSQL में स्मृति समस्या को हल किया

ऐसा क्यों?

आर्थिक छँटाई

आगे और भी

अनुवर्ती विचार

More articles: