[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: SQLite vacuum or auto_vacuum?

From: Stefan Fuhrmann <stefan.fuhrmann_at_wandisco.com>
Date: Mon, 3 Dec 2012 13:43:31 +0100

On Mon, Dec 3, 2012 at 1:08 PM, Philip Martin <philip.martin_at_wandisco.com>wrote:

> Prompted by a question on users I wondered how SQLite's vacuum
> (http://sqlite.org/lang_vacuum.html) would affect wc.db size. On a
> Subversion trunk working copy I have been using for months the size was
> reduced from 2.3MB to 1.3MB which isn't really a significant change.

So, even after months for (normal) usage,
the db's size is still only a tiny fraction of
the working copy - pristines in particular.

For a further test I checked-out a ^/subversion/branches working copy
> for a wc.db of 93MB with 121738 rows, I made it sparse with 66046 rows
> and it was still 93MB, then I ran vacuum and it was reduced to 51MB. I
> have a gcc working copy with some subtrees switched to an empty
> directory. There vacuum reduced wc.db from 47MB to 8.1MB.
> So it appears that vacuum is interesting if the number of rows decreases
> dramatically.

But we keep the pristines in thoses cases
as well, i.e. the working copy size is not
reduced as much until the next cleanup.

> SQLite has auto_vacuum but it comes with a warning that it may make
> fragmentation worse (http://sqlite.org/pragma.html#pragma_auto_vacuum)
> so it's not clear whether we should enable it.

Given the modest savings, potential overhead
during normal operation and the warning
above, I'm -1 on auto_vacuum ATM.

Perhaps we should add a
> "vacuum" to cleanup? A full vacuum rewrites all the tables so it's not
> a trivial operation but it is reasonably fast for the working copies on
> local disk that I tried.

Since we use cleanup to reduce the pristine
store as well, IIRC, this would be the right time
and place to run a vacuum on wc.db.

-- Stefan^2.

Certified & Supported Apache Subversion Downloads:
Received on 2012-12-03 13:44:06 CET

This is an archived mail posted to the Subversion Dev mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.