I never said a word aboout SQLite. You ass U Me too much I suspect. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mark Spiegel Sent: Thursday, September 18, 2008 11:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Vista frustrations
I'm sorry, I have to take issue with that statement. The design of the file system/cache manager is not "pitiful". It strives to provide good performance in the entire application space, not just your little corner of it. It is doing the best it can with the "hint" you've given it. If another (or no) hint provides better performance in your application, who's fault is that? Do you realize that without the cache manager, fast I/O would not be possible? Run on a debug system where only IRP based I/O is possible any you will be singing another tune in a hurry. Why do you think these hints are even available? It is to help you optimize your application. The SQLite memory subsystem doesn't work well on my platform I don't run around calling SQLite "pitiful". I recognize that the authors' implementation(s) is probably a good performance compromise in the generic case. If it is a big enough problem (which it is for me), I write my own version to optimize my performance. While better, the integer encoding is not as good as it could be for me. Does that mean the SQLite is pitiful? I should also note that as of the last time I talked to her, Molly is no longer handling the cache manager. I believe she has moved back into the kernel group after a brief departure, but is working on something else. I haven't seen the talks that Robert refers to, but suspect they are close to the versions I have seen in person. I would bet they are still very useful and relevant. Fred Williams wrote: > Is a sad day when an application program is forced to compensate for pitiful > OS design and performance :-( > > -----Original Message----- > From: [EMAIL PROTECTED] > [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----- > From: [EMAIL PROTECTED] > [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 > > FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the > 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 > calls. > > Jay A. Kreibich wrote: > >> On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the >> > wall: > >> >>> 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 >> > sequential > >> 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@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 > > _______________________________________________ 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
