Postgres को धीमा बनाने के तरीके

• Postgres की performance को बेहद खराब कर सकने वाले parameter combinations पर एक प्रयोगात्मक दृष्टिकोण का परिचय
• आम तौर पर cache, index, WAL, I/O जैसे कई तत्वों को उल्टा tune किया गया
• shared_buffers, autovacuum, WAL से जुड़े options को चरम स्तर पर बदलकर TPS में 42,000 गुना गिरावट हासिल की गई
• नए Postgres 18/19 versions के io_method और io_workers जैसी नवीनतम सुविधाओं का उपयोग करके single I/O thread limit का प्रयोग भी किया गया
• प्रयोग के परिणाम दिखाते हैं कि सिर्फ Postgres configuration file से भी performance में बेहद गिरावट लाई जा सकती है

---

अवलोकन

यह लेख आम तौर पर Postgres tuning को तेज़ बनाने पर केंद्रित करने के बजाय, केवल उसे धीमा बनाने के लक्ष्य से विभिन्न PostgreSQL settings को बदलकर performance को उसकी सीमा तक गिराने वाले प्रयोग का विवरण है।
टेस्ट BenchBase के TPC-C workload (128 warehouses, 100 connections, प्रत्येक में अधिकतम 10,000 transactions/second का प्रयास, नवीनतम Postgres 19devel, Ryzen 7950x, 32GB RAM, 2TB SSD environment, 120 seconds की अवधि) पर आधारित है।
डिफ़ॉल्ट मानों पर performance 7082 TPS थी, और हर parameter manipulation के साथ यह चरण-दर-चरण देखा गया कि यह कितना धीमा होता है।

caching को बहुत कम करना

• Postgres disk I/O कम करने के लिए शक्तिशाली cache (shared_buffers) का उपयोग करता है
• डिफ़ॉल्ट मान (10GB) से shared_buffers को 8MB तक घटाने पर, TPS लगभग 1/7 स्तर (1052 TPS) तक गिर गई
• cache hit ratio 99.90% से घटकर 70.52% हो गया, और read syscall 300 गुना से अधिक बढ़ते देखे गए
• इसे 128kB तक घटाने की कोशिश की गई, लेकिन Postgres ने न्यूनतम लगभग 2MB तक ही अनुमति दी, जिससे यह और गिरकर 485 TPS हो गई

background work बढ़ाना (autovacuum tuning)

• autovacuum से जुड़े सभी thresholds को न्यूनतम कर दिया गया, ताकि लगभग हर operation पर vacuum चले
  • autovacuum_vacuum_insert_threshold=1, autovacuum_naptime=1 आदि का संयोजन
  • vacuum तब भी लगभग हर 1 second में बार-बार चलता रहा जब करने के लिए वास्तविक काम नहीं था
• इस प्रक्रिया में maintenance_work_mem को 128kB तक घटाया गया, और सभी autovacuum logging को सक्षम किया गया
• इसके परिणामस्वरूप TPS 293 तक गिर गई (मूल की तुलना में 1/20 से भी कम)
• autovacuum से जुड़े real-time logs के माध्यम से पुष्टि हुई कि performance गिरावट का कारण वास्तव में लगातार background work था

WAL (Write-Ahead Log) write work को सबसे खराब बनाना

• WAL से जुड़े parameters को पूरी तरह सबसे खराब स्थिति के लिए सेट किया गया
  • wal_writer_flush_after=0, wal_writer_delay=1, wal_sync_method=open_datasync आदि
  • checkpoint को हर 30 seconds में force किया गया, और min/max_wal_size=32MB पर न्यूनतम रखा गया
  • wal_level=logical, wal_log_hints=on आदि के साथ WAL में अनावश्यक जानकारी भी लिखी गई
  • track_wal_io_timing, summarize_wal जैसे अतिरिक्त overhead भी सक्षम किए गए
• इसके परिणामस्वरूप TPS 98 तक गिर गई (मूल की तुलना में 1/70 से भी कम)
• logs में checkpoints हर कुछ सौ ms पर दोहराए जाते दिखे, जिससे असामान्य व्यवहार की पुष्टि हुई

index effect हटाना

• index usage की लागत को अधिकतम cost के रूप में गणना करने वाले values (random_page_cost=1e300, cpu_index_tuple_cost=1e300) पर सेट किया गया, जिससे index व्यावहारिक रूप से निष्क्रिय हो गए
• स्थिरता बनाए रखने के लिए shared_buffers को 8MB तक बढ़ाया गया, और TPS 0.87 तक गिर गई (7,000 गुना धीमा होने का लक्ष्य हासिल)

single-threaded I/O को force करना

• नवीनतम Postgres 18+ feature का उपयोग किया गया
• io_method=worker, io_workers=1 के साथ सभी I/O को एक ही worker thread पर force किया गया
• TPS और गिरकर 0.016 हो गई (42,000 गुना धीमा)
• 100 connections और 120 seconds के टेस्ट में केवल 11 transactions ही सफल हो पाए, इतनी सीमित performance रही

निष्कर्ष और पुनरुत्पादन मार्गदर्शिका

• कुल 32 parameter manipulations के माध्यम से यह सिद्ध किया गया कि production DB को व्यावहारिक रूप से "पंगु" स्थिति तक पहुँचाया जा सकता है
• केवल postgresql.conf settings को बदलकर performance degradation को अधिकतम किया जा सकता है
• जो उपयोगकर्ता इस प्रयोग को पुन: करना चाहते हैं, वे BenchBase Postgres, ऊपर बताए गए TPC-C environment, और सभी settings की सूची देखें
• कुछ अतिरिक्त parameters या और अधिक धीमा बनाने की कोशिशें इसमें शामिल नहीं हैं

parameter summary list

• shared_buffers = 8MB
• autovacuum से जुड़े thresholds/scale_factor = 0~1 तक न्यूनतम
• vacuum से जुड़ी cost, memory, logging: न्यूनतम और अधिकतम के चरम पर
• WAL से जुड़े sync/flush/logging/level आदि: धीमा बनाए रखने के लिए सेट
• index से जुड़े random_page_cost, cpu_index_tuple_cost: 1e300 पर सेट
• io_method = worker, io_workers = 1
• अन्य विस्तृत values के लिए मुख्य पाठ की सूची देखें

समापन

• सिर्फ postgresql.conf फ़ाइल के माध्यम से भी गंभीर performance degradation पैदा की जा सकती है
• व्यावहारिक काम में, इस संयोजन को उल्टा समझना (कुशल performance improvement के लिए) उपयोगी हो सकता है
• लेख का अंत लेखक द्वारा प्रयोग के दौरान कमर दर्द के कारण इसे रोकने के उल्लेख के साथ होता है

[GN⁺]

Hacker News टिप्पणियाँ

• इंडेक्स, कई टेबल, ट्रांज़ैक्शन, entity relationships, referential integrity जैसी चीज़ों को पूरी तरह भूलकर, TRIRIGA के शुरुआती versions की तरह, सारे data को एक ही table में रखकर KVS NoSQL SQL की तरह इस्तेमाल करने का तरीका बताया गया है
• यह तरीका इतना मज़ेदार लगा कि लगा इस पर एक लगातार चलने वाली series के रूप में ‘काम को और खराब कैसे करें’ जैसी किताब भी आनी चाहिए; सीखते-सीखते उल्टा बेहतर तरीके खोजे जा सकते हैं; अगर O’Reilly स्टाइल में इसका cover किसी बेढंगे fantasy जानवर से सजाया जाए (जैसे दोनों तरफ सिर वाला unicorn जो AirPods पर बात करते हुए किसी ठग को पैसे दे रहा हो, PowerPoint बना रहा हो, ज़्यादा खा रहा हो और drugs ले रहा हो) तो और अच्छा होगा
  • द्वितीय विश्व युद्ध के दौरान वास्तव में ऐसी रणनीति का इस्तेमाल हुआ था; पायलटों की जीवित वापसी बढ़ाने के लिए मौसम वैज्ञानिकों ने पहले उन परिस्थितियों की पहचान की जिनमें सबसे ज़्यादा जानें जाती थीं, फिर mission design को उन परिस्थितियों से बचाते हुए बनाया; संदर्भ लिंक Suppose I wanted to kill a lot of pilots
  • creative writing classes में भी अभ्यास के दौरान गलत लिखी गई रचनाओं को पढ़कर उनका विश्लेषण करने, या अच्छी writing को जानबूझकर खराब ढंग से फिर से लिखने का अभ्यास होता था, और वही सबसे मददगार writing exercise थी
  • ORLYBooks पैरोडी साइट भी देखी जा सकती है
• काश observability tools के प्रयोग के लिए production environment sandbox होता; किसी ठीक-ठाक आकार के SaaS-style system पर usage simulation के साथ, और बस औसत-से postgres/rabbit setup पर debugging tools या strategies की अपनी क्षमता जाँचना अच्छा लगता
• यह विचार सच में जीनियस है; अगर optimization अच्छी तरह करना है, तो शुरुआत में या control group के रूप में चीज़ों को बिल्कुल उल्टा बिगाड़कर देखना उपयोगी हो सकता है; यह खुद से पूछने जैसा है कि क्या हम सच में database या system को वैज्ञानिक ढंग से संभाल रहे हैं, या बस धुंधले अंदाज़े से किसी चीज़ की नकल कर रहे हैं
  • मैं हर बार नया cloud service इस्तेमाल करते समय यही करता हूँ; पहले Series A को जितनी जल्दी हो सके इस्तेमाल करता हूँ, फिर cloud cost optimization पर जाता हूँ
• The Defence of Duffer's Drift इस शैली का शुरुआती उदाहरण है; पहली कहानी में squad tactics को बुरी तरह बिगाड़ दिया जाता है और ज़्यादातर लोग खो दिए जाते हैं, फिर बाद की कहानियों में tactical conditions बदलते हुए नतीजे धीरे-धीरे बेहतर होते जाते हैं; Musicians of Mars 2 जैसी नई tactical books भी यही तरीका अपनाती हैं; पहली Team Badger कहानी Duffer's Drift से बहुत मिलती-जुलती है, बस location, equipment और technology के हिसाब से बदली गई है; संबंधित PDF यहाँ देखे जा सकते हैं: The Defence of Duffer's Drift और Musicians of Mars 2; Team Badger के commander का करारी हार के बाद यह सोचना कि आत्मविश्वास और तैयारी के बावजूद इतनी बुरी हार क्यों हुई, काफी प्रभावशाली लगा
  • इसी तरह के संदर्भ में Groundhog Day और खासकर Edge of Tomorrow जैसी फ़िल्में याद आती हैं, और हाल में एक ब्रिटिश military blog पर छपी आधुनिक homage भी देखने लायक है Defence Baltic Bridge Dreams
• deadlock का ज़िक्र दिलचस्प था; सोच रहा हूँ क्या transaction isolation level settings को tweak करने वाला हिस्सा भी होगा
• B Sanderson का ज़िक्र देखकर अच्छा लगा
• यह Hyperbole and a Half और db admin का मिला-जुला रूप लगा; पढ़ते हुए अच्छा महसूस हुआ और कुछ बातें सीखने को भी मिलीं
• लिखने का अंदाज़ और विचारों को खोलने का तरीका सच में शानदार था; बहुत मज़े से पढ़ा जा सकता था

[sonic0987]

बहुत बढ़िया। मुझे ऐसा दृष्टिकोण बहुत पसंद है।