Normally there is a small "Memory Mapped" buffer into the FileSystem cache that maintains a constant size. This area is "mapped" into the Cache Working Set. As you read/write different parts of files in the cache, the "mapped" are is "moved" so that the I/O can be carried out by the pager. The same thing is done when you explicitly create a memory mapped file, except that you are in control of the mapped region and its size.
When the heuristics detect "backwards" sequential/strided movement though a cached file, the new "beginning" of the mapped area is changed, but the "end" of the mapped area is not (like I said, this is probably a missing brace-bracket in the code, or a switch that falls though because the "break" was left out). When you are reading randomly or in forward (according to the heuristics) *both* the start and end points of the mapping are changed. This is why following a sequential/strided "backwards" read by a sequential/strided "forward read" fixes the mapping. This is a bug in Windows and it has (apparently) existed for quite a while. I guess no one ever reported it to Microsoft (or maybe they have and it was either (a) ignored or (b) the ticket was closed before it reached anyone capable of understanding it). Of course, reporting bugs to Microsoft is very difficult -- you have to navigate many layers of "flappers" before you get to anyone even capable of understanding what you are talking about! Probably a CVE needs to be generated for this Denial-of-Service bug with a high severity in order to get any attention and fix at all. It probably needs a catchy name and a website (plus media press releases) as well. --- 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 Keith Medcalf >Sent: Friday, 15 June, 2018 17:12 >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >According to Mark Russinovich's fine RAMMap tool (part of the >SysInternals Suite) Windows is "converting" the file from a >"standard" FileSystem cached file to a "Memory Mapped" file and back >again. "Memory Mapped" files occupy RAM (as in allocate RAM so it >cannot be used for other purposes) whereas the standard "Cached" >files are just stored in otherwise unused RAM and are completely >discardable pages that can be instantly re-used for another purpose). > >No idea why Windows is doing this useless crap ... although I do >recall someone else quite a while back questioning why Windows was >creating "Memory Mapped" files when none had been requested ... Now I >think we have the answer. Bet you Microsoft is missing some brace- >brackets in their code somewhere after an if statement ... thus >fricking with their cache management code ... > >--- >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 Keith Medcalf >>Sent: Friday, 15 June, 2018 16:22 >>To: SQLite mailing list >>Subject: Re: [sqlite] .timer >> >> >>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 > > > >_______________________________________________ >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
