コンピュータ間でSQLiteデータベースをより高速にコピーする方法
(alexwlchan.net)- 大きなSQLiteデータベースをリモートサーバーからローカルへ移すとき、元の
.dbの代わりに gzip 圧縮した SQL ダンプをコピーすると、転送量を大幅に減らせる - ファイルが大きくなる主な原因はインデックスであり、クエリを高速化するためにテーブルデータを重複保存してディスク使用量を増やす
- SQLite の
.dumpはデータベースを SQL 文のテキストに変換し、インデックスは実データではなくCREATE INDEX1行で表現されるため、重複転送を避けられる - 例のデータベースは元のサイズが3.4GB、ダンプテキストが1.3GB、gzip 圧縮版が240MBで、圧縮ダンプは元ファイルより14倍小さかった
- コピー前にダンプファイルを固定しておけば、転送中に元データが変わって
database disk image is malformedエラーになる状況を減らせる
SQLiteダンプを圧縮してコピーする
- 小さな SQLite データベースなら
rsyncで直接コピーしても十分- 例:
rsync --progress username@server:my_remote_database.db my_local_database.db
- 例:
- データベースが大きくなると、直接コピーは遅く不安定になることがある
- 250MB のデータベースを家庭用インターネット経由でウェブサーバーからダウンロードするのに約1分かかる
- 数 GB 規模のデータベースでは待ち時間がさらに大きくなる
- サイズ増加のボトルネックはインデックスにある
- インデックスはクエリ速度を大きく向上させるが、データベースファイルも肥大化させる
- あるデータベースでは、特定のインデックス1つだけでディスクサイズの半分を占めていた
- インデックスは固有のデータを持つというより、他のテーブルのデータを重複して保持して検索を高速化する
- SQLite はデータベース全体をテキストファイルとしてダンプできる
- コマンド:
sqlite3 my_database.db .dump > my_database.db.txt - 出力されるファイルは
CREATE TABLE、INSERT INTO、CREATE INDEXのような SQL 文で構成される - インデックスは実際のインデックスデータではなく、
CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]);のような作成コマンドとして保存される
- コマンド:
- ダンプファイルは SQLite データベースに復元できる
- コマンド:
cat my_database.db.txt | sqlite3 my_reconstructed_database.db
- コマンド:
- SQL ダンプは繰り返しが多いため、gzip 圧縮の効率が高い
- 例:
sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz - サイズ比較:
- 元の SQLite データベース: 3.4GB
- テキストダンプ: 1.3GB
- gzip 圧縮テキスト: 240MB
- gzip 圧縮版は元の SQLite データベースより14倍小さかった
- 例:
実際の手順と安定性への効果
- 新しい方法では、リモートサーバーで圧縮ダンプを作成し、そのファイルだけをダウンロードしてからローカルでデータベースを再構築する
- コピー手順は次のとおり
- サーバーで gzip 圧縮テキストファイルを作成:
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz" - ローカルへコピー:
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" - ローカルで展開:
gunzip my_local_database.db.txt.gz - ローカルデータベースを再構築:
cat my_local_database.db.txt | sqlite3 my_local_database.db - ローカルのテキストファイルを削除:
rm my_local_database.db.txt
- サーバーで gzip 圧縮テキストファイルを作成:
- 圧縮ダンプは一貫したコピー元として機能する
- 大きなデータベースを長時間かけてコピーしている間に元データが更新されると、結果のファイルは前半が更新前、後半が更新後の状態になることがある
- こうしたファイルはローカルで開くと
database disk image is malformedエラーを出す可能性がある
- コピー前にテキストダンプを作っておけば、
rsyncが転送するファイルは途中で変化しない- 完全で一貫したテキストファイルをコピーできる可能性が高まる
- 大きな SQLite データベースを頻繁に移動するなら、この方法でダウンロード時間を短縮し、コピーの信頼性を高められる
1件のコメント
Hacker Newsの意見
稼働中で更新を受けているデータベースファイルをそのままコピーすれば、当然破損する可能性がある
SQLiteデータベースを安全に複製するならLitestreamがある: https://github.com/benbjohnson/litestream
好みのサービスはあるが、どのSFTPサーバーでも可能
[1] https://github.com/benbjohnson/litestream/issues/140
[2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
もちろん複数ファイルなら、すべてが同じトランザクション内でコピーされるようサブボリュームにまとめる必要があり、単に
cp --reflink=alwaysするだけでは不十分SIGSTOPでプロセスを止めるのも似た結果は出せるかもしれないが、それに頼りたくはない.backupコマンド**も、稼働中のデータベースのスナップショット版を作ってコピーできるようにするための公式ツール記憶が正しければ彼は完璧なガイドを書いていたが、見つけられる可能性はほぼゼロ。このリファレンスを知っている人がリンクを貼ってくれるとありがたい
コンピューター間でデータベースをコピーする話なのに、円だけ描いて残りのフクロウを省略している感じ
他の人も言っているように増分
rsyncのほうがずっと速いだろうが、もっと気になるのは、SQL文を送るほうがデータベースを送るより速いと言いながら、そのSQL文を実行しなければならない事実を完全に抜かしている点。その後に/optimize/も回し、/vacuum/も回す必要がある今まさにCSVファイルからデータベースを「増分再構築」しなければならない状況があるが、自分の場合は最初から作り直すほうが最適。それでもかなり最適化したにもかかわらず、空のメモリデータベースにバッチ挿入してインデックスを作るだけで30分かかる
SQLite3で高速に挿入する方法を非常によくまとめた記事
250MBのデータベースをダウンロードするのに1分かかるとあったが、自分は1Gbpsの光回線のおかげで、会社のサーバーから2GBのSQLiteテストデータベースを15秒で取得できた
BloomフィルタやLRUキャッシュのようなテクニックを使う必要はあったが、今では6GBのデータベースを20分ほどで作れる
SQLiteにはこの用途の公式ツールがある: https://www.sqlite.org/rsync.html
ページレベルで動作する
「プロトコルは、レプリカが各ページの暗号学的ハッシュを元側に送り、元側はハッシュが一致しないページの全内容を送り返す、という方式である」
このツールを使うには自分でコンパイルするしかない
最近リリースされた
sqlite_rsyncユーティリティは、SQLiteデータベースの内部構造に合わせて最適化したrsyncアルゴリズムのバージョンを使う。内部データページを効率よく比較したうえで、変更されたページまたは存在しないページだけを同期する記事のコツも良いが、今では組み込みユーティリティを使うほうが簡単 :)
動作原理を詳しくブログに書いておいた: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works
sqlite3_rsyncは今ではrsync.netプラットフォームに組み込まれているssh user@rsync.net sqlite3_rsync … blah blah …先週追加されたばかりで、まだ全リージョンには展開されていないが、初期ユーザーは全員、期待どおりに動作したと言っている
sqlite_rsyncはWALモードでのみ使える。WALモードの追加制約として、データベースファイルはローカルディスク上になければならないたいていは当然そうするだろうが、それが不可能な場合はこのユーティリティを使えない
rsyncが提供する転送中の圧縮を試していなかったのは意外だった-z, --compressは転送中にファイルデータを圧縮し、--compress-level=NUMは圧縮レベルを明示的に指定するおそらく gzip で圧縮してから転送するほうが速い場合もあるだろうが、フラグ1つで転送を改善できるのは良い点
-zと一緒にsqlite3_rsync（https://sqlite.org/rsync.html）を使うほうがよいSQLite のトランザクションと WAL を認識する rsync に、転送中の圧縮まで付くようなもの
こういう作業では、ダンプをそのまま gzip にストリーミングする。たいてい中間ファイルなしで宛先へ直接ストリーミングする方法も見つかる
さらにこうすれば、宛先には圧縮された状態で保存される。貧者のレプリケーションではなくバックアップが目的なら、特にそうだ
rsyncを使うと、rsyncを使う核心である ローリングチェックサムに基づく差分転送 を完全に迂回してしまう--remove-source-filesを使えば、もう一度 SSH で入ってrmしなくてもよかったはずテキストファイルとして保存するのは非効率。自分は SQLite データベースを
VACUUM INTOで保存しているsqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"https://sqlite.org/lang_vacuum.html によると、
INTO節付きのVACUUMコマンドは、実行中のデータベースのバックアップコピーを作るバックアップ API の代替手段である。利点は、生成されるバックアップデータベースのサイズが最小化され、ファイルシステム I/O を減らせる可能性があることVACUUM INTO方式ではインデックスが維持されるテキストファイルはそのままでは非効率かもしれないが、gzip のような原始的なツールでもよく圧縮できる。SQLite のバイナリ形式も同じくらいよく圧縮できるかは確かではないが、可能性はある
DuckDB では同じことをしつつ Parquet にエクスポートでき、そうするとテキストベースの SQL 文よりデータサイズが一桁倍率で小さくなる。転送も速く、ロードも速い
https://duckdb.org/docs/stable/sql/statements/export.html
duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"自分のテスト用データベースでは、gzip 圧縮したテキスト SQL 文より約20%小さかった
SQLite には、テーブル集合の変更を追跡し、以前のバージョンの SQLite データベースに適用できる changeset/patchset を作るセッション拡張がある
https://www.sqlite.org/sessionintro.html
自分のプロジェクトの一つでは、すべての SQL 文とパラメータを別データベースに記録し、それを同期して再生する形で、貧者のセッションを自前実装した。毎日約0.1%だけ変わる 30GB 程度のデータベースには十分うまく動いている
古いバージョンから新しいバージョンへ定期的に同期するなら、gzip の
--rsyncableオプション でさらに最適化できる圧縮率は約1%低下するが、あるバージョンから次のバージョンへの差分が圧縮出力全体に連鎖せず、局所化される
もう一つの方法は、ダンプ出力を圧縮せず、
rsyncに以前の非圧縮ダンプと現在のダンプの差分を計算させてから、ネットワークで送る変更集合だけを圧縮させること。つまりrsync -zを使えばよいこれは一つの パイプライン として処理できそう
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db-oCompression=onや-Cにより SSH 圧縮 を有効にするなら、gzip/gunzipは重複かもしれないpvが入るべきなのも、かなり明白に思える