Keith,
You spoon fed me on a previous thread how to load extensions using a core_init function placed at the end of the sqlite3.c code. I do have the series.c in my core_init so it is available to me and works fine in my cpp code. I don’t see how that relates to sqlite3.exe though. How do you get generate_series into that (to say nothing of those code changes)? The documentation says “The generate_series(START,END,STEP) table-valued function<https://sqlite.org/vtab.html#tabfunc2> is a loadable extension<https://sqlite.org/loadext.html> included in the SQLite source tree, and compiled into the command-line shell<https://sqlite.org/cli.html>.” That said, I can tell from my own app that having SQLITE_WIN32_FILE_RANDOM defined made no difference, in fact it made it considerably worse for the descending query. With the memory flushed the descending query was taking 10 times the time it took with the cache unflushed. That’s up from around 4. Having widows defender turned off changed nothing. ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Keith Medcalf <kmedc...@dessus.com> Sent: Friday, June 15, 2018 9:41:18 AM To: SQLite mailing list Subject: Re: [sqlite] .timer Yes, that is correct. Then compile with the preprocessor symbol SQLITE_WIN32_FILE_RANDOM defined. generate_series is an extension module located in ext/misc/series.c https://www.sqlite.org/src/dir?ci=567e09ef2a8cd84a&name=ext/misc By default I load almost every one of those extensions in every sqlite3.dll and sqlite3.exe I compile (along with the INIT hook to get them loaded on each connection automatically -- basically make them all builtin extensions), along with a bunch of others I wrote myself that do things like add the entire standard math library, running statistics, a bunch of Windows functions (get the computer name, username, sids, translate sids between blobs and strings, check the token for sids, handle uuid functions etc), ip address functions, unicode case/accent folding, etc. It makes the DLL and static shell about 2 MB, but I have no shortage of CPU or memory anywhere ... (though I have to keep remembering whenever I move stuff to other servers and VMs that use actual slow hard drives that I have to keep the I/O rate within the piddly bounds of spinning rust -- NVMe and SSD drives with GB/s rather than KB/s IO rates can kill you). I presume you are running current Win10Pro. There was a "bug" introduced in Defender back in late November that has now supposedly been fixed (don't know if it was in Defender or in Windows itself, but turning off the real-time defender fixed the issue) that adversely affected a bunch of crap (the main thing I noticed is that is doubled or more my build times -- by introducing the generic "doing nothing" (ie, some build processes loaded up all the SMT threads on the processor, and ran at 100% CPU usage for each thread on each core for a couple of minutes -- that time doubled and the CPU usage dropped to about 50% average on each thread) for which Microsoft is famous). That has since been fixed (in March or something) and I haven't noticed anything else acting "weird" -- though like I said I have no idea if it was Defender that was the problem or if that just brought out some other issue in the windows kernel 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 02:06 >To: SQLite mailing list >Subject: Re: [sqlite] .timer > >Keith, I can find no instance of 2966 in my sqlite.c code. I have >found the code you mentioned > > > > if( isDelete ){ > >#if SQLITE_OS_WINCE > > dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN; > > isTemp = 1; > >#else > > dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY > > | >FILE_ATTRIBUTE_HIDDEN > > | >FILE_FLAG_DELETE_ON_CLOSE; > >#endif > > }else{ > > dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL; > > } > > > >Immediately after that section of code I’ve replaced > > > >#if SQLITE_OS_WINCE > > dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS; > >#endif > > > >With > > > > /* Reports from the internet are that performance is always > > ** better if FILE_FLAG_RANDOM_ACCESS is used. Ticket #2699. */ > >#if SQLITE_OS_WINCE || SQLITE_WIN32_FILE_RANDOM > > dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS; > >#elif SQLITE_WIN32_FILE_SEQUENTIAL > > dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN; > >#elif SQLITE_WIN32_FILE_WRITETHROUGH > > dwFlagsAndAttributes |= FILE_FLAG_WRITE_THROUGH; > >#elif SQLITE_WIN32_FILE_NOBUFFER > > dwFlagsAndAttributes |= FILE_FLAG_NO_BUFFERING; > >#endif > > > >Is that correct? > > > >BTW ‘select * from generate_series(1,10)’ gives me an error ‘no such >table : generate_series’ in sqlite3.exe. I thought it was compiled >into the shell by default? > > > > > > > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Keith Medcalf <kmedc...@dessus.com> >Sent: Friday, June 15, 2018 8:10:19 AM >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >Yes. Search your sqlite3.c for #2966 (there is only one instance and >it is in the winOpen function, somewhere around line 44847). The >code that is there will be an #ifdef that forces RANDOM_ACCESS only >for WINCE. Just change it so that you can compile it with >RANDOM_ACCESS set and see if that makes a difference. And yes, it >will work in both 32 and 64 bit on Windows ... It will at least make >the Windows caching deterministic (technically LRU). > >I know that Windows supposedly has some builtin rules about how it >sets the default cache mode, but you should really have the whole >file in the cache after each command since that is the purpose of the >cache (memory not used is money wasted). > >Then re-run your test without freeing up physical cache memory >between the runs (so the database stays in the cache). The first run >though the file will take a long time (SYS I/O Time) but subsequent >passes should be very fast since the data is all already in the cache >... (the USER time should stay about the same, but the SYS time will >change depending on whether the file is in the cache or not). > >--- >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 00:50 >>To: SQLite mailing list >>Subject: Re: [sqlite] .timer >> >>Thanks for all the replies and great suggestions. I’m just up but >>will shortly investigate all. >> >> >> >>After reading this post by Clemens Ladisch >> >> >> >>http://sqlite.1065341.n5.nabble.com/This-is-driving-me-nuts- >>tp102034p102105.html >> >> >> >>I was guessing the answer might be something along the lines of what >>is suggested below although I was clueless about how to go about it. >>To be honest I’m still not sure Keith. >> >> >> >>1 Is #2699 an sqlite ticket? I can’t find it. >> >>2 Do I add this code to my sqlite3.c file? If so, whereabouts? >> >>3 Will it still work if I compile in 64 bit mode? >> >> >> >>________________________________ >>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >>behalf of Keith Medcalf <kmedc...@dessus.com> >>Sent: Thursday, June 14, 2018 10:09:50 PM >>To: SQLite mailing list >>Subject: Re: [sqlite] .timer >> >> >>See the following web page for how the default "Microsoft Magical >>Mystery Cache Mode" works on Windows. The term "Intelligent Read- >>Ahead" applies only if you are 12 years old (typical Microsoft >>behaviour). >> >>http://flylib.com/books/en/4.491.1.101/1/ >> >>Note that the default mode is completely fracking useless for most >>intents and purposes, and for databases the SEQUENTIAL mode is >bloody >>awful as well. >> >>However, the SEQUENTIAL mode seems to match what is being seen (the >>look ahead is only working in one direction and pages are being >>unmapped from the system cache at the wrong (read most stoopid) time >>possible). >> >>So, the changes I made are to function winOpen as follows (after the >>ticket #2699 comment). Effectively I always set the flags for >RANDOM >>mode even though I am not Winders Crappy Edition ... >> >> if( isDelete ){ >>#if SQLITE_OS_WINCE >> dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN; >> isTemp = 1; >>#else >> dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY >> | FILE_ATTRIBUTE_HIDDEN >> | FILE_FLAG_DELETE_ON_CLOSE; >>#endif >> }else{ >> dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL; >> } >> /* Reports from the internet are that performance is always >> ** better if FILE_FLAG_RANDOM_ACCESS is used. Ticket #2699. */ >>#if SQLITE_OS_WINCE || SQLITE_WIN32_FILE_RANDOM >> dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS; >>#elif SQLITE_WIN32_FILE_SEQUENTIAL >> dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN; >>#elif SQLITE_WIN32_FILE_WRITETHROUGH >> dwFlagsAndAttributes |= FILE_FLAG_WRITE_THROUGH; >>#elif SQLITE_WIN32_FILE_NOBUFFER >> dwFlagsAndAttributes |= FILE_FLAG_NO_BUFFERING; >>#endif >> >>This adds the SQLITE_WIN32_FILE_ ... defines and applies them in >>order to all files opened by the Windows VFS. I define >>SQLITE_WIN32_FILE_RANDOM to make sure that the cache mode is always >>set for RANDOM access and that read-ahead and idiot-mode (ie, >>Microsoft-style) cache pruning are disabled, thus making the >>FileSystem cache act in a deterministic LRU page ejecting fashion. >> >>--- >>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: Thursday, 14 June, 2018 14:16 >>>To: SQLite mailing list >>>Subject: Re: [sqlite] .timer >>> >>> >>>Cannot reproduce. >>> >>>I am using the current trunk that I compile myself with MinGW 8.1.0 >>>on Windows 10 1803 Pro for Workstations. The laptop has a 4 Ghz >>Quad >>>Core Xeon and the disk is a Samsung NVMe drive. About the only >>>relevant change is that I have forced the Windows caching mode from >>>"magical mystery Microsoft mode" to always use "Random access >mode". >>>It is also using the Win32 heap allocator. I also have 32 GB of >RAM >>>and no third or fourth level page indirection or Virtual Arena >>>diddling (that is, swapping is turned off). I also have SQLite set >>>to a 4K pagesize and have set 262144 pages of cache in SQLite by >>>default. >>> >>>I forgot how slow CTE's are until I did this ... almost 3 times >>>slower than using generate_series >>> >>>In RAM only -- Windows caching modes and disk I/O are irrelevant: >>> >>>>sqlite >>>SQLite version 3.25.0 2018-06-13 17:19:20 >>>Enter ".help" for usage hints. >>>Connected to a transient in-memory database. >>>Use ".open FILENAME" to reopen on a persistent database. >>>sqlite> create table test as -- creates test table with 100 million >>>rows >>> ...> with recursive cte(x,y) as >>> ...> (values(1,'012345678901234567890123456789') >>> ...> union all select x+1,y from cte where x<100000000) >>> ...> select * from cte; >>>Run Time: real 34.619 user 34.625000 sys 0.000000 >>>sqlite> drop table test; >>>Run Time: real 1.578 user 1.578125 sys 0.000000 >>>sqlite> create table test (x integer, y text); >>>Run Time: real 0.000 user 0.000000 sys 0.000000 >>>sqlite> insert into test select value, >>>'012345678901234567890123456789' from generate_series where start=1 >>>and stop=100000000; >>>Run Time: real 12.226 user 12.234375 sys 0.000000 >>>sqlite> create temp table tasc1 as select rowid from test order by >>>rowid asc; >>>Run Time: real 11.408 user 11.140625 sys 0.265625 >>>sqlite> create temp table tdesc1 as select rowid from test order by >>>rowid desc; >>>Run Time: real 10.251 user 9.875000 sys 0.375000 >>>sqlite> create temp table tdesc2 as select rowid from test order by >>>rowid desc; >>>Run Time: real 10.188 user 9.828125 sys 0.359375 >>>sqlite> create temp table tasc2 as select rowid from test order by >>>rowid asc; >>>Run Time: real 11.563 user 11.218750 sys 0.328125 >>>sqlite> ^Z >>> >>>Timings are all pretty constant ... now with the db on disk ... >>> >>>>sqlite junk.db >>>SQLite version 3.25.0 2018-06-13 17:19:20 >>>Enter ".help" for usage hints. >>>sqlite> create table test (x integer, y text); >>>Run Time: real 0.015 user 0.000000 sys 0.000000 >>>sqlite> insert into test select value, >>>'012345678901234567890123456789' from generate_series where start=1 >>>and stop=100000000; >>>Run Time: real 21.206 user 13.234375 sys 6.562500 >>>sqlite> create temp table tasc1 as select rowid from test order by >>>rowid asc; >>>Run Time: real 13.658 user 10.000000 sys 3.656250 >>>sqlite> create temp table tdesc1 as select rowid from test order by >>>rowid desc; >>>Run Time: real 12.658 user 9.500000 sys 3.156250 >>>sqlite> create temp table tasc2 as select rowid from test order by >>>rowid asc; >>>Run Time: real 13.017 user 10.031250 sys 2.984375 >>>sqlite> create temp table tdesc2 as select rowid from test order by >>>rowid desc; >>>Run Time: real 13.001 user 9.750000 sys 3.250000 >>>sqlite> .exit >>> >>> >>>2018-06-14 13:47:30 PY2 [D:\] >>>>dir junk.db >>>2018-06-14 13:44 4,450,889,728 junk.db >>> 1 File(s) 4,450,889,728 bytes >>> 0 Dir(s) 330,480,447,488 bytes free >>> >>>So we have some difference in SYS time for I/O, but that is about >>it. >>>Still constant times. >>> >>>--- >>>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: Thursday, 14 June, 2018 12:31 >>>>To: SQLite mailing list >>>>Subject: Re: [sqlite] .timer >>>> >>>>Thanks for the replies. It is Windows Defender I’m using on a ms >>>>surface pro 4 with 16 GB ram and 512 GB SSD. OS is Windows 10 Pro. >>>>I’ve come up with the following demo using the sqlite shell. In it >>I >>>>use a couple of small apps called AvlRAM and FlushMem. Apart from >>>>minor background tasks sqlite3.exe is the only app running. >>>> >>>>I downloaded FlushMem from here https://chadaustin.me/flushmem/ >and >>>>changed it slightly so that it reported the available RAM before >>and >>>>after the flush. I was primarily interested in how sqlite was >>>>performing after restarting the computer before any info was lying >>>>about in caches as I was encountering some strange timings with >>>such. >>>>FlushMem saved me from continually rebooting and yielded results >>>>similar to the ones I was experiencing with rebooting. >>>> >>>>The code for AvlRAM is as follows >>>> >>>>double FreeGBs() >>>>{ >>>> MEMORYSTATUSEX status; >>>> status.dwLength = sizeof(status); >>>> GlobalMemoryStatusEx(&status); >>>> return status.ullAvailPhys / (1024.0 * 1024 * 1024); >>>>} >>>> >>>>int _tmain(int argc, _TCHAR* argv[]) >>>>{ >>>> std::cout << FreeGBs() << " GBs" << std::endl; >>>> return 0; >>>>} >>>> >>>> >>>>Here’s the demo interspersed with comments >>>> >>>>SQLite version 3.24.0 2018-06-04 19:24:41 >>>>Enter ".help" for usage hints. >>>>Connected to a transient in-memory database. >>>>Use ".open FILENAME" to reopen on a persistent database. >>>>sqlite> .timer on >>>>sqlite> .open MyTemp.db -- open empty DB >>>>sqlite> .shell FlushMem >>>>Available RAM - pre flush = 12.913 GBs - post flush = 14.1749 GBs >>>> >>>>sqlite> create table test as -- creates test table with 100 >million >>>>rows >>>> ...> with recursive cte(x,y) as >>>> ...> (values(1,'012345678901234567890123456789') >>>> ...> union all select x+1,y from cte where x<100000000) >>>> ...> select * from cte; >>>>Run Time: real 77.239 user 68.750000 sys 7.468750 >>>>sqlite> .shell AvlRAM >>>>14.1059 GBs >>>> >>>>sqlite> create temp table tasc as select RowID from test order by >>>>RowID; >>>>Run Time: real 32.473 user 25.203125 sys 7.203125 >>>>sqlite> .shell AvlRAM >>>>14.1084 GBs >>>>// little change to RAM, respectable time >>>> >>>>sqlite> create temp table tdesc as select RowID from test order by >>>>RowID desc; >>>>Run Time: real 32.056 user 24.515625 sys 7.531250 >>>>sqlite> .shell FlushMem >>>>Available RAM - pre flush = 9.96339 GBs - post flush = 14.7108 GBs >>>>// why does the RAM drop 4+ GB due to executing the above?, >>>>respectable time >>>> >>>> >>>>sqlite> create temp table tasc2 as select RowID from test order by >>>>RowID; >>>>Run Time: real 38.285 user 26.109375 sys 9.000000 >>>>sqlite> .shell FlushMem >>>>Available RAM - pre flush = 14.5936 GBs - post flush = 14.7553 GBs >>>>// no change to RAM, time a bit more sluggish after ‘reboot’ but >>>>still respectable >>>> >>>>sqlite> create temp table tdesc2 as select RowID from test order >by >>>>RowID desc; >>>>Run Time: real 117.765 user 28.265625 sys 13.828125 >>>>sqlite> .shell FlushMem >>>>Available RAM - pre flush = 10.5519 GBs - post flush = 14.6888 GBs >>>>// RAM drops 4+ GB, time is woeful. Why? >>>> >>>>// repeat above 2 ‘create temp table’ commands to demonstrate not >>>>fluke >>>> >>>>sqlite> create temp table tasc3 as select RowID from test order by >>>>RowID; >>>>Run Time: real 41.747 user 26.562500 sys 10.625000 >>>>sqlite> .shell FlushMem >>>>Available RAM - pre flush = 14.78 GBs - post flush = 14.754 GBs >>>>// no change to RAM, time a bit more sluggish after ‘reboot’ but >>>>still respectable >>>> >>>>sqlite> create temp table tdesc3 as select RowID from test order >by >>>>RowID desc; >>>>Run Time: real 118.282 user 29.406250 sys 13.765625 >>>>sqlite> .shell FlushMem >>>>Available RAM - pre flush = 10.6947 GBs - post flush = 14.5856 GBs >>>>// RAM drops 4- GB, time is woeful. Why? >>>> >>>>sqlite> .exit >>>> >>>> >>>> >>>>Why is using ‘RowID desc’ so much slower than ‘RowID asc’ after a >>>>‘reboot’? I get the impression Windows is caching the pages from >>the >>>>‘desc’ but not the ‘asc’ and that’s what’s slowing it down. On >this >>>>particular journey I’ve come across tables where the reverse was >>the >>>>case (I.e. it was the ‘asc’ that was woefully slow). >>>> >>>>Anyone cast any light on this? >>>>_______________________________________________ >>>>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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
