SQLite vacuum or auto_vacuum?

[Philip Martin <ph...@wandisco.com>]

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.

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.

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.  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.

-- 
Certified & Supported Apache Subversion Downloads:
http://www.wandisco.com/subversion/download

Re: SQLite vacuum or auto_vacuum?

[Stefan Fuhrmann <st...@wandisco.com>]

On Mon, Dec 3, 2012 at 1:08 PM, Philip Martin <ph...@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:
*

http://www.wandisco.com/subversion/download
*

Re: SQLite vacuum or auto_vacuum?

["C. Michael Pilato" <cm...@collab.net>]

On 12/03/2012 07:39 AM, Hyrum K Wright wrote:
> I think adding "vacuum" to cleanup is a reasonable first step.  "cleanup" is
> an explicit operation that a user could reasonably expect to take some
> non-trivial amount of time.  We already remove unneeded pristines during
> cleanup, might as well do the same thing with wc.db space.

Yup, agreed.  +1.

-- 
C. Michael Pilato <cm...@collab.net>
CollabNet   <>   www.collab.net   <>   Enterprise Cloud Development

Re: SQLite vacuum or auto_vacuum?

[Ivan Zhakov <iv...@visualsvn.com>]

On Mon, Dec 3, 2012 at 4:39 PM, Hyrum K Wright <hy...@hyrumwright.org> wrote:
>
> On Mon, Dec 3, 2012 at 7:08 AM, Philip Martin <ph...@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.
>>
>> 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.
>>
>> 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.  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.
>
>
> I think adding "vacuum" to cleanup is a reasonable first step.  "cleanup" is
> an explicit operation that a user could reasonably expect to take some
> non-trivial amount of time.  We already remove unneeded pristines during
> cleanup, might as well do the same thing with wc.db space.
>
+1.

-- 
Ivan Zhakov

Re: SQLite vacuum or auto_vacuum?

[Hyrum K Wright <hy...@hyrumwright.org>]

On Mon, Dec 3, 2012 at 7:08 AM, Philip Martin <ph...@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.
>
> 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.
>
> 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.  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.
>

I think adding "vacuum" to cleanup is a reasonable first step.  "cleanup"
is an explicit operation that a user could reasonably expect to take some
non-trivial amount of time.  We already remove unneeded pristines during
cleanup, might as well do the same thing with wc.db space.

-Hyrum