Database compaction guidance

[Len Kawell]

The documentation on CBLDatabase -compact is fairly minimal ("Compacts the Database file by purging non-current Revisions and deleting unused Attachments") so I was wondering Jens or other members of the community might have some additional guidance on its use?

Some questions that I've encountered using compact in our iOS 7.1 app are:

(1) A previous post in this forum suggested that compact runs on the replicator's thread, but from what I can see it seems to run synchronously on the thread it's been invoked from (e.g., main thread). Is that the case?

(2) Does compact "lock" the database from other access and threads?

(3) Is there any benefit or problem with invoking compact on another thread using either CBLManager -backgroundTellDatabaseNamed:to: or by using a CBLManager copy to make another CBLDatabase instance in another thread?

(4) Is compact "safe" to run automatically without the user's involvement? For example, if the user powers off their device while compact is in-progress, should the database be OK? Or should I display a warning message to try to avoid that?

(5) Are there any replication issues to be aware of with compact? For example, does it play nice with the replicator task itself? Will pending, non-replicated updates get replicated OK after the compact if it's done while offline?

(6) Are there any built-in database statistics or metadata that I could use to trigger compact? Or should my app maintain its own document and attachment update and deletion counts somewhere? Or maybe just do a compaction periodically?

(7) What sort of performance should we expect from compact? Is there any way to give the user a progress UI or possibly an estimate of the time it will take? For example, I found running it the first time on a 7.5GB test database it took about 2 minutes on an iPad Air: 

2014-07-08 11:17:47.278 App[6015:60b] Starting database compact...

11:19:40.907| CBLDatabase: Deleting JSON of old revisions...

11:19:41.728| Deleting old attachments...

11:19:42.131| Deleted 75 attachments

11:19:42.131| Flushing SQLite WAL...

11:19:42.414| Vacuuming SQLite database...

11:19:49.736| Closing and re-opening database...

11:19:49.740| ...Finished database compaction.

2014-07-08 11:19:49.739 App[6015:60b] Database compact done - error = (null)

On a different 10.6GB database it took about 40 seconds on an iPad 4th gen:

2014-07-08 15:14:39.370 App[225:60b] Starting database compact...

15:15:12.776| CBLDatabase: Deleting JSON of old revisions...

15:15:14.791| Deleting old attachments...

15:15:15.334| Deleted 118 attachments

15:15:15.335| Flushing SQLite WAL...

15:15:15.426| Vacuuming SQLite database...

15:15:21.264| Closing and re-opening database...

15:15:21.266| ...Finished database compaction.

2014-07-08 15:15:21.265 App[225:60b] Database compact done - error = (null)

Additional compactions shortly after the previous ones took only about 5-6 seconds:

2014-07-08 17:02:45.534 App[234:60b] Starting database compact...

17:02:45.710| CBLDatabase: Deleting JSON of old revisions...

17:02:46.947| Deleting old attachments...

17:02:47.033| Deleted 0 attachments

17:02:47.033| Flushing SQLite WAL...

17:02:47.033| Vacuuming SQLite database...

17:02:51.651| Closing and re-opening database...

17:02:51.654| ...Finished database compaction.

2014-07-08 17:02:51.654 App[234:60b] Database compact done - error = (null)

Thanks very much.

Len

[Jens Alfke]

On Jul 8, 2014, at 5:31 PM, Len Kawell <len.k...@gmail.com> wrote:

> (1) A previous post in this forum suggested that compact runs on the replicator's thread, but from what I can see it seems to run synchronously on the thread it's been invoked from (e.g., main thread). Is that the case?

Yes, it runs on the thread you invoke it on.

> (2) Does compact "lock" the database from other access and threads?

Yes.

> (3) Is there any benefit or problem with invoking compact on another thread using either CBLManager -backgroundTellDatabaseNamed:to: or by using a CBLManager copy to make another CBLDatabase instance in another thread?

Well, it will let your main thread do other stuff during the compaction. But if it makes any CBL calls it’ll block till the compaction finishes, because the database is locked.

> (4) Is compact "safe" to run automatically without the user's involvement? For example, if the user powers off their device while compact is in-progress, should the database be OK? Or should I display a warning message to try to avoid that?

Well, you don’t want the app to get terminated by the OS in the middle of a compact. The database shouldn’t be corrupted by this (it’s supposed to be impossible to corrupt a SQLite database these days) but I don’t know what state it’ll be in afterwards.

> (5) Are there any replication issues to be aware of with compact? For example, does it play nice with the replicator task itself? Will pending, non-replicated updates get replicated OK after the compact if it's done while offline?

The only effect it’ll have on replication is that it will prune some very old revisions. That won’t affect anything that hasn’t been replicated already.

> (6) Are there any built-in database statistics or metadata that I could use to trigger compact? Or should my app maintain its own document and attachment update and deletion counts somewhere? Or maybe just do a compaction periodically?

I would just do it periodically.

> (7) What sort of performance should we expect from compact? Is there any way to give the user a progress UI or possibly an estimate of the time it will take?

Not really; most of it is just a small number of SQLite commands that each can take a long time to finish.

> For example, I found running it the first time on a 7.5GB test database it took about 2 minutes on an iPad Air:

Yow, that’s really big. Compaction is definitely going to take a long time on a database that size — it should be run more regularly.

> 2014-07-08 11:17:47.278 App[6015:60b] Starting database compact...
> 11:19:40.907| CBLDatabase: Deleting JSON of old revisions...
> 11:19:41.728| Deleting old attachments...
> 11:19:42.131| Deleted 75 attachments
> 11:19:42.131| Flushing SQLite WAL...
> 11:19:42.414| Vacuuming SQLite database...
> 11:19:49.736| Closing and re-opening database...
> 11:19:49.740| ...Finished database compaction.
> 2014-07-08 11:19:49.739 App[6015:60b] Database compact done - error = (null)

It looks like pruning old revisions is what’s taking most of the time (almost two minutes.) I don’t know if that query could be made more efficient. Mostly I’d recommend running the compaction more often so there’s less to clean up.

(For what it’s worth, ForestDB, which we’re going to switch to from SQLite in the future, runs compaction automatically in the background and it doesn’t block access to the database. It’s much more like CouchDB compaction.)

—Jens

[Len Kawell]

Thanks Jens!

[Frederic Yesid Peña Sánchez]

Hi Jens

ForestDB Compaction is automatic? so there is no method to call, just run and ForestDB will compact the database?

[Jens Alfke]

On Apr 14, 2015, at 4:23 PM, Frederic Yesid Peña Sánchez <freder...@gmail.com> wrote:

ForestDB Compaction is automatic? so there is no method to call, just run and ForestDB will compact the database?

Database file compaction is automatic, yes. But there are some other compaction tasks that are run by the -compact method, such as deleting unused attachment files, so you should still call it periodically.

—Jens