SQLiteのVACUUMメモ

Posted bysiguniangPosted indatabaseTags:, ,

Cross-platform C library that implements a self-contained, embeddable, zero-configuration SQL database engine.
高頻度でインサートし、一定期間が過ぎたものはデータが不要になるシステムの場合、定期的に不要データを掃除しないとぶくぶくデータサイズが膨れ上がる。組込型データベースの SQLite ではデータを SQL で DELETE するだけでは物理削除されず、PostgreSQL のように VACUUM する必要がある。
VACUUM のバリエーションはいろいろあるものの、結論としては $ sqlite filename.db vacuum とやるのが一番無難。
これまでは個人用途のしょうもないプログラムのためにしか使って来なかったため、スルーしていた SQLite の VACUUM 機能をメモ。

VACUUM の種類

SQLite の VACUUM には大きく分けて vacuum と auto_vacuum の2つがある。

vacuum
http://sqlite.org/lang_vacuum.html

まずは vacuum から。vacuum はデータベースファイル全体に対して

  • データベースページのパックや空き領域の開放
  • デフラグメンテーション

などを行う

注意点

  • データベースのリビルド前に既存のデータベースを一時ファイルにコピーするので、既存のDB以上のディスク空き容量が必要
  • vacuum 後は rowid が変わることがある (select rowid from table_name で確認可能)

使い方

sqlite> vacuum;

auto_vacuum
http://sqlite.org/pragma.html#pragma_auto_vacuum

次に 3.1 以降(2005年リリース)から追加された auto_vacuum。

以下の3種類がある

  • 0 : NONE(デフォルト。無効)
  • 1 : FULL(トランザクションがコミットされるたびに、空きページをファイル末尾に移動して削除。デフラグやページの再構成は行わない)
  • 2 : INCREMENTAL(VACUUMは自動では行わない。指定した数だけ空きページをファイル末尾に移動して削除。)

注意点

because it(=auto_vacuum) moves pages around within the file, auto-vacuum can actually make fragmentation worse.
http://sqlite.org/pragma.html#pragma_auto_vacuum

using auto_vacuum can lead to extra database file fragmentation. And auto_vacuum does not compact partially filled pages of the database as VACUUM does.
http://sqlite.org/lang_vacuum.html

使い方

auto_vacuum の種類は pragma で宣言する。

非常に重要な点として、テーブル作成後は auto_vacuum をデフォルトの 0(None) から変更できない。テーブルが auto_vacuum = NONE の状態で作成されると auto_vacuum は使えず、VACUUM で頑張るしか無い。

auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.
http://www.sqlite.org/pragma.html#pragma_auto_vacuum

auto_vacuum = full

$ sqlite database.db
sqlite> pragma auto_vacuum = full;
sqlite> pragma auto_vacuum;
1

auto_vacuum = incremental

空きページは pragma incremental_vacuum(n) で削除する。

$ sqlite database.db
sqlite> pragma auto_vacuum=incremental;
sqlite> pragma auto_vacuum;
2
...
sqlite> pragma incremental_vacuum(100);

空きページの調べ方

SQLite では使われていないデータベースページは freelist で管理されている。

A database file might contain one or more pages that are not in active use. Unused pages can come about, for example, when information is deleted from the database. Unused pages are stored on the freelist and are reused when additional pages are required.
http://sqlite.org/fileformat2.html#freelist

データベースのページ数、空きページ数は PRAGMA コマンド の page_count, freelist_count で取得できる

sqlite> PRAGMA freelist_count;
639
sqlite> PRAGMA page_count;
1091
sqlite> pragma incremental_vacuum(100);
sqlite> PRAGMA freelist_count;
539
sqlite> PRAGMA page_count;
990

See Also

Leave a comment