On Sat, Oct 27, 2012 at 6:38 AM, David Barrett <dbarr...@expensify.com>wrote:
> I'm trying to learn more about MySQL versus sqllite when it comes to > vacuuming and fragmentation, especially as it relates to SSD storage. Can > anybody summarize for me the differences? > > 1) My understanding is sqlite, in general, has no automatic > defragmentation: there is no process to gradually and incrementally > reposition data on disk in index order. (Even auto-VACUUM just reclaims > deleted data by inserting new transactions in the holes left behind by > previous deletes, which the documentation says can actually worsen > fragmentation.) The only defragmentation option is a full VACUUM, which > rebuilds the entire database from scratch in index order. During this > period, the whole database is locked and cannot be used. Is this roughly > right? > Roughly. SQLite does make some effort to keep things in index order as changes are made. If SQLite needs a new database page because of new data being inserted, it looks for a free page in approximately the right spot. But SQLite never moves pages around in order to put them all in the right order (apart from VACUUM) since that involves extra I/O and slows down the INSERT. In practice, though, fragmentation can still occur. Depending on your workload. In WAL mode, a VACUUM can be ongoing while there are other readers. This will lead to a *-wal file that is as big or bigger than the original database, however. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
