1) No. But one would expect that performance would be less if the file is not in the Windows cache such as would happen if you rebooted, flushed memory, deleted the file, or if the entire file could not be cached. Reading from RAM takes nanoseconds. Reading from disk (even SSD) takes milliseconds.
2) This is likely something to do with the way your runtime is handling the vector and its memory usage interactions with Windows since the SQLite select will run with the same elapsed time seen in the shell whether you are selecting and discarding the data, a bit slower to write the output to a table, and then it depends what your code is doing with each returned item. I ran the select under Python storing the results in a list and the dynamic memory management of the Python runtime made it take *hours* to run a select that normally takes less than a minute to run when writing the data to a file, and about a quarter of that if stored in an in-memory temp file rather than writing to disk. --- 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: Saturday, 16 June, 2018 14:35 >To: SQLite mailing list >Subject: Re: [sqlite] .timer > >Keith, are you rebooting or flushing the memory between these >creates? I only notice the slowdown when they’re being started from >scratch. > > > >Have you any idea why, in my last set of tests, writing the ascending >select RowIDs to file exhibited ‘normal’ behaviour on the ascending >yet the slowdown occurred when the exact same ascending select RowIDs >were copied to a vector? > > > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Keith Medcalf <kmedc...@dessus.com> >Sent: Saturday, June 16, 2018 6:56:19 PM >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >I have confirmed that the distributed shell on sqlite.org displays >the same behaviour. You can duplicate this on Windows with the >included. > >When the select(s) containing the "order by _rowid_ desc" is >processed Windows appears to convert the cached file entry in the >Windows Cache from a straight "cached" file (pages fully discardable >and reuseable) into a "memory mapped file" which causes other >files/cache/working set to be ejected from memory. You can see this >by the increase in memory being allocated and also if you use the >RAMMAP tool available with the SysInternals package from SysInternals >(now part of Microsoft) available at >https://docs.microsoft.com/en-us/sysinternals/downloads/sysinternals- >suite > >Note that I am using 400000000 for the stop on the generate series. >This results in a 16GB database, (you can make this 100000000 for a >4GB database). I upped the size to make what was going on easier to >see on a machine with 32GB of physical RAM (and no swap file). In >the script uncomment EITHER the CTE or the generate_series code to >generate the test data. > >Sqlite is NOT using memmap'ed files at all, just regular I/O and I do >not see where this can be anything in the sqlite3 code -- it is >Windows itself -- in this case WIndows 10 1803 Pro for Workstations >(though it happens on at least the regular Pro version -- and >probably all versions of Windows 10 and maybe earlier). > >I tried it with a value that created a database that would be bigger >than my Physical RAM to see what happened. I will report that in a >moment as I expect explosions and fireworks! > > >.echo on >.timer on >.eqp on >.stats on >pragma temp_store=file; >pragma cache_size=5120; >--- >create table t >( > x integer not null, > y text collate nocase not null >); >--- >--- Use either the generate_series or the CTE to populate the table t >--- generate_series is much faster but if you do not have the >extension >--- available or builtin, using the CTE works just as well (although >--- slower. 400000000 is the number of rows. 100000000 generates >--- about 4 GB of data so you can change the number of rows generated >--- to match the size of the test data you want to generate >--- >---.load series >---insert into t >---select value, >--- '012345678901234567890123456789' >--- from generate_series >--- where start=1 >--- and stop=400000000; >--- >--- OR >--- >insert into t >with recursive cte(x,y) as > (values(1,'012345678901234567890123456789') > union all select x+1,y from cte where x<400000000) >select * from cte; >--- >--- > create temp table ta1 as > select _rowid_ > from t >order by _rowid_ asc; >--- > create temp table ta2 as > select _rowid_ > from t >order by _rowid_ asc; >--- > create temp table td1 as > select _rowid_ > from t >order by _rowid_ desc; >--- >create temp table td2 as >select _rowid_ > from t >order by _rowid_ desc; >--- > create temp table ta3 as > select _rowid_ > from t >order by _rowid_ asc; >--- > create temp table td3 as > select _rowid_ > from t >order by _rowid_ desc; >--- > create temp table td4 as > select _rowid_ > from t >order by _rowid_ desc; >--- > create temp table ta4 as > select _rowid_ > from t >order by _rowid_ asc; >--- > > > > >--- >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 18:28 >>To: SQLite mailing list >>Subject: Re: [sqlite] .timer >> >> >>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 > > > >_______________________________________________ >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