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
