Re: [sqlite] .timer explanation anywhere

[Keith Medcalf]

On Windows the GetProcessTimes Win32 API is used to get the user and kernel 
(sys) times for the current process since getrusage only exists on unix-like 
platforms.  In all cases the precision and accuracy are limited by the 
underlying OS timer accuracy.  

The vfs call to get the current time is limited to milliseconds notwithstanding 
that the underlying precision and accuracy of the underlying OS call may be 
different than 1 millisecond.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer explanation anywhere

[Keith Medcalf]

According to the code in shell.c the .timer on/off sets a flag that tells 
whether you want timer data printed or not, and then for each statement:
 
if .timer is turned on
  save the current wall clock and getrusage times (usr and sys times)
execute the statement.
if .timer is turned on
  get the new wall clock and getrsuage times
  display the difference between the new and old values


sort of like you would know how long X() took (in wall clock elapsed seconds) 
if you did:

begin = time();
X();
finish = time();
elapsed = finish - begin;

Just that .timer saves and reports three values provided by the underlying 
Operating System, not just one.  "real" is the current time (in seconds) 
reported by the underlying OS, and user/sys are the underlying times in seconds 
reported by the Operating System getrusage call for user/sys CPU usage times 
for the current process.

-- 
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  On
>Behalf Of Jose Isaias Cabrera
>Sent: Thursday, 13 February, 2020 06:48
>To: SQLite mailing list 
>Subject: [sqlite] .timer explanation anywhere
>
>
>Greetings.
>
>I was searching on sqlite.org for [sqlite command line tool .timer
>explanation] and found nothing. I also searched on the internet and found
>an old thread[1] of when .timer had just two entries:
>
>CPU Time: user 880.710398 sys 353.260288
>
>And, although, there is some good information there, I would like for us
>to have a solid answer somewhere. :-)  Maybe even explain it on the site
>[2], or better yet, have an option on .timer (on|off|?) to explain each
>piece of the output. One-liners will suffice.
>
>I also found this other interesting post [3], which I think is not
>totally correct, but I will let you guys explain why it is true.  Or, at
>least, if it has some truth in it.
>
>Thanks for your support.
>
>josé
>
>[1] http://sqlite.1065341.n5.nabble.com/timer-on-in-the-shell-tool-
>td79626.html
>[2]
>https://www.sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_
>[3] https://stackoverflow.com/questions/40329106/how-to-measure-the-
>execution-time-of-each-sql-statement-query-in-sqlite
>___
>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] .timer explanation anywhere

[Jose Isaias Cabrera]

Greetings.

I was searching on sqlite.org for [sqlite command line tool .timer explanation] 
and found nothing. I also searched on the internet and found an old thread[1] 
of when .timer had just two entries:

CPU Time: user 880.710398 sys 353.260288

And, although, there is some good information there, I would like for us to 
have a solid answer somewhere. :-)  Maybe even explain it on the site [2], or 
better yet, have an option on .timer (on|off|?) to explain each piece of the 
output. One-liners will suffice.

I also found this other interesting post [3], which I think is not totally 
correct, but I will let you guys explain why it is true.  Or, at least, if it 
has some truth in it.

Thanks for your support.

josé

[1] http://sqlite.1065341.n5.nabble.com/timer-on-in-the-shell-tool-td79626.html
[2] https://www.sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_
[3] 
https://stackoverflow.com/questions/40329106/how-to-measure-the-execution-time-of-each-sql-statement-query-in-sqlite
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

No, I have not.  The .timer discrepancy is due to the overhead of memory 
management which is done via DPC's and thus they accrue to the Supervisor and 
not the user process, the user process sees it as missing time.  The underlying 
issue with the cache stride management has not been addressed yet ...

---
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 curmudgeon
>Sent: Saturday, 22 September, 2018 08:17
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] .timer
>
>Keith, did you ever get any feedback from ms about this bug? There's
>certainly been no windows update that solved the problem.
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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

Re: [sqlite] .timer

[curmudgeon]

Keith, did you ever get any feedback from ms about this bug? There's
certainly been no windows update that solved the problem.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

The following pure python code does the same thing, memmapping the file when 
reading backwards ... works in Python 2 and 3, 32 and 64 bit.

Emulates what sqlite3 is doing as closely as I can manage.  As long as the mmap 
fits in memory it does not seem to affect performance.

---//---
from __future__ import absolute_import, print_function, division, 
unicode_literals

import random
import time
import os
import sys

if sys.version_info.major > 2:
xrange = range

blocksize = 4096
blocks = 1024 * 4096

buffer = []
for i in xrange(blocksize):
buffer.append(chr(random.randint(ord('A'), ord('z'
buffer = ''.join(buffer)

if sys.version_info.major > 2:
buffer = buffer.encode('utf_8')

if os.path.exists('junk.dat'):
print('Deleting junk.dat test file')
os.unlink('junk.dat')

if not os.path.exists('junk.dat'):
print('Creating 0 length junk.dat test file')
f = open('junk.dat', 'wb')
f.close()

f = open('junk.dat', 'rb+', buffering=0)

print('Writing File Forward', end=' ')
st = time.time()
for i in xrange(blocks):
f.seek(i * blocksize)
f.write(buffer)
f.flush()
os.fsync(f.fileno())
print(time.time() - st, 'seconds')
print()

def readforward():
print('Reading File Forward', end=' ')
st = time.time()
for i in xrange(blocks):
f.seek(i * blocksize)
f.read(blocksize)
f.flush()
os.fsync(f.fileno())
print(time.time() - st, 'seconds')
print()

def readbackwards():
print('Reading File Backward', end=' ')
st = time.time()
for i in xrange(blocks -1 ,-1, -1):
f.seek(i * blocksize)
f.read(blocksize)
f.flush()
os.fsync(f.fileno())
print(time.time() - st, 'seconds')
print()

readforward()
readbackwards()
readforward()
readbackwards()
readbackwards()
readforward()

f.close()
---//---


---
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 David Raymond
>Sent: Monday, 18 June, 2018 07:10
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>I haven't grasped all the fancy memory talk that's been going on
>here, but I have one request. Would you try the slowdown tests with a
>SQLite version compiled with...
>SQLITE_DEFAULT_MMAP_SIZE=0
>SQLITE_MAX_MMAP_SIZE=0
>...and see if anything changes? I started compiling with those
>options after some similar speed issues "back when" and things seem
>to have cleared up since then. I'm curious if it's because I added
>that or if it's just a coincidence.
>___
>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

Re: [sqlite] .timer

[Keith Medcalf]

These are with SQLITE3's memmap turned off (SQLITE_DEFAULT_MMAP_SIZE 0).  I set 
the MAX_SIZE to 0 as well and it made no difference.

Windows is memmapping the file by 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 David Raymond
>Sent: Monday, 18 June, 2018 07:10
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>I haven't grasped all the fancy memory talk that's been going on
>here, but I have one request. Would you try the slowdown tests with a
>SQLite version compiled with...
>SQLITE_DEFAULT_MMAP_SIZE=0
>SQLITE_MAX_MMAP_SIZE=0
>...and see if anything changes? I started compiling with those
>options after some similar speed issues "back when" and things seem
>to have cleared up since then. I'm curious if it's because I added
>that or if it's just a coincidence.
>___
>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

Re: [sqlite] .timer

[David Raymond]

I haven't grasped all the fancy memory talk that's been going on here, but I 
have one request. Would you try the slowdown tests with a SQLite version 
compiled with...
SQLITE_DEFAULT_MMAP_SIZE=0
SQLITE_MAX_MMAP_SIZE=0
...and see if anything changes? I started compiling with those options after 
some similar speed issues "back when" and things seem to have cleared up since 
then. I'm curious if it's because I added that or if it's just a coincidence.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Keith, I posted I was wrong about the limit offset comparison between 23.1 and 
24.0 but I’m unsure where I  gave you the impression the remainder of your post 
on the subject would be news to me. I’m well aware sqlite has to read all the 
rows to reach that offset. My point was that if we were able to specify it 
shouldn’t be buffered (I.e. it did not have to be saved in memory) then we’d 
maybe be spared the missing 86 secs (in the worst case test). I certainly 
wasn’t advocating that having offset  in a 1 table was a good 
strategy.

Thanks for reporting the bug. Hopefully something will be done but I suspect 
it’s been around a long time. I read three threads today complaining about 
sqlite’s cold cache performance. They were many years old going back as far as 
2006.

I disagree about the severity though. It’s devastated my rowid collection 
methods. I’m averse to pagination as I want to see the record number / count at 
the bottom of my grid and the option to move the scrollbar thumb track to any 
point and have that page come up instantly with the record number being known. 
It all worked fine until I tried making the collection from both ends (thus 
making pages near the top / bottom of the table accessible immediately) and 
having them meet in the middle. It was at that point I discovered the 
descending queries were crippling the performance.

Thanks again for all your work on this.

Tom

From: Keith Medcalf<mailto:kmedc...@dessus.com>
Sent: 17 June 2018 16:02
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] .timer


No, when you use OFFSET you are reading and discarding rows.  There is no 
difference between:

select * from t;

and discarding all the results except the last row, and,

select * from t limit ,1;

for a table containing 1 rows.  In both cases you have to read the 
entire table in order to find the last row.

However,

select * from t order by _rowid_ desc limit 1;

returns the last row directly rather than requiring the entire table to be read 
and will be fast no matter whether the file is cached or not.

The optimization was for LIMIT without the OFFSET, meaning that you know you 
only want so many rows of the results from the beginning, not at some offset 
(which increases the number of rows included, they are just not returned to you 
-- being discarded -- they still have to be read and processed) by not even 
calculating the rows beyond the LIMIT, if those can be determined and primarily 
affects complicated queries.

I have fed the bug report for the Windows cache handling back to the OEM where 
it will get to Microsoft.  While not severe, it does affect the usage of the 
FileSystem cache and when triggered under memory pressure can force page 
eviction (or swapping) that is unnecessary or even detrimental.  I suspect it 
was not found previously because the specific access pattern for a file is 
rather uncommon -- however that should make it easy to find and fix I should 
think.

However, what you are talking about here is now the difference between doing 
physical I/O and avoiding I/O by reading from a cache in RAM.

I/O is slow and the best way to optimize it (speed it up) is not to do it.  
(Gee that is old, from the 1960's I believe -- cannot remember who to attribute 
it to though).

---
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: Sunday, 17 June, 2018 06:35
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>It’s like having a world class soccer team and having to play in a
>league where the players are forced to wear stiletto heels. And it
>gets worse.
>
>Richard was saying he had sped up LIMIT queries in 3.24. I checked
>this out and was running LIMIT queries in sqlite expert (3.23.1) and
>my app (3.24). The former was taking 3 times as long to run the
>queries (not sure how much of that was down to improvements or sqlite
>expert). Anyway, I was getting quite excited until I realised I was
>conducting the test with a warm cache. When I tried the same with a
>cold cache I felt depressed even though the factor of 3 was still
>evident.
>
>The code below will illustrate. In one case it takes 109 secs to
>retrieve one record from a table that only took 77 secs to create.
>Keith mentioned earlier he was unable to test FILE_FLAG_NO_BUFFERING
>because of some setting in sqlite3. While I’ve no experience of the
>ramifications of setting this flag the LIMIT OFFSET looks like it’s
>tailor made for such a setting. Short of getting Microsoft to fix
>this, maybe what we need is a ‘pragma buffering = on/off’ although
>I’ve no idea if that’s possible or the difficulty involved.
>
>SQLite versi

Re: [sqlite] .timer

[Keith Medcalf]

No, when you use OFFSET you are reading and discarding rows.  There is no 
difference between:

select * from t;

and discarding all the results except the last row, and,

select * from t limit ,1;

for a table containing 1 rows.  In both cases you have to read the 
entire table in order to find the last row.  

However,

select * from t order by _rowid_ desc limit 1;

returns the last row directly rather than requiring the entire table to be read 
and will be fast no matter whether the file is cached or not.

The optimization was for LIMIT without the OFFSET, meaning that you know you 
only want so many rows of the results from the beginning, not at some offset 
(which increases the number of rows included, they are just not returned to you 
-- being discarded -- they still have to be read and processed) by not even 
calculating the rows beyond the LIMIT, if those can be determined and primarily 
affects complicated queries.

I have fed the bug report for the Windows cache handling back to the OEM where 
it will get to Microsoft.  While not severe, it does affect the usage of the 
FileSystem cache and when triggered under memory pressure can force page 
eviction (or swapping) that is unnecessary or even detrimental.  I suspect it 
was not found previously because the specific access pattern for a file is 
rather uncommon -- however that should make it easy to find and fix I should 
think.

However, what you are talking about here is now the difference between doing 
physical I/O and avoiding I/O by reading from a cache in RAM.  

I/O is slow and the best way to optimize it (speed it up) is not to do it.  
(Gee that is old, from the 1960's I believe -- cannot remember who to attribute 
it to though).

---
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: Sunday, 17 June, 2018 06:35
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>It’s like having a world class soccer team and having to play in a
>league where the players are forced to wear stiletto heels. And it
>gets worse.
>
>Richard was saying he had sped up LIMIT queries in 3.24. I checked
>this out and was running LIMIT queries in sqlite expert (3.23.1) and
>my app (3.24). The former was taking 3 times as long to run the
>queries (not sure how much of that was down to improvements or sqlite
>expert). Anyway, I was getting quite excited until I realised I was
>conducting the test with a warm cache. When I tried the same with a
>cold cache I felt depressed even though the factor of 3 was still
>evident.
>
>The code below will illustrate. In one case it takes 109 secs to
>retrieve one record from a table that only took 77 secs to create.
>Keith mentioned earlier he was unable to test FILE_FLAG_NO_BUFFERING
>because of some setting in sqlite3. While I’ve no experience of the
>ramifications of setting this flag the LIMIT OFFSET looks like it’s
>tailor made for such a setting. Short of getting Microsoft to fix
>this, maybe what we need is a ‘pragma buffering = on/off’ although
>I’ve no idea if that’s possible or the difficulty involved.
>
>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
>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<1)
>   ...> select * from cte;
>Run Time: real 77.348 user 68.156250 sys 8.234375
>
>sqlite> .shell flushmem
>Available RAM - pre flush = 13.6147 GBs - post flush = 14.1428 GBs
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 30.722 user 3.515625 sys 17.609375
>
>sqlite> .shell flushmem
>Available RAM - pre flush = 14.2898 GBs - post flush = 14.4573 GBs
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 33.748 user 5.00 sys 18.078125
>
>sqlite> .shell flushmem
>Available RAM - pre flush = 14.4758 GBs - post flush = 14.4825 GBs
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 44.493 user 5.281250 sys 25.625000
>
>Above results unimpressive and inconsistent.
>
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 7.269 user 2.609375 sys 4.656250
>
>sqlite> select rowid from test order by rowid limit ,1;
>

Re: [sqlite] .timer

[x]

>Richard was saying he had sped up LIMIT queries in 3.24. I checked this out 
>and was running LIMIT queries >in sqlite expert (3.23.1) and my app (3.24). 
>The former was taking 3 times as long to run the queries (not >sure how much 
>of that was down to improvements or sqlite expert). Anyway, I was getting 
>quite excited >until I realised I was conducting the test with a warm cache. 
>When I tried the same with a cold cache I felt >depressed even though the 
>factor of 3 was still evident.

I’m talking drivel there. Having checked I must’ve been doing the sqlite expert 
queries on a cold cache and the ones in my app on a warm cache.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

It’s like having a world class soccer team and having to play in a league where 
the players are forced to wear stiletto heels. And it gets worse.

Richard was saying he had sped up LIMIT queries in 3.24. I checked this out and 
was running LIMIT queries in sqlite expert (3.23.1) and my app (3.24). The 
former was taking 3 times as long to run the queries (not sure how much of that 
was down to improvements or sqlite expert). Anyway, I was getting quite excited 
until I realised I was conducting the test with a warm cache. When I tried the 
same with a cold cache I felt depressed even though the factor of 3 was still 
evident.

The code below will illustrate. In one case it takes 109 secs to retrieve one 
record from a table that only took 77 secs to create. Keith mentioned earlier 
he was unable to test FILE_FLAG_NO_BUFFERING because of some setting in 
sqlite3. While I’ve no experience of the ramifications of setting this flag the 
LIMIT OFFSET looks like it’s tailor made for such a setting. Short of getting 
Microsoft to fix this, maybe what we need is a ‘pragma buffering = on/off’ 
although I’ve no idea if that’s possible or the difficulty involved.

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
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<1)
   ...> select * from cte;
Run Time: real 77.348 user 68.156250 sys 8.234375

sqlite> .shell flushmem
Available RAM - pre flush = 13.6147 GBs - post flush = 14.1428 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 30.722 user 3.515625 sys 17.609375

sqlite> .shell flushmem
Available RAM - pre flush = 14.2898 GBs - post flush = 14.4573 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 33.748 user 5.00 sys 18.078125

sqlite> .shell flushmem
Available RAM - pre flush = 14.4758 GBs - post flush = 14.4825 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 44.493 user 5.281250 sys 25.625000

Above results unimpressive and inconsistent.

sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 7.269 user 2.609375 sys 4.656250

sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 7.230 user 2.859375 sys 4.375000

Above 2 with warm cache.

sqlite> .shell flushmem
Available RAM - pre flush = 14.497 GBs - post flush = 14.4306 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 103.339 user 4.062500 sys 20.671875

sqlite> .shell flushmem
Available RAM - pre flush = 10.1498 GBs - post flush = 14.4109 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 92.210 user 3.812500 sys 15.50

sqlite> .shell flushmem
Available RAM - pre flush = 10.3382 GBs - post flush = 14.5637 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 109.676 user 3.796875 sys 21.562500

Woeful results and again inconsistent.

sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 7.405 user 2.062500 sys 5.343750

sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 7.440 user 2.546875 sys 4.890625

Above 2 with warm cache.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

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  on
>behalf of Keith Medcalf 
>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 4 for the stop on the generate series.
>This results in a 16GB database, (you can make this 1 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.  4 is the number of rows.  1 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=4;
>---
>--- OR
>---
>insert into t
>with recursive cte(x,y) as
> (values(1,'012345678901234567890123456789')
>  union all select x+1,y from cte where x<4)
>select * from cte;
>---
>---
>  create temp table ta1 as
>  select _rowid_
>from t
>order by _rowid_ asc;
>---
>  crea

Re: [sqlite] .timer

[x]

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  on behalf of 
Keith Medcalf 
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 4 for the stop on the generate series.  This 
results in a 16GB database, (you can make this 1 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.  4 is the number of rows.  1 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=4;
---
--- OR
---
insert into t
with recursive cte(x,y) as
 (values(1,'012345678901234567890123456789')
  union all select x+1,y from cte where x<4)
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
&

Re: [sqlite] .timer

[x]

> Yeah, I had a lot of problems with the fileio.c extension after the fsdir 
> virtual table was added.  It needs a >header file "test_windirent.h" to be 
> available.

That was the first thing I had to fix. The compiler couldn’t find the 
test_windirent.h file. I added the .../sqlite/src path to my system include 
path and got beyond that. I’ve no idea how to resolve the errors though and I’m 
still unsure of working with shell.c.

[bcc64 Error] fileio.c(507): unknown type name 'DIR'
[bcc64 Error] fileio.c(661): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(663): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(666): incomplete definition of type 'struct DIRENT'
fileio.c(659): forward declaration of 'struct DIRENT'
>Failed


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

Well, I tried it using a db file that was bigger than would fit in all 
available memory (ie, was not really cacheable) and where the whole thing could 
not be memory mapped.  Good news is that Windows did not crash.  The memory 
manager seemed to realize that it was on the edge of doing so an after evicting 
everything it could it appeared to "limit" the size of the memory mapped region 
so that a couple of GB (just over 2) remained available for re-assignment out 
of what was left of the cache (about 3 GB).  However, performance was, to say 
the least, somewhat wonky.  It is still running but there appears to be lots of 
crap Windows is doing in the background that it making the performance weird, 
to say the least.  (It is very slow to deal with these over 32 GB file sizes 
and takes quite a while, even with an SSD, for the commands to execute, and 
Windows I/O is not very efficient to begin with).

It does however appear to eventually release all the memory it is using and 
return to normal when the opposite file scan happens, but as I said, it really 
appears to mess up the otherwise deterministic timings by adding lots of 
unattributed "wait" time during the file scanning operations.

I tried running other memory intensive apps at the same time it appeared at 
steady-state (ie, with only a couple of GB available and the huge memory mapped 
file), and they all appeared to work (they caused a new steady state that was 
about the same, and when they were terminated Windows returned the unused 
memory to the free pool where is was re-used to get back to where it was 
before).  In the usage of memory there appeared to be a couple of cliffs at 
which Windows internally changed its memory management algorithms, which is 
interesting, the major one occurring at about 50% physical RAM usage after 
which it appears to the "converting" the cached file to a memory mapped region 
at half speed.

---
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: Saturday, 16 June, 2018 11:56
>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 4 for the stop on the generate series.
>This results in a 16GB database, (you can make this 1 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.  4 is the number of rows.  1 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=4;
>---
>--- OR
>---
>inse

Re: [sqlite] .timer

[Keith Medcalf]

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 4 for the stop on the generate series.  This 
results in a 16GB database, (you can make this 1 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.  4 is the number of rows.  1 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=4;
---
--- OR
---
insert into t
with recursive cte(x,y) as
 (values(1,'012345678901234567890123456789')
  union all select x+1,y from cte where x<4)
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 ti

Re: [sqlite] .timer

[Keith Medcalf]

Yeah, I had a lot of problems with the fileio.c extension after the fsdir 
virtual table was added.  It needs a header file "test_windirent.h" to be 
available.  I had to do some fiddling to get it to compile properly using MinGW 
(GCC) on Windows.  I thought Richard had fixed it.

It also makes calls back into the sqlite3 core (the 
sqlite_win32_utf8_to_unicode) that cannot be resolved when compiling as an 
extension.  This has not been fixed yet. (but it does compile inline in shell.c 
and I have managed to get it added to the core sqlite3.c build).

It took me a while to get it to works with GCC.  However, it gets included in 
the shell.c automatically and I presume the shell.c compiles under MSVC so 
however it works it does work.  You may just need to make sure the 
test_windirent.c header is available 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 x
>Sent: Saturday, 16 June, 2018 08:06
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>I’m trying to add fileio.c to my core_init function so I can
>investigate if the readfile and writefile functions would help here.
>I’ve added
>
>#include “fileio.c”
>
>Inside the core_init function what do I add ?
>
>nErr +=
>sqlite3_auto_extension((void(*)())sqlite3_fileio_init); //
>??
>or
>nErr +=
>sqlite3_auto_extension((void(*)())sqlite3_readfile_init); //
>??
>nErr +=
>sqlite3_auto_extension((void(*)())sqlite3_writefile_init); //
>?
>
>When the compiler reaches the ‘#include “fileio.c” I get several
>error messages
>
>[bcc64 Error] fileio.c(507): unknown type name 'DIR'
>[bcc64 Error] fileio.c(661): incomplete definition of type 'struct
>DIRENT'
>  fileio.c(659): forward declaration of 'struct DIRENT'
>[bcc64 Error] fileio.c(662): incomplete definition of type 'struct
>DIRENT'
>  fileio.c(659): forward declaration of 'struct DIRENT'
>[bcc64 Error] fileio.c(662): incomplete definition of type 'struct
>DIRENT'
>  fileio.c(659): forward declaration of 'struct DIRENT'
>[bcc64 Error] fileio.c(663): incomplete definition of type 'struct
>DIRENT'
>  fileio.c(659): forward declaration of 'struct DIRENT'
>[bcc64 Error] fileio.c(666): incomplete definition of type 'struct
>DIRENT'
>  fileio.c(659): forward declaration of 'struct DIRENT'
>Failed
>Elapsed time: 00:00:01.3
>
>I can see these are defined in test_windirent.h and fileio.c has the
>line “include “test_windirent.h” so I don’t know why I’m getting
>these.
>___
>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

Re: [sqlite] .timer

[x]

I’m trying to add fileio.c to my core_init function so I can investigate if the 
readfile and writefile functions would help here. I’ve added

#include “fileio.c”

Inside the core_init function what do I add ?

nErr += sqlite3_auto_extension((void(*)())sqlite3_fileio_init); // 
??
or
nErr += sqlite3_auto_extension((void(*)())sqlite3_readfile_init); // 
??
nErr += sqlite3_auto_extension((void(*)())sqlite3_writefile_init); // 
?

When the compiler reaches the ‘#include “fileio.c” I get several error messages

[bcc64 Error] fileio.c(507): unknown type name 'DIR'
[bcc64 Error] fileio.c(661): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(662): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(663): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
[bcc64 Error] fileio.c(666): incomplete definition of type 'struct DIRENT'
  fileio.c(659): forward declaration of 'struct DIRENT'
Failed
Elapsed time: 00:00:01.3

I can see these are defined in test_windirent.h and fileio.c has the line 
“include “test_windirent.h” so I don’t know why I’m getting these.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Simon Slavin]

On 16 Jun 2018, at 8:12am, x  wrote:

> In my travels the only thing I found was a tumbleweed thread on stackoverflow 
> where an sqlite user was complaining that the first time he ran a query it 
> took 10 times longer than subsequent executions. He said he raised the point 
> on this forum but was told it was a windows issue. [snip]

Caching.  The first time through it secures enough memory to store stuff.  
Although it clears this memory out at the end of the first run, when it needs 
that memory again it has a beautiful long clear block all ready to allocate in 
one operation.

> Simon, the .333 didn’t help. I was unable to test the memory stick as the 
> only one I had wouldn’t accept a file of MyTemp.db size. There was enough 
> room so I’m not sure what the problem was.

You need at least twice as much free space as your intended filesize.

But at least you eliminated one of the known foibles of windows, in that it 
treats files it expects to be databases weirdly when it tries to cache them.

From everything I've read in this thread, your unexpected timings are all 
related to Windows caching foibles.  And you can't turn it off, so you may as 
well just get used to it.  The hope on the horizon is that each version of 
Windows caches differently, so a future version may do something different.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Keith, many thanks for your detective work. Much of this is way above my head 
but, even if a solution doesn’t exist, your confirmation that I’m not mad is a 
good start to the day for me.

Given how bad the problem is I’m surprised there’s not a lot more about it on 
the web. In my travels the only thing I found was a tumbleweed thread on 
stackoverflow where an sqlite user was complaining that the first time he ran a 
query it took 10 times longer than subsequent executions. He said he raised the 
point on this forum but was told it was a windows issue.

> And you are correct, it appears to be related to "reading backwards" somehow.

I’m not sure I agree with that. I’m sure I’ve had cases in the past where the 
problem was reversed. Test 4 in my last post was an ascending case which slowed 
considerably after changing from storing the RowIDs in a tmpfile to storing in 
a vector. That one was really freaky as a speed up would’ve been expected.

Simon, the .333 didn’t help. I was unable to test the memory stick as the only 
one I had wouldn’t accept a file of MyTemp.db size. There was enough room so 
I’m not sure what the problem was.



From: Keith Medcalf<mailto:kmedc...@dessus.com>
Sent: 15 June 2018 23:22
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 clos

Re: [sqlite] .timer

[Keith Medcalf]

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 commit

Re: [sqlite] .timer

[Keith Medcalf]

That will do nothing.  

I explicitly set the cache mode.  I always disable all "Mystical Microsoft 
Magical Behaviour" wherever I can find it -- though there are a very few 
"autotuning" things that work properly by themselves, just not many (unless 
they originated with an actual Engineer rather than a Microsoftian).  

I despise magical behaviour since I discovered long ago that it usually does 
not work properly -- and that holds true to this very day.  

If I want something then I will set in explicitly and not depend on 
Microsoft-AutoMagic (prayer mode).  

Determinism and reduction of complexity is how one obtains reliability.  
Enabling (or not disabling) "Magic Modes" is (a) non-deterministic and (b) 
increases complexity, thus reducing reliability and reproducibility.

---
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 Simon Slavin
>Sent: Friday, 15 June, 2018 17:10
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>On 15 Jun 2018, at 11:21pm, Keith Medcalf 
>wrote:
>
>> And you are correct, it appears to be related to "reading
>backwards" somehow.
>
>If you're comparing the two directions of reading the same file,
>there are ways to fake out Windows' read-ahead tricks and see if
>they're what's messing up your program.
>
>A) Plug in a Flash drive and create/open the file on that instead of
>the boot drive.
>
>B) Rename the file to something which doesn't have a database-like
>extension.  I suggest an extension of ".333'.
>
>Try those -- separately not together -- and see if you see any
>difference.
>
>Simon.
>___
>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

Re: [sqlite] .timer

[Keith Medcalf]

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 i

Re: [sqlite] .timer

[Simon Slavin]

On 15 Jun 2018, at 11:21pm, Keith Medcalf  wrote:

> And you are correct, it appears to be related to "reading backwards" somehow.

If you're comparing the two directions of reading the same file, there are ways 
to fake out Windows' read-ahead tricks and see if they're what's messing up 
your program.

A) Plug in a Flash drive and create/open the file on that instead of the boot 
drive.

B) Rename the file to something which doesn't have a database-like extension.  
I suggest an extension of ".333'.

Try those -- separately not together -- and see if you see any difference.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

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 < 

Re: [sqlite] .timer

[x]

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

Re: [sqlite] .timer

[Keith Medcalf]

Your other "right", the one on the left :)


---
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 07:56
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>
>You are using Windows 10?  It is on the "Performance" tab, select the
>wee graph on the right for "Memory".  In the detail, right underneath
>"Available" and beside "Committed" at the bottom where all the text
>is.  Oh, you have to be in "more details" view, not in the "simple"
>default view ...
>
>---
>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 curmudgeon
>>Sent: Friday, 15 June, 2018 07:13
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: Re: [sqlite] .timer
>>
>>PS I can't find 'cache' in task manager.
>>
>>
>>
>>--
>>Sent from: http://sqlite.1065341.n5.nabble.com/
>>___
>>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

Re: [sqlite] .timer

[Keith Medcalf]

You are using Windows 10?  It is on the "Performance" tab, select the wee graph 
on the right for "Memory".  In the detail, right underneath "Available" and 
beside "Committed" at the bottom where all the text is.  Oh, you have to be in 
"more details" view, not in the "simple" default view ...

---
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 curmudgeon
>Sent: Friday, 15 June, 2018 07:13
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] .timer
>
>PS I can't find 'cache' in task manager.
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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

Re: [sqlite] .timer

[Keith Medcalf]

>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

shell.c is the "application" that gets linked with "sqlite3.c" SQLite Library 
to create "sqlite3.exe".  You can create a file which has shell.c appended to 
the end of sqlite3.c (which already has the additional series.c and core_init 
appended) and compile the result as an executable to get sqlite3.exe, or you 
can compile the two separately and link them together to form an sqlite3.exe 
that has your additional extensions available.

>“The generate_series(START,END,STEP) table-valued
>function is a loadable
>extension included in the SQLite
>source tree, and compiled into the command-line
>shell.”

I don't think that the CLI includes generate_series, though you can compile 
series.c as a loadable extension (its own DLL) and load it into the CLI.

>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.

That is very strange indeed!

>Having widows defender turned off changed nothing.

Ok, so it is not defender.

Something however is interfering with being able to read a file properly and 
that strikes me as very strange -- especially since on an SSD you should be 
able to read forward-sequential, backward-sequential, and in completely random 
order in about the same elapsed time ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[curmudgeon]

PS I can't find 'cache' in task manager.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

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  on behalf of 
Keith Medcalf 
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;
>
>

Re: [sqlite] .timer

[David Raymond]

2 MB, not 2 GB.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Warren Young
Sent: Thursday, June 14, 2018 5:54 PM
To: SQLite mailing list
Subject: Re: [sqlite] .timer

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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

The increase/decrease  in memory is almost certainly the cache (after running 
the command once and before flushing look and see what Task Manager says for 
"Cached", then look again after you do the flush and see if it releases it.  
This is memory that would otherwise be unused being used by the filesystem 
cache.  However, since USER+SYS is not equal or close to REAL, that means that 
there is something else going on that is consuming time (waiting, defender, or 
that something is being dispatched as a DPC and its execution time is not being 
attributed to your process).


---
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:23
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>>Actually I cannot reproduce even if I turn off forced RANDOM mode
>for the cache, reduce the size of the >sqlite3 cache to the default,
>and make sure the temp_store is on disk (not my default).
>
>Are you rebooting or flushing the cache between the commands? I don’t
>have any problems (except maybe on the first call) if I don’t flush.
>
>>I do note, however, that the actual CPU used is relatively constant
>(USER+SYS) and that it is the REAL time >only that is going "bonkers"
>which suggests some process other than sqlite is what is causing the
>>slowdown.  It could be something the hardware is doing -- I have
>never used a "tablet" as if it were a >computer ...
>
>After rebooting it doesn’t matter how long I wait before running the
>test. If it’s some background task it must surely be triggered by
>what I’m doing. That said, if the slowness and increasing /
>decreasing memory is down to windows caching the data why is ‘sys’
>not able to include it?
>___
>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

Re: [sqlite] .timer

[Keith Medcalf]

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  on
>behalf of Keith Medcalf 
>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 r

Re: [sqlite] .timer

[x]

>Actually I cannot reproduce even if I turn off forced RANDOM mode for the 
>cache, reduce the size of the >sqlite3 cache to the default, and make sure the 
>temp_store is on disk (not my default).

Are you rebooting or flushing the cache between the commands? I don’t have any 
problems (except maybe on the first call) if I don’t flush.

>I do note, however, that the actual CPU used is relatively constant (USER+SYS) 
>and that it is the REAL time >only that is going "bonkers" which suggests some 
>process other than sqlite is what is causing the >slowdown.  It could be 
>something the hardware is doing -- I have never used a "tablet" as if it were 
>a >computer ...

After rebooting it doesn’t matter how long I wait before running the test. If 
it’s some background task it must surely be triggered by what I’m doing. That 
said, if the slowness and increasing / decreasing memory is down to windows 
caching the data why is ‘sys’ not able to include it?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

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  on behalf of 
Keith Medcalf 
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  on
>behalf of Keith Medcalf 
>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_A

Re: [sqlite] .timer

[Keith Medcalf]

Actually I cannot reproduce even if I turn off forced RANDOM mode for the 
cache, reduce the size of the sqlite3 cache to the default, and make sure the 
temp_store is on disk (not my default).

I do note, however, that the actual CPU used is relatively constant (USER+SYS) 
and that it is the REAL time only that is going "bonkers" which suggests some 
process other than sqlite is what is causing the slowdown.  It could be 
something the hardware is doing -- I have never used a "tablet" as if it were a 
computer ...

---
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  on
>behalf of Keith Medcalf 
>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

Re: [sqlite] .timer

[Keith Medcalf]

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  on
>behalf of Keith Medcalf 
>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 maili

Re: [sqlite] .timer

[x]

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  on behalf of 
Keith Medcalf 
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<1)
>   ...> select * from cte;
>Run Time: real 34.619 user 34.625000 sys 0.00
>sqlite> drop table test;
>Run Time: real 1.578 user 1.578125 sys 0.00
>sqlite> create table test (x integer, y text);
>Run Time: real 0.000 user 0.00 sys 0.00
>sqlite> insert into test select value,
>'012345678901234567890123456789' from generate_series where start=1
>and st

Re: [sqlite] .timer

[Warren Young]

On Jun 14, 2018, at 12:31 PM, x  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

Re: [sqlite] .timer

[Simon Slavin]

On 14 Jun 2018, at 10:04pm, Simon Slavin  wrote:

> Now do exactly the same test, but have the file stored on a USB Flash drive 
> instead of the boot volume.  You should be able to do something like
> 
> .open d:\MyTemp.db
> 
> See how this influences any change.

Oh, also try the same drive but a different filename which doesn't suggest a 
database:

.open MyTemp.333

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

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<1)
>   ...> select * from cte;
>Run Time: real 34.619 user 34.625000 sys 0.00
>sqlite> drop table test;
>Run Time: real 1.578 user 1.578125 sys 0.00
>sqlite> create table test (x integer, y text);
>Run Time: real 0.000 user 0.00 sys 0.00
>sqlite> insert into test select value,
>'012345678901234567890123456789' from generate_series where start=1
>and stop=1;
>Run Time: real 12.226 user 12.234375 sys 0.00
>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 ...
>
>>sqlit

Re: [sqlite] .timer

[Simon Slavin]

On 14 Jun 2018, at 7:31pm, x  wrote:

> 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).

Windows storage devices traditionally use read-ahead strategy.  This means that 
if you ask to read sector n they will sometimes read sector n+1 into cache at 
the same time on the assumption that you will need that next.  This strategy 
backfires bigtime when applied to solid state drives because unlike spinning 
rust disks, reading two sectors takes almost twice as long.

Your computer should know this, and shouldn't be trying to apply a read-ahead 
strategy but something may have gone wrong.

> Anyone cast any light on this?

Now do exactly the same test, but have the file stored on a USB Flash drive 
instead of the boot volume.  You should be able to do something like

.open d:\MyTemp.db

See how this influences any change.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

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<1)
   ...> select * from cte;
Run Time: real 34.619 user 34.625000 sys 0.00
sqlite> drop table test;
Run Time: real 1.578 user 1.578125 sys 0.00
sqlite> create table test (x integer, y text);
Run Time: real 0.000 user 0.00 sys 0.00
sqlite> insert into test select value, '012345678901234567890123456789' from 
generate_series where start=1 and stop=1;
Run Time: real 12.226 user 12.234375 sys 0.00
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.00 sys 0.00
sqlite> insert into test select value, '012345678901234567890123456789' from 
generate_series where start=1 and stop=1;
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.00 sys 3.656250
sqlite> create temp table tdesc1 as select rowid from test order by rowid desc;
Run Time: real 12.658 user 9.50 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.75 sys 3.25
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.ullAvailP

Re: [sqlite] .timer

[x]

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<1)
   ...> select * from cte;
Run Time: real 77.239 user 68.75 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.00
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

Re: [sqlite] .timer

[Warren Young]

On Jun 14, 2018, at 11:00 AM, Bob Friesenhahn  
wrote:
> 
> On Thu, 14 Jun 2018, Warren Young wrote:
> 
>> On Jun 14, 2018, at 8:36 AM, x  wrote:
>>> 
>>> It is indeed windows Ryan and at times we’re talking 120 secs versus 30 + 
>>> 14.
>> 
>> Are you using Windows Defender or some other antimalware solution?
> 
> Definitely a +1 on this one.  Beside Windows Defender, Windows 10's built-in 
> file indexing service…

To clarify, I was putting Windows Defender in a separate class from the more 
aggressive antimalware packages with that “or”.

While vetting these tests:

https://www.sqlite.org/fasterthanfs.html

we found that disabling Defender only impacted the native file I/O case.  
SQLite was just as fast with Defender enabled because the I/Os were internal to 
the file, which was kept open throughout the benchmark.  Thus, only one check 
was presumably made of  the SQLite DB, whereas the comparison against separate 
files was much slower with Defender enabled, since each of the 10 test 
files had to be checked for malware separately.  The numbers reported are with 
Defender disabled, but only the non-SQLite numbers are greatly affected.

I am simply speculating that there are antimalware products for Windows that 
will slow SQLite down, unlike Defender.  I couldn’t name one, having not run 
anything but Defender on my Windows boxes since it first came out.

If your application is closing and re-opening the SQLite DB frequently, then I 
would expect it to be impacted by Defender.  In the tests linked above, the 
impact from enabling Defender on the pile-o-files was roughly 10x, but since 
it’s a synthetic benchmark, your reported ~3x difference might still be due to 
this.  If so, keep the DB file open!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Bob Friesenhahn]

On Thu, 14 Jun 2018, Warren Young wrote:


On Jun 14, 2018, at 8:36 AM, x  wrote:


It is indeed windows Ryan and at times we’re talking 120 secs versus 30 + 14.


I can think of two good possibilities:

1. Are you using Windows Defender or some other antimalware solution?

If it’s a third-party product, some of those are very aggressive, and they may 
be poking around in the internals of the SQLite DB on every I/O, which adds 
tremendous overhead.  That overhead would be charged to another process, not to 
the system, giving your reported symptom.


Definitely a +1 on this one.  Beside Windows Defender, Windows 10's 
built-in file indexing service will open each new and updated file to 
inspect its content, consuming substantial CPU and I/O as well as 
blocking access to the content.  These actions are documented to only 
occur when it won't impact the user, but of course that is not true.


Something else which can take substantial time which is not attributed 
to the program is memory page faults.  These might not be attributed 
to the program (e.g. as 'sys' time) since a kernel driver performs the 
I/O for page faults.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Warren Young]

On Jun 14, 2018, at 8:36 AM, x  wrote:
> 
> It is indeed windows Ryan and at times we’re talking 120 secs versus 30 + 14.

I can think of two good possibilities:

1. Are you using Windows Defender or some other antimalware solution?

If it’s a third-party product, some of those are very aggressive, and they may 
be poking around in the internals of the SQLite DB on every I/O, which adds 
tremendous overhead.  That overhead would be charged to another process, not to 
the system, giving your reported symptom.

2. The other possibility is dodgy hardware, especially the disk holding the 
SQLite DB, such that the system is getting I/O interrupts with much greater 
latency than normal, causing it to do work in neither user space nor kernel 
space, but instead just waiting out wall clock time a lot due to jammed I/O 
buffers.

So: does the program do the same thing on a different computer?  If you cannot 
test on another computer, do you get anything interesting in the Windows Event 
Viewer?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

Exactly.  

REAL is the elapsed time according to the wall clock
USER is the actual time the CPU spent executing user code
SYS  is the actual time the CPU spent executing system code

In "modern" Operating Systems USER usually reflects CPU usage by your process 
while the CPU is in USER mode (non-protected, or executing at Ring-3 or below), 
while SYS reflects CPU usage by your process in SUPERVISOR mode (protected, or 
executing code at Ring-2 or above).

WAIT = REAL - (USER + SYS)

where WAIT is the amount of time that the program was not actually executing 
(ie, it was waiting for something to happen, whether that waiting occurred in 
user or system code).  Some I/O devices are co-processsed and interrupt driven 
or use third-party DMA, these will increase WAIT time.  Some I/O devices use 
I/O driven by the main CPU, and those devices will increase SYS time, even when 
they are waiting for something to happen.  Examples of CPU run devices (under 
Windows) are (almost) anything attached via USB, or non-interrupt driven 
printers.  Examples of co-processed devices are most (modern) SATA/SCSI type 
devices (especially if they use NCQ or Data Phase Disconnect).  Older IDE 
interfaces are usually CPU operated and not interrrupt driven or co-processed.

Some Ring-0 operations that occur in SUPERVISOR mode may be attributed to WAIT 
(ie, not show up in SYS) even though they are dependant on the CPU to carry 
them out, examples being "fiddling" with the Memory Mapper Page and Indirection 
tables (including SMM operations), and context switching, because although they 
may be triggered by your process, such fiddling cannot usually be attributed to 
a specific process.  

USER and SYS are the sum of all the CPU used in that mode by each core/thread, 
so it is possible for SYS+USER to exceed REAL even without explicit 
multithreading in your code (and even easier if you do have multiple threads).

The ratio between REAL and (USER + SYS) across all the processes running at the 
same time is called the Multiprogramming Ratio.

---
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 Dan Kennedy
>Sent: Thursday, 14 June, 2018 09:13
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] .timer
>
>On 06/14/2018 03:08 PM, Simon Slavin wrote:
>> On 14 Jun 2018, at 8:33am, x  wrote:
>>
>>> Could someone describe what the return values real, user and sys
>mean and why there’s sometimes a big difference between real and the
>sum of user & sys?.
>> [The following is simplified for clarity.]
>>
>> 'real' -- Elapsed time between the start and end of the command, as
>measured by the clock on your wall.  Sometimes called 'wall time'.
>>
>> The other two figures both concern just the process you're
>interested in, ignoring the many other things the computer is doing
>at the same time like seeing if you've clicked your mouse, updating
>your screen, checking to see if your laptop battery is going to run
>out, etc..
>>
>> 'user' -- Processor time taken by the command itself.  If you look
>at all the source code for that command, this is the time taken to
>run that source code.
>>
>> 'sys' -- Processor time taken to execute the system calls the
>command used.  If the command used system calls to find the current
>time, allocate memory, and write some bytes to a file, the amount of
>time each system call took contributes to 'sys', not 'user'.
>>
>> If 'sys' + 'user' > 'real', something weird happened.
>
>Multi-threaded apps often have (sys+user)>real. And you can
>build/configure SQLite to use multiple threads when sorting large
>amounts of data, so it is possible. Not terribly common though, I
>would
>think.
>
>> If 'sys' + 'user' < 'real', your computer is busy doing a lot of
>stuff in the background.  You probably have a printer plugged in, an
>ethernet or WiFi connection active, a keyboard and mouse pointer
>being monitored, etc..  This is normal on a modern computer,
>
>Another explanation is that your query had to load or sync data from
>or
>to the storage device, not just the OS cache. The "real" time
>increases
>while waiting on IO, but "user" and "sys" do not (since the CPU is
>idle).
>
>Dan.
>
>___
>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

Re: [sqlite] .timer

[Dan Kennedy]

On 06/14/2018 03:08 PM, Simon Slavin wrote:

On 14 Jun 2018, at 8:33am, x  wrote:


Could someone describe what the return values real, user and sys mean and why 
there’s sometimes a big difference between real and the sum of user & sys?.

[The following is simplified for clarity.]

'real' -- Elapsed time between the start and end of the command, as measured by 
the clock on your wall.  Sometimes called 'wall time'.

The other two figures both concern just the process you're interested in, 
ignoring the many other things the computer is doing at the same time like 
seeing if you've clicked your mouse, updating your screen, checking to see if 
your laptop battery is going to run out, etc..

'user' -- Processor time taken by the command itself.  If you look at all the 
source code for that command, this is the time taken to run that source code.

'sys' -- Processor time taken to execute the system calls the command used.  If 
the command used system calls to find the current time, allocate memory, and 
write some bytes to a file, the amount of time each system call took 
contributes to 'sys', not 'user'.

If 'sys' + 'user' > 'real', something weird happened.


Multi-threaded apps often have (sys+user)>real. And you can 
build/configure SQLite to use multiple threads when sorting large 
amounts of data, so it is possible. Not terribly common though, I would 
think.



If 'sys' + 'user' < 'real', your computer is busy doing a lot of stuff in the 
background.  You probably have a printer plugged in, an ethernet or WiFi 
connection active, a keyboard and mouse pointer being monitored, etc..  This is 
normal on a modern computer,


Another explanation is that your query had to load or sync data from or 
to the storage device, not just the OS cache. The "real" time increases 
while waiting on IO, but "user" and "sys" do not (since the CPU is idle).


Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

It is indeed windows Ryan and at times we’re talking 120 secs versus 30 + 14. 
This is related to the thread

http://sqlite.1065341.n5.nabble.com/This-is-driving-me-nuts-td102034.html

or at least the latter part of it.



I’m currently working on a repeatable shell demonstration to show how bad a 
light it shows sqlite in.




From: sqlite-users  on behalf of 
R Smith 
Sent: Thursday, June 14, 2018 10:34:58 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] .timer


On 2018/06/14 10:28 AM, x wrote:
> Thanks for the detail Simon. I’m consistently getting some really big 
> differences where user + sys < real. I’ll post another thread on it but it 
> does seem to be something windows is doing rather than sqlite.

If sys + user << real  -  you are probably running Windows. :)


On a more serious note, if I may - how big are those "big differences"
and on which OS and media and what kind(s) of query?


___
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

Re: [sqlite] .timer

[R Smith]

On 2018/06/14 10:28 AM, x wrote:

Thanks for the detail Simon. I’m consistently getting some really big differences 
where user + sys < real. I’ll post another thread on it but it does seem to be 
something windows is doing rather than sqlite.


If sys + user << real  -  you are probably running Windows. :)


On a more serious note, if I may - how big are those "big differences" 
and on which OS and media and what kind(s) of query?



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Simon Slavin]

On 14 Jun 2018, at 9:28am, x  wrote:

> Thanks for the detail Simon. I’m consistently getting some really big 
> differences where user + sys < real. I’ll post another thread on it but it 
> does seem to be something windows is doing rather than sqlite.

You're welcome.  It's barely worth the post here.  SQLite has some control over 
'user' and 'sys' but none over 'real'.  If your 'real' got too big, Windows is 
doing an update, or indexing files, or defragging a hard disk or something.  
I've seen a badly-written printer driver which checked ink levels every 5 
seconds instead of the intended 5 minutes.  That's the sort of thing that gives 
you bbd 'real' times.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Thanks for the detail Simon. I’m consistently getting some really big 
differences where user + sys < real. I’ll post another thread on it but it does 
seem to be something windows is doing rather than sqlite.




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Thursday, June 14, 2018 9:08:31 AM
To: SQLite mailing list
Subject: Re: [sqlite] .timer

On 14 Jun 2018, at 8:33am, x  wrote:

> Could someone describe what the return values real, user and sys mean and why 
> there’s sometimes a big difference between real and the sum of user & sys?.

[The following is simplified for clarity.]

'real' -- Elapsed time between the start and end of the command, as measured by 
the clock on your wall.  Sometimes called 'wall time'.

The other two figures both concern just the process you're interested in, 
ignoring the many other things the computer is doing at the same time like 
seeing if you've clicked your mouse, updating your screen, checking to see if 
your laptop battery is going to run out, etc..

'user' -- Processor time taken by the command itself.  If you look at all the 
source code for that command, this is the time taken to run that source code.

'sys' -- Processor time taken to execute the system calls the command used.  If 
the command used system calls to find the current time, allocate memory, and 
write some bytes to a file, the amount of time each system call took 
contributes to 'sys', not 'user'.

If 'sys' + 'user' > 'real', something weird happened.

If 'sys' + 'user' < 'real', your computer is busy doing a lot of stuff in the 
background.  You probably have a printer plugged in, an ethernet or WiFi 
connection active, a keyboard and mouse pointer being monitored, etc..  This is 
normal on a modern computer,

Simon.
___
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

Re: [sqlite] .timer

[Simon Slavin]

On 14 Jun 2018, at 8:33am, x  wrote:

> Could someone describe what the return values real, user and sys mean and why 
> there’s sometimes a big difference between real and the sum of user & sys?.

[The following is simplified for clarity.]

'real' -- Elapsed time between the start and end of the command, as measured by 
the clock on your wall.  Sometimes called 'wall time'.

The other two figures both concern just the process you're interested in, 
ignoring the many other things the computer is doing at the same time like 
seeing if you've clicked your mouse, updating your screen, checking to see if 
your laptop battery is going to run out, etc..

'user' -- Processor time taken by the command itself.  If you look at all the 
source code for that command, this is the time taken to run that source code.

'sys' -- Processor time taken to execute the system calls the command used.  If 
the command used system calls to find the current time, allocate memory, and 
write some bytes to a file, the amount of time each system call took 
contributes to 'sys', not 'user'.

If 'sys' + 'user' > 'real', something weird happened.

If 'sys' + 'user' < 'real', your computer is busy doing a lot of stuff in the 
background.  You probably have a printer plugged in, an ethernet or WiFi 
connection active, a keyboard and mouse pointer being monitored, etc..  This is 
normal on a modern computer,

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .timer

[x]

Could someone describe what the return values real, user and sys mean and why 
there’s sometimes a big difference between real and the sum of user & sys?.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Simon Slavin]

On 15 Feb 2017, at 7:22pm, Simon Slavin  wrote:

> sqlite> .changes on
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for help

Fix now committed and distributed to beta-testers.  The commands '.timer on' 
and '.timer off' once again work in the latest beta version of macOS.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Simon Slavin]

On 17 Feb 2017, at 9:09pm, Scott Perry  wrote:

> In the future it would be most appreciated if problems with Apple's version 
> of SQLite were reported by Radar , doubly so 
> for pre-release versions of operating systems where the issues can be fixed 
> before the final release.

Please see report 30544420.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Scott Perry]

On Feb 15, 2017, at 11:46 AM, Simon Slavin  wrote:
> On 15 Feb 2017, at 7:39pm, Richard Hipp  wrote:
>> Are you saying it is the one built into MacOS - not one you obtained
>> from https://www.sqlite.org/download.html?  If so, can you provide me
>> with details of what OS version you are running, please?
> 
> Answered privately because it’s a non-public release of macOS.  Sorry, folks, 
> shouldn’t have posted in public.

In the future it would be most appreciated if problems with Apple's version of 
SQLite were reported by Radar , doubly so for 
pre-release versions of operating systems where the issues can be fixed before 
the final release.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Simon Slavin]

On 15 Feb 2017, at 7:39pm, Richard Hipp  wrote:

> Are you saying it is the one built into MacOS - not one you obtained
> from https://www.sqlite.org/download.html?  If so, can you provide me
> with details of what OS version you are running, please?

Answered privately because it’s a non-public release of macOS.  Sorry, folks, 
shouldn’t have posted in public.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Richard Hipp]

On 2/15/17, Simon Slavin  wrote:
>
> On 15 Feb 2017, at 7:17pm, Ben Newberg  wrote:
>
>> Is that a homebrew version of 3.16.0?
>
> Not in any way.  It’s one which comes with a very recent version of macOS:

Are you saying it is the one built into MacOS - not one you obtained
from https://www.sqlite.org/download.html?  If so, can you provide me
with details of what OS version you are running, please?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Simon Slavin]

On 15 Feb 2017, at 7:17pm, Ben Newberg  wrote:

> Is that a homebrew version of 3.16.0?

Not in any way.  It’s one which comes with a very recent version of macOS:

180:Desktop simon$ which sqlite3
/usr/bin/sqlite3
180:Desktop simon$ uname -a
Darwin 180.192.187.81.in-addr.arpa 16.5.0 Darwin Kernel Version 16.5.0: Tue Jan 
31 18:57:20 PST 2017; root:xnu-3789.50.195.1.1~1/RELEASE_X86_64 x86_64

I am mystified why it doesn’t work.  I actually found the command in the .help 
display and copied and pasted it.  Tested to see whether it was the argument 
which was causing the problem.

sqlite> .changes on
sqlite> .timer on
Error: unknown command or invalid arguments:  "timer". Enter ".help" for help

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Ben Newberg]

Is that a homebrew version of 3.16.0?

SQLite version 3.16.0 2017-01-02 11:57:58
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>

3.15.1 was released on 2016-11-04, but it works on that version too.

On Wed, Feb 15, 2017 at 1:09 PM, Simon Slavin  wrote:

> simon$ sqlite3 ~/Desktop/fred.sql
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
> sqlite> .timer off
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
>
>
> It’s still there in the .help command.  What happened ?  Has it been fixed
> in a later version ?
>
> Simon.
> ___
> 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

Re: [sqlite] .timer command missing from CLI ?

[Richard Hipp]

On 2/15/17, Simon Slavin  wrote:
> simon$ sqlite3 ~/Desktop/fred.sql
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for

Unable to recreate the problem.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .timer command missing from CLI ?

[Simon Slavin]

simon$ sqlite3 ~/Desktop/fred.sql
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> .timer on
Error: unknown command or invalid arguments:  "timer". Enter ".help" for help
sqlite> .timer off
Error: unknown command or invalid arguments:  "timer". Enter ".help" for help


It’s still there in the .help command.  What happened ?  Has it been fixed in a 
later version ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] '.timer on' in the shell tool

[Hick Gunter]

Units are "CPU Seconds". "user" time is spent within user code, i.e. SQLite, 
"sys" time is spent within system calls, i.e. reading/writing files.

The balance between the times depends  on various parameters, including the 
state of the disc cache and the complexity of your INSERT...SELECT statements.

Performing complex calculations to arrive at field values will tend to increase 
user time. Performing complex joins will tend to increase sys time as more data 
needs to be read from file.

It is hard to tell without the schemata and the SQL involved. Look at the 
output from EXPLAIN and EXPLAIN QUERY PLAN to get an idea of the work involved.

-Ursprüngliche Nachricht-
Von: Simon Slavin [mailto:slav...@bigfraud.org]
Gesendet: Montag, 15. Dezember 2014 10:12
An: General Discussion of SQLite Database
Betreff: [sqlite] '.timer on' in the shell tool

Okay.  I used '.timer on' in the shell tool.  SQLite 3.7.13, if it matters.  
Here are two sample lines I got in response to different INSERT ... SELECT 
commands:

CPU Time: user 880.710398 sys 353.260288

CPU Time: user 5073.001124 sys 11609.266484

The two commands were issued one after another on a computer which was 
otherwise idle.

Question 1: What are the units ?

Question 2: I would have expected consistency in that user time was always 
greater than system time.  Or perhaps the other way around.  Why is a different 
one greater for the two examples ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] '.timer on' in the shell tool

[Paul]

Hi, Simon.

> Question 1: What are the units ?
> 
> Question 2: I would have expected consistency in that user time was always 
> greater than system time. Or perhaps the other way around. Why is a different 
> one greater for the two examples ?
> 

System time may be much greater if it involves a lot of tasks performed by OS.
For the sqlite part, it's probably reading data from database file.
Specifically it's lseek() and read() libc functions.

On the outher hand, I doubt that sqlite's part in that time value is big.
If I understand correctly the result of SELECT has returned a lot of data.
Specifically that data was writen as an output to your console.
If this is the case, a lot of system time was wasted just to write the results.
It may be because each written line calls fflush() on stdout.

Regards,
Paul
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] '.timer on' in the shell tool

[Dominique Devienne]

On Mon, Dec 15, 2014 at 10:24 AM, Simon Slavin  wrote:
>
> On 15 Dec 2014, at 9:20am, Donald Shepherd 
> wrote:
> > - Units are seconds.
> > - IIRC user time is time spent in SQLite code, sys time is time spent in
> > system (OS) calls.  Both can vary from run to run and (at least in my
> > testing) sys time tends to vary based off system usage.
> >
> > If you want the best "real" time results, a later version of the command
> > line also includes a "real" time that represents actual time elapsed but
> it
> > also has updated query planning IIRC.
>
> Okay.  That explains that.  What I really needed was wall time, which I
> guess this version doesn't have.  Not to worry.  I don't need to know it,
> it would just have been interesting.


One final note: User time can exceed wall time if parallelism is involved
(i.e. multi-threading), which I guess is now possible with the new
MT-sorter. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] '.timer on' in the shell tool

[Simon Slavin]

On 15 Dec 2014, at 9:20am, Donald Shepherd  wrote:

> - Units are seconds.
> - IIRC user time is time spent in SQLite code, sys time is time spent in
> system (OS) calls.  Both can vary from run to run and (at least in my
> testing) sys time tends to vary based off system usage.
> 
> If you want the best "real" time results, a later version of the command
> line also includes a "real" time that represents actual time elapsed but it
> also has updated query planning IIRC.

Okay.  That explains that.  What I really needed was wall time, which I guess 
this version doesn't have.  Not to worry.  I don't need to know it, it would 
just have been interesting.

Thanks, Donald.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] '.timer on' in the shell tool

[Donald Shepherd]

Let's see if I remember my notes from work ok at home:

- Units are seconds.
- IIRC user time is time spent in SQLite code, sys time is time spent in
system (OS) calls.  Both can vary from run to run and (at least in my
testing) sys time tends to vary based off system usage.

If you want the best "real" time results, a later version of the command
line also includes a "real" time that represents actual time elapsed but it
also has updated query planning IIRC.

On Mon Dec 15 2014 at 8:12:27 PM Simon Slavin  wrote:

> Okay.  I used '.timer on' in the shell tool.  SQLite 3.7.13, if it
> matters.  Here are two sample lines I got in response to different INSERT
> ... SELECT commands:
>
> CPU Time: user 880.710398 sys 353.260288
>
> CPU Time: user 5073.001124 sys 11609.266484
>
> The two commands were issued one after another on a computer which was
> otherwise idle.
>
> Question 1: What are the units ?
>
> Question 2: I would have expected consistency in that user time was always
> greater than system time.  Or perhaps the other way around.  Why is a
> different one greater for the two examples ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] '.timer on' in the shell tool

[Simon Slavin]

Okay.  I used '.timer on' in the shell tool.  SQLite 3.7.13, if it matters.  
Here are two sample lines I got in response to different INSERT ... SELECT 
commands:

CPU Time: user 880.710398 sys 353.260288

CPU Time: user 5073.001124 sys 11609.266484

The two commands were issued one after another on a computer which was 
otherwise idle.

Question 1: What are the units ?

Question 2: I would have expected consistency in that user time was always 
greater than system time.  Or perhaps the other way around.  Why is a different 
one greater for the two examples ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .timer with windows cli

[Bob Chapman]

Kim Boulton wrote:
> Is there a compiled v3.6.11 sqlite3.exe with the .timer facility
> enabled.

I don't think so for windows -- but I've been wrong many times before. ;)

[From shell.c]

#if !defined(_WIN32) && !defined(WIN32) && . . . .
#include 
#include 
. . . .
. . . .
#define BEGIN_TIMER beginTimer()
#define END_TIMER endTimer()
#define HAS_TIMER 1
#else
#define BEGIN_TIMER
#define END_TIMER
#define HAS_TIMER 0
#endif

Bob

--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .timer with windows cli

[Bob Chapman]

Kim Boulton 
wrote:
> Is there a compiled v3.6.11 sqlite3.exe 
with the .timer facility 
> enabled.
 
Not for windows (I 
believe) as follows:
 
[From 
shell.c]
 
#if !defined(_WIN32) 
&& !defined(WIN32) && . . .
#include 

#include 
. . 
.
. . 
.
#define BEGIN_TIMER 
beginTimer()
#define END_TIMER endTimer()
#define HAS_TIMER 
1
#else
#define BEGIN_TIMER
#define END_TIMER
#define HAS_TIMER 
0
#endif
 
But I've been wrong 
many times before. ;)
 
Bob
 
-- 

_
Windows Live™ Hotmail®…more than just e-mail. 
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_022009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer with windows cli

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kim Boulton wrote:
> However, how do you get the thing to compile with MinGW?

You tried way too hard :-)

Use the .tar.gz version of the amalgamation as it includes the shell
source code (the .zip doesn't).  Then this command works (no need to use
Make or batch files):

  gcc -O2 -o shell.exe shell.c sqlite3.c

You'll need to add -D flags or edit the source as appropriate.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkml5yUACgkQmOOfHg372QS0WQCgzI70kEvfQ4gUIY4v4Y9Z4Dcy
OxcAn3TJrrkZIGig/V4gg4X4UysMjhDf
=RDiY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer with windows cli

[Kim Boulton]

Roger,

Thanks for that.

However, how do you get the thing to compile with MinGW?
I Installed mingw and msys, fixed a problem with the msys batch file, 
fixed the paths to mingw so that gcc was found, did a compile and then a 
make but get linker failures at that point.

To get this far I referred the compile guidelines on sqlite.org although 
that info seems a little out of date.

I have MS VC++6 stored away somewhere although I'd rather get minGW 
working if that's the way it's normally compiled.

All this just to turn the timer function on, it might be wiser for the 
sqlite bods to release windows binaries which have the optional 
functions available. The timer would be a popular feature :-)

Cheers.

kim

Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Kim Boulton wrote:
>   
>> Anywhere I can download one would be nice.
>> 
>
> You can get the free MinGW compiler from http://www.mingw.org - it is
> what I use to compile SQLite and other code on Windows.  MinGW is a
> Windows port of gcc plus other GNU tools.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkmlFmcACgkQmOOfHg372QS+DACeNpfb7gF67XkSJGMQ749YHH+A
> CYcAoKlA/O112SUGYrY4LUTMuHq0xjN3
> =rnfb
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer with windows cli

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kim Boulton wrote:
> Anywhere I can download one would be nice.

You can get the free MinGW compiler from http://www.mingw.org - it is
what I use to compile SQLite and other code on Windows.  MinGW is a
Windows port of gcc plus other GNU tools.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmlFmcACgkQmOOfHg372QS+DACeNpfb7gF67XkSJGMQ749YHH+A
CYcAoKlA/O112SUGYrY4LUTMuHq0xjN3
=rnfb
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .timer with windows cli

[Kim Boulton]

Hello,

Is there a compiled v3.6.11 sqlite3.exe with the .timer facility 
enabled. I don't have a C compiler on this machine.

Anywhere I can download one would be nice.

Thanks

kim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] timer in shell.c part II

[Brannon King]

Thanks to Carl Clemens, I now have a working shell with a timer in it. I've
posted the shell.c on the contrib page along with a Windows exe compiled
using VC71. It is built on the latest shell.c version and should compile on
Linux as well, though I have not tested that. (My Linux box is down today.)
Use the .noprint option for doing time measurements. (The console slows
things down.) I assume it will require the NT kernel for the high
performance timer.

We've had some discussion here that VC71 actually compiles faster code. See
for yourself. It has all the compile optimizations on excluding SSE2 and
omit frame pointers.
 

__
Brannon King
¯¯

Re: [sqlite] timer in shell.c

[carl clemens]

Hi Brannon,

Try this:
SQLite version 3.3.6
Enter ".help" for instructions
sql3> .help
.databases List names and files of
attached databases
.dump ?TABLE? ...  Dump the database in an SQL
text format
.echo ON|OFF   Turn command echo on or off
.exit  Exit this program
.explain ON|OFFTurn output mode suitable for
EXPLAIN on or off.
.feedback ON|OFF   Print number of rows
selected/affected on or off.
.header(s) ON|OFF  Turn display of headers on or
off
.help  Show this message
.import FILE TABLE Import data from FILE into
TABLE
.indices TABLE Show names of all indices on
TABLE
.mode MODE ?TABLE? Set output mode where MODE is
one of:
 csv  Comma-separated
values
 column   Left-aligned
columns.  (See .width)
 html HTML  code
 insert   SQL insert
statements for TABLE
 line One value per line
 list Values delimited by
.separator string
 tabs Tab-separated values
 tcl  TCL list elements
.newline   Print a new line
.noprint ON|OFFNo output
.nullvalue STRING  Print STRING in place of NULL
values
.output FILENAME   Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit  Exit this program
.read FILENAME Execute SQL in FILENAME
.schema ?TABLE?Show the CREATE statements
.separator STRING  Change separator used by output
mode and .import
.show  Show the current values for
various settings
.tables ?PATTERN?  List names of tables matching a
LIKE pattern
.timeout MSTry opening locked tables for
MS milliseconds
.timing ON|OFF Turn elapsed time on or off
.width NUM NUM ... Set column widths for "column"
mode
sql3> 


changes are:

feedback
noprint
newline
timing


--- Brannon King <[EMAIL PROTECTED]> wrote:

> I'd like to modify the shell.c so that I get an
> "time taken" output with
> each query executed (similar to the mysql client).
> Where would I put the
> start and stop/print code in shell.c so that I
> capture the query time but
> not the time required to output the data to the
> console? Thanks.
> 
> __
> Brannon King
> ¯¯
> 
> 
> 

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] timer in shell.c

[Brannon King]

I'd like to modify the shell.c so that I get an "time taken" output with
each query executed (similar to the mysql client). Where would I put the
start and stop/print code in shell.c so that I capture the query time but
not the time required to output the data to the console? Thanks.

__
Brannon King
¯¯