Ok, I have been able to reproduce this but have not figured out what is happening.
And you are correct, it appears to be related to "reading backwards" somehow. It is unaffected by the WIN32 cache mode that is set (I tested all modes and they all behave the same -- except that I could not test the NOBUFFER mode since SQLite3 I/Os are not page and cluster aligned), it happens whether using the WIN32 allocator or the system malloc. It happens with both 32-bit code and 64-bit code. The memory *IS NOT* being allocated to SQLite3 or whatever process is running the sqlite3 database engine (its memory usage stays constant) and it is not actually being "used by" the FileSystem cache, it is not allocated to the process at all. Even though it appears to be "eating" several Gigs of RAM (actually, it almost looks like a duplicate cache of the file being read, but this makes no logical sense whatsoever), this ram is not being allocated to the process because the 32-process does not get an Out-of-Memory error, and 32-bit processes have a limited arena to play in. It is also not "dirty" write buffers since those are accounted for separately. The RAM is being allocated without being committed (so it is a temporary usage thing). However unlike actual FileSystem cache (which uses RAM not allocated for any other purpose) this appears usage appears to have "priority" over the FileSystem cache and over the process working set thus forcing working set and/or filesystem cache to be discarded. Interestingly, sometimes the allocated memory "cleans itself up", however, if you follow the "desc" by an "asc" operation it appears to "clean up" and convert that usage into something else. It is really very weird and is something in Windows itself, though what I have not a clue. I never noticed it because I have lots of physical space and no swap file and their associated management overhead/tables, but it does show up as "allocated" memory and I presume that if you do not have lots and lots of extra RAM available it has an impact on performance as Windows fracks and un-fracks itself ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Friday, 15 June, 2018 10:53 >To: SQLite mailing list >Subject: Re: [sqlite] .timer > >Thanks Keith. I did look on the performance page but didn’t realise >clicking the items on the left brought up different info. I am on >windows 10 pro. > >I haven’t had time to try creating the ‘personalised’ sqlite3.exe so >the following relates to my own app run under the original conditions >(i.e. without your code changes). In that app, rather than asking >sqlite to create the table of RowIDs, my code steps through the >select and stores the RowIDs in either a vector or a temporary file. >For storing to the tmpfile it stores the data in a vector of size >8192, writes those 8192 int64’s to the file using fwrite and so on in >blocks of 8192. Note that if the procedure is run more than once >without closing the app the same tmpfile is reused. > >The following relates to storing the RowIDs in a temp file run in 64 >bit mode. > >select RowID from Test order by RowID; >----------------------------------------------------- >FlushMem >Cached < 1 GB >Run >During run Cached climbs in linear fashion to ~4.7 GB in 20.5 secs. >Run again without closing app >During run Cached increases slightly (5.4 max) then returns to 4.7 GB >in 16.5 secs. >Each subsequent run (without closing or flushing) returns similar >results and ditto for flushing and going through the whole thing >again. >App closed down. >Cached stays ~ 4.7 GB >Run returns much the same results had it not been shut down. >All of this is pretty much the results I’d expect – first run a bit >slower than subsequent runs where the data is already cached. Nothing >to see here imo. > >select RowID from Test order by RowID desc; >------------------------------------------------------------ >FlushMem >Cached < 1 GB >Run >During run Cached rises very slowly but never above 1.5 GB. When the >procedure finishes running Cached is showing under 1 GB but at the >moment it finishes it jumps to 5 GB. Time = 91.4 secs. >Run again without closing app. >During run Cached gradually reduces from 5GB to 1GB in linear fashion >but bursts back up 5 GB when procedure finishes. Time = 16 secs. >Each subsequent run (without closing or flushing) returns similar >results and ditto for flushing and going through the whole thing >again (first run again takes over 90 secs with Cached < 1 GB >throughout but bursts to 4.7 GB as procedure finishes). >Plenty to fathom here. > >We now switch to vector mode. Select is stepped through and each >RowID returned is stored in the vector of size 100,000,000. The >tmpfile is never created. NB I’m doing the desc select first this >time just in case. > >select RowID from Test order by RowID desc; >----------------------------------------------------- >FlushMem >Cached < 1 GB >Run >During run Cached never gets beyond 600 MB (for about 20 secs it >seemed frozen on 297 MB). When the procedure finishes it’s showing >600 MB then shortly after shows 4.8 GB. Time = 85.3 secs. >Run again without closing app. >During run Cached linearly decreases to 900 MB then bursts to 4.8 GB >on completion. Time = 14 secs. >Each subsequent run (without closing or flushing) returns similar >results and ditto for flushing and going through the whole thing >again (first run takes 96? secs with Cached < 1 GB throughout but >bursts to 4.7 GB as procedure finishes). >Similar to previous test. > >select RowID from Test order by RowID; >----------------------------------------------------- >FlushMem >Cached < 1 GB >Run >During run Cached climbs in linear fashion to ~4.7 GB in 63.3 secs. >Unexpected time????? >Run again without closing app >During run Cached stays approximately the same (~4.7 GB) in 14 secs. >Each subsequent run (without closing or flushing) returns similar >results and ditto for flushing and going through the whole thing >again (first run again took 60 secs). >App closed down. >Cached stays ~ 4.7 GB >Run returns much the same results had it not been shut down (14s). > >This last unexpected set of results suggests it’s nothing to do with >the select direction. Because it differed so much from the first test >I went through test 4 several times but always got similar results. I >also ran the first test (tmpfile version) another couple of times >but, again, there was no change. It was the only one that gave me the >expected results. I’m totally lost. > > > > > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
