Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory
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 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
Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory
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 On Wed, Jul 8, 2009 at 10:39 AM, Mark Spiegel wrote: > In the meantime, you may want to look at the backup interface provided > by SQLite. This should avoid any problems with getting an inconsistent > snapshot which you just can't avoid with explorer even if you solve the > RAM issue. You could write a command line program that just performs a > copy on a database in short order to use in place of explorer. Someone > here could probably post pseudo code for that in just a few minutes. > Maybe this presents its own set of problems, I don't know. > ___ > 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
Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory
On Tue, Jul 7, 2009 at 1:10 PM, Mark Spiegel wrote: > This should give you a picture of who is opening the file and with what > flags. In particular, how is your SQLite app and Explorer opening the file? Thanks for the pointer to Filemon; I'm always happy to learn about a new tool. Apparently its functionality has been integrated into Procmon, so that's what I used to follow your debugging steps. To address some of the points you mention earlier: * I'm running as Administrator, and I can see SYSTEM-owned processes in Task Manager. Not sure if that means I can see all processes or not, but I suspect that I can. * I was actually wrong about VM going up. In fact, the Pagefile doesn't seem to grow much. Physical Memory (as reported by Resource Monitor) is what's growing. Once it peaks (at around 99%) the page fault rate goes through the roof. * When this situation occurs, the system is most definitely not CPU-bound, as the CPU usage hovers around 1%. The high page fault rate is what seems to be making other applications unresponsive. * Working Set Size and Peak Working Set size of all processes in Task Manager are very modest; the memory appears to be going to some sort of caching mechanism in the OS. Using procmon, I filtered on "Path" for my particular sqlite file and repeated my experiments from the original post. I verified that sqlite3.exe and Explorer.EXE are the only processes opening the file.The thing that stands out in the pathological cases is the presence of SHARING VIOLATION result code. Whichever one of sqlite or Explorer attempts to open the file second will throw this error, and that's when I see physical memory begin to grow. Here's a trace of sqlite accessing the file, then Explorer copying it (CSV format): "Time of Day","Process Name","PID","Operation","Path","Result","Detail" "6:01:46.1439881 PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired Access: Read Attributes, Disposition: Open, Options: Open For Backup, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, Impersonating: TESTBED9\Administrator, OpenResult: Opened" "6:01:46.1446133 PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired Access: Read Attributes, Disposition: Open, Options: Open For Backup, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, Impersonating: TESTBED9\Administrator, OpenResult: Opened" "6:01:46.1448474 PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired Access: Generic Read/Write, Disposition: OpenIf, Options: Synchronous IO Non-Alert, Non-Directory File, Random Access, Attributes: N, ShareMode: Read, Write, AllocationSize: 0, Impersonating: TESTBED9\Administrator, OpenResult: Opened" "6:01:56.1737399 PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SHARING VIOLATION","Desired Access: Generic Read, Write Data/Add File, Disposition: Open, Options: Sequential Access, Synchronous IO Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read, AllocationSize: n/a" "6:01:56.1738486 PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired Access: Generic Read, Write Data/Add File, Disposition: Open, Options: Sequential Access, Synchronous IO Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened" And Explorer copying the file then sqlite3 opening it: "6:30:23.1071085 PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired Access: Generic Read, Write Data/Add File, Disposition: Open, Options: Sequential Access, Synchronous IO Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read, AllocationSize: n/a, OpenResult: Opened" "6:30:41.0615955 PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired Access: Read Attributes, Disposition: Open, Options: Open For Backup, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, Impersonating: TESTBED9\Administrator, OpenResult: Opened" "6:30:41.2108393 PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired Access: Read Attributes, Disposition: Open, Options: Open For Backup, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, Impersonating: TESTBED9\Administrator, OpenResult: Opened" "6:30:41.2687134 PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SHARING VIOLATION","Desired Access: Generic Read/Write, Disposition: OpenIf, Options: Synchronous IO Non-Alert, Non-Directory File, Random Access, Attributes: N, ShareMode: Read, Write, AllocationSize: 0, Impersonating: TESTBED
Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory
Just to make sure I understood you correctly, is this what you are suggesting? * Open file with sqlite app (in my case sqlite3) * execute BEGIN EXCLUSIVE TRANSACTION; * initiate the file copy * COMMIT; after the copy is finished. I tried doing this, but very early into the copy Windows issues the following error: Errror 0x80070021: The process cannot access the file because another process has locked a portion of the file. On Tue, Jul 7, 2009 at 1:03 PM, John Stanton wrote: > You should synchronize your backup (copy). Try surrounding it with an > exclusive transaction. > > Stan Bielski wrote: >> 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 >> 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] Repost: Accessing a DB while copying it causes Windows to eat 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accessing a DB while copying it causes Windows to eat virtual memory
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reducing memory usage when reading a blob
Hello,
I'm cooking up some blob reading code based on the following example ->
http://www.sqlite.org/cvstrac/wiki?p=BlobExample
The author prepares a statement and reads the blob value in the following
manner ->
*if*( rc==SQLITE_ROW ){**
*pnBlob =
sqlite3_column_bytes
(pStmt, 0);
*pzBlob = (unsigned char *)malloc(*pnBlob);
memcpy(*pzBlob,
sqlite3_column_blob
(pStmt, 0), *pnBlob);
}
...
rc = sqlite3_finalize
(pStmt);
At the point prior to the call to sqlite3_finalize, the blob is going to
more stored twice in memory. In my application, however, memory is very
tight. Is there an easy way I can get around sqlite3_finalize freeing the
blob data? Deferring the finalize until I am done processing the blob really
isn't a palatable option
On a similar note, are there any hacks available to write a blob to a file
using a limited memory buffer? As far as I can tell, the sqlite API requires
that a blob be read in its entirety, which is rather unfortunate when the
blob is bigger than memory...
Thanks,
-Stan
Re: [sqlite] Performance problems potentially related to index scalability
On 2/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Stan Bielski" <[EMAIL PROTECTED]> wrote: > > Anyone care to take a guess what's going on here? The problem is that you are thrashing. The working set on the database file is exceeding the amount of memory that your OS has set aside for disk cache. The best solution, if possible, is to insert records in sorted order by index. If you are inserting in batches, try inserting them all into a TEMP table first. Then do INSERT INTO maintab SELECT * FROM temptab ORDER BY indexcolumn; DELETE FROM temptab; Thanks for your reply. I understand that I'm experiencing thrashing, but I'm curious as to why I'm experiencing it. My understanding is that the row_id PRIMARY KEY column has an index on it by default. Inserting into this index doesn't cause thrashing, but inserting into the secondary index does. I hoped that enabling the shared cache mode and periodically doing the select operation on it would keep it in SQLITE's cache. Based on what I saw from I/O stat, top, and my timing measurements, that doesn't seem to be the case. In fact, my cache "priming" experiment showed that it didn't help at all, despite a cache size of 500 MB and the index being on a column with an INTEGER pragma. The transaction immediately following the cache priming would be just as slow as the transactions far away from it. Why would a transaction inserting 1024 rows of 0.5 KB each result in the eviction of that index, despite the abundance of cache? The solution you propose doesn't work when your table has multiple indices of this type. One can imagine altering the schema as to store each indexed column in a separate table, with a column containing the rowid to the main table, but that would result in a significant amount of redundant overhead. Again, thanks for the reply... I'm just trying to figure out what's happening internally without adding instrumentation to the source code ;-) -Stan ' If SQLite were smarter, it might be able to deal with this situation automatically. It is not clear at this time if making SQLite smart enough to deal with this is compatible with the goal of keeping SQLite small and nimble. We are working the problem. Do not expect a resolution in the near term. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance problems potentially related to index scalability
On 2/8/07, Griggs, Donald <[EMAIL PROTECTED]> wrote: Regarding: "...Next, I add in an index on [one] of my numeric columns. ... This is where the problem comes in." You may already know this, but if you can tolerate waiting until the loads are complete before adding the index, the total performance should be better. If I wait until after the inserts, the index will take less than a minute to build. Unfortunately, that's not an option for this application :-( -Stan
[sqlite] Performance problems potentially related to index scalability
Greetings, I'm running into some performance problems with my insert operations using sqlite 3.35. OS is linux with a 2.69 kernel using an out-of-box ext3 file system. My schema consists of a single table with 12 columns with a numeric affinity and 5 columns with text affinity. The table has a primary integer key on a monotonically increasing row id number. My first experiment proceeds as follows: Using a single thread, I insert 5 million rows containing around 0.5 KB of data to the table. These inserts occur in transactions of 1000 inserts each. In this case, performance is very fast. The entire experiment takes around 10 minutes to run. Typical transaction time is between 0.1 and 0.2 seconds for every 1000 inserts. Next, I add in an index on of my numeric columns. The value of this column is a random 32 bit integer. This is where the problem comes in. At first, the experiment is fast, and I see transaction times similar to what I've observed without the index. Then, after around 800,000 entries, I start seeing drastically larger transaction times of around 2-5 seconds per transaction. After around 2,500,000 entries, these times are consistently hovering around 5 seconds. At that point I killed the process. With the help of I/O stat, it seems that at the point that things get slow we're seeing an I/O wait of around 50%. Towards the end of the experiment, I/O wait is around 85%. Additionally, at the beginning of the experiment, I'm observing a negligible amount of I/O read. When it begins to get slow, I/O occurs at around 1/10th the rate of I/O write. I suspect that this had something to do with the way sqlite caches my index. I modified the code to call sqlite3_enable_shared_cache. It then created a dummy table, inserted a row, and then performed a select on it. In the callback for the select, I then proceeded with the experiment (this is similar to how Mozilla's storage module enables the shared cache). In this case, performance was slightly worse at first (0.4 seconds for each transaction, I suspect that's because it's cheaper to use the buffer cache). As the experiment proceeded, however, the transaction times were consistent until I filled SQLite's cache. Even with an absurdly high cache PRAGMA (~500 MB), it managed to fill after around 1,000,000 rows. At that point, I began to see the dreaded 5 second transaction times again. I tried a few more things (periodically dumping the cache by closing the connections, then "priming" it by doing a select on the value of the index), but I didn't have any luck speeding things up. Anyone care to take a guess what's going on here? Is it a problem with rebalancing the btree that's storing the index? It seems to me that something is doing random I/O, and I was hoping that if I could "force" the index's pages to stay in the cache, then I'd be able to avoid that. Any help is appreciated, -Stan
