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 <[EMAIL PROTECTED]> wrote: > 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 > Mail: [EMAIL PROTECTED] > Web: http://www.fornazinconsultoria.com.br > > > -----Original Message----- > From: [EMAIL PROTECTED] > [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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
