Re: [sqlite] When to call VACUUM - revisited

[Jay A. Kreibich]

On Thu, Jul 05, 2012 at 01:29:18PM +0100, Simon Slavin scratched on the wall:
> 
> On 5 Jul 2012, at 9:34am, _ph_  wrote:
> 
> > I already read your previous replies, but to revisit my scenaro:
> > 
> > - My OS is "sensitive to fragmentation"
> > - We are running with auto-vacuum enabled, so the freelist_count is usually
> > small (not a good indicator) 
> 
> Ah.  If you're always running auto-vacuum, then I don't think
> explicitly issuing VACUUM is going to be useful at all.  Don't bother.

  Yes, bother.  Auto-vacuum only deals with free pages.  It does not do
  all the other things a normal vacuum does.  Even if you run
  auto-vacuum, it is still a good idea to vacuum a very dynamic
  database from time to time.  Auto vacuum tends to *increase* the amount
  of fragmentation within the file, since it moves pages around to keep
  the free list short.  This means that pages for a given object (table,
  index, etc.) may be spread out across the SQLite file, which can cause 
  extra seeks during table/index scans.  OS level (filesystem)
  defragmentation won't help with this.

  http://www.sqlite.org/pragma.html#pragma_auto_vacuum
 
 Note, however, that auto-vacuum only truncates the freelist pages
 from the file. Auto-vacuum does not defragment the database nor
 repack individual database pages the way that the VACUUM command
 does. In fact, because it moves pages around within the file,
 auto-vacuum can actually make fragmentation worse.

  On the other hand, if the internal structure of the SQLite file is
  badly fragmented, having the file be fragmented in the filesystem
  isn't such a big deal.  You only take the hit once.

> >   but fragmentation supposedly gets worse
> 
> Fragmentation of the database file on disk is something that SQLite
> can't control, so you are down to the various defragmentation
> facilities (including the one built into Windows) to solve that.

  You can also get fragmentation inside the SQLite file, in the way
  that the pages are used.  VACUUM is the only way to fix this, since
  the defrag process has to do with moving SQLite pages around inside
  the SQLite file.

> > -We use sqlite as application data format, a typical user has dozens
> > of files. 
> >   This makes "During a support call" is not an option

  Yes and no.  If there is some hidden menu feature to force a VACUUM,
  that might come in handy if you have a customer with a particularly 
  large (or slow) file.  It is easy to put in "just in case", and 
  doesn't change the customer experience if it isn't in their face.

  If you're using databases as application files, I'm assuming they're
  not all that huge, however.  If the files are moderately small (a few
  dozen megs or less) you might just vacuum the file every time you
  open it (if file updates tend to be very dynamic) or every 20th time
  or something.  A file that's only a few megs only takes a few seconds
  to VACUUM.  You can put up a dialog that says "Optimizing file
  structure...".

  On the other hand, a file that's only a few megs is not likely to
  see much of a performance boost from a VACUUM.  I'd be more concerned
  about filesystem fragmentation than I would be about SQLite
  fragmentation.

> You could use the shell tool to turn the database file into SQL commands,
> and then back into a new database file on disk.  This will both 
> defragment the file, and make sure it's not using unneeded space.

  For all intents and purposes, this is what VACUUM does.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When to call VACUUM - revisited

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/07/12 05:29, Simon Slavin wrote:
> Fragmentation of the database file on disk is something that SQLite
> can't control,

However you can ask SQLite to do things that will mitigate fragmentation
by extending the file in larger blocks.  See the file controls, especially
 SQLITE_FCNTL_CHUNK_SIZE:

  http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk/1oosACgkQmOOfHg372QQSRACgglDh1oe9cL7Wm7ShbUYjDWdZ
ONMAoJApn6aFivn9CtZiD66QIDJUn4md
=t518
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When to call VACUUM - revisited

[Simon Slavin]

On 5 Jul 2012, at 9:34am, _ph_  wrote:

> I already read your previous replies, but to revisit my scenaro:
> 
> - My OS is "sensitive to fragmentation"
> - We are running with auto-vacuum enabled, so the freelist_count is usually
> small (not a good indicator) 

Ah.  If you're always running auto-vacuum, then I don't think explicitly 
issuing VACUUM is going to be useful at all.  Don't bother.

>   but fragmentation supposedly gets worse

Fragmentation of the database file on disk is something that SQLite can't 
control, so you are down to the various defragmentation facilities (including 
the one built into Windows) to solve that.

> -We use sqlite as application data format, a typical user has dozens of
> files. 
>   This makes "During a support call" is not an option

Okay.

> So for me from reading documentation and this list, there's an "omnious
> cloud of defragmentation looming" - wthout much data how bad it is or can
> be. 

You could use the shell tool to turn the database file into SQL commands, and 
then back into a new database file on disk.  This will both defragment the 
file, and make sure it's not using unneeded space.  Of course, it's a very slow 
process, and most people will use it only when they think their database file 
is corrupt.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When to call VACUUM - revisited

[_ph_]

Hi Simon,

I already read your previous replies, but to revisit my scenaro:

 - My OS is "sensitive to fragmentation"
 - We are running with auto-vacuum enabled, so the freelist_count is usually
small (not a good indicator) 
   but fragmentation supposedly gets worse
 -We use sqlite as application data format, a typical user has dozens of
files. 
   This makes "During a support call" is not an option

So for me from reading documentation and this list, there's an "omnious
cloud of defragmentation looming" - wthout much data how bad it is or can
be. 

--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/When-to-call-VACUUM-revisited-tp63114p63128.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When to call VACUUM - revisited

[Simon Slavin]

On 4 Jul 2012, at 10:54am, Hauptmann Peter  wrote:

> However, they also have rough indicators, whether or not it's necessary 
> (fragmentation or
> a recommendation when closing Outlook)
> 
> Q: Are there any useful indicators to suggest a VACUUM?
>(Or, to reverse it: indicators that show VACUUM is not necessary?)

I forgot the more useful information:



"PRAGMA freelist_count;

Return the number of unused pages in the database file."

The bigger this is, the more point there is in doing a VACUUM.  Unless the next 
thing you're going to do is add more data to the database in which case the 
VACUUM is a waste of time.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When to call VACUUM - revisited

[Simon Slavin]

On 4 Jul 2012, at 10:54 AM, Hauptmann Peter  wrote:

> However, VACUUM is by far requiring the most time, and with most uncertain 
> results 
> (i.e. whether or not it improves subsequent performance).

VACUUM exists only to release unused space from the database file. If you have 
been entering new data and not deleted old data it will do nothing usefull 
unless your OS is sensitive to fragmentation (i.e. Windows). Provide your users 
with a way to do it so you can, during a support call, decide it might help. 
Don't bother doing it on a regular basis.

Simon.
-- 
Sent from my iPhone
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When to call VACUUM - revisited

[Hauptmann Peter]

Hi List,

I've read what I could find about the issue, this thread:
http://thread.gmane.org/gmane.comp.db.sqlite.general/57770/focus=57775 

pretty much gathers the positions, ranging from

 - don't
 - don't worry
 - don't expect to much
 - let the user decide

The last is closest to my approach - even though the evidence for "VACUUM 
Miracles" is sketchy.
However, I have a problem here:

Q: If I can't decide when to VACUUM, how should my user?


The application: We already have a "maintenance" operation where VACUUM would 
be a good fit. 

However, VACUUM is by far requiring the most time, and with most uncertain 
results 
(i.e. whether or not it improves subsequent performance).

We use SQLite as applicaiton data format, the user is encouraged to work with 
the files for
sharing data etc. 

We run with auto-vaccum ON, since we frequently add and remove large-ish 
blocks, 
and reclaiming free space seems to be important for users.


As one reply here states, 'OS has defrag disk, Outlook has compact folders'. 


However, they also have rough indicators, whether or not it's necessary 
(fragmentation or
a recommendation when closing Outlook)

Q: Are there any useful indicators to suggest a VACUUM?
   (Or, to reverse it: indicators that show VACUUM is not necessary?)

Since we plan to run with auto-vacuum, free size isn't an indicator. 
I've also considered brewing something with last-vacuum-date and a change 
counter.



Any ideas?
ph
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users