PostgreSQL optimizer में 10 साल का सुधार

 (rmarcus.info)
2 पॉइंट द्वारा GN⁺ 2024-04-19 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • PostgreSQL 8 से 16 तक Join Order Benchmark के साथ 90वें percentile query latency की तुलना कर, लंबी अवधि में tail performance सुधार को अनुभवजन्य रूप से सत्यापित किया गया
  • PostgreSQL 8 की तुलना में 16 में tail latency लगभग आधी हो गई, और 13~16 रेंज में यह अधिकांशतः स्थिर स्तर पर रही
  • regression analysis के अनुसार हर major version बढ़ने पर औसतन 15% performance improvement दिखाई दिया, लेकिन linear model बदलाव के पैटर्न को अच्छी तरह समझा नहीं सकता
  • प्रयोग में GCC 13.2, Arch Linux Docker, shared_buffers 8GB, work_mem 8MB के साथ शर्तों को स्थिर रखा गया ताकि query optimizer quality पर फोकस किया जा सके
  • सुधार की मात्रा की व्याख्या करते समय optimizer के अलावा parallel workers और JIT compilation जैसे execution engine बदलावों को भी साथ में ध्यान में रखना चाहिए

PostgreSQL 8~16 benchmark सेटअप

  • विश्लेषण का लक्ष्य open source query optimizer PostgreSQL के version 8 से 16 तक के major versions हैं
  • benchmark के लिए जटिल joins वाले query set Join Order Benchmark का उपयोग किया गया
    • यह benchmark “How Good are Query Optimizers, Really?” पेपर में प्रस्तुत किया गया था
  • हर PostgreSQL version को Arch Linux Docker container के भीतर GCC 13.2 से build किया गया
  • measurement environment को index या I/O performance की बजाय query optimizer quality देखने के लिए सेट किया गया
    • shared_buffers को 8GB पर सेट किया गया, जो पूरे database को समाहित करने के लिए पर्याप्त बड़ा था
    • work_mem को सभी versions में 8MB पर स्थिर रखा गया
  • हर query को cache warming के लिए एक बार चलाने के बाद, अतिरिक्त 5 runs की median latency दर्ज की गई
  • हर major version के लिए उसका latest minor version इस्तेमाल किया गया
    • उदाहरण के लिए PostgreSQL 8 के लिए 8.4.22 को लिया गया
    • ये minor versions आमतौर पर नए major version के बाद आए थे, लेकिन इनमें सामान्यतः केवल bug fixes होते हैं, नए features या performance improvements नहीं

माप परिणाम और व्याख्या

  • PostgreSQL की tail performance कुल मिलाकर काफी बेहतर हुई है
    • PostgreSQL 8 और 16 की तुलना करने पर tail latency लगभग आधी हो गई
    • PostgreSQL 13 से 16 तक यह सामान्यतः स्थिर स्तर बनाए रखती है
  • regression analysis का उपयोग यह जांचने के लिए किया गया कि major version number और query latency के बीच गिरावट का रुझान सांख्यिकीय रूप से महत्वपूर्ण है या नहीं, और version-दर-version सुधार की मात्रा को मापा जा सके
    • linear regression के आधार पर हर नए major version पर Join Order Benchmark में औसतन 15% performance improvement दिखाई देता है
    • हालांकि, वास्तविक बदलाव के पैटर्न को मापने के लिए linear model उपयुक्त न भी हो सकता है
  • सभी सुधारों को केवल query optimizer से समझाना कठिन है
    • parallel workers और JIT compilation जैसे execution engine improvements भी performance को प्रभावित करते हैं
    • JOB की हर query plan समय के साथ कैसे बदली, यह आगे के अलग विश्लेषण का विषय है
  • PostgreSQL 8 से 16 पर upgrade करने की स्थिति में workload की tail latency में बड़ी कमी आने की संभावना है
  • research comparison में यह महत्वपूर्ण है कि PostgreSQL स्वयं लगातार मजबूत होता जा रहा baseline है
    • Neo और Bao ने PostgreSQL 11 से तुलना की थी, लेकिन नई studies PostgreSQL 14, 15, 16 से तुलना करती हैं
    • भले ही पुरानी तकनीक PostgreSQL की तुलना में 30% improvement और नई तकनीक 25% improvement दिखाए, नई तकनीक संभव है कि अधिक मजबूत PostgreSQL के मुकाबले मापी गई हो
  • मूल माप मान raw data में देखे जा सकते हैं

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

1 टिप्पणियां

 
GN⁺ 2024-04-19
Hacker News की टिप्पणियाँ

  • मैं 15 साल से Postgres इस्तेमाल कर रहा हूँ और अपने करियर का ज़्यादातर हिस्सा mathematical optimization problems को मॉडल करने और हल करने में लगाया है; इस विषय में मुझे तीन बातें मुख्य लगती हैं
    हर optimization problem को cost data चाहिए, और data जितना ज़्यादा व बेहतर हो, उतना अच्छा. Postgres में cross-column statistics जैसी सुधारें हुई हैं, लेकिन अभी भी system call latency जैसे बड़े gaps बचे हैं. डिस्क से page पढ़ने की latency सिस्टम के हिसाब से बहुत बदलती है, फिर भी Postgres इसे सीधे मापता नहीं और settings पर निर्भर करता है. foreign key statistics भी गायब हैं, इसलिए foreign key को follow करने वाले joins में खराब plan नहीं आना चाहिए, लेकिन कभी-कभी अब भी आ जाता है
    खासकर बड़े और महंगे queries के लिए deferred planning या alternative scenario planning की ज़रूरत है. अभी execution से पहले plan तय हो जाता है, लेकिन execution के शुरुआती चरणों में मिलने वाली row counts या cardinality estimates बाद के plan को बहुत बेहतर बना सकती हैं
    machine learning भी सुधार की गुंजाइश वाला क्षेत्र है, लेकिन अब तक मैंने जो कोशिशें देखी हैं वे प्रभावशाली नहीं रहीं. plan पर सीधे machine learning लगाने के बजाय इसे cost discovery और estimation में लगाना चाहिए. बेहतर cost model बनाना चाहिए, और optimization engine को वह data इस्तेमाल करने देना चाहिए

    • और राय सुनना चाहूँगा. उदाहरण के लिए system call latency का top list में होना मुझे थोड़ा surprising लगा. database community का आम नज़रिया यह है कि cost model कुल मिलाकर ठीक है, असल में cardinality estimation बहुत खराब है
      deferred/alternative planning में मुझे उत्सुकता है कि adaptive query execution सही तरीका है या नहीं. query execution की शुरुआती जानकारी को आगे के plan पर असर डालने दिया जा सकता है, लेकिन अगर शुरुआती कुछ joins गलत चुन लिए जाएँ—जो आम बात है—तो Yannakakis/SIPs जैसी चीज़ों के बिना recover करना मुश्किल होगा, यह चिंता है
      “query optimization के लिए machine learning” पर मेरी bias निश्चित रूप से है. फिर भी, मैंने जितने भी “planning के लिए machine learning” approaches देखे हैं, वे अंदरूनी तौर पर अंततः cost discovery/estimation में ही machine learning इस्तेमाल करते हैं. ये approaches इकट्ठा किए जाने वाले data, यानी exploration, और बनाए गए plan की quality, यानी exploitation, के बीच संतुलन बनाने की कोशिश करते हैं. दिलचस्प बात यह है कि planning से पूरी तरह अलग तरीके से machine learning इस्तेमाल करने पर estimates तो ज़्यादा accurate हो जाते हैं, लेकिन वास्तविक query plans और खराब हो जाते हैं: https://people.csail.mit.edu/tatbul/publications/flowloss_vl...
      इस क्षेत्र में मेरा हित जुड़ा है, इसलिए मेरी राय को उसी हिसाब से देखें
    • alternative plans सच में अच्छे लगते हैं. कुछ समय पहले देखे गए एक query plan में किसी subquery से लगभग 1,000 rows आने का अनुमान था, इसलिए index scan पर nested loop लगाया गया था, लेकिन असल में लगभग 1 billion rows निकलीं
      estimate इतना गलत क्यों था, यह अभी नहीं पता, लेकिन अगर row count किसी threshold से ऊपर जाते ही nested loop से hash join पर switch किया जा सके, तो disastrous plan से बचने में बहुत मदद मिलेगी
    • foreign key statistics गायब होने से उनका ठीक-ठीक मतलब क्या है, यह जानना चाहूँगा. Postgres भी कई relational databases की तरह foreign keys पर indexes अपने-आप नहीं बनाता, यह बात आपको शायद पहले से पता होगी
      क्या आप join order की समस्या की बात कर रहे हैं?
    • जानना चाहूँगा कि क्या MSSQL इस मामले में बेहतर है

  • Postgres query optimizer डिस्क से पढ़े जाने वाले pages की संख्या और intermediate results के रूप में डिस्क पर लिखे जाने वाले pages की संख्या घटाने की कोशिश करता है. इसलिए सभी data को समा लेने जितने बड़े shared buffers रखकर query optimizer को benchmark करना गलत लगता है
    तब आप generated query plan की quality नहीं, बल्कि query optimizer और join processor की speed माप रहे होते हैं. असल में हर version में generated plans सभी समान रहे हों और केवल execution speed मापी गई हो, तो भी आश्चर्य नहीं होगा

    • ऐसा नहीं है. optimization target सिर्फ डिस्क से पढ़े जाने वाले pages नहीं, बल्कि CPU usage जैसी चीज़ें भी शामिल करने वाला cost है
      cost कोई disk read count नहीं, बल्कि elapsed time से correlate करने के लिए बनाया गया arbitrary unit है, इसलिए जब सब कुछ RAM में loaded हो तब plans की तुलना करना भी पूरी तरह valid है. परंपरा के अनुसार डिस्क से एक page पढ़ने को 1.0 पर scale किया जाता है, लेकिन यह “optimizer disk page reads की संख्या minimize करता है” कहने से अलग है. किसी arbitrary machine पर 1ms को 1.0 मानते तो भी चल सकता था
    • plans समान रहे हों और execution engine improvements मापी गई हों, यह संभावना निश्चित रूप से है. Join Order Benchmark optimizer quality test करने के लिए design किया गया है
      PG optimizer सिर्फ डिस्क से पढ़े जाने वाले pages की संख्या ही नहीं, बल्कि CPU द्वारा जाँचे जाने वाले tuples की संख्या, predicates evaluate करने की संख्या आदि भी घटाने की कोशिश करता है, और ये सभी संख्याएँ “cost” में मिलकर वह function बनती हैं जिसे optimizer minimize करता है
      cold cache और warm cache performance measurements अलग नतीजे दे सकते हैं, और यह experiment निश्चित रूप से warm cache scenario है. लेकिन cold cache में भी बताई गई समस्या है. Join Order Benchmark data size पर PG के B-tree improvements से कुछ I/O बचाने का असर CPU-based improvements से ज्यादा हावी हो सकता है
      संदर्भ के लिए, P90 latency query का plan PG 8.4 में loop join और merge join इस्तेमाल करने वाले plan से बदलकर PG 16 में hash join इस्तेमाल करने वाला plan हो गया, और यह query अब P90 query नहीं रही. इसे कम-से-कम optimizer improvements का कुछ evidence माना जा सकता है

  • लेख में PostgreSQL के JIT compiler का ज़िक्र था, लेकिन अब तक मैंने इसे query performance घटाते ही देखा है. अपने installation checklist में इसे disable करना डाल रखा है

    • एक ग्राहक को Postgres पर switch करने के बाद सबसे खराब performance problem आई. अजीब बात यह थी कि यह सिर्फ Docker और test server settings में होती थी, developer machines पर नहीं. developer Homebrew से Postgres चला रहे थे
      पता चला कि Homebrew ने Postgres को JIT support के बिना install किया था, और developer machine पर एक query 200ms में खत्म होती थी, लेकिन JIT enabled environment में 4–5 seconds लगते थे. मैं Postgres को बहुत गहराई से इस्तेमाल नहीं करता, इसलिए कारण ढूँढने में थोड़ा समय लगा, और उसके बाद से हमेशा JIT बंद रखता हूँ और पीछे मुड़कर नहीं देखता
    • JIT compiler analytical queries के लिए शानदार है
      PostgreSQL में JIT activation threshold भी set किया जा सकता है, इसलिए JIT चालू होने की threshold को और ऊँचा किया जा सकता है
    • pg का JIT यह बात काफी अच्छे से दिखाता है कि LLVM JIT के लिए बहुत अच्छा नहीं है, और Postgres में persistent shared query cache न होने से यह और खराब हो जाता है
      अगर future queries के लिए asynchronous compilation कर सके तो शायद कम नुकसानदेह होगा. सच कहें तो सामान्य JIT, खासकर optimization backends, इसी तरीके के ज़्यादा करीब होते हैं
    • क्या Postgres किसी query को एक बार JIT compile करके फिर compiled query को कई बार execute नहीं कर सकता?

  • दिलचस्प है, लेकिन Postgres का version numbering सिस्टम v10 में बदल गया था। 9.6, 9.5, 9.4, 9.3, 9.2, 9.1, 9.0, 8.4, 8.3, 8.2, 8.1, 8.0 असल में सभी अलग-अलग major versions हैं
    यह देखना भी दिलचस्प होगा कि उन versions में performance कैसे बदली

    • फिर भी v9.0 से 9.6 तक सिर्फ binary बदलकर तेज in-place upgrade संभव बनाने के लिए major version filesystem compatibility बनाए रखने की बात सराहनीय थी
      हो सकता है इसी वजह से कुछ सीमाएं भी लगी हों, लेकिन ज्यादा downtime या reindexing मांगने वाले सालाना updates खास अच्छे नहीं लगते, और यही वजह हो सकती है कि कई sites पुराने version का support खत्म होने तक upgrade टालती हैं। खासकर AWS RDS users के लिए ऐसा होगा
      v10 के बाद logical replication upgrades में availability के लिहाज से फायदे हैं, लेकिन अगर schema अपेक्षाकृत सरल न हो तो यह unavoidable cost और बड़े risk वाला बड़ा project है
    • पूरी तरह सहमत। मैंने version numbers को semver तरीके से interpret करके हर major version का latest version चुना, लेकिन यह PostgreSQL के पारंपरिक रूप से major version numbers को handle करने के तरीके से अलग है
      उदाहरण के लिए PG 8.2 और 8.1 अलग-अलग major versions हैं, लेकिन मैंने उन्हें minor versions की तरह interpret किया। ऐसा करने की मुख्य वजह test किए जाने वाले versions की संख्या घटाना था, और मैं मानता हूं कि अधिक complete analysis में हर वास्तविक major version को test करना चाहिए

  • “बेशक यह सुधार पूरी तरह query optimizer की वजह से नहीं है” कहा गया था; version दर version execution plan changes हुए थे या नहीं, यह देखना दिलचस्प होगा

  • Proebsting का नियम याद आता है: https://proebsting.cs.arizona.edu/law.html

    • compiler optimization की अच्छी बात यह है कि मौजूदा CPU को physically छुए बिना performance बेहतर की जा सकती है। हर साल किसी की design की हुई machine से और performance निचोड़ी जाती है, और cumulative रूप से यह बड़ा हो जाता है
      सोचिए Python performance को 1% optimize करने का environmental impact कितना होगा। वातावरण में CO2 कितनी घटेगी? शायद यह आपके, आपके परिवार और दोस्तों के कुल environmental footprint से भी बड़ा हो। शायद आपके पूरे शहर के बराबर भी हो सकता है। सिर्फ इसलिए कि किसी ने कुछ bit operation tricks implement करने में समय लगाया
    • समझ नहीं आ रहा कि क्यों। वह नियम तो लगता है software performance improvements के ज्यादा मायने न होने की बात कहता है, जबकि यह लेख बताता है कि Postgres में सुधार काफी था
      क्या इसलिए कि 15% कम आंकड़ा माना जा रहा है? इस context में यह बिल्कुल कम नहीं है। यह link किए गए नियम के 60% से कम है, और 15/10 जैसा divide करें तो और छोटा लगेगा, लेकिन Postgres performance की तुलना hardware improvements से नहीं करनी चाहिए। यहां जिस चीज को मापा जा रहा है, उसमें 1% performance improvement के बराबर पहुंचने के लिए बहुत बड़ा hardware improvement चाहिए होगा
      मुझे नहीं लगता कि वह नियम उतना हास्यास्पद है जितना दूसरे लोग कहते हैं, लेकिन वह programming language compile time के बारे में है। ऐसी अपेक्षाकृत कम महत्वपूर्ण चीज की तुलना data storage और consumption से नहीं करूंगा, जिसे computer science की सबसे महत्वपूर्ण चीजों में से एक कहा जा सकता है
    • इस मामले में researcher ने सभी PostgreSQL versions को उसी GCC 13.2 से build किया और उसी operating system पर test किया
    • यह काफी कमजोर “नियम” जैसा लगता है। क्या इसे मजाक में बनाया गया था? आधार ऐसे numbers हैं जिनका स्रोत पता नहीं, “मान लेते हैं” जैसे, और निष्कर्ष भी काफी गलत दिशा में है। ऐसा इशारा करता लगता है कि दुनिया भर के बहुत से software की performance हर साल 4% सुधारने वाली optimization समय की बर्बादी है
      तुलना के लिए सिर्फ Murphy का नियम दिया गया है। तेज hardware develop करने की लागत और compiler को लगातार बेहतर बनाते रहने की लागत में कितना अंतर है, यह जानना चाहूंगा। percent performance improvement per dollar जैसे तरीके से ROI की तुलना करने पर इस “नियम” को कुछ वजन मिल भी सकता है
      दूसरी ओर, यह Postgres लेख optimization में diminishing returns दिखाता लगता है, जो इस “नियम” की उस धारणा को खारिज करता है कि हर साल लाभ स्थिर रहता है। साथ ही, यह लंबे समय में optimization को खराब निवेश बताने वाले Proebsting के संकेत को साबित भी कर सकता है

  • यह analysis थोड़ा उलझाने वाला है। graph में न दिखने वाले गिरावट के trend को data में कैसे confirm किया गया, समझ नहीं आता
    median शुरुआती कुछ versions में थोड़ा नीचे जाता है और हाल के कुछ versions में फिर ऊपर जाता दिखता है। R² बहुत कम होने की वजह से correlation convincing नहीं लगता। मूल रूप से tail latency बेहतर हुई है, और बाकी चीजें environment पर निर्भर हैं—ऐसा ही लगता है

    • blog post का author हूं
      “tail latency बेहतर हुई है और बाकी environment पर निर्भर है” वाली interpretation सही है, लेकिन मैं इसे conservative reading मानता हूं। बेशक कई, शायद ज्यादातर applications में tail latency बेहद महत्वपूर्ण होती है। साथ ही tail latency वही target भी है जिस पर optimizer engineers मुख्य रूप से ध्यान देते हैं—यानी सबसे ज्यादा समय लेने वाली queries का execution time घटाना

  • क्वेरी optimization कैसा दिखता है? जिज्ञासा है कि यह SQL स्तर पर optimize करना है या algorithm स्तर पर optimize करना है

    • PostgreSQL को छोड़कर, जिन databases का मैंने इस्तेमाल किया है उनमें ज़्यादातर optimization algorithm स्तर पर होता है। यानी किसी खास query के लिए सबसे अच्छा algorithm और execution order चुनना
      लगता है इसकी वजह यह है कि कई अलग-अलग SQL queries एक ही “command” या execution plan में बदल सकती हैं, और SQL semantics खुद language-level optimization की बहुत ज़्यादा गुंजाइश नहीं देती
      जैसा दूसरे जवाबों में कहा गया है, अहम फैसलों में से एक यह है कि full table scan को index lookup या index scan में बदला जा सकता है या नहीं
      उदाहरण के लिए, अगर full table scan ज़रूरी है और हर row के लिए result set में शामिल करना है या नहीं यह तय करने हेतु काफी calculation करनी पड़ती है, तो optimizer full table scan को parallel table scan में बदल सकता है और हर parallel task के results को merge कर सकता है
      compiler के लिए high-performance code लिखते समय यह जानना पड़ता है कि compiler optimizer source code को machine code में कैसे बदलता है। तभी आप ऐसे code को प्राथमिकता दे सकते हैं जिसे optimizer अच्छी तरह handle करता है, और उन patterns से बच सकते हैं जो धीमा machine code पैदा करते हैं। आखिरकार optimizer को खास patterns detect करके transform करने के लिए program किया गया होता है
      query optimizer और execution plan के साथ भी यही बात है। आपको सीखना होगा कि जिस database का आप उपयोग कर रहे हैं उसका query optimizer किन patterns को handle करके efficient execution plan बना सकता है
    • SQL को execute करने के सभी तरीकों का वर्णन किया जाता है और फिर तेज़ plan चुना जाता है। मसलन, अगर user_id xx वाले user की row ढूंढनी हो, तो यह चुनना होगा कि पूरी table पढ़कर filter किया जाए या कोई dedicated data structure इस्तेमाल किया जाए
      index इस्तेमाल करने पर row count के लिहाज़ से log time में खोजा जा सकता है। इसके अलावा join order चुनना, join strategy चुनना, filter conditions को source की तरफ push down करना जैसी कई चीज़ें संभव हैं। यही SQL optimization का व्यापक क्षेत्र है
    • बहुत high level पर देखें तो query planner का लक्ष्य disk से data पढ़ने की लागत को कम से कम करना है। यह row count और unique values की संख्या जैसी पहले से calculate की गई column statistics इकट्ठा करके अनुमान लगाता है कि query कितनी rows से match करेगी
      इसी जानकारी का इस्तेमाल join order तय करने, index चुनने आदि के लिए किया जाता है। join को hash, loop, merge जैसे कई algorithms से किया जा सकता है। सबसे सस्ता विकल्प इस बात पर निर्भर करता है कि एक side working memory में फिट होती है या नहीं, दोनों sides पहले से sorted हैं या नहीं, जैसे कि index scan की वजह से
    • query optimization का मतलब SQL द्वारा मांगे गए results देने वाले algorithm को चुनना है

  • लगता है site down है, इसलिए इसकी जगह इसे देख सकते हैं: https://web.archive.org/web/20240417050840/https://rmarcus.i...