Eureka! I tried the latest version of sqlite3.exe, and I verified that both the VACUUM and the access when the file is being copied no longer monopolize physical memory.
I dug up your "Vista frustrations" thread, and I agree that this seems to be bad behavior on part of the cache manager. If a process opens a file using FILE_FLAG_RANDOM_ACCESS, then it (or any other concurrent process) that happens to access sequentially can grind the system to a halt. Sounds like a nightmare for any install running an AVS or backup. Thanks for your help! -Stan On Wed, Jul 8, 2009 at 5:15 PM, Robert Simpson<sqlite_l...@bcsft.com> wrote: > I believe the issue was resolved in this ticket: > > http://www.sqlite.org/cvstrac/tktview?tn=3387 > > You're being bitten by Vista and Win2008's aggressive cacheing of the > database. > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mark Spiegel > Sent: Wednesday, July 08, 2009 2:02 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Repost: Accessing a DB while copying it causes Windows > to eat virtual memory > > > Stan Bielski wrote: >> Hello again, >> >> Copying the database with Explorer and accessing it via sqlite is just >> a contrived example that exhibits the same problem I'm having in my >> application. The app does make a copy of the DB, but it has app-layer >> locking that will prevent modifications unless someone decides to >> start fiddling with the DB outside of my software (in which case I >> have bigger problems). The app is multi-threaded, and a thread other >> than the copying thread may attempt to open the DB and read from it >> while the copy is occurring. >> >> My contrived example aside, I just discovered that issuing the VACUUM >> command on the same 20 GB DB in sqlite3 causes similar memory issues, >> even when another process is not accessing the database file. >> sqlite3.exe has a peak working set of 40 MB in Task Manager, but >> Resource Monitor reports 99% Used Physical Memory (of 4 GB). If it >> were all buffer cache, I'd expect that simply copying the file would >> result in the same amount of memory being used, but it doesn't. I'm >> going to head to a Windows forum to try to find out more about what's >> happening, but the list users may want to be aware of this if they >> plan on using large sqlite DBs with Windows 2008. >> >> Thanks, >> -Stan >> >> >> > > Maybe. > > First, forget what I said about mapping the file. That didn't make > sense just minutes after I hit the send button. I was a few cups of > coffee shy of fully awake. > > I just haven't done enough work with Win 2008 yet to be able to say with > certainty what is going on. However, here are a couple more things to > consider. First, it used to be in windows that the amount of address > space (and RAM) that could be used for various things was fixed. This > is no longer the case. What you _may_ be seeing is that in trying to > help, Windows is allocating as much address space (and RAM) as it can to > the cache manager. The file is not opened for unbuffered access so NTFS > is going to try to use the cache manager on the file. I don't know if > any of the user mode tools will tell you this. If you have a kernel > debugger attached to the machine in this state, the "!VM" command > _might_ shed light on how much address space is allocated for what. > > Second, it occurred to me that in the nominal copy case where you don't > see a lot of RAM being consumed, explorer, knowing that it is just > sequentially copying a file, may have it opened for unbuffered access > for both source and destination. This would bypass the cache manager > completely. I've implemented copy this way in the past to prevent some > of the adverse effects of large copies on the system, but I have no idea > if explorer would use this technique. The downside is that for files > already in use, copy can be a bit slower. You can find out with filemon. > > Wish I could offer more in the way of a solution... > > > _______________________________________________ > 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
