Postgres क्वेरी को 1,000 गुना तेज़ बनाना

 (mattermost.com)
22 पॉइंट द्वारा xguru 2024-05-22 | 3 टिप्पणियां | WhatsApp पर शेयर करें
  • Mattermost डेटाबेस लोड कम करने और कहीं तेज़ search results देने के लिए Elasticsearch का उपयोग करता है
    • Elasticsearch के सही तरह काम करने के लिए search के लिए लक्षित सभी data को index करना ज़रूरी है
    • जो data पहले से index हो चुका है, उसके बाद नए posts और files की indexing काफ़ी तेज़ होती है
    • लेकिन, बहुत बड़े डेटाबेस (10 करोड़ posts) को शुरुआत से पूरी तरह index करना बहुत धीमा था (18 घंटे में आधा भी पूरा नहीं हुआ और समय के साथ और धीमा होता गया)
  • प्रति डेटाबेस कॉल लगने वाले समय के ग्राफ़ से पता चला कि PostStore.GetPostsBatchForIndexing मेथड की SQL query समस्या थी
    • यह query मूल रूप से creation timestamp के आधार पर posts को sort करती है और दिए गए timestamp से नए N posts लौटाती है
    • इस query को बार-बार चलाकर indexing job तब तक चलती है जब तक सभी posts index नहीं हो जाते
  • EXPLAIN (ANALYZE, BUFFERS) का उपयोग करके query execution plan का विश्लेषण किया गया:
    • Posts table पर index scan करते समय Filter condition लागू करने के लिए 4 करोड़ blocks प्रोसेस किए जा रहे थे (309GB)
    • Channels table के साथ JOIN समस्या नहीं था
    • WHERE condition के OR clause में से केवल Posts.CreateAt > ?1 हिस्सा लागू करने पर यह बहुत तेज़ था (30ms)
    • वहाँ Posts.CreateAt = ?1 AND Posts.Id > ?2 condition लागू करने पर यह बेहद तेज़ हो गया (0.047ms)
  • कारण की पहचान:
    • मूल query Posts की सभी rows को scan करके Filter से छाँट रही थी, जबकि बदली हुई query केवल index देखती है और ज़रूरी rows ही निकालती है
    • समय के साथ query के धीमे होने का कारण यह था कि उसे लगातार अधिक rows को फ़िल्टर करना पड़ रहा था
  • समाधान:
    • PostgreSQL के row constructor comparison फीचर का उपयोग करके condition को (Posts.CreateAt, Posts.Id) > (?1, ?2) में बदला गया
    • इस बदलाव के बाद query का execution time घटकर 34 मिलीसेकंड रह गया
    • लेकिन MySQL में बदली हुई query उल्टा और धीमी चली। MySQL में मूल query तेज़ थी, इसलिए DB के अनुसार अलग query इस्तेमाल करने के लिए code branching की गई
  • सीखी गई बातें:
    • EXPLAIN चलाते समय BUFFERS option हमेशा इस्तेमाल करें
    • Filter की बजाय Index Cond का उपयोग करने की कोशिश करें
    • यह मानकर चलें कि PostgreSQL और MySQL लगभग हमेशा अलग तरह से व्यवहार करते हैं
  • निष्कर्ष
    • optimization के ज़रिए query execution time को 1000 गुना से अधिक कम किया गया
    • यह optimization Mattermost v9.7.0 और v9.5 ESR versions में शामिल किया गया
    • इस optimization process से बहुत कुछ सीखने को मिला

संबंधित पढ़ाई

3 टिप्पणियां

 
dontcryme 2024-05-23

आखिरी लेख में भी यह बात है, लेकिन इस लेख का शीर्षक थोड़ा clickbait जैसा लगता है... अगर इसे थोड़ा ज़्यादा व्यावहारिक तरीके से बदलें तो

'गलतियों से सीखे गए PostgreSQL उपयोग के उदाहरण'

शायद यही होगा..
 
vwjdalsgkv 2024-05-23

उम्... निजी तौर पर मुझे लगता है कि अगर इस स्तर का लेख किसी खास कंपनी/प्रोडक्ट को सामने रखकर लिखा जाए, तो उल्टा उस प्रोडक्ट पर मेरा भरोसा काफी कम हो जाएगा.
सारांश साफ़-सुथरा है, लेकिन उसके भीतर मौजूद तकनीकी मूल्य थोड़ा कम लगता है, इसलिए अफसोस होता है.
 
savvykang 2024-05-23

मैंने भी यह लेख पढ़ने के बाद उल्टे उस पर भरोसा कम कर दिया। क्योंकि इसका मतलब है कि पैसे लेकर बेच रहे प्रोडक्ट ने बड़े पैमाने के प्रोसेसिंग टेस्ट के बिना ही फीचर रिलीज़ कर दिया। मुझे लगता है कि इतना साधारण index तो feature development चरण में ही सेट कर देना चाहिए था। ऐसा लगता है कि software development process के कई चरण छोड़ दिए गए थे।