Re: [sqlite] How to determine when to VACUUM?

>> > > > > Simon, you gave an interesting explanation, but does this rule work in > > general? I mean there are many models, many sizes and so on. > > Don't know. You could test it. Write a program that creates a file half > the size of the drive, then writes to random parts of it timing each >

Re: [sqlite] How to determine when to VACUUM?

On 8 Jul 2010, at 9:30am, Max Vlasov wrote: >> Actually the SSD possibility makes it worse, not better. >> > > Simon, you gave an interesting explanation, but does this rule work in > general? I mean there are many models, many sizes and so on. Don't know. You could test it. Write a program

Re: [sqlite] How to determine when to VACUUM?

> >> (I guess it well might not on an SSD disk, but on a conventional > >> rotational disk, pager could read several pages ahead with one seek - > >> but does it?) > > > > No, the pager does not. Among other things, my feeling is that the > > locality of pages is not very strong, unless the

Re: [sqlite] How to determine when to VACUUM?

On 7 Jul 2010, at 4:14pm, Jay A. Kreibich wrote: >> (I guess it well might not on an SSD disk, but on a conventional >> rotational disk, pager could read several pages ahead with one seek - >> but does it?) > > No, the pager does not. Among other things, my feeling is that the > locality of

Re: [sqlite] How to determine when to VACUUM?

On Wed, Jul 07, 2010 at 01:03:26PM +0400, Igor Sereda scratched on the wall: > > It's never time to VACUUM a database. > > This is an interesting statement. In our application, all tables get > heavily fragmented with time (99% or more). I was considering VACUUM > as a means to defragment, and,

Re: [sqlite] How to determine when to VACUUM?

> (I guess it well might not on an SSD disk, but on a conventional > rotational disk, pager could read several pages ahead with one seek - > but does it?) You mean that pager should read several pages at once even if it doesn't need them right now but it estimates that it will need them in near

Re: [sqlite] How to determine when to VACUUM?

On 7 Jul 2010, at 10:03am, Igor Sereda wrote: >> It's never time to VACUUM a database. > > This is an interesting statement. In our application, all tables get > heavily fragmented with time (99% or more). I was considering VACUUM > as a means to defragment, and, presumably, improve search

Re: [sqlite] How to determine when to VACUUM?

My two cents, to complement other answers: leave it to the user. In case of a client-side GUI app, let the user run some maintenance action, like an OS has "defragment disk" action, or Outlook has "compact folders" action. In case of a server-side app, make a script or admin command to do that.

Re: [sqlite] How to determine when to VACUUM?

> It's never time to VACUUM a database. This is an interesting statement. In our application, all tables get heavily fragmented with time (99% or more). I was considering VACUUM as a means to defragment, and, presumably, improve search performance. Was I misguided, and search performance does not

Re: [sqlite] How to determine when to VACUUM?

On Tue, Jul 06, 2010 at 11:45:18PM +0200, Kristoffer Danielsson scratched on the wall: > > I've been reading the documentation. I've been googling and thinking. > > Still, I can't figure out the best way to determine when to run > the VACUUM-command. Note that I do NOT want to enable "auto

Re: [sqlite] How to determine when to VACUUM?

On 7/6/2010 2:58 PM, Kristoffer Danielsson wrote: > What you are saying makes sense. Thanks for your advice! > > > > However, I do believe there are times when vacuuming would be beneficial. For > instance, if a database for software X is detected to have 90% unused space > for a couple of

Re: [sqlite] How to determine when to VACUUM?

On Tue, Jul 6, 2010 at 5:58 PM, Kristoffer Danielsson wrote: > > However, I do believe there are times when vacuuming would be beneficial. For > instance, if a database for software X is detected to have 90% unused space > for a couple of weeks, then why bloat the

Re: [sqlite] How to determine when to VACUUM?

ite-users@sqlite.org > Subject: Re: [sqlite] How to determine when to VACUUM? > > > On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote: > > > Q: How do I programmatically (through sqlite-APIs?) determine if it's time > > to VACUUM a database? > > It's never tim

Re: [sqlite] How to determine when to VACUUM?

On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote: > Q: How do I programmatically (through sqlite-APIs?) determine if it's time to > VACUUM a database? It's never time to VACUUM a database. The VACUUM command is useful only if you have want to recover unused space from the database

[sqlite] How to determine when to VACUUM?

I've been reading the documentation. I've been googling and thinking. Still, I can't figure out the best way to determine when to run the VACUUM-command. Note that I do NOT want to enable "auto vacuum". I do remember reading something about calculating empty space, used pages etc etc.