วิธีคัดลอกฐานข้อมูล SQLite ระหว่างคอมพิวเตอร์ให้เร็วขึ้น

 (alexwlchan.net)
1 คะแนน โดย GN⁺ 2025-05-02 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • เมื่อต้องย้าย ฐานข้อมูล SQLite ขนาดใหญ่จากเซิร์ฟเวอร์ระยะไกลมายังเครื่อง local การคัดลอก SQL dump ที่บีบอัดด้วย gzip แทนไฟล์ .db ต้นฉบับจะช่วยลดปริมาณการส่งข้อมูลได้อย่างมาก
  • สาเหตุหลักที่ทำให้ไฟล์มีขนาดใหญ่ขึ้นคือ index ซึ่งเก็บข้อมูลตารางซ้ำเพื่อให้ query เร็วขึ้น และทำให้ใช้พื้นที่ดิสก์มากขึ้น
  • .dump ของ SQLite จะแปลงฐานข้อมูลเป็นข้อความคำสั่ง SQL และแทน index ด้วยบรรทัด CREATE INDEX เพียงบรรทัดเดียว ไม่ใช่ข้อมูลจริง จึงหลีกเลี่ยงการส่งข้อมูลซ้ำ
  • ฐานข้อมูลตัวอย่างมีไฟล์ต้นฉบับ 3.4GB, ข้อความ dump 1.3GB, และไฟล์บีบอัด gzip 240MB โดย dump ที่บีบอัดแล้วมีขนาดเล็กกว่าต้นฉบับ 14 เท่า
  • หากทำไฟล์ dump ให้คงที่ไว้ก่อนคัดลอก จะช่วยลดกรณีที่ต้นฉบับเปลี่ยนระหว่างการส่งจนเกิดข้อผิดพลาด database disk image is malformed

คัดลอก SQLite dump หลังบีบอัด

  • สำหรับฐานข้อมูล SQLite ขนาดเล็ก การคัดลอกโดยตรงด้วย rsync ก็เพียงพอ
    • ตัวอย่าง: rsync --progress username@server:my_remote_database.db my_local_database.db
  • เมื่อฐานข้อมูลมีขนาดใหญ่ขึ้น การคัดลอกโดยตรงอาจช้าและไม่เสถียร
    • ฐานข้อมูลขนาด 250MB ใช้เวลาประมาณ 1 นาทีในการดาวน์โหลดจากเว็บเซิร์ฟเวอร์ผ่านอินเทอร์เน็ตบ้าน
    • สำหรับฐานข้อมูลขนาดหลาย GB เวลาในการรอจะเพิ่มขึ้นอีก
  • คอขวดของการเพิ่มขนาดอยู่ที่ index
    • index ช่วยเพิ่มความเร็ว query ได้มาก แต่ก็ทำให้ไฟล์ฐานข้อมูลใหญ่ขึ้นด้วย
    • ในฐานข้อมูลหนึ่ง index บางตัวกินพื้นที่ดิสก์ไปครึ่งหนึ่ง
    • index ไม่ได้เก็บข้อมูลที่ไม่ซ้ำเป็นหลัก แต่ทำซ้ำข้อมูลจากตารางอื่นเพื่อให้ค้นหาได้เร็วขึ้น
  • SQLite สามารถ dump ฐานข้อมูลทั้งหมดเป็นไฟล์ข้อความ ได้
    • คำสั่ง: sqlite3 my_database.db .dump > my_database.db.txt
    • ไฟล์ผลลัพธ์ประกอบด้วยคำสั่ง SQL เช่น CREATE TABLE, INSERT INTO, CREATE INDEX
    • index ถูกบันทึกเป็นคำสั่งสร้าง เช่น CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]); ไม่ใช่ข้อมูล index จริง
  • ไฟล์ dump สามารถกู้คืนกลับเป็นฐานข้อมูล SQLite ได้
    • คำสั่ง: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
  • SQL dump มีข้อมูลซ้ำจำนวนมาก จึงเหมาะกับ การบีบอัดด้วย gzip อย่างมีประสิทธิภาพ
    • ตัวอย่าง: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
    • เปรียบเทียบขนาด:
      • ฐานข้อมูล SQLite ต้นฉบับ: 3.4GB
      • dump แบบข้อความ: 1.3GB
      • ข้อความที่บีบอัดด้วย gzip: 240MB
    • ไฟล์ gzip มีขนาดเล็กกว่าฐานข้อมูล SQLite ต้นฉบับ 14 เท่า

ขั้นตอนจริงและผลด้านความเสถียร

  • วิธีใหม่คือสร้าง dump ที่บีบอัดบนเซิร์ฟเวอร์ระยะไกล ดาวน์โหลดเฉพาะไฟล์นั้น แล้วค่อยประกอบฐานข้อมูลใหม่บนเครื่อง local
  • ขั้นตอนการคัดลอกมีดังนี้
    • สร้างไฟล์ข้อความที่บีบอัดด้วย gzip บนเซิร์ฟเวอร์: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
    • คัดลอกมายังเครื่อง local: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
    • ลบไฟล์บีบอัดชั่วคราวบนเซิร์ฟเวอร์: ssh username@server "rm my_remote_database.db.txt.gz"
    • แตกไฟล์บนเครื่อง local: gunzip my_local_database.db.txt.gz
    • ประกอบฐานข้อมูล local ใหม่: cat my_local_database.db.txt | sqlite3 my_local_database.db
    • ลบไฟล์ข้อความบนเครื่อง local: rm my_local_database.db.txt
  • dump ที่บีบอัดทำหน้าที่เป็น ต้นทางการคัดลอกที่สอดคล้องกัน
    • หากต้นฉบับถูกอัปเดตระหว่างการคัดลอกฐานข้อมูลขนาดใหญ่ที่ใช้เวลานาน ไฟล์ผลลัพธ์อาจมีส่วนต้นเป็นสถานะก่อนอัปเดต และส่วนท้ายเป็นสถานะหลังอัปเดต
    • ไฟล์ลักษณะนี้อาจเกิดข้อผิดพลาด database disk image is malformed เมื่อเปิดบนเครื่อง local
  • เมื่อสร้าง text dump ก่อนคัดลอก ไฟล์ที่ rsync ส่งจะไม่เปลี่ยนกลางทาง
    • จึงมีโอกาสสูงขึ้นที่จะคัดลอกไฟล์ข้อความที่สมบูรณ์และสอดคล้องกัน
  • หากต้องย้ายฐานข้อมูล SQLite ขนาดใหญ่บ่อย ๆ วิธีนี้ช่วยลดเวลาดาวน์โหลดและเพิ่มความน่าเชื่อถือในการคัดลอกได้

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

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

 
GN⁺ 2025-05-02
ความคิดเห็นจาก Hacker News

  • ถ้าคัดลอกไฟล์ฐานข้อมูลที่กำลังทำงานอยู่และกำลังรับการอัปเดตไปตรง ๆ ก็ย่อมมีโอกาส เสียหาย ได้อยู่แล้ว
    ถ้าต้องการทำสำเนาฐานข้อมูล SQLite อย่างปลอดภัย มี Litestream: https://github.com/benbjohnson/litestream

    • Litestream ทำงานได้แม้บน SFTP ธรรมดา ๆ ดังนั้นจึงสามารถสตรีมการทำสำเนาฐานข้อมูลไปยัง UNIX endpoint แทบทุกตัวที่เข้าถึงได้ผ่าน SSH
      มีบริการที่ชอบใช้อยู่ แต่ SFTP server ไหนก็ใช้ได้
      [1] https://github.com/benbjohnson/litestream/issues/140
      [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
    • คำว่า “ต้อง” เสียหายนั้นมีข้อยกเว้นอยู่ หากคัดลอกไฟล์หรือ subvolume แบบอะตอมมิกบน Btrfs หรือ ZFS สำหรับฐานข้อมูล ACID หรือ LSM tree อย่างแย่ที่สุดก็แค่เกิด rollback
      แน่นอนว่าถ้ามีหลายไฟล์ ก็ต้องรวมไว้เป็น subvolume เพื่อให้ทั้งหมดถูกคัดลอกภายใน transaction เดียวกัน และแค่ใช้ cp --reflink=always อย่างเดียวไม่พอ
      การหยุดโปรเซสด้วย SIGSTOP ก็อาจให้ผลคล้ายกันได้ แต่ไม่อยากพึ่งวิธีนั้น
    • คำสั่ง .backup ที่มากับ SQLite เองก็เป็นเครื่องมือทางการที่ตั้งใจให้สร้างเวอร์ชัน snapshot ของฐานข้อมูลที่กำลังรันอยู่เพื่อให้คัดลอกได้
    • ในระบบของผมใช้ Litestream ได้ดี แต่ก็มีเอกสารที่อธิบายค่อนข้างละเอียดถึงวิธีทำงานคล้ายกันแบบ manual ด้วยเครื่องมือที่มีมาในตัวเท่านั้น ซึ่งก็ดีเหมือนกัน: https://litestream.io/alternatives/cron/
    • Litestream เจ๋งมาก ตั้งใจว่าจะลองใช้สำหรับ การสำรองและกู้คืน SQLite ในระดับคอนเทนเนอร์ แบบที่อดีต Googler ผู้สร้างสตาร์ทอัพ KVM ขนาดเล็กเคยทำตอนโกดังของเขาถูกน้ำท่วมระหว่างไปพักร้อน
      ถ้าจำไม่ผิด เขาเขียนไกด์ไว้ครบถ้วนมาก แต่โอกาสที่ผมจะหาเจอแทบเป็น 0 ถ้ามีใครรู้ reference นี้ ช่วยโพสต์ลิงก์ให้หน่อยก็ดี

  • บอกว่าเป็นการคัดลอกฐานข้อมูลระหว่างคอมพิวเตอร์ แต่รู้สึกเหมือนวาดแค่วงกลมแล้วละส่วนที่เหลือของนกฮูกไว้เลย
    อย่างที่คนอื่นบอก incremental rsync น่าจะเร็วกว่ามาก แต่สิ่งที่น่าหงุดหงิดกว่าคือ ส่วนที่บอกว่าการส่ง SQL statement เร็วกว่าการส่งฐานข้อมูล กลับลืมไปสนิทว่า ต้อง execute SQL statement เหล่านั้นด้วย จากนั้นยังต้องรัน /optimize/ และ /vacuum/ อีก
    ตอนนี้มีสถานการณ์ที่ต้อง “rebuild แบบ incremental” ฐานข้อมูลจากไฟล์ CSV อยู่ ซึ่งในกรณีของผม การสร้างใหม่ตั้งแต่ต้นเหมาะสมกว่า ถึงจะ optimize ไปมากแล้ว แต่แค่ batch insert ลงฐานข้อมูลใน memory เปล่า ๆ แล้วสร้าง index ก็ใช้เวลา 30 นาที

    • หวังว่าได้ดู https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite แล้ว
      เป็นบทความที่สรุปวิธี insert ให้เร็วใน SQLite3 ได้ดีมาก
    • ไม่ว่าจะ optimize แบบไหน สิ่งสำคัญตรงนี้คือ คอขวด อยู่ที่ไหน กรณีในต้นฉบับดูเหมือนคอขวดคือ bandwidth ส่วน CPU/disk I/O น่าจะเหลือพอ
      เขาบอกว่าดาวน์โหลดฐานข้อมูล 250MB ใช้เวลา 1 นาที แต่ผมมีไฟเบอร์ 1Gbps เลยดึงฐานข้อมูลทดสอบ SQLite ขนาด 2GB จากเซิร์ฟเวอร์บริษัทมาได้ใน 15 วินาที
    • 30 นาทีดูนานนะ ข้อมูลเยอะมากไหม? ผมกำลังทำงาน bootstrap ฐานข้อมูล SQLite ด้วยข้อมูล JSON จำนวนมากอยู่ และพบ performance sweet spot ที่ค่อนข้างดี คือรวมค่าเป็น list แล้ว insert ทีละ 10,000 รายการ ทำให้ใส่ข้อมูลได้หลายล้านแถวภายในไม่กี่นาที
      ต้องใช้เทคนิคอย่าง Bloom filter กับ LRU cache แต่ตอนนี้สร้างฐานข้อมูล 6GB ได้ในราว 20 นาที

  • SQLite มีเครื่องมือทางการสำหรับงานนี้: https://www.sqlite.org/rsync.html
    ทำงานใน ระดับ page
    “โปรโตคอลนี้ให้ replica ส่ง hash แบบ cryptographic ของแต่ละ page ไปยังฝั่งต้นทาง แล้วต้นทางจะส่งเนื้อหาทั้งหมดของ page ที่ hash ไม่ตรงกันกลับมา”

    • ถูกต้อง แต่น่าเสียดายที่ทีม SQLite ไม่ได้ใส่เครื่องมือนั้นไว้ใน autotools tarball ที่ distro ส่วนใหญ่และ brew ใช้สำหรับแพ็กเกจ SQLite
      ถ้าจะใช้เครื่องมือนี้ ก็มีแต่ต้องคอมไพล์เอง

  • ยูทิลิตี sqlite_rsync ที่เพิ่ง release ล่าสุด ใช้เวอร์ชันของ อัลกอริทึม rsync ที่ optimize ให้เข้ากับโครงสร้างภายในของฐานข้อมูล SQLite โดยเปรียบเทียบ data page ภายในอย่างมีประสิทธิภาพ แล้ว sync เฉพาะ page ที่เปลี่ยนไปหรือยังไม่มี
    เทคนิคในบทความก็ดี แต่ตอนนี้ใช้ยูทิลิตีที่มีมาให้จะง่ายกว่า :)
    เขียนรายละเอียดหลักการทำงานไว้ในบล็อกแล้ว: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

    • ตอนนี้ sqlite3_rsync ถูกใส่ไว้ใน แพลตฟอร์ม rsync.net แล้ว
      ssh user@rsync.net sqlite3_rsync … blah blah …
      เพิ่งเพิ่มเข้าไปเมื่อสัปดาห์ที่แล้ว จึงยังไม่ได้ deploy ไปครบทุก region แต่ผู้ใช้ช่วงแรกทั้งหมดบอกว่าทำงานได้ตรงตามที่คาด
    • sqlite_rsync ใช้ได้เฉพาะใน WAL mode เท่านั้น ข้อจำกัดเพิ่มเติมของ WAL mode คือไฟล์ฐานข้อมูลต้องอยู่บนดิสก์ local
      ส่วนใหญ่ก็คงเป็นแบบนั้นอยู่แล้ว แต่ถ้าทำไม่ได้ ก็ใช้ยูทิลิตีนี้ไม่ได้
    • ถ้า sync บ่อย ๆ ควรเพิ่ม page size เพราะเรื่อง bandwidth

  • น่าแปลกที่ไม่ได้ลองใช้การบีบอัดระหว่างถ่ายโอนที่ rsync มีให้
    -z, --compress จะบีบอัดข้อมูลไฟล์ระหว่างถ่ายโอน และ --compress-level=NUM จะระบุระดับการบีบอัดอย่างชัดเจน
    บางทีการ gzip ก่อนแล้วค่อยส่งอาจเร็วกว่า แต่ข้อดีคือสามารถปรับปรุงการถ่ายโอนได้ด้วยแฟล็กเดียว

    • ถ้าพูดถึงปัญหาความเสียหาย ใช้ sqlite3_rsync(https://sqlite.org/rsync.html) ร่วมกับ -z น่าจะดีกว่า
      ก็เหมือน rsync ที่เข้าใจ transaction ของ SQLite และ WAL แถมมีการบีบอัดระหว่างถ่ายโอนด้วย
    • ประเด็นหลักคือการข้าม ดัชนี และเรื่องนี้ต้องทำก่อนการบีบอัด
      เวลาทำงานแบบนี้จะสตรีม dump เข้า gzip โดยตรง ปกติยังหาวิธีสตรีมตรงไปยังปลายทางได้โดยไม่ต้องมีไฟล์กลางด้วย
      นอกจากนี้ยังทำให้ปลายทางเก็บไว้ในสถานะที่ถูกบีบอัดแล้ว โดยเฉพาะถ้าเป้าหมายคือการสำรองข้อมูล ไม่ใช่การจำลองแบบสไตล์คนงบน้อย
    • มองว่าการบีบอัดมีประโยชน์เฉพาะบนเครือข่ายที่ช้าเท่านั้น
    • อันนี้ต้องทำแน่นอน ถ้าใช้ rsync กับไฟล์ที่บีบอัดแล้ว ก็เท่ากับอ้อมข้ามหัวใจสำคัญของการใช้ rsync อย่าง การถ่ายโอนส่วนต่างตาม rolling checksum ไปทั้งหมด
    • น่าจะใช้ --remove-source-files ได้ จะได้ไม่ต้อง SSH เข้าไปอีกครั้งเพื่อ rm

  • การเก็บเป็นไฟล์ข้อความไม่มีประสิทธิภาพ ผมเก็บฐานข้อมูล SQLite ด้วย VACUUM INTO
    sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
    ตาม https://sqlite.org/lang_vacuum.html คำสั่ง VACUUM ที่มี clause INTO เป็นทางเลือกแทน backup API สำหรับสร้างสำเนาสำรองของฐานข้อมูลที่กำลังทำงานอยู่ ข้อดีคือฐานข้อมูลสำรองที่ได้มีขนาดเล็กที่สุด จึงอาจลด I/O ของระบบไฟล์ได้

    • เท่ก็จริง แต่ไม่ได้แก้ ปัญหาเรื่องดัชนี ที่บทความต้นฉบับพูดถึง ไอเดียหลักคือไม่ส่งข้อมูลดัชนีผ่านลิงก์ที่ช้า แต่แนวทาง VACUUM INTO ยังคงเก็บดัชนีไว้
      ไฟล์ข้อความอาจไม่มีประสิทธิภาพถ้าใช้ตามนั้น แต่บีบอัดได้ดีแม้ด้วยเครื่องมือพื้นฐานอย่าง gzip ส่วนรูปแบบไบนารีของ SQLite จะบีบอัดได้ดีพอ ๆ กันไหมยังไม่แน่ใจ แต่ก็เป็นไปได้
    • วิธีนั้นเก็บดัชนีไว้หรือเปล่า? ในต้นฉบับบอกว่าเหตุผลที่ไฟล์ SQLite ใหญ่คือ ดัชนี

  • ใน DuckDB สามารถทำสิ่งเดียวกันแต่ส่งออกเป็น Parquet ได้ ซึ่งทำให้ขนาดข้อมูลเล็กกว่าคำสั่ง SQL แบบข้อความเป็นระดับหลักเดียว ส่งก็เร็วกว่า โหลดเข้าก็เร็วกว่า
    https://duckdb.org/docs/stable/sql/statements/export.html

    • ทำจาก command line ได้แบบนี้
      duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
      ในฐานข้อมูลทดสอบของผม ขนาดเล็กกว่าคำสั่ง SQL แบบข้อความที่ gzip แล้วประมาณ 20%

  • SQLite มี session extension ที่ติดตามการเปลี่ยนแปลงของชุดตาราง และสร้าง changeset/patchset ที่นำไปใช้กับฐานข้อมูล SQLite เวอร์ชันก่อนหน้าได้
    https://www.sqlite.org/sessionintro.html

    • ยังไม่เคยเห็น SQLite binding ไหนรองรับสิ่งนี้เลย ดังนั้นถ้าไม่ได้จะเขียนแอปเป็น C หรือแพตช์ language binding เอง ก็มีประโยชน์ลดลงค่อนข้างมาก
      ในโปรเจกต์หนึ่งของผม ผมทำ session แบบคนงบน้อยเอง โดยบันทึกคำสั่ง SQL และพารามิเตอร์ทั้งหมดลงฐานข้อมูลแยก จากนั้นซิงก์และ replay มัน วิธีนี้ทำงานได้ดีพอสำหรับฐานข้อมูลราว 30GB ที่เปลี่ยนแค่ประมาณ 0.1% ต่อวัน
    • เคยลองใช้ไหม? อ่านเอกสารแล้ว แต่ไม่เคยได้ยินว่ามีใครใช้ extension นี้จริง ๆ
    • ถ้า SQLite มีวิธีพื้นฐานสำหรับ change data capture ด้วย session หรือแนวทางคล้ายกันก็คงดีมาก

  • ถ้าซิงก์จากเวอร์ชันเก่าไปเวอร์ชันใหม่เป็นประจำ สามารถปรับให้เหมาะขึ้นได้ด้วย ตัวเลือก --rsyncable ของ gzip
    อัตราการบีบอัดจะลดลงประมาณ 1% แต่ความต่างจากเวอร์ชันหนึ่งไปยังเวอร์ชันถัดไปจะถูกจำกัดอยู่เฉพาะบริเวณ ไม่ลามต่อเนื่องไปทั่วเอาต์พุตที่บีบอัดทั้งหมด
    อีกวิธีคือไม่ต้องบีบอัดเอาต์พุต dump แล้วให้ rsync คำนวณส่วนต่างระหว่าง dump แบบไม่บีบอัดตัวเก่ากับตัวปัจจุบัน จากนั้นค่อยบีบอัดเฉพาะชุดการเปลี่ยนแปลงที่จะส่งผ่านเครือข่าย กล่าวคือใช้ rsync -z

  • น่าจะทำเป็น pipeline เดียวได้
    ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

    • ถ้าเปิด การบีบอัดของ SSH ด้วย -oCompression=on หรือ -C ในคำสั่ง SSH แล้ว gzip/gunzip ก็อาจซ้ำซ้อน
    • ผมก็คิดถึงอันนั้นเป็นอย่างแรกเหมือนกัน และดูค่อนข้างชัดด้วยว่าควรมี pv ใส่เข้าไปด้วย