Designing operating systems is hard work, and I don't envy the teams that do
it.  I've found my Vista experience to be rather enjoyable, especially post
SP1.  I don't think there's an OS out today that isn't annoying in one
fashion or another.  Having an application or library implement a workaround
for an OS design flaw is not unique to Windows by any stretch.

-----Original Message-----
[mailto:[EMAIL PROTECTED] On Behalf Of Fred Williams
Sent: Thursday, September 18, 2008 8:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

Is a sad day when an application program is forced to compensate for pitiful
OS design and performance :-(

-----Original Message-----
[mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
Sent: Thursday, September 18, 2008 10:31 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations

After watching Molly Brown's Channel9 videos on the cache manager, I'm
convinced the behavior for SQLite should be to not give the filesystem any
hints about caching and let the cache manager itself figure it out.  The
exception being Windows CE, where we can confirm that when this flag is not
set, the device will use compression in memory and degrade performance.

If that's the general consensus, I'll open a ticket.

-----Original Message-----
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
Sent: Thursday, September 18, 2008 7:56 AM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

cache manager (CC) in Windows and the underlying file system(s).  With
respect to the cache manager, it is going to affect whether or not there
is read ahead, how much read ahead will be used, and how long data will
remain in the cache (or another way, how quickly it will be dropped).
It has been some time since I've talked to the Queen of Cache Manger
about this, but as I recall CC will try to figure out what you are doing
if you don't give it a hint.  If you do give it a hint, then it is going
to run with that hint no matter what the cost.  Note that CC or the file
system are perfectly within their right to ignore your hints.  CC
generally does honor them.  NTFS, well that's another matter.

It has been MY experience (YMMV) that database and temp file reads are
fairly random.  Database files also have the "nice" property that read
and writes are often sector (page) aligned.  Journal files should be
opened for sequential scan and are generally not sector (page) aligned.
Setting SQLite aside for a moment, for very large files that are only
going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
huge performance gains.  However, if most or all of a file is going to
be touched, even in random order, then it doesn't get you much and can
hurt you.  Most SQLite data bases _probably_ fall into that second
case.  If you have enough memory and a small enough file such that the
cache manager can hold the entire file, you are golden.  That's why some
people see such great SQLite performance by just sequentially reading
their DB files before running their SQLite application.

The elephants in the room with that previous paragraph is 1) the amount
of RAM in the system and 2) the other applications running.  Windows
will try to share its resources among all the applications running as
best it can.

I have not seen any "bugs" in SQLite in this area.  It gives a
reasonable hint for the general case.  To be fair however, I should note
that I have my own VFS.  It does unbuffered I/O for database files and
sequential, cached I/O for journal files.  If you think you can get
better performance with different flags, create your own VFS, starting
with the Windows VFS and make the changes.  You can get as sophisticated
with your hints as you want.  You can write your own caching system if
you've ingested way too much caffeine.  (Did I mention that the VFS
stuff is great!)

I would not as a general rule advise people (customers) to change the
way their Windows system caches globally for the benefit of one of your
applications.  Eventually, that is going to bite you with some support

Jay A. Kreibich wrote:
> On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the
>> The second is that SQLite when opening a file under Windows explicitly
>> tells Windows that the file will be used for random access even though
>> that is not the case.  Windows uses this hint to override its builtin
>> heuristics which can cause bug #1.
>> Bug #2 is that SQLite is lying to the operating system and could result
>> in performance degradation if the operating system actually pays
>> attention to the hint.
>   SQLite is not "lying."  After poking around a bit to refresh my
>   understanding of SQLite's file structure, I think it is safe to say
>   that SQLite will almost never do a sequential file read, even if
>   you're doing a sequential table scan.
>     sequential table scan != sequential file access
>   There are some specific situations when you might get bursts of
>   reads, but only for very specific page layouts with very specific
>   types of queries.  In short, not the common case.  Furthermore, even
>   those patterns can get broken up and shuffled around depending on the
>   state of SQLite's page cache-- especially if it is bumped up a few
>   dozen megs.  So simply running different types of queries can change
>   the access patterns (this is true of the OS's file system cache as
>   well, of course).
>   It might be worth instrumenting a few systems and having a look, but
>   in general, if you had to label SQLite's access pattern, I think
>   "random" would be the most appropriate label.
>   I also contend that if the Windows file cache becomes some kind of
>   bumbling idiot if you actually try to define an access pattern, then
>   something is wrong.  There is a very good reason why the POSIX
>   functions for doing this kind of thing are called "*advise()".  You
>   might seed the heuristic statistics in a specific direction, but they
>   should never be totally over-ridden.  That quickly leads to stupid
>   behaviors, like grabbing all the RAM on the system and not letting go.
>   Of course, we could argue philosophy for a long time.  In the here
>   and now to work around MS's inconsistencies, it looks like the best
>   bet is turn it on with CE and off on Vista, because it appears to
>   have two totally different meanings.
>    -j,
sqlite-users mailing list

sqlite-users mailing list

sqlite-users mailing list

sqlite-users mailing list

Reply via email to