Maybe if you write-protected the file before starting the copy... My guess is that the process that connects to do the query opens the file in read/write mode causing the operating system to think someone is trying to write to the file. So the OS tries to keep a copy of the data being read by the COPY command around till the COPY is finished.
If the file is write-protected I believe the db connection will open the file in read-only mode so the OS will probably recognized that it does not need to retain a separate copy for COPY. -Jeff -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Stan Bielski Sent: Tuesday, July 07, 2009 11:50 AM To: sqlite-users Subject: [sqlite] Repost: Accessing a DB while copying it causes Windows toeat virtual memory Sorry for the repost, but the original thread was hijacked by another list user. This is a serious problem IMHO; it looks like the DB can't be backed-up without rendering the machine unusable if a query hits it while a copy is in progress. Hello, In the course of copying a largish (20 GB) database file while accessing it via sqlite3, the machine became very unresponsive. I opened task manager and found that the system was using a huge amount of virtual memory, causing it to thrash. Per-process memory usage looked normal and did not add up to anywhere near system-wide VM usage. I ran into this issue at a customer site and was able to reproduce it using a local Windows 2008 installation. I have not installed any backup software or a virus scanner. Storage is local disk, SQLite version is 3.3.17. At first I thought that this was a general Windows problem involving a process accessing a file that is being copied, but other binaries I tested do not cause the same behavior that sqlite3 does. I performed the following experiments to try to diagnose the issue. Case 1: * I copy a 20 GB sqlite DB using Windows' own copy utility (e.g. via explorer). * At any point during the copy, I open the file being copied in sqlite3 * I exit sqlite3. * During the rest of the copy the OS will consume virtual memory linear (seemingly identical) to the amount of data copied since the process opened the file. I repeated this experiment using a similarly-sized file created from /dev/zero (i.e. an invalid DB) and the results were the same. Case 2: * I copy the sqlite DB using Windows' own copy utility (e.g. via explorer). * At any point during the copy, I run 'strings' with the file as an argument. * I exit strings. * The copy does not result in the OS consuming additional virtual memory. Case 3: * I open the DB in sqlite3 * I let sqlite3 idle and do not input any commands. * I copy a sqlite DB using Windows' own copy utility (e.g. via explorer). * I continue to let sqlite3 idle and do not input any commands. * During the rest of the copy the OS will consume virtual memory linear (seemingly identical) to the amount of data copied since the process opened the file. Is there a workaround for this issue? Any assistance or info is appreciated. Thanks, -Stan _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
