On Jun 14, 2018, at 12:31 PM, x <tam118...@hotmail.com> wrote:
>
> It is Windows Defender I’m using
So does the symptom go away when you turn Defender off? I would not expect it
to, but let’s close the loop on this, okay?
> a ms surface pro 4 with 16 GB ram and 512 GB SSD
I think we can provisionally rule out hardware problems, then. No spinning
rust, and it’s too new to be running into SSD leveling problems.
> GlobalMemoryStatusEx(&status);
> return status.ullAvailPhys / (1024.0 * 1024 * 1024);
That’s going to exclude pages taken by the OS page cache, which I believe is
important here.
> 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
That looks like the Windows page cache to me. If you write the test DB to disk
instead of holding it in memory, it’s 4.1 GiB, just about exactly your delta.
Therefore, I believe Windows is at times caching your whole DB for its own
dubious purposes.
Keep in mind that computers are just very fast idiots. You cannot expect
intelligent behavior from them. They don’t know what you’re trying to
accomplish; they can only recite some tricks they’ve been taught by nerds.
By default, SQLite will only cache a smidge under 2 GiB, so it cannot explain
your result:
https://sqlite.org/pragma.html#pragma_cache_size
> 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?
Try doing the test in reverse: flush the caches, do the descending-order copy,
then do the ascending test. Do the timings flip?
The actual comparison adds only an integer negation operation down in SQLite’s
VDBE in DESC mode. (Currently line 2147 in src/vdbe.c, within the OP_Compare
opcode implementation.) That should cause an immeasurable difference in your
test.
One possible explanation is that a reverse sort will cause Windows to read some
sequences of memory pages in reverse order, which might play havoc with the
CPU’s precaching logic.
Remember those fast idiots? One of them is in your CPU, and its job is to try
and figure out which RAM lines the programs you are running will want next, so
that it can pull them into the L1-3 caches ahead of time, since RAM accesses
are about 250x slower than register accesses.
I highly recommend that you watch this non-trivial presentation:
https://www.youtube.com/watch?v=4_smHyqgDTU
(“Memory and Caches” by Matt Godbolt, a person you should know if you do not
already.)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
