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
