Re: [sqlite] Feature request: copying vacuum

2010-09-10 Thread Jim Wilcoxson
I'd also be interested in a VACUUM TO feature, more for performance aspect than the fragmentation, although that's a plus too. The backup program I'm working on packs many files into archives, which are SQLite databases. I have run some vacuum tests here; the filesystem cache was purged before

Re: [sqlite] Feature request: copying vacuum

2010-09-09 Thread Taras Glek
On 09/01/2010 11:41 AM, Taras Glek wrote: > Hi, > Currently VACUUM takes care of sqlite-level fragmentation. > Unfortunately it does little for fs-level fragmentation since the same > file is being reused. It would be really beneficial for Mozilla > performance if we could get a

Re: [sqlite] Feature request: copying vacuum

2010-09-02 Thread Jay A. Kreibich
On Thu, Sep 02, 2010 at 05:42:17AM +0200, Ben Danper scratched on the wall: > > On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich wrote: > > There is no reason to assume the filesystem > > will over-write the existing allocations, rather than just create new > > ones, especially if the

Re: [sqlite] Feature request: copying vacuum

2010-09-02 Thread Shawn Wilsher
On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich wrote: >  On a personal level, I don't think it is worth it.  In the end, you're >  still hoping the OS and filesystem will make smart choices about block >  allocations.  An application shouldn't need to be spending a lot >  of

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Scott Hess
On Wed, Sep 1, 2010 at 10:09 PM, Max Vlasov wrote: >> I agree with Jay - while it is tempting to have SQLite bite off >> optimizing this kind of thing, it's pretty far out of scope.  Next >> we'll be talking about running SQLite on raw partitions! >> > > Scott, thought about

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Max Vlasov
> I agree with Jay - while it is tempting to have SQLite bite off > optimizing this kind of thing, it's pretty far out of scope. Next > we'll be talking about running SQLite on raw partitions! > Scott, thought about it, ironically sqlite vfs is flexible enough to implement raw partition sqlite

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Simon Slavin
On 2 Sep 2010, at 4:42am, Ben Danper wrote: > On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich wrote: >> Maybe there would be some way to pre-populate the rollback journal >> with the full contents of the original database. Then the file could >> be truncated before the copy-back

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Ben Danper
On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich wrote: > There is no reason to assume the filesystem > will over-write the existing allocations, rather than just create new > ones, especially if the pages are shuffled in groups... Actually there's no reason to do the opposite, as it

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Scott Hess
On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich wrote: > On Wed, Sep 01, 2010 at 11:41:00AM -0700, Taras Glek scratched on the wall: >> Currently VACUUM takes care of sqlite-level fragmentation. Unfortunately >> it does little for fs-level fragmentation since the same file is

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Jay A. Kreibich
On Wed, Sep 01, 2010 at 11:41:00AM -0700, Taras Glek scratched on the wall: > Hi, > Currently VACUUM takes care of sqlite-level fragmentation. Unfortunately > it does little for fs-level fragmentation since the same file is being > reused. It would be really beneficial for Mozilla performance

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Richard Hipp
On Wed, Sep 1, 2010 at 2:41 PM, Taras Glek wrote: > Hi, > Currently VACUUM takes care of sqlite-level fragmentation. Unfortunately > it does little for fs-level fragmentation since the same file is being > reused. It would be really beneficial for Mozilla performance if we >

Re: [sqlite] Feature request: copying vacuum

2010-09-01 Thread Max Vlasov
> This yields two benefits: > >A less fragmented db > >~50% vacuum speedup since the data is only copied once > > Currently we can copy the data to a new file, but it is a pretty > invasive change to swap all of the current sqlite connections to the new > file. Things like prepared

[sqlite] Feature request: copying vacuum

2010-09-01 Thread Taras Glek
Hi, Currently VACUUM takes care of sqlite-level fragmentation. Unfortunately it does little for fs-level fragmentation since the same file is being reused. It would be really beneficial for Mozilla performance if we could get a vacuum/hotcopy mode. As I understand it, currently vacuum works