สิ่งใหม่บางอย่างที่ได้เรียนรู้จากการใช้งาน SQLite ในระบบจริง

 (jvns.ca)
1 คะแนน โดย GN⁺ 9 시간 전 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • SQLite สามารถใช้ในโปรดักชันของเว็บไซต์ขนาดเล็กได้ แต่เมื่อให้ Django ORM รับภาระงานมากขึ้น ก็เริ่มสัมผัสได้ถึง ความซับซ้อนในการปฏิบัติการของฐานข้อมูล
  • การค้นหาแบบ FTS5 บนตาราง 4,000 แถวใช้เวลา 5 วินาที แต่หลังจากรัน ANALYZE ก็ลดลงเหลือประมาณ 0.05 วินาที แสดงให้เห็นว่าสถิติของ query planner สามารถส่งผลต่อประสิทธิภาพได้มาก
  • หาก DELETE จำนวนมากใช้เวลาเกิน 5 วินาที งานเขียนอื่น ๆ ก็อาจหมดเวลา และถึงขั้นทำให้ทั้ง worker และ VM ถูกปิดได้ จึงแก้ด้วยการแบ่งงานทำความสะอาดออกเป็น แบตช์เล็ก ๆ
  • ใช้ VACUUM INTO และ restic สำหรับการสำรองข้อมูล แต่เคยเจอการปิดตัวเพราะ OOM และกำลังทดลอง Litestream เพื่อทำ incremental backup ที่มีประสิทธิภาพกว่า
  • สามารถแยกตารางออกเป็นไฟล์ SQLite หลายไฟล์ได้ และประเมินว่า Mess with DNS ซึ่งรันด้วย SQLite มาตั้งแต่ปี 2022 เป็นเวลา 4 ปีนั้น เป็นทางเลือกที่เหมาะกับโปรเจกต์นี้

การใช้งาน SQLite กับเว็บไซต์ Django

  • เลือกใช้เป็นฐานข้อมูลของเว็บไซต์ Django โดยอ้างอิงจากบทความหลายชิ้นที่บอกว่า SQLite ใช้กับเว็บไซต์โปรดักชันขนาดเล็กได้
  • SQLite เองก็เป็น ฐานข้อมูลที่ซับซ้อน และต้องอาศัยความรู้ด้านการปฏิบัติการ โดยในโปรเจกต์นี้ให้ Django ORM รับภาระงานมากกว่าเดิม
  • เปิดใช้ WAL mode ตั้งแต่แรกตามคำแนะนำหลายข้อ
  • เว็บไซต์นี้เป็นเว็บลำดับที่ 4 ที่ใช้ SQLite

ANALYZE ที่เปลี่ยนประสิทธิภาพการค้นหา

  • คำสั่งค้นหาแบบ full-text search ด้วย SQLite FTS5 บนตาราง 4,000 แถวใช้เวลา 5 วินาที
  • หลังจากรัน ANALYZE คำสั่งเดียวกันลดเหลือประมาณ 0.05 วินาที จนแทบไม่จำเป็นต้องสืบต่อ
  • ยังไม่สามารถยืนยันได้ว่าเกิดปัญหา query plan แบบใดแน่ แต่คาดว่าอาจคล้ายกับกรณีความซับซ้อนเชิงเวลาระดับกำลังสองโดยไม่ตั้งใจ
  • ANALYZE จะสร้าง ข้อมูลสถิติ เช่น จำนวนแถวในตาราง เพื่อช่วยให้ query planner เลือกแผนที่ดีกว่า
  • ตอนนี้ยังอ่าน query plan ไม่เป็น

การทำความสะอาดข้อมูลและข้อจำกัดเรื่อง single writer

  • เมื่อมีการลบแถวที่ไม่จำเป็นจำนวนมาก เช่น งานที่เสร็จแล้วใน django-tasks-db ก็เกิดปัญหาต่อเนื่อง
    • คำสั่งทำความสะอาดที่จัดการหลายแถวใช้เวลานานกว่า 5 วินาที
    • ระหว่างนั้น การเขียนฐานข้อมูลจาก worker อื่นก็เกินเวลาจำกัด 5 วินาทีที่ตั้งไว้และเกิด timeout
    • worker ที่เขียนล้มเหลวเกิด crash และ VM ก็ถูกปิดด้วย
  • ยังไม่ทราบสาเหตุที่แท้จริงว่าทำไม DELETE จึงช้า และกำลังพิจารณาด้วยว่าอาจเป็นเพราะมี Python code จำนวนมากรันอยู่ภายใน transaction
  • ตอนนี้จึงแก้ด้วยการแบ่งงานทำความสะอาดเป็น แบตช์เล็ก ๆ เพื่อให้แต่ละคำสั่งฐานข้อมูลใช้เวลาไม่เกิน 5 วินาที
  • ประสบการณ์นี้ทำให้เห็นชัดว่าทำไมบางงานจึงต้องใช้ฐานข้อมูลอย่าง Postgres ที่รองรับงานเขียนหลายชุดพร้อมกันได้
  • ในอนาคตอาจพิจารณาปิดเว็บไซต์ชั่วคราวเพื่อทำ scheduled maintenance ระหว่างงานลักษณะนี้ แต่ยังไม่ได้วาง workflow ไว้

ORM query และขนาดข้อมูลปัจจุบัน

  • จนถึงตอนนี้สร้าง query ที่ต้องการด้วย Django ORM โดย ไม่ได้ตรวจสอบประสิทธิภาพแยกต่างหาก แต่ยกเว้นปัญหา ANALYZE แล้ว ส่วนใหญ่ก็ทำงานได้ตามปกติ
  • ฐานข้อมูลมีขนาดเล็กประมาณ 10,000 แถว และคาดว่าจะยังเล็กต่อไปในอนาคต

วิธีสำรองข้อมูลของ SQLite

  • ได้ลองใช้ทั้ง restic และ Litestream สำหรับการสำรองข้อมูลของ SQLite
  • โดยปกติจะเฝ้าดูงานสำรองข้อมูลด้วย dead man's switch แต่ดูเหมือนว่ายังไม่ได้ทดสอบการกู้คืนจริง

  • การสำรองข้อมูลด้วย restic

    • ใช้ VACUUM INTO เพื่อสร้างสำเนาฐานข้อมูล จากนั้นบีบอัดด้วย gzip แล้วอัปโหลดขึ้น S3
    • ใช้ restic เพื่อสร้างแบ็กอัป ตรวจสอบ snapshot ลบแบ็กอัปเก่า และทำความสะอาด
    • บางครั้งงานสำรองข้อมูลถูก OOM จนหยุดทำงานและทิ้ง 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

  • Incremental backup ด้วย Litestream

    • เริ่มใช้ Litestream เพื่อหลีกเลี่ยงปัญหาการหยุดทำงานแบบ OOM ของ restic และทดลองประสิทธิภาพของ incremental backup
    • หลังจากเขียนไฟล์ตั้งค่าแล้ว ก็รันการ replicate ด้วยคำสั่งต่อไปนี้
    litestream replicate -config litestream.yml
    • กำหนด retention: 400h เพื่อเก็บประวัติฐานข้อมูลไว้ช่วงหนึ่ง แต่ยังไม่แน่ใจว่าทำงานตรงตามตั้งใจหรือไม่
    • ตอนนี้สำรองข้อมูลไว้บน AWS แต่การสร้าง credentials ผ่านคอนโซลค่อนข้างยุ่งยาก จึงกำลังพิจารณาย้ายไปใช้ บริการที่เข้ากันได้กับ S3 อื่นในอนาคต

การใช้ไฟล์ฐานข้อมูล SQLite หลายไฟล์

  • ปัจจุบันโปรเจกต์นี้ใช้ฐานข้อมูลเพียงก้อนเดียว แต่ใน Mess with DNS ได้แยกตารางที่ไม่จำเป็นต้องอยู่ร่วมกันออกเป็น ไฟล์ฐานข้อมูล 3 ไฟล์
  • มองว่าการแยกแบบนี้ช่วยให้การปฏิบัติการง่ายขึ้น
  • Mess with DNS ใช้ SQLite มาตั้งแต่ปี 2022 เป็นเวลา 4 ปี และสำหรับโปรเจกต์นั้น การย้ายจาก Postgres มาเป็น SQLite ถือเป็นการตัดสินใจที่ดี

ฟีเจอร์พื้นฐานที่เพิ่งค้นพบระหว่างการใช้งานจริง

  • แม้จะเริ่มใช้ SQLite กับเว็บโปรเจกต์ตั้งแต่ ปี 2022 แต่เพิ่งมารู้จัก ANALYZE ในครั้งนี้
  • แม้แต่ฟีเจอร์พื้นฐานของเทคโนโลยีที่ใช้อยู่ ก็ยังค่อย ๆ เรียนรู้เพิ่มเติมได้ตลอดหลายปีจากการใช้งานจริง

บทความที่เกี่ยวข้อง

1 ความคิดเห็น

 
GN⁺ 9 시간 전
ความเห็นจาก Hacker News

  • ถ้าใช้ โหมด .expert ของ SQLite ก็อาจเลื่อนวันที่ต้องเรียนรู้การอ่าน 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); และเมื่อสร้างแล้วลองวิเคราะห์อีกครั้ง ก็จะแสดงว่าไม่ต้องมีดัชนีใหม่แล้ว
    แม้แต่ในฐานข้อมูล “จริงจัง” อย่าง Postgres ก็มักแนะนำให้แบ่งงาน cleanup ออกเป็น batch เล็กๆ เช่นกัน เพียงแต่ในระบบขนาดเล็กความไม่มีประสิทธิภาพของงานแบบนี้จะเห็นได้ไม่ชัด ดังนั้นแนวทางในบทความต้นฉบับจึงถูกต้องกว่าที่คิด

    • ในบางฐานข้อมูล การ ลบ 10 ล้านแถว จะทำให้มีการบันทึก undo data ปริมาณเท่ากันด้วย ใน Oracle ถ้าไม่สำรองและล้างมันออกให้ทันเวลา พื้นที่ดิสก์สำหรับ archive log ก็อาจหมดได้
      การ commit บ่อยๆ ช่วยได้ แต่เวลาต้อง cleanup ฐานข้อมูลขนาดใหญ่เป็นประจำ การทำ partitioning มีประสิทธิภาพที่สุด ลบ partition ที่เก่าที่สุดแล้วแทบจะเสร็จทันที
      แต่ตรงประโยค “worker เขียนฐานข้อมูลไม่ได้จนชนกันและ VM ถูกปิด” ยังไม่ชัดว่า VM ปิดไปทำไม ในที่นี้ VM น่าจะหมายถึง virtual machine ที่รันระบบปฏิบัติการอยู่
    • ใน MySQL ขนาดใหญ่ที่ใช้ row-based replication ต้องรัน UPDATE หรือ DELETE ที่เปลี่ยนหลายล้านแถวเป็น batch เพราะถ้าทำด้วย query เดียว จะต้องส่งหลายล้านแถวที่ถูกอัปเดตไปยัง replica ทุกตัวพร้อมกัน
    • ดูคล้ายกับ EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html
      EXPLAIN แบบปกติมักพ่น bytecode ที่ยืดยาวเกินจำเป็น แต่ EXPLAIN QUERY PLAN จะแสดงแผนแบบสรุป
    • สงสัยว่าการที่ “ทำให้เห็นความไม่มีประสิทธิภาพของงานนั้นน้อยลง” ถูกมองเป็นข้อดีหรือเปล่า

  • ในฐานะคนที่ทำงานกับฐานข้อมูล อ่านแล้วอึดอัด และอยากหาสาเหตุจริงมาแก้เอง ถ้าเป็น ตารางที่มีแค่ 10,000 แถว การสแกนทั้งตารางก็ควรจะเร็วมากอยู่แล้ว และถ้าเป็น SQLite ที่รันอยู่ในโปรเซสบนเครื่องเดียวกันทางกายภาพก็ยิ่งควรเป็นแบบนั้น
    วิธีแก้ที่ผุดขึ้นมาในหัวตามธรรมชาติก็คือ “สร้างดัชนีสิ” การลบที่ช้าน่าจะเป็น ปัญหา N+1 แบบคลาสสิกที่ผู้ใช้ ORM มักเจอก่อนจะเข้าใจการโต้ตอบกับฐานข้อมูลภายในดีพอ และหวังว่า Julia จะเขียนบทความต่อ

  • การสร้าง credentials ใน AWS Console ยุ่งยากเกินไป เลยทำเครื่องมือมาแก้ปัญหานั้นโดยเฉพาะเมื่อหลายปีก่อน
    uvx s3-credentials create my-existing-s3-bucket
    คำสั่งนี้จะพิมพ์ credentials สำหรับอ่านและเขียนที่ถูกจำกัดขอบเขตไว้แค่ bucket นั้น จะจำกัดสิทธิ์เพิ่มด้วย --read-only, --write-only หรือให้เขียนอ่านได้เฉพาะ key ที่ขึ้นต้นด้วย prefix ที่กำหนดผ่าน --prefix foo/bar ก็ได้
    ลองใช้กับ Restic และ Cloudflare R2 ด้วยแล้ว และทำงานได้ดีมาก

    • วิธีที่ทั่วไปกว่าสำหรับจัดการบริการ AWS ที่ซับซ้อนคือเรียนรู้ Terraform แค่พอประมาณ แล้วปล่อยที่เหลือให้ LLM จัดการ แบบนี้ไม่ต้องจำทรัพยากรที่สร้างไว้ และถอนออกทีหลังก็ง่ายขึ้นมาก
    • มีเครื่องมือเดิมที่คล้ายกันคือ https://litestream.io/
    • เป็นเครื่องมือที่ยอดเยี่ยมมาก ดูเหมือน AWS CLI น่าจะรับไปรวมได้เลย แต่ก็สงสัยว่า credentials แบบ write-only จะมีความจำเป็นตอนไหน

  • พอเข้าสู่ยุค LLM แล้วก็ยิ่งชื่นชมบทความของ Julia มากขึ้น กระบวนการสำรวจค้นหาอย่างจริงใจ เป็นยาแก้พิษให้กับบทความที่ถูกสร้างอัตโนมัติแบบทำเป็นรู้ทุกอย่างอย่างมั่นใจ

  • ผมรันแบ็กอัปแบบนี้:
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    ถ้า writer ใช้ WAL ก็จะไม่ไปบล็อกมัน และได้ dump ที่บีบอัดได้ดีแต่ก็ sync ได้ง่าย ฐานข้อมูล Home Assistant มีขนาด 1.8GB แต่ dump ที่บีบอัดแล้วมีแค่ 286MB และคาดว่าประมาณ 90% ของข้อมูลที่สร้างในแต่ละวันจะเหมือนเดิม

    • สงสัยว่าใน Home Assistant แบ็กอัปอะไรอยู่บ้าง แบ็กอัปปกติใหญ่เกินไป เลยเก็บไว้แค่ค่าตั้งค่า และตัดวิดีโอ, แคช, repository ที่ HACS ดาวน์โหลดออกไป ไม่แน่ใจว่าพลาดอะไรสำคัญหรือเปล่า
      และก็สงสัยด้วยว่าที่ฐานข้อมูลโตขนาดนั้นเป็นเพราะเก็บ บันทึกอนุกรมเวลา ไว้นานหรือไม่
    • VACUUM INTO, .backup, sqlite3_rsync, Litestream ก็ไม่บล็อก writer เช่นกัน .backup ใช้ backup API
    • เคยโดน .dump ล็อกฐานข้อมูลจริงอยู่ครั้งหนึ่ง เลยเปลี่ยนมาใช้ .backup ถึงอย่างนั้นวิธี เขียนลง .part แล้วค่อย mv ก็ยังดูเรียบร้อยดี

  • มองว่าบทความนี้แทบไม่มีเนื้อหาสาระจริง เพราะมีแต่ถ้อยคำอย่าง “ยังไม่ได้สืบต่อ”, “นี่คือการคาดเดาที่ดีที่สุด”, “อย่างอื่นก็น่าจะมีด้วย?”, “อาจมีโค้ด Python จำนวนมากรันอยู่ภายในทรานแซ็กชัน” เท่านั้น อีกทั้งบางครั้งยังคาดเดาผิดโดยที่ไม่ได้ตรวจสอบหรือค้นคว้าให้ดี
    ในฐานะผู้ใช้ Debian เหตุผลที่พอค้นเรื่อง Linux แล้วเจอฟอรัม Ubuntu ก็จะไม่ค่อยกดเข้าไปดู ก็เพราะมีการคาดเดาที่ผิดเยอะเกินไป ตรงกันข้าม แม้ Arch จะต่างจาก Debian มาก แต่ Arch Wiki ที่เขียนโดยคนมีความรู้ก็มักจะเป็นแหล่งที่ตรวจดูอยู่เสมอ

    • Julia Evans มีความรู้ด้านเทคนิคกว้างขวางมาก และเก่งเป็นพิเศษในการสลายความลึกลับของเทคโนโลยีและทำให้เข้าใจว่ากระบวนการแก้ปัญหาจริง ๆ เป็นอย่างไร
      บทความนี้ไม่ได้แสร้งทำเป็นคำอธิบายจากผู้เชี่ยวชาญ SQLite ระดับโลก และตั้งความคาดหวังไว้อย่างชัดเจนตั้งแต่ชื่อว่า “เรียนรู้อะไรใหม่ ๆ บางอย่างเกี่ยวกับการใช้งาน SQLite”
      ใจความที่ไหลผ่านงานเขียนของ Julia คือ ไม่จำเป็นต้องรู้ทุกอย่างหรือทำเป็นว่ารู้ทุกอย่าง ก็สามารถค่อย ๆ ทำความเข้าใจปัญหาและสั่งสมความรู้ได้ด้วยวิธีง่าย ๆ การแบ่งปันสิ่งที่ค้นพบมาจนถึงตอนนี้ให้ชัดเจนที่สุดถือเป็นท่าทีที่ดี
    • มองว่าบทความนี้ยอดเยี่ยมเพราะสะท้อนสถานการณ์ของผู้ใช้ทั่วไปที่ฉลาดและใช้งานเทคโนโลยีนี้ได้ดี โฟกัสของผู้เขียนคือการดูแลเว็บไซต์ และปัญหาแบบนี้ก็คือจุดที่ผู้ใช้ทั่วไปซึ่งไม่ได้ใช้เครื่องมือเฉพาะตัวเดียวทั้งวันมักจะสะดุดกัน
      เมื่อวานนี้เองตอนทำฟีเจอร์หนึ่ง ฉันใช้ภาษาโปรแกรม 2 ภาษา, build system 2 ตัว, ผู้ให้บริการคลาวด์, เครื่องมือจัดการความลับ, เฟรมเวิร์กการสื่อสาร client-server ที่ซับซ้อนข้ามสองภาษา, ระบบควบคุมเวอร์ชัน, editor และเครื่องมือ CI นอกจากนี้ยังสามารถขุดลึกลงไปถึงระบบปฏิบัติการและเวอร์ชัน runtime, ฐานข้อมูล, reverse proxy, cache และ business logic ได้อีก
      ถ้าจะขุดลึกทุกหัวข้อที่เจอ สุดท้ายจะทำอะไรไม่เสร็จเลย ดังนั้นจึงต้องเลือกปัญหาที่จะโฟกัสเหมือนผู้เขียน
    • Arch Wiki เป็นหนึ่งในแหล่งข้อมูล Linux ที่ดีที่สุด ตอนใช้ Mint ก็เปิดดูอยู่เสมอ แต่พอมาใช้ Arch จริง ๆ กลับเปิดดูน้อยกว่าตอนใช้ Mint เสียอีก

  • ปัญหา DELETE บรรเทาได้ง่ายด้วยการลบแบบเป็นชุด, การหน่วงเวลาระหว่างแต่ละชุด และการใช้ SELECT เพื่อโหลด rowid ล่วงหน้า โดย SELECT ไม่ก่อให้เกิดการบล็อก
    ถ้าข้อมูลถูกเพิ่มเข้ามาในตารางเดียวกันแบบต่อเนื่อง ก็มีโอกาสสูงว่าจะถูกเก็บในไฟล์ในลำดับใกล้เคียงกัน ดังนั้นการลบตามลำดับนั้นหรือย้อนลำดับอาจทำได้เร็วกว่า ทั้งนี้ผลลัพธ์จะแตกต่างกันไปตามสื่อจัดเก็บและเงื่อนไขอื่น ๆ

    • การโหลด row ID ล่วงหน้ามีประสิทธิภาพมากไม่ใช่แค่กับ SQLite แต่กับคลัสเตอร์ Aurora MySQL หรือ Postgres ขนาดใหญ่ด้วย เพราะสามารถส่ง SELECT ไปยัง replica ได้ และแรงกดดันด้านหน่วยความจำของดัชนีที่เกิดจากการกรองแถวก็สร้างภาระหนักให้กับ CPU และ buffer cache ของฐานข้อมูลอยู่แล้ว
      ถ้าไม่สามารถกันข้อมูลที่ไม่จำเป็นออกจากเส้นทางประมวลผลหลักได้ง่าย ๆ ด้วยวิธีอย่างการตัด partition ก็ถือเป็นกลยุทธ์ที่ทรงพลังมาก

  • การขุดลึกเรื่องฐานข้อมูลให้มากกว่าระดับที่คุ้นเคยในตอนนี้หรือเกินกว่าที่งานต้องใช้เพียงเล็กน้อย ยังเป็นวิธีที่ดีในการพัฒนาฝีมืออยู่เสมอ นักพัฒนาเว็บจำนวนมากมักไปต่อไม่ถูกเมื่อเจอกับเครื่องมือฐานข้อมูล และฉันเองก็ติดขัดคล้ายกันกับเทคโนโลยีปฏิบัติการบางอย่างอย่าง K8s
    ถ้ารู้ว่า SQL ถูกแปลงไปเป็นการอ่านและเขียนข้อมูลบนดิสก์อย่างไร ก็จะช่วยมากในการตัดสินเชิงสัญชาตญาณว่าแนวทางไหนน่าจะใช้ได้ดี และควรเข้าใจระบบการล็อกของฐานข้อมูลควบคู่กันไปด้วย
    ถ้ามีความรู้แบบนี้ เวลาที่แม้แต่ COUNT ง่าย ๆ บน Postgres ก็ยังไม่จบเร็ว ก็จะตกใจน้อยลง

  • ใน sqlite_stat1 และ sqlite_stat4 จะเก็บข้อมูลสถิติหลายอย่างเกี่ยวกับการกระจายค่าของดัชนี และ query planner จะใช้ข้อมูลเหล่านี้เพื่อประเมินความสามารถในการคัดกรองและความมีประโยชน์ของดัชนี
    sqlite_stat1 ให้เพียงจำนวนเรคอร์ดของดัชนีและจำนวนเรคอร์ดเฉลี่ยต่อค่าเท่านั้น ส่วน sqlite_stat4 หากเปิดใช้งานจะเก็บข้อมูล histogram ด้วย

  • Litestream น่าสนใจมาก และสามารถเชื่อม S3 เป็น backend แล้วรันได้สำเร็จ ทำให้แอปจำนวนมากที่ใช้ SQLite แทบไม่ต้องพึ่งพาสถานะของไฟล์ระบบเลย
    สถานะของ S3 จัดการได้ง่ายกว่ามาก และเรื่องแบ็กอัปกับการซิงก์ก็เป็นหน้าที่ที่ผู้ให้บริการจัดการให้