SQLite in general and the .Net provider in particular are most often
shipped as components of other applications.  I dont think having
developers tell their end users to disable superfetch is a viable
solution.  As much as I hate to propose this maybe a runtime check is
in order to see what the OS version is and not use the flag where it's
known to be problematic.

On Wed, Sep 17, 2008 at 12:14 PM, Virgilio Alexandre Fornazin
> Could not this bug be related with Vista feature called 'Superfetch' ?
> It tries to keep in memory the most accessed files for user, avoiding
> disk for read access.
> If you disable (or stop) this service, the problem remains or not ?
> Virgilio Alexandre Fornazin
> High performance and realtime systems development
> Rua Brigadeiro Vicente Faria Lima, 268
> Bela Vista    Leme-SP    CEP 13611-485
> Phone: +55 19 3571-5573
> Cell: +55 19 8111-4053
> +55 11 8357 1491
> Web: http://www.fornazinconsultoria.com.br
> -----Original Message-----
> [mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich
> Sent: quarta-feira, 17 de setembro de 2008 13:01
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
> On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall:
>> Robert Simpson wrote:
>> > To me this seems like an obvious bug in Vista,
>> Actually I'd argue that it is behaving as designed.
>  You could argue it is behaving as designed, but I'd still argue it is
>  behaving poorly.
>  Further, if a system component who's sole purpose is to increase
>  performance-- as all cache systems are-- has the overall effect of
>  decreasing performance, not only of the process it is trying to speed
>  up, but of the whole system, it is pretty easy to argue that's a serious
>  functional bug.
>  Given the speed of most storage systems, filesystem cache management
>  is an important component of overall system performance.  However, if
>  the cache system is grabbing so much physical memory (and, apparently,
>  refusing to let go of it) that processes are forced to aggressively
>  page and the net result is a massive performance loss, then something
>  isn't right.
>  As with so many things, cache management (and, indeed, the whole
>  concept of caches) tends to be a huge web of compromises.  It is
>  extremely difficult, if not impossible, to cover all cases.  But
>  these things are not exactly new, and it should be easy enough to
>  never get in a situation where things are actually made worse--
>  especially that they're not made worse for the whole system.
>> Generally
>> filesystem code will try to detect what is going on under the hood.  In
>> particular if it looks like you are doing sequential access(*) then they
>> will start doing read ahead, whereas read ahead is a waste for random
>> access.
>  Not to get into a whole argument about cache strategies, but this
>  often not true.  If we assume free memory isn't a big concern,
>  when a process opens a file for random-access we can either
>  read-ahead the whole thing or we can read blocks here and there until
>  (if the process touches the majority of the file) we have the whole
>  thing in memory.  Both systems, in the end, will result in the same
>  memory usage.
>  However, if I'm going to be doing random access on a file of moderate
>  or smaller size, it is much cheaper for the OS to just suck the whole
>  thing into memory via one bulk read operation than it is to grab it
>  piecemeal.
>  The whole trick is defining "moderate" both in terms of first-return
>  read times (time to return the block the process actually asked for,
>  which might not be the first block pulled off disk) vs how likely the
>  process is to touch the majority of file blocks (something that is
>  somewhat less likely as the file gets bigger).
>  As the file gets larger, there is also the real-world issue of how
>  much RAM the system has, and how much of it is actually in-use with
>  process and OS pages.  This is true of both sequential AND random
>  access, although memory usage is generally easier to control in
>  sequential patterns.
>  This is where Vista appears to be breaking down and making very poor
>  decisions.  It seems to be giving cache pages more priority than
>  process and OS system pages, and generally that should never happen.
>  If we're correctly understanding what is going on, Vista might very
>  well be paging out SQLite's internal page cache to fit a few extra file
>  blocks in RAM.  How much sense does that make?
>> By using the sequential or random flags you are explicitly
>> telling the filesystem to ignore its heuristics and do as you say only.
>  Even if that's true (most APIs present the flags as "hints" not
>  absolute truths), the worst an incorrect flag should do is hurt the
>  file access performance of the process that provided the hint.  Even
>  then, the lower end should be the same performance one would expect
>  if there was no cache (e.g. constant misses).
>  A poor or incorrect flag is no excuse to be overly aggressive with
>  holding pages in RAM and killing the whole system.  Even if a flag
>  alters the read-ahead policy or cache replacement strategy, a flag
>  should never override the decisions the cache system has to face when
>  the system starts to run thin on free physical RAM-- especially on a
>  file that is larger than than the RAM footprint of most machines.
>  And in this case, I'm not even sure the flag is incorrect....
>> Since SQLite cannot tell in advance whether access is almost entirely
>> random or almost entirely sequential, it makes far more sense to let the
>> operating system use its builtin heuristics and optimise accordingly.
>  Since a full table scan is unlikely to result in a sequential file
>  read for anything but the most pristine database (e.g. one that is
>  bulk loaded and never touched), I'm not sure that's true.  I would
>  guess the file access patterns are almost always random, making the
>  hint correct.
>  On the other hand, given that SQLite does it's own cache management
>  and tries to make few assumptions about the underlying system, I can
>  also see leaving the OS to do what it wants.  The hint may be
>  correct, but it might always be appropriate.
>   -j
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
sqlite-users mailing list

Reply via email to