SQLite चलाते हुए सीखी गई कुछ नई बातें

 (jvns.ca)
1 पॉइंट द्वारा GN⁺ 9 시간 전 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • छोटे वेबसाइटों के production environment में भी SQLite इस्तेमाल किया जा सकता है, लेकिन Django ORM के जिम्मे अधिक काम आते ही database की operational complexity महसूस हुई
  • 4,000 rows वाली table में FTS5 search में 5 सेकंड लग रहे थे, लेकिन ANALYZE चलाने के बाद यह लगभग 0.05 सेकंड हो गया; इससे पता चला कि query planner statistics performance पर बड़ा असर डाल सकते हैं
  • अगर bulk DELETE 5 सेकंड से ज्यादा चले, तो दूसरे write operations भी timeout हो सकते हैं और worker व VM तक बंद हो सकते हैं, इसलिए cleanup jobs को छोटे batches में बांटकर process किया गया
  • Backup के लिए VACUUM INTO और restic इस्तेमाल किए, लेकिन OOM termination का सामना हुआ; अधिक efficient incremental backup के लिए Litestream भी test किया जा रहा है
  • Tables को कई SQLite files में अलग किया जा सकता है, और Mess with DNS 2022 से 4 वर्षों तक SQLite पर चला; इसे project के लिए उपयुक्त विकल्प माना गया

Django साइट पर SQLite चलाना

  • छोटे production sites में भी SQLite इस्तेमाल किया जा सकता है, इस बारे में कई लेखों को संदर्भ के रूप में लेकर इसे Django site के database के रूप में अपनाया गया
  • SQLite भी operational knowledge मांगने वाला complex database है, और इस project में Django ORM के जरिए इसे पहले से अधिक काम सौंपे जा रहे हैं
  • कई recommendations के अनुसार शुरुआत से ही WAL mode enable करके operate किया गया
  • यह SQLite इस्तेमाल करने वाली चौथी website है

ANALYZE ने search performance कैसे बदली

  • 4,000 rows वाली table पर SQLite FTS5 का उपयोग करने वाली full-text search query में 5 सेकंड लग रहे थे
  • ANALYZE चलाने पर वही query लगभग 0.05 सेकंड में पूरी होने लगी, इसलिए आगे investigation की जरूरत ही नहीं रही
  • ठीक-ठीक कौन-सी query plan issue थी, यह confirm नहीं हो पाया; अनुमान है कि यह अनजानी quadratic time complexity जैसी स्थिति रही होगी
  • ANALYZE table row counts आदि सहित statistics बनाता है, जिससे query planner बेहतर choices कर पाता है
  • अभी query plans पढ़ने का तरीका सीखा नहीं गया है

Data cleanup और single write constraint

  • django-tasks-db के completed tasks जैसे अनावश्यक rows को bulk में delete करते समय cascading problems आईं
    • कई rows process करने वाला cleanup command 5 सेकंड से ज्यादा चला
    • इस दौरान दूसरे workers के database writes configured 5-second limit से आगे निकलकर timeout हो गए
    • Write failure वाले workers crash हुए और VM भी बंद हो गई
  • DELETE धीमा होने का exact कारण confirm नहीं हुआ; यह संभावना भी देखी जा रही है कि transaction के अंदर बहुत सा Python code चल रहा हो
  • अभी cleanup work को छोटे batches में split किया गया है ताकि हर database query 5 सेकंड से ज्यादा न चले
  • इस अनुभव से यह समझ आया कि Postgres जैसे databases की जरूरत क्यों पड़ती है, जो कई write operations को साथ में handle कर सकते हैं
  • आगे ऐसे कामों के दौरान site को down कर scheduled maintenance करने का विकल्प भी consider किया जा रहा है, लेकिन workflow अभी तैयार नहीं है

ORM queries और मौजूदा data size

  • अब तक Django ORM से desired queries बनाते समय performance अलग से check नहीं की गई, लेकिन ANALYZE issue के अलावा ज्यादातर चीजें ठीक से चलीं
  • Database लगभग 10,000 rows का है और छोटा है; आगे भी इसका छोटा ही रहने की उम्मीद है

SQLite backup method

  • SQLite backup के लिए restic और Litestream, दोनों तरीके आजमाए गए
  • Backup jobs को आमतौर पर dead man’s switch से monitor किया जाता है, लेकिन actual restore test अभी किया गया प्रतीत नहीं होता

  • restic का इस्तेमाल कर backup

    • VACUUM INTO से database की copy बनाई, उसे gzip से compress किया और फिर S3 पर upload किया
    • restic से backup creation, snapshot check, पुराने backups delete करना और cleanup किया गया
    • Backup कभी-कभी OOM से terminate हो जाता था, जिससे lock बच जाता था और restic unlock भी चलाना पड़ता था
    sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ prune

  • Litestream का इस्तेमाल कर incremental backup

    • restic backup के OOM termination से बचने और incremental backup की efficiency test करने के लिए Litestream इस्तेमाल करना शुरू किया गया
    • config file लिखने के बाद नीचे दिए command से replication चलाया गया
    litestream replicate -config litestream.yml
    • Database history को कुछ समय तक रखने के लिए retention: 400h set किया गया, लेकिन यह इच्छित तरीके से काम कर रहा है या नहीं, अभी confirm नहीं हुआ
    • Backup AWS पर हो रहा है, लेकिन console में credentials बनाना झंझट भरा है, इसलिए आगे किसी दूसरे S3-compatible service पर move करने का विकल्प भी consider किया जा रहा है

कई SQLite database files इस्तेमाल करना

  • मौजूदा project में केवल एक database इस्तेमाल हो रहा है, लेकिन Mess with DNS में जिन tables को साथ रखना जरूरी नहीं था, उन्हें 3 database files में split किया गया
  • माना गया कि इस तरह का separation operations में मददगार रहा
  • Mess with DNS 2022 से 4 वर्षों तक SQLite पर चला, और उस project में Postgres से SQLite पर जाना अच्छा फैसला था

Operations के दौरान देर से पता चली basic features

  • Web project में SQLite पहली बार 2022 में इस्तेमाल किया गया था, लेकिन ANALYZE के बारे में इस बार पहली बार पता चला
  • इस्तेमाल की जा रही technology की basic features भी actual operations के दौरान कई वर्षों में लगातार सीखने को मिलती हैं

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

1 टिप्पणियां

 
GN⁺ 9 시간 전
Hacker News की रायें

  • SQLite का .expert mode इस्तेमाल करने से query plan पढ़ना सीखने का दिन थोड़ा टाला जा सकता है: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
    SELECT * FROM x1 WHERE a=? AND b>?; का विश्लेषण करने पर यह CREATE INDEX x1_idx_000123a7 ON x1(a, b); जैसा सुझाया गया index बताता है, और उसे बनाने के बाद दोबारा विश्लेषण करने पर दिखाता है कि नए index की जरूरत नहीं है
    Postgres जैसे “असली” databases में भी cleanup jobs को आम तौर पर छोटे batches में बाँटना सुझाया जाता है। बस छोटे scale पर यह बात कम दिखती है कि वह काम inefficient है, इसलिए मूल लेख का approach उम्मीद से ज्यादा सही था

    • कुछ databases में 1 करोड़ rows delete करने पर उतना ही undo data भी record होता है। Oracle में अगर इसे समय पर backup करके खाली न किया जाए, तो archive logs के लिए disk space खत्म हो सकता है
      बार-बार commit करने से मदद मिलती है, लेकिन बड़े databases की regular cleanup में partitioning सबसे असरदार रही। सबसे पुराना partition delete करने पर काम लगभग तुरंत खत्म हो जाता है
      हालांकि “workers database में लिख नहीं पाते, crash होते हैं और VM बंद हो जाता है” में VM क्यों बंद होता है, यह स्पष्ट नहीं है। यहाँ VM से लगता है कि operating system चलाने वाली virtual machine का मतलब है
    • row-based replication इस्तेमाल करने वाले बड़े MySQL में लाखों rows बदलने वाले UPDATE या DELETE को batches में चलाना पड़ता था। क्योंकि एक ही query से करने पर updated लाखों rows को सभी replicas पर एक साथ भेजना पड़ता है
    • यह EXPLAIN QUERY PLAN जैसा दिखता है: https://sqlite.org/eqp.html
      सामान्य EXPLAIN अक्सर जरूरत से ज्यादा verbose bytecode output करता है, लेकिन EXPLAIN QUERY PLAN summarized plan दिखाता है
    • यह जानना चाहूँगा कि “inefficient operation होने की बात कम उजागर होती है” को क्या वे benefit मान रहे हैं

  • database संभालने वाले के नाते इसे पढ़ना खीझ पैदा करने वाला था, और मन हुआ कि खुद वजह ढूँढकर ठीक करूँ। अगर table में सिर्फ 10 हजार rows हैं, तो full table scan भी बहुत तेज होना चाहिए, और अगर SQLite उसी physical server पर process के अंदर चल रहा हो तो और भी ज्यादा
    दिमाग में आया साफ solution था: “index बनाइए।” slow delete संभवतः ORM users को internal database interactions समझने से पहले अक्सर होने वाली classic N+1 problem लगती है, और उम्मीद है Julia follow-up post लिखेंगी

  • AWS console में credentials बनाना इतना झंझट वाला था कि कुछ साल पहले मैंने सिर्फ उसी समस्या को हल करने वाला tool बनाया था
    uvx s3-credentials create my-existing-s3-bucket
    यह command सिर्फ उस bucket तक सीमित read-write credentials output करती है। --read-only, --write-only से permissions को और सीमित किया जा सकता है, या --prefix foo/bar से सिर्फ किसी खास prefix से शुरू होने वाली keys को read और write कराया जा सकता है
    Restic और Cloudflare R2 के साथ भी इस्तेमाल करके देखा, और यह बहुत अच्छी तरह काम किया

    • जटिल AWS services को संभालने का ज्यादा general तरीका है Terraform थोड़ा सीखना और बाकी LLM पर छोड़ देना। बनाए गए resources याद रखने की जरूरत नहीं रहती और बाद में उन्हें हटाना भी बहुत आसान हो जाता है
    • इसी तरह का मौजूदा tool https://litestream.io/ भी है
    • बहुत शानदार tool है, AWS CLI में शामिल किए जाने लायक लगता है। हालांकि write-only credentials कब चाहिए होते हैं, यह जानने की उत्सुकता है

  • LLM के दौर में Julia की writing की और भी ज्यादा कद्र होने लगी है। ईमानदार खोज की प्रक्रिया उन auto-generated लेखों का antidote है जो आत्मविश्वास से सब कुछ जानने का दिखावा करते हैं

  • backup इस तरह चलाते हैं:
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    अगर writers WAL इस्तेमाल करें तो यह उन्हें block नहीं करता, और अच्छी compression ratio के साथ sync-friendly dump बनता है। Home Assistant database 1.8GB है, लेकिन compressed dump 286MB है, और अनुमान है कि रोज बनने वाली content का करीब 90% समान रहेगा

    • Home Assistant में क्या backup किया जाता है, यह जानना चाहूँगा। default backup बहुत बड़ा था, इसलिए मैंने सिर्फ settings रखीं और videos, cache, HACS द्वारा download किए गए repositories को exclude कर दिया, लेकिन पता नहीं कोई महत्वपूर्ण चीज छूट रही है या नहीं
      यह भी जानना चाहूँगा कि database इतना बड़ा लंबे समय तक रखे गए time-series history की वजह से हुआ या नहीं
    • VACUUM INTO, .backup, sqlite3_rsync, Litestream भी writers को block नहीं करते। .backup backup API इस्तेमाल करता है
    • live database में .dump की वजह से एक बार lock लग गया था, फिर .backup पर switch कर दिया। फिर भी .part में लिखकर mv करने का तरीका साफ-सुथरा है

  • मुझे लगता है कि लेख में असल सामग्री नहीं है, क्योंकि इसमें बस “और जांच नहीं की”, “सबसे अच्छा अनुमान है”, “शायद दूसरी चीज़ें भी?”, “शायद transaction के अंदर काफी Python code चलता हो” जैसी अभिव्यक्तियां हैं। ठीक से पता लगाए या जांच किए बिना कभी-कभी गलत अनुमान तक लगाए गए हैं
    Debian user के तौर पर Linux से जुड़े search results में Ubuntu forums दिखें तो उन्हें न खोलने की वजह भी यही है कि उनमें गलत अनुमान बहुत ज्यादा होते हैं। इसके उलट Arch, Debian से काफी अलग है, लेकिन जानकार लोगों द्वारा लिखी गई Arch Wiki को मैं आम तौर पर देखता हूं

    • Julia Evans की technical knowledge बहुत गहरी है, और वह technology के रहस्य वाले भाव को हटाकर असली problem-solving process कैसी होती है, यह समझाने में खास तौर पर बेहतरीन हैं
      यह लेख SQLite के दुनिया के सबसे बड़े विशेषज्ञ की व्याख्या होने का दिखावा नहीं करता, और शीर्षक से ही “SQLite चलाने के बारे में कुछ बातें सीखना” कहकर अपेक्षाएं साफ कर देता है
      Julia के लेखन में यह संदेश लगातार दिखता है कि सब कुछ जानना या जानने का दिखावा करना जरूरी नहीं; आसान तरीकों से समस्या को समझते हुए ज्ञान बनाया जा सकता है। अब तक जो पता चला है उसे जितना हो सके साफ तौर पर साझा करना अच्छा रवैया है
    • मुझे यह लेख इसलिए शानदार लगता है क्योंकि यह उस technology का इस्तेमाल करने वाले समझदार आम user की स्थिति अच्छी तरह दिखाता है। लेखक का focus website operations पर है, और ऐसी समस्याएं उन रोजमर्रा के users को अक्सर फंसा देती हैं जो पूरा दिन किसी एक खास tool पर ही काम नहीं करते
      मैंने भी कल एक feature बनाते हुए 2 programming languages, 2 build systems, एक cloud provider, secret management tool, दो languages में फैला जटिल client-server communication framework, version control system, editor और CI tool इस्तेमाल किए। इसमें operating system और runtime versions, database, reverse proxy, cache और domain logic तक में भी गहराई से जाया जा सकता है
      सामने आने वाले हर विषय में गहराई तक उतरेंगे तो कोई काम पूरा नहीं होगा, इसलिए लेखक की तरह किस समस्या पर focus करना है, यह चुनना पड़ता है
    • Arch Wiki Linux के सबसे अच्छे resources में से एक है। Mint इस्तेमाल करते समय मैं इसे हमेशा देखता था, लेकिन सच में Arch इस्तेमाल करने लगा तो Mint के दिनों की तुलना में इसे कम देखने लगा

  • DELETE वाली समस्या को batch deletion, batches के बीच delay, और SELECT से rowid पहले से load करने के तरीके से आसानी से कम किया जा सकता है। SELECT blocking नहीं करता
    अगर data उसी table में क्रम से जोड़ा गया था, तो file में भी लगभग उसी क्रम में stored होने की संभावना ज्यादा है, इसलिए उसी क्रम या उल्टे क्रम में delete करने से यह तेज हो सकता है। storage medium और दूसरी conditions के हिसाब से result बदल सकता है

    • row ID preloading सिर्फ SQLite में नहीं, बड़े Aurora MySQL या Postgres clusters में भी बहुत असरदार रहा। SELECT को replica पर भेजा जा सकता था, और row filtering की वजह से index memory pressure database के CPU और buffer cache पर बड़ा बोझ डाल रहा था
      अगर partition pruning जैसे तरीकों से गैर-जरूरी data को main processing path से बाहर रखना मुश्किल हो, तो यह बहुत शक्तिशाली strategy है

  • अभी की परिचित level या काम की जरूरत से database में थोड़ा और गहराई तक जाना अब भी skill बढ़ाने का अच्छा तरीका है। कई web developers database tools के सामने अटक जाते हैं, और मैं भी K8s जैसी कुछ operations technologies में इसी तरह अटकता हूं
    SQL कैसे disk से data पढ़ने और लिखने की operations में बदलता है, यह जानना इस intuition के लिए बहुत मददगार है कि कौन सा approach ठीक रहेगा। database का locking model भी साथ में समझना चाहिए
    यह जानकारी होने पर Postgres में simple COUNT तक जल्दी खत्म न हो तो घबराने की संभावना कम हो जाती है

  • sqlite_stat1 और sqlite_stat4 में index value distribution से जुड़ी कई statistics store होती हैं, और query planner इन्हीं के आधार पर index की selectivity और usefulness का अनुमान लगाता है
    sqlite_stat1 index में records की संख्या और प्रति value औसत records की संख्या ही देता है, और enable करने पर sqlite_stat4 histogram data भी store करता है

  • Litestream बहुत दिलचस्प है, और मैं इसे S3 को backend के तौर पर जोड़कर चलाने में सफल रहा। SQLite इस्तेमाल करने वाली अनगिनत apps को file system state पर लगभग निर्भर न रहने वाला बनाया जा सकता है
    S3 की state manage करना कहीं आसान है, और backup व synchronization भी provider संभाल देता है